Using Numbers to calculate running totals?
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I want to create a spreadsheet to keep track of the bank account I use to pay off my credit card bill. I want to be able to enter each transaction and as I do the amount left in the account will decrease and the amount owing on the card will increase so I have three columns (?) - the balance in my account (A) , the transaction amount (B) and the balance owing on my card (C). I can get the amounts to change on a one time basis but I wondered if there was a way to set it up so that each time I enter something in B, then A is decreased automatically and C is increased automatically? In addition, if poss. I want to be able to reset both column A (if more money gets paid into the account) and column C (to reflect my paying off my credit card bill each month)?
15 replies
Sign in or join Beachball Help to reply.
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I'm not sure why you'd want three columns for the calculations. Maybe I don't understand. Here is what I think you are talking about:
Debits are negative amounts; credits are positive. The balance formula can be entered in the first row (below the headers) and filled down. With this formula completely filled down, when the table grows, new formulas will be created automatically for the new rows.
- 0
Jonathan Myers ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks - this would work if I only wanted a transaction (or a deposit) to change the balance on one column but what I want it to do is to in effect have the opposite effect on two different columns i.e. when I enter a transaction, I want my credit card balance to increase but my bank account to decrease. In addition, I want to be able to zero the credit card balance once I pay off my monthly statement and also to increase my bank account balance when money is deposited in it.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
- 0
Jonathan Myers ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks too but maybe I am just not getting it because when I enter your formulae, I still only get an operation that works for one transaction but then not for any more?
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
OK... I think what you are asking for is possible, but we need to decide how to "code" the transactions, that is, how will we express whether an amount is: a deposit to the bank account, a transfer from banking toward the credit card balance, or a credit card expense. (You might also want to be able to express a transaction which is a withdrawal from the bank account, but I'll ignore that for now.) Here are
some possibilities.+Option 1+ uses the wording of the Transaction to determine what to do. "transfer" and "deposit" have special reserved meaning. All other transactions can be descriptive of the credit card charge. This option is terse but has rather complicated formulas and is rather inflexible.
+Option 2+ uses separate columns for credit card and bank account amounts. It is very straight forward and flexible but requires a double entry for transfers. Descriptions can be what ever you desire.
+Option 3+ uses an extra column that codes the transaction. It formulas are straight forward and flexible. All transactions can be descriptive and the codes are implemented in pop ups to prevent erroneous codes.
If you'd like additional details on any of these or have another coding scheme in mind, let me know.
- 0
pw1840 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Jonathan,
I think this is what you are looking for. Under 'Transactions', three types of statements appear. As it's been set up in the example below, four types of statements may appear.
1) The first is always the initial amounts for "My acct" and "Cred Card" (These are constants. No formulas are involved)
2) Payments from "My Acct" to "Cred Card" (The first 7 characters of this statement must be "Payment". This will reduce the amount in both 'My Acct" and "Cred Card".)
3) Deposits made to "My Acct" (The first 7 characters must be "Deposit" and may be followed with any additional information. The amount is added to "My Acct" but the "Cred Card" balance is unchanged.)
4) Any other rows not beginning with "Payment" or "Deposit" are assumed to be debit transactions to the "Cred Card" account. (This increases the "Cred Card" balance but leaves "My Acct" unchanged.)
I hope this is what you wanted. Formulas are shown below.
pw1840
- 0
Jonathan Myers ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks for the help - it is not exactly what I was thinking of but it is the closest and maybe I cannot get what I want - I want something that increases the credit card bill and decreases the bank account as each transaction is entered.
I do have one question about your reply too - I am not sure about the two formulae on your example - what is the difference and what is the relation between the one on the header row and the one in the yellow sticky? Thanks for your time.
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Jonathan Myers wrote:
Thanks for the help - it is not exactly what I was thinking of but it is the closest and maybe I cannot get what I want - I want something that increases the credit card bill and decreases the bank account as each transaction is entered.This is possible, but somewhat strange, since your bank account isn't really debited until you pay a credit card bill (the transfer operation). In both pw1840's and my model, the credit card payment (transfer) event is what decrements the bank account, not a credit card transaction.
I can see, though, where you might like it to look that way in order to not over spend your available funds with your credit card. You might like to have an additional column, your effective bank balance, which always displays the amount your bank account would have if you paid your credit card debit at that moment. This can easily be added to any of the solutions presented (pw1840's included) since it just the bank balance minus the credit card balance.
- 0
pw1840 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Jonathan,
Between my model and 5|=vv’s models and most recent response you have what you’re looking for. The “Cred Card Account Balance” column in my posting or “Credit Card” column in 5|=vv’s gives you the balance after each transaction. His/her suggestion of an additional column in a later post should satisfy your interest in knowing the balance after each transaction.
As to the question of the difference in the two formulas shown in my model, the similarity in their appearance may be confusing. The IF statements merely serve as a sort of sieve to separate the three possible transactions: payment (or transfer), deposit, and purchase. The appropriate actions to take place for each case are slightly different, but are described in notes 2, 3, and 4 preceding the model in my posting.
pw1840
- 0
Jonathan Myers ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I really appreciate both your answers and I have learned a lot from what you have suggested but I think I am a real novice at this and have some very basic questions. I am not sure if this needs to be a new thread but what I don't seem to be able to do is to get whatever I input into one cell to be reproduced in subsequent cells. To put it very basically, I understand that if you use the formula =D3-C4 on cell D4, you will get a subtraction, and conversely E3+C4 on cell E4, you will get an addition, but how do you get this to happen on cells D5, 6, 7 etc and E5,6,7 etc? I realize that this is probably very basic stuff but this is all completely new to me.
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
The cells in a table have, basically, one of three kinds of things in them: (1) nothing, (2) a formula, (3) a value. If you want something to appear automatically in a cell, a running total for instance, it must contain a formula.
When creating a solution like a running total of account balances, it is necessary to replicate a formula that works for one cell into adjacent cells. This is accomplished in Numbers in one of two ways: (1) the fill handle on a selection or (2) by enlarging a table (I won't discuss this initially to avoid confusion). Without getting too deep into the weeds, using one of these two methods will fill additional cells with a formula that is adjusted for its new cell's address.
So for a simple running total in column B of values in column A, you might have in B1's formula simply be =A1. This will copy the value from A1 into B1 to get the running total started. Then in B2, you'll have the formula =A2 + B1. This will provide the value for the second running total item and also provide the basis of all the subsequent running totals items. With cell B1 selected, you drag the little round handle in the corner of the selection down to fill in an adjusted formula into cells below. You can fill in as many cells as you need (and fill more later if you run out). If, now, you click on cell B3, you can see how fill adjusted the formula to be =A3 + B2. That is, B3 is the sum of the cell to the left and the cell above, just as was the case for B2.
This just scratches the surface of this topic, but I'll leave it at that for now.
- 0
Jonathan Myers ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I would be interested in how you enlarge a table if this means that the total will be adjusted automatically when new transactions are entered. I did try the autofill method but that means that all the cells 'ahead' of any new transactions are filled with the current balance and I want them to remain empty until a new calculation is performed. I wonder if my use of the phrase 'running total' has misled some people. Here is a screen shot of what I have got so far:

- 0
Jonathan Myers ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
There are two ways to go:
(1) Make the table larger than you have entries. Populate extra running total cells "ahead" but write the formula in such a way to avoid the unsightly running total display beyond your last transaction entered. To do this, modify the existing running total formula that you are current using and re-fill it down. As an example, if your current running total formula in E3 looked like:
= E2 + D3
the modification would be something like:
= IF(ISBLANK(D3),"", E2 + D3)
You can "wrap" this IF(ISBLANK( phrase around a more complicated formula you might have now. The principle is the same: the formula becomes visually inert if there is not an amount in D3.
(2) Make the table only as large as the number of transactions, growing the table as new transactions are entered. Here we avoid the both the unsightly extra running total and the unsightly extra cells all together. The difficulty with this is that, depending on the formula for running total, Numbers may or may not automatically populate a formula into the new cells that form as a consequence of the table growing. I can tell from your screen shot that the formulas you are currently using will
not automatically fill in as the table grows. If you don't mind filling them down with each new transaction, then nothing more is needed.If you'd like to have them auto-fill, they will have to be rewritten in a different way. The crux of this is that Numbers will not auto-fill unless all the cells (except header and footer, if any) in the column are "relatively similar" (my phase for which, unfortunately, I don't currently have a concise definition). For the running total type problem, the issue is the first cell that "kicks off" the running total. It does not contain a formula that is in any way similar to all the subsequent cells in the column, and, therefore Numbers will not auto-fill it. Eliminating the special case for the start of the running total requires thinking of the problem in a different way. Sometimes it may not be possible to express the solution without an initial special case cell.
Personally, when possible, I prefer (2). To me it leveraging unique advantages of tables in a way that is not possible in other "tableless" spreadsheets like Excel.
You might also step back and reconsider the necessity of a running total. Perhaps that isn't as important as just displaying the current balance. A running total may be a bit of an artifact from the bygone days of paper ledgers, anyway. If you can be convinced of that, then there are more straight forward solutions that allow you to enter transactions without further concern of formulas that will display the current balances.
- 0
Jonathan Myers ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks for the help to you and to everyone else who suggested solutions that I eventually worked out how to look at and learn from.
One last question - what do you mean by a current balance (as opposed to a running total)?





