I am looking for some help with a budget and check register file I have created. I would like to have a "Summary Dashboard" that keeps an updated total of each account running balance, such as checking, savings, CD's, etc. I have been using "Offset" in Google Sheets, but cannot seem to get it to work in numbers. I am running Big Sur 11.6 and Numbers 11.2
Hi Flynn
Here's an alternate to the OFFSET formula using INDEX. You may want to try it to check on calculation time needed to produce a new total when you make an entry. I suspect the difference will be small, but it may be enough to merit consideration of a change.
Table 1 represents your merc account table. The values in column J are for demo purposes only. Each is the Sum of the row numbers (minus 1) above it. The two darker cells in2 of Table 2, and has the same goal as the one in B2 of your Account Balances table.
The range notations (Table 1::J and Table 1::I ) are 'full column' references and, when used with COUNT or INDEX, includes ALL cells in the specified column. Since COUNT counts only cells containing numbers or quasi numeric values (such as durations and Date&Time values), the +1 is needed to add the header row—empty in the example, and containing the text value "balance" in your merc table)—which will not be counted by COUNT.
Regards,
Barry
Hi Flynn
Here's an alternate to the OFFSET formula using INDEX. You may want to try it to check on calculation time needed to produce a new total when you make an entry. I suspect the difference will be small, but it may be enough to merit consideration of a change.
Table 1 represents your merc account table. The values in column J are for demo purposes only. Each is the Sum of the row numbers (minus 1) above it. The two darker cells in2 of Table 2, and has the same goal as the one in B2 of your Account Balances table.
The range notations (Table 1::J and Table 1::I ) are 'full column' references and, when used with COUNT or INDEX, includes ALL cells in the specified column. Since COUNT counts only cells containing numbers or quasi numeric values (such as durations and Date&Time values), the +1 is needed to add the header row—empty in the example, and containing the text value "balance" in your merc table)—which will not be counted by COUNT.
Regards,
Barry
Hi Flynn Man,
Take a look at the use of SUMIF formulas in the Personal Budget template.
For your document, each account would be used in the same manner as the "categories" in the Summary by Categories table of this document.
Further questions? A screen shot of your Transactions and Summary tables would be useful. (A copy of the table, with entry values and account names edited t remove personal information would work, provided it contained sample data inplace of the 'real' data.
Regards,
Barry
Barry,
I have kept playing with the Offset Function, and finally got it to work. OFFSET(Merc::Table 1::J2,COUNT(Merc::Table 1::J2:J$5999)−1,0)
This what I used. Thank you for your assistance and interest in helping me.
Thank you again,
Flynnman
Barry,
I have attached 2 screenshots, one of my Checkbook Register, and one of my Monthly Dashboard where I want the running totals.
Thank you for the alternative method and your expertise. 👍
Numbers Spreadsheet Functions/Formulas
