Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide error messages? | Excel Discussion (Misc queries) | |||
Hide #VALUE! error in cell? | Excel Discussion (Misc queries) | |||
hide the #N/A error | Excel Discussion (Misc queries) | |||
Hide Error Message | Excel Discussion (Misc queries) | |||
Hide #div/0 Error When Averaging | Excel Worksheet Functions |