Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Subtraction formula for consecutive cells in a column, skipping blanks
Column with Formulas (results of differences)
6 Blank 8 2 4 -4 Blank Blank 2 -2 19 17 2 -17 Blank Blank Blank Blank 4 2 I want a separate column (the right column) that has a standard formula that can go along side the left column that represents the differences between consecutive cells in this left vertical column. It should have the results noted on the right column. Do you know what formula that I could use as a standard, filled down through the second column that would get these variance results? Thank you, Dave |
#2
|
|||
|
|||
Hi!
Try this: Assume your data is in the range A1:An Enter this formula in cell B2 and copy down as needed: =IF(OR(A2="",COUNT(A$1:A2)<2),"",A2-LOOKUP(9.99999999999999E+307,A$1:A1)) If the first cell in the data list will always have an entry then you can use this: =IF(A2="","",A2-LOOKUP(9.99999999999999E+307,A$1:A1)) Biff wrote in message ups.com... Column with Formulas (results of differences) 6 Blank 8 2 4 -4 Blank Blank 2 -2 19 17 2 -17 Blank Blank Blank Blank 4 2 I want a separate column (the right column) that has a standard formula that can go along side the left column that represents the differences between consecutive cells in this left vertical column. It should have the results noted on the right column. Do you know what formula that I could use as a standard, filled down through the second column that would get these variance results? Thank you, Dave |
#3
|
|||
|
|||
Biff Rocks!!!
An amazing formula Biff. It really works. I have it subtracting weekly data and skipping any days, like Sundays, with zero data. Excellent. One column it was refering to was getting formulas from another worksheet, so when I used your formula, it calculated correctly, but caused an #VALUE! error for days that should be blank. THEN, I got the bright idea of modifying the values in your formula to point to the other worksheet just like the column it was refering to... Once I did this, no more #VALUE! errors. Beautiful. I really appreciate your quick and brilliant assistance with this matter. Cheers! Dave Biff wrote: Hi! Try this: Assume your data is in the range A1:An Enter this formula in cell B2 and copy down as needed: =IF(OR(A2="",COUNT(A$1:A2)<2),"",A2-LOOKUP(9.99999999999999E+307,A$1:A1)) If the first cell in the data list will always have an entry then you can use this: =IF(A2="","",A2-LOOKUP(9.99999999999999E+307,A$1:A1)) Biff wrote in message ups.com... Column with Formulas (results of differences) 6 Blank 8 2 4 -4 Blank Blank 2 -2 19 17 2 -17 Blank Blank Blank Blank 4 2 I want a separate column (the right column) that has a standard formula that can go along side the left column that represents the differences between consecutive cells in this left vertical column. It should have the results noted on the right column. Do you know what formula that I could use as a standard, filled down through the second column that would get these variance results? Thank you, Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
see the cells the formula relates to by double clicking the cell | Excel Discussion (Misc queries) | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
Macro or Formula to remove Text from Cells | Excel Worksheet Functions | |||
How do i select different cells to be use in a formula. | Excel Discussion (Misc queries) | |||
How do I set up a formula to repat 5 consecutive cells in a colum. | Excel Worksheet Functions |