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.

 

[Guest]

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

 

[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

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

Da

 

[durerca]

Thank you for pointing out about using SUMIF and COUNTIF. I didn't kno
about those functions and it's very useful for doing conditiona
averages. I've also taken your point about doing averages of average
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
range of data in non-continguous cells. I want it to sum A1+A20+A4
only if A1 or A20 or A40 are >0.