Trying to get rid of the #DIV/0

[Matt]

I have this formula in my cell but Can I get rid of the#DIV/0?



The formula in the cell is =SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)

 

[UlvaZell]

Combine the ISERROR function with an IF statement...

 

[Peo Sjoblom]

=IF(SUM(B38:O38)<>0,AVERAGE(IF(B38:O38<>0,B38:O38)),0)


entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom

 

[Tom Hutchins]

Try

=IF(COUNT(B38:O38)=0,0,SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)))

Hope this helps,

Hutch

 

[Sheeloo]

You don't need the part
-COUNTIF(B38:O38,0)
if you want to exclude blank cells.

Your formula will work if the range contains numbers in each cell (it may be
zero). It will fail if ALL are zero
Part

 

[UlvaZell]

=IF(ISERROR(SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0))),"
",SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)))

 

[Peo Sjoblom]

Actually that will still return the error if there are actual zeros in
B38:O38 and no other numbers which I assume it can be given the last part of
the OP's formula

COUNT(B38:O38)-COUNTIF(B38:O38,0)

--


Regards,


Peo Sjoblom

 

[Matt]

wORKED GREAT tHANKS

Try

=IF(COUNT(B38:O38)=0,0,SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)))

Hope this helps,

Hutch

 

[Jim Thomlinson]

Time to hop up on my soap box. IsError is not the correct function to use
here. Is error catches all errors be they #Div/0 or #value or... and treats
them all the same. If there is a #ref error in the source data I probably
don't want this formula to return zero as that would be incorrect. If you
reasonably anticipate a specific type of error then test for that error only.
If other errors you do not anticipate come up you are much better off to have
the error value come through. When checking for #Div/0 check if the
denominator equals zero.

Is error can be a useful formula and it has a place, but it is an easy one
to abuse. I personally can not remember the last time I used it and I write a
lot of spreadsheets.

 

[Dana DeLouis]

... Can I get rid of the#DIV/0?


Hi. Excel 2007...

=IFERROR(AVERAGEIF(A1:A5,"<>0"),0)

--
Dana DeLouis

 

[Peo Sjoblom]

Amen to that!

--


Regards,


Peo Sjoblom

Time to hop up on my soap box. IsError is not the correct function to use
here. Is error catches all errors be they #Div/0 or #value or... and
treats
them all the same. If there is a #ref error in the source data I probably
don't want this formula to return zero as that would be incorrect. If you
reasonably anticipate a specific type of error then test for that error
only.
If other errors you do not anticipate come up you are much better off to
have
the error value come through. When checking for #Div/0 check if the
denominator equals zero.

Is error can be a useful formula and it has a place, but it is an easy one
to abuse. I personally can not remember the last time I used it and I
write a
lot of spreadsheets.

 

[Jim Thomlinson]

IMO... Bad idea... See my post above. IfError is way to "catch all" and as
such is inherantly dangerous. Check only for the specific error that you
reasonably anticipate. Let any other error flow through.

 

[David Biddulph]

If that really is the formula in your cell, I wouldn't expect it to show
#DIV/0! It should flag an error as you have mismatched parentheses.
You may have intended =SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)) ?

In future, don't try to retype a formula from your worksheet to the
newsgroup and risk typing errors. Just copy from the formula bar and paste
here.

If my guess as to what formula you were trying to use was correct, you could
avoid the #DIV/0! by using
=IF((COUNT(B38:O38)-COUNTIF(B38:O38,0))=0,"",SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)))
if you want to show a blank if there are no non-zero values in your range.