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

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

On Wed, 25 Jan 2006 09:32:03 -0800, 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...

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

The AVERAGE worksheet function ignores empty cells.
--ron

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

#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,...,Shee t14!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...

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.


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=504992