Hide the #DIV/0! Error

[RyanH]

Is there a way to not show the #DIV/0! Error in a cell? I would like to have
the cell blank instead.

Thanks in advance.

 

[Gary''s Student]

IF(ISERROR(A1/B1),"",A1/B1)

 

[JE McGimpsey]

One way:

Instead of

=A1/B1

use

=IF(B1=0,"",A1/B1)

 

[JE McGimpsey]

Note that this will cause any other errors to be masked as well.

There's no real reason to get the reference to A1 and to do the division
calculation either, the #DIV/0 error only appears if B1=0, so

=IF(B1=0,"",A1/B1)

works more efficiently.

 

[RyanH]

Thanks for the quick reply! That is what I got already. I just didn't know
if there was a setting somewhere to turn off that particular error
description.

But I am having a conditional formatting issue though. I want Columns(Q:S)
to have a background color of red if the value is greater than 70%. The
problem is, if the cell is blank it turns red anyway. Is this because it has
a formula in it?

Here is the formula I have in the Columns:

Col. Q =IF(ISERROR(P3*24/N3),"",P3*24/N3)
Col. R =IF(ISERROR(M3/N3),"",M3/N3)
Col. S =IF(ISERROR(Q3+R3),"",Q3+R3)

For Example, (Col. Q) if P3 and N3 is blank Q3 show red, Why?

 

[JE McGimpsey]

One way:

Change your CF to

CF1: Formula is =AND(ISNUMBER(Q3),Q3>0.7)
Format1: <patterns>/<red>

Or since Q3 depends on N3

CF1: Formula is =(N3<>0,Q3>0.7)
Format1: <patterns>/<red>

BTW: your formulae would be more efficient as

Q3: =IF(N3=0,"",P3*24/N3)
R3: =IF(N3=0,"",M3/N3)
S3: =IF(N3=0,"",Q3+R3)

 

[Chip Pearson]

In Excel 2003 and earlier, use a formula like either of the following:

=IF(B1=0,0,A1/B1)
=IF(ISERROR(A1/B1),0,A1/B1)

In Excel 2007, you can use

=IFERROR(A1/B1,0)


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)