Hello,


When I insert rows into my table, the running balance formula doesn't properly adjust. Any help would be greatly appreciated.

I have a table for financial projections into which I input any credits or debits I'm expecting so I often need to add and delete rows.

=IF(AND(ISBLANK(C5),ISBLANK(D5)),"",SUM(E4,C5,-D5))

This formula just sums the running balance from the row above (E column) and the credit and debit from the same row (C and D column). The IF part of the formula just keeps the running balance column blank below the last credit or debit entry (when the credit and debit entries are both blank). This is not necessary so if I need to lose this function that's fine.

In the example above if I insert a row above row 5, the inserted row's formula adjusts correctly but the row below becomes

=IF(AND(ISBLANK(C6),ISBLANK(D6)),"",SUM(E4,C6,-D6))

The E4 cel reference should have become E5 (ie the row above). All the rows below row 6 adjust correctly. Only the row below the one inserted ever contains this error. Of course, I could just keep copying and pasting the formula back in every time but this gets tedious.

I'm sure plenty of you know what is going on. If you have time to enlighten me I'd be grateful.

I'm sorry, I don't know how to delete this first reply.

I had to replace this post with a better and I hope correct formula unless a better solution comes up.
Enter the following formula in:
E1 = C1-D1
E2 = SUM($C$1:$C2)-SUM($D$1:$D2), copy the formula down the rows in Col E.

Hi Marcia,
Thanks so much for that. I found this worked well. I found I could enter the full formula in row 1 and copy it down without the need for the first shorter formula.

Also, I found it worked with my added...

=IF(AND(ISBLANK(C4),ISBLANK(D4)),"",SUM($C4:$C$4)-SUM($D4:$D$4))

so that the balance column remains blank under the last entry. I'm fussy like that.

Now I just need to figure out why it works to deeper my learning a little bit!

Glad to be of help. This forum is full of very nice and very helpful people. You could shorten the formula a bit.
=IF(AND(C4="",D4=""),"",SUM($C$4:$C4)-SUM($D$4:$D4))