Subtract a value from the last non-blank cell in the same column

Question

JessA1965 Author

Level 1

10 points

Subtract a value from the last non-blank cell in the same column

I have a Numbers table with a date in column A and a Value (number) in column B.

In column C, I need a formula to subtract the value in B from the “last non-blank cell” value in column B, such as B8- B5, then B9 - B8 (see table below). The table will often have multiple blank cells between values entered into column B.

I found a similar question, but their values were chronological (always increasing). So, I could not get their recommended solution to work. In my case, the values in column B can increase or decrease with each entry.

Any help would be greatly appreciated.

MacBook Pro (2020 and later)

Posted on Aug 11, 2021 5:55 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,456 points

Aug 11, 2021 8:38 AM in response to JessA1965

C3 =IF(B3≠"",B3−XLOOKUP(REGEX("\d+"),B$1:B2,B$1:B2,"",2,−1),"")

Fill down from there to complete the column.

C2 will not have the formula.

You will need the custom format provided by SGIII to put the "+" onto the front of the positive numbers.

Reply

Answer 2

SGIII

Level 8

37,550 points

Aug 11, 2021 6:53 AM in response to JessA1965

One way is do add an extra column, which you can hide:

In C2, filled down the column:

=IF(B2<>"",B2,C1)

In D3, filled or copied down the column:

=IF(B3="","",C3−C2)

Then select the body cells in column D, and choose Custom format in the Data Format dropdown in the pane at the right:

And set up a rule like this (a + is typed in the beginning of the box under Rule 1):

SG

Reply

Subtract a value from the last non-blank cell in the same column

Similar questions