how to divide by the number of cells as they change

Question

how to divide by the number of cells as they change

I have done some reading in the Numbers Help and on here, but I am not even sure how to ask to question in a concise manner. I am a total noob at this. Hey, I did get some ways down the road on setting up simple tables for my credit cards and budget.

Two Questions:

1 - I have built a credit card sheet for each credit card. Quite proud I got it all working, using some SUMs and such. Super minor, I know, but I was proud I got it going and my wife and I are now visualizing our finances in a much clearer method.

I just received a new statement, and I added in the expenses in the cells of a new column for that month, easy enough, done. On the right most column I have a sum of the cells per row. Easy enough, done. How do I get the SUM column that uses a simple SUM(C3:F3), for example, to then auto update the selection to include the new column? Instead of me typing in SUM(C3:G3) to reflect the new column, I have a hunch there is a way to have it auto select and include new columns as they are added. How is this done?

2 - I want the column next to summary/total column to be an average per month of the sum. Each column reflects one month. This is where I am moving beyond my ability to grasp at the moment. I know I can manually make an entry per cell to take a certain cell and divide it by 12, for example, for one year of data. Right now I have a formula for that as follows: D194/12. Simple for a manual static formula.

But the number of months will continue to grow. As I add in a new month, how would I set up a formula so that it will auto update the division to include the number of cells being divided back into the SUM of the cells?

Hope this makes sense, math is a weakness for me, so trying to figure out how to ask this question is not natural to me.

Thanks

iMac 27", macOS 10.14

Posted on Jul 9, 2019 9:53 AM

Reply

Answer 1

Barry

Level 9

60,023 points

Jul 9, 2019 12:28 PM in response to Texas_Man_Luvs_His_mac

"On the right most column I have a sum of the cells per row. Easy enough, done. How do I get the SUM column that uses a simple SUM(C3:F3), for example, to then auto update the selection to include the new column? "

The example above by Yellowbox takes advantage of a feature of Header Columns: Cells in Header Columns are excluded from full row references in some functions (Including SUM and AVERAGE).

Similarly, cells in Header Rows, and Footer Rows are excluded from full column references in those functions.

Unfortunately, Numbers does NOT support 'Footer Columns', meaning your choice of the rightmost column for your SUM calculations requires the range to be specified by the C3:F3 reference you've used.

One solution could be to immediately add enough columns to handle a full year's data, with December in column M.

Place your SUM formula, SUM(C3:M3) in cell N3.

Add column O, and place this formula in O3: AVERAGE(C3:M3)

AVERAGE

SUMs the amounts in all cells from C3 to M3 and
COUNTs the cells containing numeric values, then
divides the SUM by the COUNT.

Empty cells, and cells containing text are ignored by the count and by the sum, so with data entered for only January, February March and April, the average is correctly calculated as SUM/4.

Like Yellowbox, I'd recommend taking a look at the Personal Budget template, and considering a redesign of your spreadsheet to more closely follow that model.

In particular, I would encourage you to use a single table for input of all data, then a set of Summary tables to extract the data pertaining to each card or provide a Summary by category table as done in the PB template.

Regards,

Barry

Reply

Answer 2

Yellowbox

Level 7

21,915 points

Jul 9, 2019 10:45 AM in response to Texas_Man_Luvs_His_mac

Hi Texas_Man,

Welcome to Numbers for Mac!

I am not sure of the layout of your Numbers document, but to answer your question 1:

This is how to sum a whole row. The formula will automatically include other columns as you add them.

Column A is a Header Column.

Formula in A2 (and Fill Down) =SUM(2:2)

Add another column to that table:

A suggestion. Take a look at Template Chooser > Personal Finance > Personal Budget.

I am sure that other users will step in soon to explain that template better than I can.

Regards, and Happy Numbering!

Ian.

Reply

Answer 3

Jul 9, 2019 12:52 PM in response to Barry

Thank you both!

I used your suggestions, got the averaging to work now, and it is making more sense.

I originally started by using the budget template, but then got creative with my own thoughts. I am wondering if that customization might shoot me in the foot in the long run. I am going to go back now and rethink all this, study the original template, look at your recommendations, and give some thought before going forward.

Reply

how to divide by the number of cells as they change

Similar questions