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)

Combine the ISERROR function with an IF statement...

"Matt" wrote:

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

"Matt" wrote in message
...
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)

Try

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

Hope this helps,

Hutch

"Matt" wrote:

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)

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

"Matt" wrote:

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(ISERROR(SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0))),"
",SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)))

"UlvaZell" wrote:

Combine the ISERROR function with an IF statement...

"Matt" wrote:

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)

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

"Tom Hutchins" wrote in message
...
Try

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

Hope this helps,

Hutch

"Matt" wrote:

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)

wORKED GREAT tHANKS

"Tom Hutchins" wrote:

Try

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

Hope this helps,

Hutch

"Matt" wrote:

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)

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.
--
HTH...

Jim Thomlinson


"UlvaZell" wrote:

Combine the ISERROR function with an IF statement...

"Matt" wrote:

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)

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

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



Hi. Excel 2007...

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

--
Dana DeLouis


"Matt" wrote in message ...

wORKED GREAT tHANKS

"Tom Hutchins" wrote:


Try

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

Hope this helps,

Hutch

"Matt" wrote:


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)

Amen to that!

--


Regards,


Peo Sjoblom

"Jim Thomlinson" wrote in message
...
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.
--
HTH...

Jim Thomlinson


"UlvaZell" wrote:

Combine the ISERROR function with an IF statement...

"Matt" wrote:

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)

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.
--
HTH...

Jim Thomlinson


"Dana DeLouis" wrote:

... Can I get rid of the#DIV/0?
=IF(COUNT(B38:O38)=0,0,SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)))


Hi. Excel 2007...

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

--
Dana DeLouis


"Matt" wrote in message ...
wORKED GREAT tHANKS

"Tom Hutchins" wrote:

Try

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

Hope this helps,

Hutch

"Matt" wrote:

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

"Matt" wrote in message
...
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)