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: 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
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 challenge? Here’s the same result, with a custom number format applied. What’s the number format? Hint: I swiped this from Mike Alexander on his now defunct Bacon Bits blog.
The mechanics of LOOKUP for this kind of problem are explained in this example. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
