Numbers formula for running total by name

JGQB9 wrote:

a column that cumulates all the "Out" columns by category, so it's a running total every time I enter the expenses("Out") for each week. So at any giving point I can see my current expenses by category.

Highly recommend considering alternatives to your very "Excel-like" table setup. I don't think you will be happy with it once you scale up and have many weeks of data.

Suggest having a look at this expanded Budget template (Dropbox download), based closely on the 'Personal Budget' template at File > New in your menu.

To adapt it to your needs, just change MONTH to WEEK in the EXPENSES BY... table and put week numbers in row 1 there instead of month numbers, and put week numbers instead of month numbers in column F of the TRANSACTIONS table (on the second sheet). Also change the category names in column A of the SUMMARY table to exactly match the category names you use in column E of the EXPENSES BY.., table (you change the categories there by changing the values in the Pop-Up Menu of E2, then copy that cell down the column).

The "running totals" you are looking for are in the 'Actual' column of the EXPENSES BY... table. As in the original template, that column will update automatically as you add transactions to the TRANSACTIONS table, as will the figures in the INCOME & EXPENSES TABLE. The charts will also update automatically.

You'll note the I just use negative numbers to denote income in the TRANSACTIONS table (on the theory that a negative expense is an inflow). You could set that up an extra column for income if you want, though that complicates life.

SG

Can you show a mockup of the table. 10 rows & 10 columns should be sufficient to get the general idea of its structure.

Include category names and the labels used at the top of the columns, and believable (but not necessarily 'real' income and expense numbers.

Regards,

Barry

Are you trying to do something like the 'Personal Budget' template at File > New in your menu? The categories are in column A of the Summary table, and subtotals for each category the Actual column of that table.

SG

A PS:

Laying the table out the way you have will work, but is more prone to accidental damage that using the model provided in the Personal Budget template recommended by SGIII.

Numbers tables can have over 60,000 rows (although they get quite sluggish long before reaching that limit), but only 255 columns. with each week taking three columns, you'll have less room than needed to keep 2 years records using the layout you've chosen.

Regards,

Barry

As noted above, I agree with SG's advice to look at and adopt the Personal Budget template model.

Following you original layout, you could add two header columns at the left side of your current table, and calculate the In and Out running totals there:

The formula shown below the table: SUMIF($1:$1,B$1,3:3)

is entered in B3.

Fill right into C3,then fill down to last row in the table.

The formula sums all values in 'this row' (row 3) in columns where the value in Row 1 matches the value in Row 1 of 'this column'.

Because columns A, B and C are header columns, the 'full row references ($1:$1 and 3:3) exclude these cells.

Regards,

Barry

Given the pattern you want to follow, the two formulas needed are pretty simple.

Start by setting up your table like the one below:

Your table should have one header column (A) and two header rows (1 and 2).

B1 is the only cell in column 1 to contain a formula:

B1: "Wk "&WEEKNUM(B2,1)

Note that the result is week number 47—true for either setting of the last argument, 1 (Sunday is 1) or 2 (Monday is 1)

B2 contains a Date and time value, with only the month and day number entered as shown.

Columns C and D will contain data, and should be left empty for the moment.

Column E contains three formulas:

E1: "Wk "&WEEKNUM(E2,1)

This can be entered as shown, or you can select cell B1 with one click, copy, then select E1 with one click and paste. Numbers will automatically change the column reference to match the new location.

E2: B2+7

This returns a date and time value 7 days after the date in B2, formatted for the same appearance as B2.

E3: B3+C3-D3

This gets the previous week's balance from column B, adds the income amount from column C and subtracts the expense amount from column D and returns the current balance.

Fill this formula down as many rows as you have categories.

Your table should now look very much like the image above.

I uses shorter labels in the income and expense columns to allow compacting the table for display here.

You can put the "Nov 17 thru Nov 23" label in C1 if you wish, but it will not update in the next step, and I suspect that editing it would take more time than entering it, so I left these cells empty. I notice also that I reversed the row locations for In and Out labels.

The data I've entered (5000 in cell B3) can be omitted at this point. I placed it there only to check that the formula in column E was working.

Select all cells in columns C, D and E as shown below by clicking on C1, holding the mouse button down and dragging to the last cell in column E. Note that the selection does NOT highlight the column reference tabs above the table with the deep blue that indicates a selection of the columns.

With the cells in those three columns selected, bring the mouse pointer close to the middle of the right boundary of the selection rectangle (between columns E and F. A Fill handle (small yellow filled circle) will appear in that double blue line, near its centre. Place the mouse pointer on the handle, then click and drag right to copy the selected cells into the rest of the columns to the right. The weekly dates (and Week numbers) and totals will appear in columns H, L and N, as dots while the mouse button is down, and as seen below when you reach the end of the table and release the mouse button.

.

Fill in the categories in column A, and the amounts in the in and out columns. Take care to not enter data in the columns showing balances, as doing so will replace the formula in that cell.

Regards,

Barry

PS: Income and expense amounts should all be entered as positive values.