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

Average of the value in one cell from multiple sheets?

I am not super familiar with number or excel so i hope this is clear.


I have a homemade budget document in Numbers with multiple sheets (two per month...or 1 for every pay cheque)


On every sheet the total I spent on food is in cell D31. I want to show the average $$ of the past year. I know i can add a function and manually add each and every instance of D31...but that is tedious and wont be automatically updated in the future.


Is there a way to have a function that looks at all the sheets in the doc and pulls that cell?


?


Thanks in advance!


Signed

Total Numbers Newbie.

Posted on Aug 26, 2015 4:38 PM

Reply
2 replies

Aug 26, 2015 11:07 PM in response to Eaglegrl

HI Eaglegrl,


Here's an example showing it is possible.

User uploaded file

There are three formulas on the Summary table.


The core of the formula pulling data from the six small tables to the corresponding cells of the Summary table is this:


=INDIRECT("'"&B$1&"'::B"&ROW())

This is the formula as it would look in cell B2 of Summary. INDIRECT constructs the cell address 'Jan-Feb'::B2 to tell Numbers to put the data from cell B2 on the table named Jan-Feb into this cell.


All is well until the formula gets to column G, where Indirect will construct the address 'Nov-Dec'::B2. That cell is empty, and Numbers will interpret that as a value of zero. If you are interested only in the Total for the year, that's not a problem. But if you are calculating the average, AVERAGE will include the zero in its calculations and return a result that is too low. To prevent that, the full formula adds a test that measures the length of the entry in the target cell. If an entry has been made, it will be at least one character long, and IF will return the value from the cell. If no entry has been made, its length will be zero, and IF will return a null string—a piece of 'text' that has zero length. AVERAGE will ignore text, and return the correct average.


Full formula:


B2: =IF(LEN(INDIRECT("'"&B$1&"'::B"&ROW()))>0,INDIRECT("'"&B$1&"'::B"&ROW()),"")


Filled down to B5; Filled right to column G


Total


H2: =SUM(B2:G2)


Filled down to H5


Average:


I2: =AVERAGE(B2:G2)


Filled down to I5 ('eye-five')


BUT


Just because it can be done doesn't make it the best way to accomplish what you want.


I would strongly suggest that you take a close look at the Personal Budget template supplied with Numbers 3, or the similar Checking Register template supplied with Numbers '09. These use a single table on which to record expenses as they occur, and one or more Summary tables to extract the category totals.


Also take a look at the suggestions in this discussion of a similar question.


Regards,

Barry

Aug 27, 2015 8:50 AM in response to Barry

Thanks for the assist. will give this a whirl.


I tried to use the personal budget template that comes with Numbers, but it didnt quite jive with method that my advisor has provided, and her method is working well! If only I could somehow marry her very simple spreadsheet and the reporting in the template!


Thanks again.


a

Average of the value in one cell from multiple sheets?

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