Hide the #DIV/0! Error
 

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.

Hide the #DIV/0! Error
 

IF(ISERROR(A1/B1),"",A1/B1)
--
Gary''s Student - gsnu200765


"RyanH" wrote:

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.

Hide the #DIV/0! Error
 

One way:

Instead of

=A1/B1

use

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

In article ,
RyanH wrote:

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.

Hide the #DIV/0! Error
 

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.



In article ,
Gary''s Student wrote:

IF(ISERROR(A1/B1),"",A1/B1)
--
Gary''s Student - gsnu200765


"RyanH" wrote:

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.

Hide the #DIV/0! Error
 

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?


"Gary''s Student" wrote:

IF(ISERROR(A1/B1),"",A1/B1)
--
Gary''s Student - gsnu200765


"RyanH" wrote:

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.

Hide the #DIV/0! Error
 

One way:

Change your CF to

CF1: Formula is =AND(ISNUMBER(Q3),Q30.7)
Format1: <patterns/<red

Or since Q3 depends on N3

CF1: Formula is =(N3<0,Q30.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)


In article ,
RyanH wrote:

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?


"Gary''s Student" wrote:

IF(ISERROR(A1/B1),"",A1/B1)
--
Gary''s Student - gsnu200765


"RyanH" wrote:

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.

Hide the #DIV/0! Error
 

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)


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