If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A 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? |
#3
|
|||
|
|||
A running-balance column in a bank-tracking table
"Sandy Mann" wrote in message
... 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! -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... 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. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "DeeDeeCee" wrote in message ... 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? |
#4
|
|||
|
|||
A running-balance column in a bank-tracking table
Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee
: 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 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? 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 -- "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/ |
#5
|
|||
|
|||
A running-balance column in a bank-tracking table
"Stan Brown" wrote in message
t... 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. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Stan Brown" wrote in message t... Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee : 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 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? 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 -- "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/ |
#6
|
|||
|
|||
A running-balance column in a bank-tracking table
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 "Stan Brown" wrote: Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee : 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 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? 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 -- "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/ |
#7
|
|||
|
|||
A running-balance column in a bank-tracking table
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. "Sandy Mann" wrote: "Stan Brown" wrote in message t... 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. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Stan Brown" wrote in message t... Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee : 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 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? 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 -- "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/ |
#8
|
|||
|
|||
A running-balance column in a bank-tracking table
You're welcome. It will save you asking later how you can hide all the
zeros g -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "DeeDeeCee" wrote in message ... 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. "Sandy Mann" wrote: "Stan Brown" wrote in message t... 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. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Stan Brown" wrote in message t... Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee : 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 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? 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 -- "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/ |
Thread Tools | |
Display Modes | |
|
|