You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Income/Expense Formula on Numbers

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

Posted on Nov 4, 2020 11:43 AM

Reply
Question marked as Top-ranking reply

Posted on Nov 4, 2020 1:11 PM

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

2 replies
Question marked as Top-ranking reply

Nov 4, 2020 1:11 PM in response to junkie.nikki

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

Income/Expense Formula on Numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.