Excel - Formula to calculate difference between columns with blank cells -

i have excel sheet values similar table below.

------------------------------------- |   |  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 f1 should =(b1-a1)+(c1-b1)+(d1-c1)+(e1-d1)

however, cells blank. so, if cell blank, should take next cell.

eg; f1 should =(c1-a1)+(d1-c1)+(e1-d1)
, f2 should =(b2-a2)+(d2-b2)+(e2-d2)
, on...

is there formula automate this?

the formula:

= (b1-a1) + (c1-b1) + (d1-c1) + (e1-d1)

can written as:

= b1 - a1 + c1 - b1 + d1 - c1 + e1 - d1

or

= - a1 + (b1 - b1) + (c1 - c1) + (d1 - d1) + e1

where first , last values prevail other void themselves, leaving formula:

= - a1 + e1

so formula becomes last non-blank value minus first non-blank value.

try 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 further explanations on worksheet functions used:

aggregate function, index function.