Hi Steve

the following array formula will give you the last value in the range that
is non Zero
{=LOOKUP(99^99,IF(B1:B31<0,B1:B31,""))}

Array formulae must be entered or edited using Control+Shift+Enter (CSE) not
just Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula. Do not type them yourself.

In your case, you want the range to be increasing as you move down so the
formula entered in say cell C2, would be

{=B2-LOOKUP(99^99,IF($B$1:$B1<0,$B$1:$B1,""))}
Copy down through C3:C31
--
Regards
Roger Govier



"steve" wrote in message
...
Hello again
Is there a function that will look up a column and then use the next cell
it
finds with a number greater than 0,
so if I have a column b1 through b31 with say 100 in b1 then I want to
subtract tomorrows number in b2 say 200 from b1 then subtract b3 from b4
and
so on.
but sometimes they forget to take the readings for the day and I have to
put in a zero for the cell, then when they do take there next reading I
have
to put the number in for that day and I get a minus and the number I just
entered. what they bring me is a log sheet that they use to record the
nimbers. hope this makes sense.
--
Thank You
Steve