[JSN]

Trying to make a spreadsheet more presentable by removing the #DIV0!
statements which result in formulas with 0 or no values.

[FSt1]

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

"JSN" wrote:

Trying to make a spreadsheet more presentable by removing the #DIV0!
statements which result in formulas with 0 or no values.

[Richard]

Try something like this: Example: If formula is in lets say C1.
If(A1="","",A1+B2)

"JSN" wrote:

Trying to make a spreadsheet more presentable by removing the #DIV0!
statements which result in formulas with 0 or no values.

[Chip Pearson]

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)






On Sat, 25 Jul 2009 06:59:01 -0700, JSN
wrote:

Trying to make a spreadsheet more presentable by removing the #DIV0!
statements which result in formulas with 0 or no values.

[[email protected]]

Am Samstag, 25. Juli 2009 18:27:28 UTC+3 schrieb Chip Pearson:
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)






On Sat, 25 Jul 2009 06:59:01 -0700, JSN
wrote:

Trying to make a spreadsheet more presentable by removing the #DIV0!
statements which result in formulas with 0 or no values.
Dear Chip Pearson,

what happens, when in A1 is a Formular? (In my example the Formular is in the place Q1, and the formular is: (P1*(A1/100))+A1))

Lets think about it like that: =IF(ISERROR(((P1*(A1/100))+A1),IF(ERROR.TYPE(Q1)=2,"",Q1),Q1) (I have absolutely no idea if this here is correct, new area for me here...)

Do you have an idea how to figure this out?
I would be very happy!
Thanks

[Peter T[_7_]]

wrote in message


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.


On Sat, 25 Jul 2009 06:59:01 -0700, JSN
wrote:

Trying to make a spreadsheet more presentable by removing the #DIV0!
statements which result in formulas with 0 or no values.

Dear Chip Pearson,

what happens, when in A1 is a Formular? (In my example the Formular is in
the place Q1, and the formular is: (P1*(A1/100))+A1))

Lets think about it like that:
=IF(ISERROR(((P1*(A1/100))+A1),IF(ERROR.TYPE(Q1)=2,"",Q1),Q1) (I have
absolutely no idea if this here is correct, new area for me here...)

Do you have an idea how to figure this out?
I would be very happy!
Thanks

As Chip Pearson suggested, since 2007 use =IFERROR(value, value_if_error).

Not sure I follow your formula but maybe this is what you want

=IFERROR((P1*(A1/100)+A1),"")

Regards,
Peter T