Formula challenge - difference from last entry - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Monday, July 6, 2020

Formula challenge - difference from last entry

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:
Basic weight tracking sheet shows difference from previous entry
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:
Problems arise when a day is skipped
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 - find the last entry and use to calculate difference
Desired result - difference using last previous entry

Assumptions

  1. A single formula is entered in D6 and copied down (i.e. same formula in all cells)
  2. The formula must handle one or many previous blank entries
  3. Removing blank entries (rows) is not allowed
  4. 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.
Getting fancy - using a custom number format
HideAnswer (click to expand)
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.
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

No comments:

Post a Comment