Lookup & Reference
Calculate difference from last non-blank entry in a sparse log (TAKE & FILTER)
Subtract the previous numeric reading in source-row order, preserving gaps and numeric zero.
Compare recorded readings across blank rows
The previous reading means the last nonempty source row above the current row, not the maximum value or latest timestamp. Sort and validate the chronological order separately. The difference covers the full interval between observations, not necessarily one day.
Blank cells and empty-string formulas are skipped; numeric zero is a valid observation. Nonempty text is rejected instead of silently skipped. A first observation has no baseline, so the formula returns No prior reading. A negative difference is retained; investigate resets or corrections before interpreting it as consumption.
| Log Day (A) | Meter Reading (B) | Prior Non-Blank Found | Interval Delta |
|---|---|---|---|
| Day 1 (Row 2) | 100 | — (Baseline start) | "" (First entry) |
| Day 2 (Row 3) | "" (Skipped) | — | "" |
| Day 3 (Row 4) | 108 | 100 (from Row 2) | +8 |
| Day 4–5 (Rows 5–6) | "" (Skipped) | — | "" |
| Day 6 (Row 7) | 115 | 108 (from Row 4) | +7 (or -5 if Row 6 = 120) |
Difference between current reading and most recent non-blank entry above
Returns blank when the current row itself has no reading; otherwise subtracts TAKE(FILTER(B$2:B6, B$2:B6 <> ""), -1).
=IF(COUNTBLANK(B7)>0,"",IF(OR(COUNT(B7)<>1,COUNT(B$2:B6)+COUNTBLANK(B$2:B6)<>5),"Invalid inputs",IF(COUNT(B$2:B6)=0,"No prior reading",B7-TAKE(FILTER(B$2:B6,ISNUMBER(B$2:B6)),-1))))Retrieve the last non-blank value above by itself
Retrieve the prior recorded value without treating it as a new measurement. Carry-forward is an explicit modeling assumption, not evidence that the value remained constant during the gap.
=IF(COUNT(B$2:B6)+COUNTBLANK(B$2:B6)<>5,"Invalid inputs",IF(COUNT(B$2:B6)=0,"No prior reading",TAKE(FILTER(B$2:B6,ISNUMBER(B$2:B6)),-1)))Try the example
Use the original values or change them to test your understanding.