don't show #DIV/0 error in cell

[Christy]

I'm using the following formula, which is GREAT, but I would like to alter it
to when Col F & Col C are 0 there is not #DIV/0 shown in the cell.
=SUMIF(F13:F51,"IN",C13:C5)/COUNTIF(F13:F51,"IN")

Same thing for the formula =IF(M3=0,"",+M3/K7*100) or =INT(K1)
Again, GREAT formulas, but when M3 and K7 are both 0, I'd like no error
message in cell as well as when K1 is 0

This is probably a very easy fix, I just can't get it right. Thanks in
advance.
Christy

 

[Barb Reinhardt]

You could try something like this:

=if(iserror(Your calculation),NA(),Your Calculation)

HTH,

Barb Reinhardt

 

[Pete_UK]

For the first formula (looks familiar !!) it is when the COUNTIF term
is zero that you will get #DIV/0 errors, so amend it like this:

=IF(COUNTIF(F13:F51,"IN")=0,0,SUMIF(F13:F51,"IN",C13:C5)/
COUNTIF(F13:F51,"IN"))

This will put 0 in the cell - if you want the cell to look blank then
change the ,0, to ,"", in the middle.

For your second formula it is when K7 is zero that you will get this
error, so you can avoid it like this:

=IF(OR(M3=0,K7=0),"",M3/K7*100))

For your third formula, try this:

=IF(ISNUMBER(K1),INT(K1),"")

Hope this helps.

Pete

 

[Christy]

Pete, Thanks again. Everything woked like a charm!
Christy

 

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete

Pete, Thanks again. Everything woked like a charm!
Christy






- Show quoted text -