Averaging cell's...problems with Div/0

  • Thread starter Thread starter Roy Bunch
  • Start date Start date
R

Roy Bunch

Hi guys. First time poster here so be gentle with me. I am looking fo
some assistance averaging a range of 1 to 3 numbers.

Here is what I have so far.

=(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1))


This works great. What it does is checks to see if there is a value i
the cell, then counts it and divides by the right number. I.E if yo
only have two values out of 3 filled in it divides the number by tw
instead of 3.

My problem... if all 3 fields are 0 then I get a divide by 0 error.
Any suggestion on how to fix this? I don't want my spreadsheet to loo
messy before I start plugging in values. What kind of formula can
plug in here to have it display 0 if all 3 values are 0
 
=IF(AND(C3=0,E3=0,G3=0),0,(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1)
))

Are these cells really zero or are they blank, if blank you could use

=IF(COUNT(C3,E3,G3),SUM(D3,F3,H3)/COUNT(C3,E3,G3),0)
--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Hi guys. First time poster here so be gentle with me. I am looking for
some assistance averaging a range of 1 to 3 numbers.

Here is what I have so far.

=(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1))


This works great. What it does is checks to see if there is a value in
the cell, then counts it and divides by the right number. I.E if you
only have two values out of 3 filled in it divides the number by two
instead of 3.

My problem... if all 3 fields are 0 then I get a divide by 0 error.
Any suggestion on how to fix this? I don't want my spreadsheet to look
messy before I start plugging in values. What kind of formula can I
plug in here to have it display 0 if all 3 values are 0.
Click to expand...

=AVERAGE(C3,E3,G3)

This will give a DIV/0 error if all three are not numbers, but does give a 0 if
all three are 0.

You could handle the error by either checking specifically for numeric entries
or using an ISERR function.


--ron
 
Back
Top