Check if Blank, If so, Check the Next Cell

I need to write a tricky formula/code to do a slightly more advanced form of subtraction. Here's the scenario:

I have a long list of values, one value per day. I need to make a separate column that takes the value for the current day and subtracts the value from the day before. This is the easy part.

The difficult part is that not every day has a value (therefore the cell is blank). If I could write formula/code that checks if the value is blank and if so checks the cell above if it is blank until it finds the next value to use in the subtraction. If the cell itself is "~" then it returns "Null". Here's an example to help clarify

Value----------------Subtraction
--4
--7------------------------3
--12-----------------------5
--15-----------------------3------.
---~----------------------Null-----l----- Skips the null and subtracts the 15 from 20, resulting in 5
--20-----------------------5------'
--22-----------------------2------.
---~----------------------Null-----l
---~----------------------Null-----l-----Skips the 3 nulls and subtracts the 22 from 30, resulting in 8
---~----------------------Null-----l
--30-----------------------8------'

Hopefully there is a way around this. Any help is appreciated! Thanks a lot for reading

Watterboy,

Attached is an exammple workbook based on the criteria you provided. In Column A is the Value, Column B is the subtraction. Row 1 is headers, so data starts on row 2. Because there is no subtraction happening for the first value, cell B2 is blank. The subtraction formula starts in cell B3:
=IF(OR(A3={"","~"}),"Null",A3-LOOKUP(2,1/(ISNUMBER($A$2:A2)),$A$2:A2))

And is copied down. Is something like that what you were looking for?

Originally Posted by tigeravatar

Watterboy,

Attached is an exammple workbook based on the criteria you provided. In Column A is the Value, Column B is the subtraction. Row 1 is headers, so data starts on row 2. Because there is no subtraction happening for the first value, cell B2 is blank. The subtraction formula starts in cell B3:
=IF(OR(A3={"","~"}),"Null",A3-LOOKUP(2,1/(ISNUMBER($A$2:A2)),$A$2:A2))

And is copied down. Is something like that what you were looking for?

This seems to be exactly what im looking for! That is some awesome code work. Could you possibly explain how you used the LOOKUP command? I understand everything else that you did in the code, but Ive never used the LOOKUP command and I'm not understanding how it works or how you used it.

Your method is far more elegant and effective than how I did it. I used a series of IF statements that performed checks. It was only effective up to 5 cells.

Thanks again!

tigeravatar,

Good one. a small improvement. Since he is looking for a last number, you can use LOOKUP like, LOOKUP(9E300,A$2:A2)

=IF(OR(A3={"","~"}),"Null",A3-LOOKUP(9E300,A$2:A2))

Watterboy,

LOOKUP will look for a last number/text (dependent on lookup_value) <=lookup_value. Here you are looking for a number. 9E300 is a scientific notation of a large number. 9 with 300 zeros, which is a really really big number. So always looking for a value <=9E300.

At B3, this will look for a last number in A$2:A2, which is A2 = 2
At A7, this will look for a last number in A$2:A6, which is A5 = 15

A2 to down starts values.
B3 to down need to show the subtraction.

In B3, if you use LOOKUP(9E300,A$2:A2) this will look for a number in A2. when you copy down, say at A11 formula will become =LOOKUP(9E300,A$2:A10) (you have blocked row # 2 with $, A$2. When you copy down this will stay with row# 2). This will look for a LAST number in A2:A10. On the example it is on A8.

In C3, enter this formula & copy down.

=LOOKUP(9E300,A$2:A2)

you can see you always get the last number.