How do I hide the #DIV0! statement in a cell?

  • Thread starter Thread starter JSN
  • Start date Start date
J

JSN

Trying to make a spreadsheet more presentable by removing the #DIV0!
statements which result in formulas with 0 or no values.
 
hi
turn you formula into an if statement.
lets say your formula is in c2 and the dependents are in A2 and B2 with B2 = 0

=if(B2=0, "",A2/B2) or =if(B2=0,0,A2?B2)

Regards
FSt1
 
There are (at least) three ways to do this.

If you want to hide *all* errors, not just #DIV/0, use the following
formula:
=IF(ISERROR(A1),"",A1)
This will display the content of A1 unless A1 is an error, in which
case it returns an empty cell.

If you are working in Excel 2007 or later and the workbook will not be
used in Excel 2003 and earlier versions, you can use the new IFERROR
function:
=IFERROR(A1,"")
This will work only in Excel 2007 and later. It accomplishes the same
thing as the formula above. If A1 is any error, an empty cell is
displayed. If A1 is not an error, its contents are displayed.

If you want to hide *only* #DIV/0 error, and display all other errors
(e.g., #NAME?), use the following formula.
=IF(ISERROR(A1),IF(ERROR.TYPE(A1)=2,"",A1),A1)
This formula hides *only* #DIV/0! errors. Any other error (e.g.,
#NAME?) is displayed. If A1 is not an error, its content is displayed.
As an aside, I think the ERROR.TYPE function is poorly designed. The
parameter passed to it must be an error. If you pass a non-error
value, ERROR.TYPE itself returns a #N/A error. It should have been
designed such that a non-error parameter would return 0, not #N/A.
Just my opinion.

Of course, in all the formulas above, change the reference to A1 to
the appropriate cell.


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