don't show #DIV/0 error in cell
 

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

don't show #DIV/0 error in cell
 

You could try something like this:

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

HTH,

Barb Reinhardt

"Christy" wrote:

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

don't show #DIV/0 error in cell
 

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",C 13: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

On Aug 11, 12:10 am, Christy
wrote:
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

don't show #DIV/0 error in cell
 

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

"Pete_UK" wrote:

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",C 13: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

On Aug 11, 12:10 am, Christy
wrote:
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

don't show #DIV/0 error in cell
 

Glad to hear it - thanks for feeding back.

Pete

On Aug 11, 1:08 am, Christy wrote:
Pete, Thanks again. Everything woked like a charm!
Christy



"Pete_UK" wrote:
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",C 13: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

On Aug 11, 12:10 am, Christy
wrote:
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- Hide quoted text -

- Show quoted text -