 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007A running-balance column in a bank-tracking table
I'm making a table to keep track of deposits, credits, etc for a bank account, with a running balance. Here is the basic layout: Column b = date of entry Column c = credits Column d = deposits Column e = bank fees [ Column F & G = other info, not relevant here ] Column H = Running balance Each row/entry will be a single transaction. It may have the same date as another entry, because there might be several deposits, credits, bank fees on a given day. So for example, there might be 3 entries on 7/25/07. But for each entry, only 1 of C-D-E will have a number-value for a given entry. I'd like to have column H look at c, d, and e and add or subtract whichever one has info in it from the previous cell in Column H. I was thinking that I could make H have a formula that would say "Subtract the sum-total of C-D-E from the previous cell". But I can't figure out how to do this. Any relatively easy way to do this, or otherwise achieve my goal of a running balance that auto-corrects as I add/delete entries over time? > thinking that I could make H have a formula that would say "Subtract the > sum-total of C-D-E from the previous cell". With the headers in Row 1 enter in H2: =IF(COUNTA(B2:G2)>0,C2-SUM(D2:E2),"") In H3 enter: =IF(COUNTA(B3:G3)>0,H2+C3-D3-E3,"") and copy down. I am assuming that it may be that there is data in F or G but no entry in C, D or E If that is not he case then you can use COUNT() in place of COUNTA() as in =IF(COUNTA(B4:G4)>0,H3+C4-D4-E4,"") although COUNTA() is doing no harm. An alternative would be: =IF(COUNT(C2:E2)>0,SUM($C$2:C2)-SUM($D$2:E2),"") in H2 and copy down. If you make an error in entering say the Bank fees by typing 10,00 when your decimal symbol is a period, the first set if formulas with give you #VALUE! whereas the SUM() one will present an blank looking cell.  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > I'm making a table to keep track of deposits, credits, etc for a bank > account, with a running balance. Here is the basic layout: [quoted text clipped - 20 lines] > a > running balance that auto-corrects as I add/delete entries over time? > If that is not he case then you can use COUNT() in place of COUNTA() as in > > =IF(COUNTA(B4:G4)>0,H3+C4-D4-E4,"") So why did you use COUNTA() again Sandy? Because I copied the wrong formula from the sheet! should be: =IF(COUNT(B2:E2)>0,C2-SUM(D2:E2),"") Doh! SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >> thinking that I could make H have a formula that would say "Subtract the >> sum-total of C-D-E from the previous cell". [quoted text clipped - 50 lines] >> a >> running balance that auto-corrects as I add/delete entries over time? Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee <DeeDeeCee@discussions.microsoft.com>: > I'm making a table to keep track of deposits, credits, etc for a bank > account, with a running balance. Here is the basic layout: [quoted text clipped - 12 lines] > this. Any relatively easy way to do this, or otherwise achieve my goal of a > running balance that auto-corrects as I add/delete entries over time? I don't understand Sandy's solution because I can't see any need for a COUNT function. To me it seems pretty straightforward to put the starting balance in H2 and this in H3: =H2 + C2 + D2 - E2 and then click and drag that formula down column H. Incidentally, did you mean column C to be "credits" in the accounting sense, meaning that they would reduce the total of cash on hand? In that case the formula would be =H2 - C2 + D2 - E2 Signature"First prove what you're saying, then whine about it." -- /The People's Court/ Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ > I don't understand Sandy's solution because I can't see any need for > a COUNT function. The purpose of the COUNT() function is to check if there are any entries in that row and if not, return an empty string. Just makes it look better that a whole column of zeros when there are no entries in those rows. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee > <DeeDeeCee@discussions.microsoft.com>: [quoted text clipped - 29 lines] > that case the formula would be > =H2 - C2 + D2 - E2 Thank you Sandy. I think Stan's solution will be easier for me and I'll have to live with the less-appealing visual appearance of the table till I'm a little more advanced with the formula-making. But I'll copy this down in my Excel notes for use then. > > I don't understand Sandy's solution because I can't see any need for > > a COUNT function. [quoted text clipped - 36 lines] > > that case the formula would be > > =H2 - C2 + D2 - E2 You're welcome. It will save you asking later how you can hide all the zeros <g> SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Thank you Sandy. I think Stan's solution will be easier for me and I'll > have [quoted text clipped - 50 lines] >> > that case the formula would be >> > =H2 - C2 + D2 - E2 Thanks for the info; this will solve my problem. I'll adjust the formula to fit those numbers that need to be added and those that need to be subtracted. ddc > Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee > <DeeDeeCee@discussions.microsoft.com>: [quoted text clipped - 27 lines] > that case the formula would be > =H2 - C2 + D2 - E2 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.