Function for finding a cell with Anumber in it

[Guest]

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.

 

[Roger Govier]

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

 

[Guest]

Thank you I'll try and work with it as soon as I can, you are great helping
us out with this.
--
Thank You
Steve

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

 

[Guest]

In C2: =IF(B2>0,B2-B1,"")
copy down as far as needed