I’ve sifted through the community but I can’t find what I am looking for. I have a simple spreadsheet with Date, Text, Income, Expense, and Total.
What I can’t figure out is how do I formulate it so when I enter an amount in income it will automatically add to the total, and when I enter an expense it will automatically subtract from the total??
nikki
MacBook Pro 15″, macOS 10.15
Hi Nikki,
Version 2 of Numbers (Numbers '09) included a Check (sic) Register template to keep track of deposits and expenditures in a chequing account.
Here's a revised version of the main table, constructed in a post '09 version. The original used a single column for deposits and withdrawals, and required entering withdrawals as negative amounts. Putting in and out amounts in separate columns (and revising the formula in the Balance column) allows all transactions to be entered as positive numbers.
The formula shown below the table is entered in cell G3, then filled down to the end of column G.
The formula has two parts—a core formula that calculates the current balance, and a 'switch' part that prevents the calculation until there is a value in that row of column F or G
IF((LEN(E3)+LEN(F3))<1,"",G$1+SUM(E$3:E3)−SUM(F$3:F3))
The part in normal weight type is the switch. LEN gets the LENgth of the entries in 'this row' of column E and adds the LENgth of the entry in 'ths row' of column G. If the result is less than 1, IF places a null string in its cell, and exits. IF either cell has an entry, IF clls the core formula (shown in bold) which gets the value from G1, adds the SUM of values in rows 3 to 'this row' of column E, and subtracts the SUM of values in rows 3 to 'this row' of column F to determine the current balance, and places that result in 'this row' of column G.
One issue I had with this template is its considering "Credit Card" as a category, The template also included a circle chart showing the percentage of each expense category as a portion of total expenses. For the expenses listed (taken from that template) the largest two were Home (37%) and Credit card (36%). In reality, the only expenses of the "Credit Card" category should be the annual fee (if there is one) and possibly, any interest or other late payment charges. Otherwise, "Credit card" is a Payment type (like "Cheque" or "Debit Card") that can be used for several categories of expense.
The Personal Budget template, which has been part of the Template Chooser list supplied with all versions of Numbers since v3, is similar to this earlier template. It does include a similar chart for showing expense categories,, but does not provide for entries of deposits or a means of tracking balances in a bank account.
Regards,
Barry
Hi Nikki,
Version 2 of Numbers (Numbers '09) included a Check (sic) Register template to keep track of deposits and expenditures in a chequing account.
Here's a revised version of the main table, constructed in a post '09 version. The original used a single column for deposits and withdrawals, and required entering withdrawals as negative amounts. Putting in and out amounts in separate columns (and revising the formula in the Balance column) allows all transactions to be entered as positive numbers.
The formula shown below the table is entered in cell G3, then filled down to the end of column G.
The formula has two parts—a core formula that calculates the current balance, and a 'switch' part that prevents the calculation until there is a value in that row of column F or G
IF((LEN(E3)+LEN(F3))<1,"",G$1+SUM(E$3:E3)−SUM(F$3:F3))
The part in normal weight type is the switch. LEN gets the LENgth of the entries in 'this row' of column E and adds the LENgth of the entry in 'ths row' of column G. If the result is less than 1, IF places a null string in its cell, and exits. IF either cell has an entry, IF clls the core formula (shown in bold) which gets the value from G1, adds the SUM of values in rows 3 to 'this row' of column E, and subtracts the SUM of values in rows 3 to 'this row' of column F to determine the current balance, and places that result in 'this row' of column G.
One issue I had with this template is its considering "Credit Card" as a category, The template also included a circle chart showing the percentage of each expense category as a portion of total expenses. For the expenses listed (taken from that template) the largest two were Home (37%) and Credit card (36%). In reality, the only expenses of the "Credit Card" category should be the annual fee (if there is one) and possibly, any interest or other late payment charges. Otherwise, "Credit card" is a Payment type (like "Cheque" or "Debit Card") that can be used for several categories of expense.
The Personal Budget template, which has been part of the Template Chooser list supplied with all versions of Numbers since v3, is similar to this earlier template. It does include a similar chart for showing expense categories,, but does not provide for entries of deposits or a means of tracking balances in a bank account.
Regards,
Barry
Have a look at this update (Dropbox download) of the old Numbers checking account template. It looks like this:
SG
Income/Expense Formula on Numbers
