Get rid of #DIV/0! when using the Average formula......

SOUTHAMERICA70
SOUTHAMERICA70 used Ask the Experts™
on
I want to get rid of the #DIV/0! in the cell when I use the Average formula:

In the cell I have this formula:
=AVERAGE(B73:E73:G73:J73:L73:O73)

Also get rid of #DIV/0! in the cell when I use the sum formula:
=SUM(P73:P81)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you have 2010 then you can use

=IFERROR(AVERAGE(B73:E73:G73:J73:L73:O73),"")

and

=IFERROR(SUM(P73:P81),"")
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

the DIV error will only come up if there is no numeric value at all in the range. Therefore, you could count the numeric values, and if they are greater than zero, perform the average.

=IF(COUNT(B73,E73,G73,J73,L73,O73)>0,AVERAGE(B73,E73,G73,J73,L73,O73),"")

or even shorter

=IF(COUNT(B73,E73,G73,J73,L73,O73),AVERAGE(B73,E73,G73,J73,L73,O73),"")

By the way, if you use more than one range in Average, you should separate each range with a comma, not the colon.

Assuming that the Sum() you mentioned will sum all the calculated averages above, this will not work without any alteration, since there are no DIV errors in the average results.

cheers, teylyn

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial