# Formula challenge - difference from last entry

### 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 be 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 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 Bacon Bits blog.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.