### 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.