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

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

Averaging averages is a dubious thing to do statistically

As if you had for example Monday 30, 20, 10 Average = 20
Tuesday 6, 4 Average= 5

Avaerage over 2 days =14 but average of averages =12.5

However if you are sure this is what you want to do

=sumif(a1:10,"=0")/countif(a1:10,"=0") should work

Regards

Dav


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

Thank you for pointing out about using SUMIF and COUNTIF. I didn't know
about those functions and it's very useful for doing conditional
averages. I've also taken your point about doing averages of averages
so I'm trying to do an average of the full original data now.

My problem now is that I cannot figure out how to get SUMIF to sum a
range of data in non-continguous cells. I want it to sum A1+A20+A40
only if A1 or A20 or A40 are 0.

Dav Wrote:
Averaging averages is a dubious thing to do statistically

As if you had for example Monday 30, 20, 10 Average = 20
Tuesday 6, 4 Average= 5

Average over 2 days =70/5 =14 but average of averages =12.5

However if you are sure this is what you want to do

=sumif(a1:10,"=0")/countif(a1:10,"=0") should work

However as your range is not continuous try
=AVERAGE(IF(ISNUMBER(D40:D190),IF(MOD(ROW(D40:D190 ),30)=10,D40:D190,"")))

entered as an array shift ctrl enter


Regards

Dav


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