Trying to get rid of the #DIV/0

  • Thread starter Thread starter Matt
  • Start date Start date
M

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)
 
=IF(SUM(B38:O38)<>0,AVERAGE(IF(B38:O38<>0,B38:O38)),0)


entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom
 
Try

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

Hope this helps,

Hutch
 
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
 
=IF(ISERROR(SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0))),"
",SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)))
 
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
 
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.
 
... Can I get rid of the#DIV/0?


Hi. Excel 2007...

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

--
Dana DeLouis
 
Amen to that!

--


Regards,


Peo Sjoblom

Jim Thomlinson said:
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.
Click to expand...
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

absolute & relative references 2
how can i find a value in a range? 1
MAKING THE FOLLOWING EQUATION DISPLAY A BLANK CELL IF NO DATA INPU 3
=VLOOKUP 8
IF formula error - #NAME? Some get errors some not 7
Help with a Lookup, I think 5
Adding parenthesis to a formula? 2
FALSE statement? 5

Back
Top