REMOVE THE #DIV/0! ERROR FROM FORMULA WHEN FIGURING MULTIPLE AVERA

[Security Dave]

I am trying to formulate a cell to figure averages from cells that have
averages.
My current formula reads
=AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33)
I am getting the error due to blank cells that have not been filled in yet.
Any suggestions?

 

[Mike H]

Settting aside that you shouldn't really be taking an average of an average
try this

=SUM(D33,G33,J33,M33,P33,S33,V33,Y33)/COUNT(D33,G33,J33,M33,P33,S33,V33,Y33)

Mike

 

[T. Valko]

I'm assuming you mean this happens when *none* of the cells contain data. If
*any* cell contained data it should work (as long as there are no error
values in the range) and AVERAGE would ignore the empty cells and text.

Try this:

=IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33),"")

 

[Security Dave]

Thanks very much for your help. It worked like a charm.

One more if you have the time. Same situation, except I am figuring
averages between 2 numbers.

Simple formula of =U20/T20
Any help would be appreciated.

 

[David Biddulph]

I'm not sure where the "average" comes into your U20/T20 formula, but you
could try =IF(T20,U20/T20,"") or =IF(T20=0,"",U20/T20)
If you want to distinguish between a blank and a zero in T20 you could do
that, for example =IF(T20="","",IF(T20=0,"infinite ratio",U20/T20))