Query on nested IF statements

[Greystone]

I think what I need is guidance on nested IF statements. I have a spreadsheet of daily closing values for stock indices, currencies and commodities from different countries stretching back years. Not every column's cells have values on the same dates/rows due to weekends and public hols, so some cells are blank. There's a maximum of 7 consecutive blank cells down each column.

I'd like columns showing daily differences and percentage changes from one row/day to the next. That's easy in itself but I'm hoping to avoid a heap of manual corrections in the case of blank cells with a formula that can handle; if B2 has a value but B3 is blank, subtract B2 from B4. If B4 is blank, subtract B2 from B5 etc. I've copied examples from related posts on IFs but can't get the nesting of the statements right.

An example is attached. Columns C and D show what I'm aiming at. Columns F and G show how far I've got with IF statements!
Any assistance would be much appreciated.

[Zizi64]

You can combine the statement OR() and the function ISBLANK() for the two (more) operands in the condition part of your formula.

[Villeroy]

it makes no sense to list dates for missing data.
if your list would be ordered by dates in ascending order, LOOKUP(this_date-1;$A$1:$B$999) returns the value in B of any date previous to the date in this row.

[Villeroy]

Sample2.ods

(19.46 KiB) Downloaded 75 times

[Greystone]

Thanks to both of you for your responses and for the sample lookup Villeroy. I have some learning to do. Regards.

[Villeroy]

The first part of my suggestion is simple: just keep it simple and enter the data you have rather than data you don't have.
The LOOKUP function is the same in all spreadsheets since Visicalc of 1979.
When used like this: =LOOKUP(number;$A$1:$X$99)
looks up the given number in the first column A1:A99 and returns the corresponding value in the matching row of X1:X99.
You can also pass the search vector and the return vector as separate arguments as long as they have the same size:
=LOOKUP(number ; $A$1:$A$99 ; $X$1:$X$99) which is handy if the 2 vecors are on separate sheets or when the search vector is right of the return vector.
LOOKUP requires that the search vector is sorted in ascending order. It scans the search vector for the search value until it either reaches the end or reaches a value that is bigger than the search value. In the first case it matches at the last position, in the second case it matches one position before the bigger value.
In a search vector 0 10 10 20 30, the search value 15 would match at the second 10 (3rd position) which is the value before the 20 which is bigger than 15. The function would return the value at the 3rd position of the return vector. Any search value bigger than 30 would match at last the position with value 30. Any value smaller than the first value 0 would return an error value #N/A.