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
Bookmarks