Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 11-08-2018, 04:42 AM
JPollard JPollard is offline Windows 10 Office 2013
Novice
 
Join Date: Nov 2018
Posts: 2
JPollard is on a distinguished road
Default Running balance column formula not auto adjusting properly in row below when a row is 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 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.

Last edited by JPollard; 11-08-2018 at 05:39 AM. Reason: To correct my explanation.
Reply With Quote
  #2  
Old 11-08-2018, 05:45 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Advanced Beginner
 
Join Date: May 2018
Posts: 98
Marcia is on a distinguished road
Default

I'm sorry, I don't know how to delete this first reply.
Reply With Quote
  #3  
Old 11-08-2018, 06:27 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Advanced Beginner
 
Join Date: May 2018
Posts: 98
Marcia is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 11-08-2018, 08:52 AM
JPollard JPollard is offline Windows 10 Office 2013
Novice
 
Join Date: Nov 2018
Posts: 2
JPollard is on a distinguished road
Default

Quote:
Originally Posted by Marcia View Post
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!
Reply With Quote
  #5  
Old 11-08-2018, 02:32 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Advanced Beginner
 
Join Date: May 2018
Posts: 98
Marcia is on a distinguished road
Default

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))
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Keep balance column empty if no amount entered baes10 Excel 3 08-14-2018 06:31 AM
Adjusting width of one column cinci-hal Word 5 03-08-2018 10:01 PM
Word 10 Tables: Column to auto number 1.1, 1.2 etc in sequential rows FrauBaker12 Word Tables 7 06-24-2016 11:52 PM
Auto-adjusting Formula (by date) ShankedS Excel 2 12-03-2014 03:56 PM
Adjusting column widths norwood Word VBA 0 09-24-2013 06:53 AM


All times are GMT -7. The time now is 05:14 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft