Formula to show amount after each expense

Question

Level 1

23 points

Formula to show amount after each expense

I don't know if my title makes sense, but what I need to do is create one formula to subtract the dollar amount in column D from the previous amount in column E. I have attached a screen shot. I have over 200 rows if not more to do and I don't want to create a new formula in every single cell 200 times. Right now added a formula in cell E5 (SUM E3-D5), then in E6 (SUM E5-D6), and then in (SUM E6-D7). If I have to type out a formula for each total cell, it will take forever to do this spreadsheet. Is there an easy simple formula that will do what I need?

If it helps, this is an expense report starting with a total and then subtracting each amount from the total but showing the total after each expense.

iMac 27″, macOS 14.6

Posted on Feb 22, 2025 8:06 AM

Reply

Answer 1

Top-ranking reply

Dubbia

Level 1

43 points

Feb 22, 2025 10:27 AM in response to mike719

If what you need is to have the last value of column E always updated dynamically deduting the amount of column D you can enter this formula in cell E4

=E3-D4

Then drag it to the end of the column.

Every time you enter an amount in D it will return you the total updated in E

Reply

Answer 2

SGIII

Level 8

37,550 points

Feb 22, 2025 9:23 PM in response to mike719

You can also use an "expanding range", something like this:

In E4, filled down:

=E$3−SUM(D$3:D4)

The $ in D$3:D4 is an "anchor", so the range expands as you fill the formula down.

The advantage of this is that no formula adjustments are needed if you insert or delete rows "in the middle" of your list, or you sort.

SG

Reply

Formula to show amount after each expense

Similar questions