Averaging 14 Cells

[Darren]

Hello - I am trying to get the average of 14 cells on 14 different sheets,
i.e. cells C14 on worksheets 1-14. The problem I am running into is that
some of these cells may contain no data and so I am getting the #DIV/0!
error. Help...

[ufo_pilot]

=AVERAGE(Sheet1!A1,Sheet2!A1,Sheet3!A1) up to 30 cells in this manner
even if there is no data for say 4 out of 14 points, then the AVERAGE
function will still average the arithmetic mean out the 10 with data.


"Darren" wrote:

> Hello - I am trying to get the average of 14 cells on 14 different sheets,
> i.e. cells C14 on worksheets 1-14. The problem I am running into is that
> some of these cells may contain no data and so I am getting the #DIV/0!
> error. Help...

[Ron Rosenfeld]

On Wed, 25 Jan 2006 09:32:03 -0800, Darren <[email protected]>
wrote:

>Hello - I am trying to get the average of 14 cells on 14 different sheets,
>i.e. cells C14 on worksheets 1-14. The problem I am running into is that
>some of these cells may contain no data and so I am getting the #DIV/0!
>error. Help...

Post your formula and the contents of the cells with "no data".

The AVERAGE worksheet function ignores empty cells.
--ron

[Darren]

Right and that is what I thought, but the problem is that I have a formula in
those cells that gives me the error and so when I average, it is averaging
something wtih an error. Sorry I didn't add that in.

"ufo_pilot" wrote:

> =AVERAGE(Sheet1!A1,Sheet2!A1,Sheet3!A1) up to 30 cells in this manner
> even if there is no data for say 4 out of 14 points, then the AVERAGE
> function will still average the arithmetic mean out the 10 with data.
>
>
> "Darren" wrote:
>
> > Hello - I am trying to get the average of 14 cells on 14 different sheets,
> > i.e. cells C14 on worksheets 1-14. The problem I am running into is that
> > some of these cells may contain no data and so I am getting the #DIV/0!
> > error. Help...

[Jerry W. Lewis]

#DIV/0! means that either there is no numeric data in ANY of the cells, or
that at least one of those cells contains a #DIV/0! error.

If the former, use =IF(COUNT(data)=0,0,AVERAGE(data))
If the later, use =AVERAGE(IF(ISNUMBER(data),data)) array entered
(Ctrl-Shift-Enter)

Note that if a cell contains text digits, it is still text and will be
ignored by the AVERAGE function, even though it looks like a number.

As was noted previously AVERAGE(data) will ignore cells that have no data,
but if none of the cells have data, then you get 0/0 which will give you the
#DIV/0! error.

Depending on how the cells are laid out, there may be multiple ways to write
the reference.
=AVERAGE(Sheet1!C14,Sheet2!C14,Sheet3!C14,...,Sheet14!C14)
will work, and will ignore empty and non-numeric cells, but it is much
easier to write it as a 3-D formula
=AVERAGE(Sheet1:Sheet14!C14)

Jerry

"Darren" wrote:

> Hello - I am trying to get the average of 14 cells on 14 different sheets,
> i.e. cells C14 on worksheets 1-14. The problem I am running into is that
> some of these cells may contain no data and so I am getting the #DIV/0!
> error. Help...

[drvortex]

I tried this (sorta) for what I'm doing. I know some of the info above works when you are staying in the same workbook; however, I'm trying to do the same thing but my main formula is in another workbook. I'm trying to average up a series of cells but in a total of 30 different sheets.