Averages from different worksheets

[bzamalin@gmail.com]

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.

[tim m]

Your formula would look something like this :

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

"bzamalin@gmail.com" wrote:

> 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.
>
>

[bzamalin@gmail.com]

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.
bzamalin@gmail.com wrote:
> 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.

[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)

<bzamalin@gmail.com> wrote in message
news:1149349238.277255.124130@j55g2000cwa.googlegroups.com...
> 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.
>

[tim m]

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.

"bzamalin@gmail.com" wrote:

> 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.
> bzamalin@gmail.com wrote:
> > 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.
>
>