I need a formula that states: If the number in A1 is larger than the number in C6, but smaller than the number in B6, select the number in D6. But, if the number in A1 is larger than the number in C5, but smaller than the number in B5, select the number in D5 and so on through Row 2.
A
B
C
D
1
#
2
0.4
10.0%
[Code] ....
I have the following data
A1 - 65.23
A2 - 1923.52
A3 - 945.18
A4 - 182.54
In column B
B1 =exp(A1)
B4 =exp(A4)
both are fine, however
B2 =exp(A2) - it is showing #NUM
B3 =exp(A3) - it is showing #NUM
Same case for all larger numbers
I'm having some problems sorting a list of numbers that some are larger than others, but it would sort it by how large the number is in regards to 123 or 45... 45 would go first instead of 123... I tried to change the column to numbers but it still won't work. as well as in the custom sort section. I'll try to upload a sample...
View 11 Replies View RelatedI have a table that receives the number sequence 1 and sequence number 0.
I need to calculate the highest sequence numbers that repeat in Secunia lines.
A
B
C
D
E
F
G
H
I
J
K
L
Result
1
1
1
0
0
1
0
1
0
1
1
0
3
[code].....
See for example the last line.
1 1 0 0 1 0 1 1 1 1 1 0 = 5
I have a sequence number 1, where it repeats 5 times segudamente.that is greater than other sequences.
Is there a way to select only negative #'s on a spreadsheet? I wasn't able to do it with "go to" ctrl-g.
I'm trying to obtain a formula that allows me to select the last 20 numbers in a range of numbers that continue to increase weekly. i.e column a3-a3000 increases by one number every week. I need the formula to sum the lowest 10 numbers of the last 20. If there is 30 numbers from a13-a33 week one. I require the formula to pick up the 10 lowest from the last 20 (a13-a33). Week two the cell increases by on to a34. The formula would then have to pick up the lowest 10 from the last 20 (a14-a44) and so on.
View 9 Replies View RelatedI am creating a spreadsheet to manage Block ID conflicts when adding mods to a Minecraft Server. Example spreadsheet is attached.
Description: Column A contains a list of available numbers (e.g. 1-20 and 31-50). Column B contains a list of numbers a new mod tries to use up by default (i.e. 1-25). A number in Column B that is not in Column A must be changed to the nearest number that is in Column A. In this example, numbers 21-25 in B are not available in A and must be changed to 31-35, respectively. Column C should be the result of changing Column B (i.e. 1-20 and 31-35).
Issue: The main problem is that as a given cell in Column B changes, that number needs to become unavailable in Column A for the next change. For example, when 21 in B returns 31, 31 can no longer be used for the next number in B (22).
I have been able to use VLOOKUP or INDEX/MATCH functions to lookup the numbers to be changed.
I built what I can only describe as a fantastic spreadsheet. It wasn't until after that I realized it is 123 MB in size, but even for the complexities within it, it still feels like that is quite large.
I can't share the file due to proprietary reasons, but any general tips as to things I can do to make it smaller (and therefore faster to run).
I tried saving it as a binary spreadsheet and that dropped it to about 80 MB, but I don't really know what that does and if it will impact the sheet performance in any way.
The jist of the problem is that employees are paid their normal wage up untill 37.5 hours of work. From 37.5 to 40 hours of work they are paid 1.5 times their wage. Any hours over 40 are paid at 2 times the regular wage.
So i need a formula that can work out their total pay. Im not sure what i could put in a dummy document to help you since it would just be one cell for their hourly wage, one for their hours worked and then one for the calculation. so you can reference any cell as an example and assume £6 as hourly rate
When grading children's test scores I want to apply letters and numerals to particular ranges eg between 21 and 25=3c 26and 30=3b. Please help with a formula.
View 14 Replies View RelatedI have some spreadsheets containing substantial rows of data, basically 3 columns as this:
Sample
Start
End
Difference (End-Start)
A
1500
1600
100
[Code] ......
And what i want to obtain is different rows of data such as every row has the same difference (20 in this case, without changing the first column), like the following (never mind the colors, they are only to make my point clear):
Sample
Start
End
Difference (End-Start)
A
1500
1520
20
[Code] ........
We can also think about splitting only one certain number into 20's, like in the case of samples B and C above, because I have already split the data into separate sheets based on their "difference" value, using link:
Copy to new worksheets. Do you think it is doable?
find a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received.
I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.
how (in VBA) to select a range within a column where all values are the same?
For example, given the following:
A B
1 ID Value
2 1 A
3 2 A
4 3 A
5 4 B
6 5 B
7 6 C
How would I select range(B2:B4) where all values = A?
I have a comparison macro that compares one sheet with the next, it is called sub comp1 (). There is a condition whereby columns must be removed before sub comp1 can run. To remove these columns I run the macro called sub colcut (). What I need is the following action:
If cell BJ4 of current worksheet = "RE COMMENT" then run sub colcut () followed by sub comp1 () otherwise only run sub comp1 (). FYI The macro is to be run manually via a button rather than triggered by the value of the cell.
I am trying to automatically select data from a different sheet to fill data in a cell based on two other cells. I've attached a simplified version of what I'm trying to do (with detailed explanations). I hope it has enough info to get my point across.
I posted this in the "programming" forum because I'm pretty sure I will need to use vba in some form or fashion to get this to work. I am more familiar with access than excel, so I may be missing some easier way to make excel do this.
I have rows of data, and the first number corresponds to the number of subsequent numbers that are relevant.
I want to paste that number of values into a second spreadsheet. How do I do this?
e.g.
A1 B1....
6 21 15 14 32 85 14 16 21 25 26 24
In this case I want to copy the 2nd-7th number, as the 6 tels me I want the next 6.
On sheet 1 in cell A2 I have a date (2/22/07). On sheet 2 I have 4 columns of data...column A has consecutive dates. I am looking for a code that will select the range that begins with the week beginning date of the date that was inputted on sheet 1 and the week ending date. For example, the code will select the range 2/19/07 to 2/25/07 (A20:D26).
View 6 Replies View RelatedI have a 30 in monitor (I know I'm lucky!) but when I open a new spreadsheet it opens all the way across the screen 100 columns and 100 rows. How can I change the size when it opens?
Is there a way of eliminating un-used rows & columns to bring your file size down.
I already know about eliminating unnecessary pages, and I've cleaned up defunct modules, etc., and anything else that's just clutter.
The Row & Column thing would be a big help.
If any cells in (Specified Range) are greater then (Number) then say true else say false. And how I would write it.
View 2 Replies View RelatedI have attached the data in an excel file here. I'd like to select (or highlight) the rows (based on names) in the table on sheet1 based on the list provided in sheet 2. I've been struggling with using a formula for this one. The list on sheet 2 may be longer than the table on sheet 1(since there are entries on the list which might not be present in the table column 2), and the reverse may also be true. I would like to select "entire rows" of names in the table that are there on the list on sheet 2, not just the names column in the table. See the desired result on sheet 3.
View 9 Replies View RelatedI am inserting a subtotal above my data set, the first cell that this subtotal takes place is "R1", then I have an unknown number of subtotals going right, depending on the number of months FCSTed by my sales managers.
Attached you will find a subset of the data and you will see that I need to select "S1 : iLastColumn(1)".
I have a worksheet which is created monthly by one of our company's employees which reports results for that months operations and successful audits. These audits are separated into various service lines and on the report which is submitted it takes on the following format:
1|Service Line|Data A|Data B|Etc.
2|___CR_______| Data | Data | Etc.
3|____________| Data | Data | Etc.
4|____________| Data | Data | Etc.
5|____________| Data | Data | Etc.
6|___DD_______| Data | Data | Etc.
7|____________| Data | Data | Etc.
8|___MS_______| Data | Data | Etc.
9|____________| Data | Data | Etc.
What I want to do is use a macro from another sheet that has the effect of: While ServiceLine = "CR" copy the row of that line and move it to my monthly summary workbook, sheet 1. Then when the Service line changes to DD have the macro copy that information to the monthly summary workbook sheet 2, and so on. The problem is, if I test the Service Line column each time it will be an empty string the majority of the time and I'm not sure how to combat that with my If, Do, For, and While statements. As I've thought about it there are two solutions that I can think of: someone knows of a way to test the row only if it has information in it, or if there is a way that I can copy the service line information down until the change in service line so I can test Service Line on each Row.
I have a user form with some option buttons (1,5,10,etc...) that are used so the user can select a given number of cells they want selected. My last option button is labled Other(optOther) and has a textbox next to it(txtOther).
What I am trying to do is give the user the option to either select one of the given numbers or be able to enter their own number. The data will then be extracted from one worksheet to another. I have all the coding for the optiong buttons with the given numbers, just cant figure the textbox one out.
My data starts on B5, so what i am trying to do is when the user enters number n, i would need data from B5:B(n+4).
I have a Lookup table with a Group and Subgroup field. I have created a range for the Group and also for the Subgroup. On my main page, I have created a list option which allows the user to select the items under Group (Day, Colour and Time).
What I want next to do is based on the primary selection for Group; I want it to filter it on the specific Subgroup. For Example,
The User selects Colour from Group, the Subgroup will subsequently only show the appropriate selection which is Green, White and Yellow, and no further selection.
I have a workbook with many tabs and I am writing a macro that works on the active sheet but needs to pick up data from another tab, which is labelled on the active sheet.
As I want to write this macro only once but different tabs have different tabs they look to, VBA code that slects the sheet named in a fixed cell on the active sheet.
This is the code that currently looks up to the tab labelled 'Cork, FA'. However, if this tab name is stored in cell AA2, I would like the code to choose the tab based on the information in AA2?
ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTA('Cork, FA'!C)"
CountClients = Selection
Selection.ClearContents
I have a workbook with 3 worksheets which contains a single spreadsheet where the user enters data and 2 worksheets containing all the named ranges and formulas(divided up by product model) used on the data entry worksheet. What I've been trying to accomplish is to choose one worksheet based on which product model I select on the data sheet.
View 1 Replies View RelatedI'm sure there is simple code for this but I have excel spreadsheets like the sample attached that are consistant in the types of fields but the only data I want to strip out are the rows where column A has a "G" in the cell, I tried using this
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
but this doesn't work when there is only one row of data. The number of rows with valid data will vary each day. I then tried an if, then statement and a loop but I could never get the loop to stop. I basically want a macro which parses through the worksheet and selects the rows that have that "G" in column A so I can copy them and paste them in another worksheet.
I have a DDE feed going into an Excel spreadsheet which gives me a share price in real-time. i.e the cell value is constantly changing.
I would like to create a formula that tells me if the price is 'Rising' or 'Falling' based on the previous value, before it was updated.
So, for example, if cell C4 said £1.00 and the the next price change changed cell C4 to £1.05 I would like cell C5 to say 'Rising' If the next price change was say to £1.04 I would like cell C5 to say 'Falling'.
I have this table with some information that I need to work with. The data is simple, but it has many issues, at least for me, to be able to work with it properly.
Basically, I just need to transfer part of the data from this table to another table (in another order). I failed to use vlookup because I need more than one parameter to search the data (Date, Shift and Sector).
The problem I am having to get the codes from the source table is that the same code sometimes shows up more than once per date, per shift and per sector. It's also not following a numeric pattern. I couldn't elaborate any combination of formulas to get the info I need....
I also can't change the format of the source table, because I'm not the one who made it, it has years already, and is constantly updated day by day by a good old man who doesn't know much about computers.
The file is attached : Example.xlsx‎