Calculate difference between cells with possible gap

E

Espen Rostad

I have a WS monitoring use of water on a daily basis. In column A i have the
dates for a whole year, in column B I put in the amount of liters passed the
measuring device and in column C i have a formula that gives the amount of
water used since last reading. (Simply latest value - previous value)

My problem occurs when there comes a day when the counter has not been
checked and there is no input value. I Want to ceep all dates in Column A

How can I solve this?
 
E

Espen Rostad

=MAKSA(B8-B7;0)

MAKSA could be MAX in english version. This is to avoid getting the negative
results in cell below last registered (in column C). I need to avoid this
becaus I have summarys for week and month.

Mike H skrev:
 
M

Mike H

Hi,

Try this suitably altered to meet your regional syntax

=MAX(B2-LARGE($B$1:B2,2),0)

basically because the numbers (meter reading) should be in ascending order
you can find the second largest number which should be the previous reading
and use that.

Mike
 
E

Espen Rostad

If you could be so kind to explain what the different elements of that
formula refers to I might be able ti alter it to work for me. I have some
diffuculties getting the formula to work

Mike H skrev:
 
M

Mike H

Hi,

I have envisaged your data looking like this

col A Col B Col C
01/01/2009 123
02/01/2009 456 333
03/01/2009 789 333
04/01/2009 0
05/01/2009 999 210

My formula goes in C2 and is dragged down and will return results like above.

=MAX(B2-LARGE($B$1:B2,2),0)


Mike
 
Top