Need help with dividing and using ISERROR for Div/0!

[durerca]

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(ISERROR(AVERAGE(D100)),"",AVERAGE(D100)),IF(ISERROR(AVERAGE(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.

[Toppers]

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(ISERROR(AVERAGE(D100)),"",AVERAGE(D100)),IF(ISERROR(AVERAGE(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
>
>

[Dav]

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]

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.

EDIT: I realised that if I did a SUM of a series of conditional SUMIFs that I could include my whole range of data. Thanks to the people who posted solutions. I now have an average of a specific set of times based on a running total instead of an average of each week's average. I'm using SUMIF divided by COUNTIF to get the results I need.