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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Security Dave - 09 May 2008 19:24 GMT

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?

Reply to this Message

Mike H - 09 May 2008 19:31 GMT

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

> 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?

Reply to this Message

David Biddulph - 09 May 2008 19:31 GMT

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

>I am trying to formulate a cell to figure averages from cells that have
> averages.
[quoted text clipped - 3 lines]
> yet.
> Any suggestions?

Reply to this Message

Security Dave - 12 May 2008 16:18 GMT

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.

> =IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33),"")
> --
[quoted text clipped - 7 lines]
> > yet.
> > Any suggestions?

Reply to this Message

David Biddulph - 12 May 2008 16:52 GMT

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))
--
David Biddulph

> Thanks very much for your help. It worked like a charm.
>
[quoted text clipped - 15 lines]
>> > yet.
>> > Any suggestions?

Reply to this Message

T. Valko - 09 May 2008 19:38 GMT

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),"")

Signature

Biff
Microsoft Excel MVP

>I am trying to formulate a cell to figure averages from cells that have
> averages.
[quoted text clipped - 3 lines]
> yet.
> Any suggestions?

Reply to this Message