How to subtract the last non-empty cell in Numbers?

Hi JB,

Restating your specifications:

Readings are recorded chronologically in column B.

Some rows will not contain a reading.

The reading shown furthest down the table will be the maximum value in column B.

When a new reading is added in a row below it, the most revent previous reading will be the previous maximum, and the new reding will become the maximum.

If there is a reading recorded on 'this row', you want to subtract the previous maximum from the current reading, and place the result in 'this row' of column C.

If there is no reading recorded on 'this row', you want the cell in 'this row of column C to appear empty.

Here's an example that should provide the result you are looking for:

The formula shown is entered in C3, then filled down to the end of column C.

C3: IF(LEN(B3)<1,"",MAX(B$2:B3)−MAX(B$2:B2))

The part shown in bold does the calculation, subtracting the maximum value in cells above 'this row' from the current maximum value (in 'this row').

The part in normal type weight checks for an entry in 'this row' of column B, and prevents the calculation if an entry is not found.

Regards,

Barry

Not too sure of what your data looks like, but since your working with metered data (i.e. the number will always go higher, not lower), you could just grab the maximum number. Not sure what your formula is really doing with a screenshot, but the equation might look something like this: =b11-Max(b)

Jason

that's what I was trying to figure out with your question. I didn't understand the reason to do it, so I just answered the max.

You state in your title you want to subtract the last non-empty cell, which is the dates increase going down on the table, the last non empty is always the max.

in your most recent response you state "I will subtract the new reading from the Jan 1 number." but that will give you a negative reading. If you subtract the original reading from the max you get current use.

So if you wanted to see use as of that date you wouldn't need to find the max, that lines number was the max that day. Just use absolute referencing to lock the initial value.

So if B1 had the original value for the month, and you are on any cell beneath that with a value for some date, you would enter this as a formula

=B2-B$1

this could be filled down and it will subtract the value in C1 from the current rows value.

Jason