Averages from different worksheets

[bzamalin]

Hello:

I want to calculate an average based upon the following scenario:

I have an Excel workbook with 5 individual worksheets

In each worksheet I have a number located in D28.

I want to calculate the average of these 5 numbers located in these
different worksheet in my workbook. (in this example the numbers are
located in D28 in each worksheet, but they may not be in the same spot
in the future)

I can calculate averages with no problem if the numbers are in the same
worksheet, but I am having problems calculating the average if the
numbers are in different worksheets.

What is the best way to handle this? Thanks for your time.

 

[Guest]

Your formula would look something like this :

=AVERAGE(Sheet3!D28,Sheet2!D28,Sheet1!D28)

 

[bzamalin]

When you say sheet 1, 2, 3, etc. am I supposed to use the actual name
I have given the sheet or literally use sheet 1, sheet2, sheet 3, etc.

 

[Bob Phillips]

Assuming the sheets are called Sheet1, Sheet2, etc., put D28 in a cell, say
B1, and use

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(1:5)&"'!"&B1),"<>"))/
SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(1:5)&"'!"&B1),"<>"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Sorry, poor expalnation on my part. Yes you would use the actual names of
your sheets. The easiest way to do this is to use the 'insert function'
button ('fx') then choose the Average function and it will walk you through
the function and you can choose the cells to average by clicking on them and
it will build the function formula for you.