Apple Event: May 7th at 7 am PT
Does anyone use Numbers to keep track their checkbook. How do you set it up to have a constant sum. In other words so you know exactly what amount of money you have in your account.
MacBook Pro 15″, macOS 11.1
Numbers '09 had a Checking Register template that created this pair of tables and a chart tracking expenditures:
The biggest error I noticed was the assigning of "Credit Card" as an expense category, while "debit card was correctly named as a 'method of payment, used, like a Credit card' for payments in several categories. The only expenses directly related to using a credit card are the interest charges on amounts charged and not paid by the due date and the annual fee, if this is a card that has one. Other than those, every payment made to a credit card account should be assigned to the category of whatever was bought in that particular transaction. Unfortunately, doing that complicates the calculations (and the data entry), which is likely the reason it was omitted from this template.
Transactions table as shown:
Rows 1 and 2 are Header rows. All values are entered directly, including the Beginning Balance in F1.
Row 3:
Columns A, B and C contain data directly from the keyboard.
Column D entries are chosen from a pop-up menu in each row. The menu is most easily created by listing all the categories in column A of the smaller table, then selecting the cells containing those categories (A2-A6 of Account Categories) and changing their Data format from Automatic to Popup menu, then setting the menus to "start with blank," an option that was not available in Numbers '09.
After creatin the popup menu cells, temporarily set the first one to 'none', then Copy the cell. Then select all of the cells in column D of Transactions and Paste the copied menu into all cells in column D.
Column E: Format as Currrency (or as Number if you don't need the currency sign showing) with two places after the decimal. All values in this column are entered directly (and without the $ sign, which is applied by setting the Currency format for the cells). The position of the currency sign, at the left end of its cell, is accomplished by choosing "Accounting style" in the inspector. The parentheses around the cost items are the style set for displaying negative numbers—the numbers are entered with a minus sign in front of payments and nothing in front of Deposit amounts.
Column F: The data format for cells in this column is the same as for cells in column E—currency in Accounting style, with negative values shown enclosed in parentheses.
F3 contains this formula: =$F$1+SUM(OFFSET(F3,3-ROW(),-1,ROW()-2))
The formula is filled down to the end of column F, and expands as more rows are added to the table.
Account Categories table: as shown.
B2 contains the formula: =SUMIF(Transactions :: $D,A2,Transactions :: E)
Fill down to the last row containing a category name in column A.
Row 7 is a Footer row.
B7 contains the formula: =SUM(B)
The B references all cells of column B except those in the header row and the footer row,
If row 7 is not defined as a Footer row, the formula will throw a self-reference error.
The Personal Budget template in versions of Numbers after Numbers '09 was derived from the Checking Register template provided in the earlier version, and will give you the basic layout of the two tables, but not the formulas from the Register.
Further information regarding the formulas is available in the Function Browser, which opens when you type an = sign in any cell, indicating you are going to enter a formula.
Regards,
Barry
Numbers '09 had a Checking Register template that created this pair of tables and a chart tracking expenditures:
The biggest error I noticed was the assigning of "Credit Card" as an expense category, while "debit card was correctly named as a 'method of payment, used, like a Credit card' for payments in several categories. The only expenses directly related to using a credit card are the interest charges on amounts charged and not paid by the due date and the annual fee, if this is a card that has one. Other than those, every payment made to a credit card account should be assigned to the category of whatever was bought in that particular transaction. Unfortunately, doing that complicates the calculations (and the data entry), which is likely the reason it was omitted from this template.
Transactions table as shown:
Rows 1 and 2 are Header rows. All values are entered directly, including the Beginning Balance in F1.
Row 3:
Columns A, B and C contain data directly from the keyboard.
Column D entries are chosen from a pop-up menu in each row. The menu is most easily created by listing all the categories in column A of the smaller table, then selecting the cells containing those categories (A2-A6 of Account Categories) and changing their Data format from Automatic to Popup menu, then setting the menus to "start with blank," an option that was not available in Numbers '09.
After creatin the popup menu cells, temporarily set the first one to 'none', then Copy the cell. Then select all of the cells in column D of Transactions and Paste the copied menu into all cells in column D.
Column E: Format as Currrency (or as Number if you don't need the currency sign showing) with two places after the decimal. All values in this column are entered directly (and without the $ sign, which is applied by setting the Currency format for the cells). The position of the currency sign, at the left end of its cell, is accomplished by choosing "Accounting style" in the inspector. The parentheses around the cost items are the style set for displaying negative numbers—the numbers are entered with a minus sign in front of payments and nothing in front of Deposit amounts.
Column F: The data format for cells in this column is the same as for cells in column E—currency in Accounting style, with negative values shown enclosed in parentheses.
F3 contains this formula: =$F$1+SUM(OFFSET(F3,3-ROW(),-1,ROW()-2))
The formula is filled down to the end of column F, and expands as more rows are added to the table.
Account Categories table: as shown.
B2 contains the formula: =SUMIF(Transactions :: $D,A2,Transactions :: E)
Fill down to the last row containing a category name in column A.
Row 7 is a Footer row.
B7 contains the formula: =SUM(B)
The B references all cells of column B except those in the header row and the footer row,
If row 7 is not defined as a Footer row, the formula will throw a self-reference error.
The Personal Budget template in versions of Numbers after Numbers '09 was derived from the Checking Register template provided in the earlier version, and will give you the basic layout of the two tables, but not the formulas from the Register.
Further information regarding the formulas is available in the Function Browser, which opens when you type an = sign in any cell, indicating you are going to enter a formula.
Regards,
Barry
Shirley2034 wrote:
have to be a mathematician to figure it out.
You may find helpful this checking register document (Dropbox download) that I put together a while back. It includes the old Numbers template, and a modernized version. It should be self-explanatory and pretty much ready to run right out of the box, but post any questions if you run into problems.
SG
Thank you Barry. It seems I have to be a mathematician to figure it out. I will try. If I do it step by step I may be able to understand what is happening. Thank you again.
Does anyone use Numbers to keep track their checkbook.
