Excel - Formula to calculate difference between columns with blank cells

0
votes

I have an Excel sheet with values similar to the table below.

-------------------------------------
|  A  |  B  |  C  |  D  |  E  |  F  |
-------------------------------------
|   95|     |   98|   96|   95|     |
-------------------------------------
|   96|   95|     |   92|   91|     |
-------------------------------------
|   93|     |   92|   98|   94|     |
-------------------------------------
|   92|   98|     |   95|   92|     |
-------------------------------------
|   95|     |   99|   92|   98|     |
-------------------------------------

The formula for F1 should be =(B1-A1)+(C1-B1)+(D1-C1)+(E1-D1)

However, some cells are blank. So, if the cell is blank, it should take the next cell.

eg; F1 should be =(C1-A1)+(D1-C1)+(E1-D1)
and F2 should be =(B2-A2)+(D2-B2)+(E2-D2)
and so on...

Is there a formula to automate this?

1
excelexcel-formula
Is it possible for A1 and E1 also to be blank? For the all the examples you've shown, the answer is just E1-A1 because the cells in between cancel out. – Tom Sharpe
Yes. Any cell can be blank. The cell in F can be 0 or negative or positive. – user1777929
So, to be clear, your third row should resolve to -186, and not -93, correct? – BruceWayne

1 Answers

3
votes

The formula:

= (B1-A1) + (C1-B1) + (D1-C1) + (E1-D1)

can also be written as:

= B1 - A1 + C1 - B1 + D1 - C1 + E1 - D1

or

= - A1 + (B1 - B1) + (C1 - C1) + (D1 - D1) + E1

where only the first and last values prevail as all other void themselves, thus leaving this formula:

= - A1 + E1

So the formula then becomes the last non-blank value minus the first non-blank value.

Try this formula:

= INDEX( $A1:$E1, 0, AGGREGATE( 14, 6, COLUMN(1:1) / ( $A1:$E1 <> "" ), 1 ))
- INDEX( $A1:$E1, 0, AGGREGATE( 15, 6, COLUMN(1:1) / ( $A1:$E1 <> "") ,1 ))

See these pages for further explanations on the Worksheet Functions used:

AGGREGATE function, INDEX function.