Surely the answer is to avoid getting the #DIv/0 in the first place by
putting a test in D20 etc to cater for the fact there is no data: use
something like

If(b1=0,"",a1/b1)


Setting the cell to null or blank (as opposed to 0) means average will
ignore the blank cells in its calculation.

HTH

"durerca" wrote:


I need help using ISERROR to ignore Div/0! values in a list of cells I'm
trying to average. My problem is that when I use ISERROR to replace the
Div/0! with "" I now get a #Value! error instead. Here's the steps I've
been following:

I have times for each week that I have averaged. However, some weeks
have no times which leaves me with a Div/0!. I now want to do a total
average for the whole month of each week. So, I did this formula:

=AVERAGE(D40,D70,D100,D130,D160,D190)
[Where Dxx is a cell for each week that has averaged those times during
the week]

However, some of those cell numbers for each week represent a Div/0!
value because there was no time set. So, it's like:

=AVERAGE(2,4,6,8,10,Div/0!)

I tried to get it to ignore the Div/0! value in that cell by using
ISERROR like so:

IF(ISERROR(AVERAGE(D190)),"",AVERAGE(D190)) [Note:Using ,0, would
skew my average]

Now my Average looks like this:

=AVERAGE(IF(ISERROR(AVERAGE(D40)),"",AVERAGE(D40)) ,IF(ISERROR(AVERAGE(D70)),"",AVERAGE(D70)),IF(ISER ROR(AVERAGE(D100)),"",AVERAGE(D100)),IF(ISERROR(AV ERAGE(D130)),"",AVERAGE(D130)),IF(ISERROR(AVERAGE( D160)),"",AVERAGE(D160)),IF(ISERROR(AVERAGE(D190)) ,"",AVERAGE(D190)))

I've tried to get the Average to ignore the Div/0! error by treating
that cell as being empty by using "". But now when it tries to average
the series of numbers, the cell that had Div/0! replaced by "" shows a
#Value! error. Is there something I'm doing wrong? Sorry, for the
length of the post, I wanted to be thorough.

Finally, I realize that I'm doing a monthly average of weekly averages
of each day (an average of averages). But doing a monthly average of
all the days instead of each week's calculated average gives the same
problem of ignoring Div/0! errors and getting #Value! errors instead.


--
durerca
------------------------------------------------------------------------
durerca's Profile: http://www.excelforum.com/member.php...o&userid=36318
View this thread: http://www.excelforum.com/showthread...hreadid=560977