The context
A couple weeks ago, I had an interesting question from a reader about tracking weight gain or loss in a simple table.
The idea is to enter a new weight each day, and calculate the difference from the previous day. When every day has an entry, the formula is straightforward:
The difference is calculated with a formula like this, entered in D6, and copied down the table:
=IF(C6<>"",C6-C5,"")
However, when one or more days are missed, things go awry, and the calculated result doesn't make sense:
No, you did not gain 157 pounds in one day
The problem is the formula uses the blank cell in the calculation, which evaluates to zero. What we need is a way to locate and use the last weight recorded in column C.
The challenge
What formula will calculate a difference from the last entry, even when days have been skipped?
Desired result - difference using last previous entry
Assumptions
- A single formula is entered in D6 and copied down (i.e. same formula in all cells)
- The formula must handle one or many previous blank entries
- Removing blank entries (rows) is not allowed
- No helper columns allowed
Note: one obvious path is to use a Nested IF formula. I would discourage this, since it won't scale well to handle an unknown number of consecutive blank entries.
Got a solution? Leave a comment with your proposed formula below.
I hacked together a formula myself, and I'll share my solution after I give the smart readers of Exceljet some time to submit their own formulas.
Extra credit
Looking for more of a challenge? Here's the same result, with a custom number format applied. What's the number format? I swiped this from Mike Alexander on his now-defunct Bacon Bits blog.
There are really good proposed solutions below, including a very compact and elegant solution by Panagiotis Stathopoulos. For the record, I went with a LOOKUP and an expanding range:
=IF(C6<>"",C6-LOOKUP(2,1/($C$5:C5<>""),$C$5:C5),"")
The mechanics of LOOKUP for this kind of problem are explained in this example.
