Take number from cell above a gap
 

Does anyone know a function to take the number from the first cell above a
gap of cells. What I've got: a date column, depth column, elevation column,
change column. Not every date has a corresponding depth/elevation/change.
I've got it set up now that the change in elevation column takes the current
elevation and subracts it from the previous elevation. The current equation
is just C15-C14. If there is a gap in the data, say 9 days, how can I get
Excel to automatically take C15-(whatever the next cell with data in it above
C15)? Thanks.

Take number from cell above a gap
 

Try

=C15-LOOKUP(9.99999999999999E+307,C$1:C14)

"GB" wrote:

Does anyone know a function to take the number from the first cell above a
gap of cells. What I've got: a date column, depth column, elevation column,
change column. Not every date has a corresponding depth/elevation/change.
I've got it set up now that the change in elevation column takes the current
elevation and subracts it from the previous elevation. The current equation
is just C15-C14. If there is a gap in the data, say 9 days, how can I get
Excel to automatically take C15-(whatever the next cell with data in it above
C15)? Thanks.