Running balance column formula not auto adjusting properly when rows are inserted.

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 be 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.

Following lack of answers here I've also posted this Q in msofficeforums and Microsoft Answers. I can't link to them as I haven't posted enough in this forum to be able to include links.

Hi, welcome to the forum

The simplest way to resolve this - dont insert new rows, just add the data to the end.

If you MUST insert new rows, the quickest way around this, is to just copy the formula from above (the correct 1) and paste all the way down