Replace #/Div
 

Isn't there an 'Excel Option' that allows you to enter value (Free Text... IE
N/A) when you get a #/div error?

I know that pivot tables have that option, but I'm not using a pivot table
and I'd like to use that option. Thanks in advance!!

Replace #/Div
 

You have to build that into the formula. Since you didn't post the formula
you can try this general syntax:

=IF(ISERROR(your_formula),"N/A",your_formula)

That will trap *all* errors, not just the #DIV/0! error.

--
Biff
Microsoft Excel MVP


"Alfred90210" wrote in message
...
Isn't there an 'Excel Option' that allows you to enter value (Free Text...
IE
N/A) when you get a #/div error?

I know that pivot tables have that option, but I'm not using a pivot table
and I'd like to use that option. Thanks in advance!!

Replace #/Div
 

=IF(ISERROR(<your formula),"N/A",<your formula)

If this post helps click Yes
---------------
Jacob Skaria


"Alfred90210" wrote:

Isn't there an 'Excel Option' that allows you to enter value (Free Text... IE
N/A) when you get a #/div error?

I know that pivot tables have that option, but I'm not using a pivot table
and I'd like to use that option. Thanks in advance!!

Replace #/Div
 

THANK YOU!!!!!

"T. Valko" wrote:

You have to build that into the formula. Since you didn't post the formula
you can try this general syntax:

=IF(ISERROR(your_formula),"N/A",your_formula)

That will trap *all* errors, not just the #DIV/0! error.

--
Biff
Microsoft Excel MVP


"Alfred90210" wrote in message
...
Isn't there an 'Excel Option' that allows you to enter value (Free Text...
IE
N/A) when you get a #/div error?

I know that pivot tables have that option, but I'm not using a pivot table
and I'd like to use that option. Thanks in advance!!

Replace #/Div
 

Thank you!!!!!

"Jacob Skaria" wrote:

=IF(ISERROR(<your formula),"N/A",<your formula)

If this post helps click Yes
---------------
Jacob Skaria


"Alfred90210" wrote:

Isn't there an 'Excel Option' that allows you to enter value (Free Text... IE
N/A) when you get a #/div error?

I know that pivot tables have that option, but I'm not using a pivot table
and I'd like to use that option. Thanks in advance!!

Replace #/Div
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Alfred90210" wrote in message
...
THANK YOU!!!!!

"T. Valko" wrote:

You have to build that into the formula. Since you didn't post the
formula
you can try this general syntax:

=IF(ISERROR(your_formula),"N/A",your_formula)

That will trap *all* errors, not just the #DIV/0! error.

--
Biff
Microsoft Excel MVP


"Alfred90210" wrote in message
...
Isn't there an 'Excel Option' that allows you to enter value (Free
Text...
IE
N/A) when you get a #/div error?

I know that pivot tables have that option, but I'm not using a pivot
table
and I'd like to use that option. Thanks in advance!!

Replace #/Div
 

"T. Valko" wrote...
....
=IF(ISERROR(your_formula),"N/A",your_formula)

That will trap *all* errors, not just the #DIV/0! error.
....

In the particular case of #DIV/0!, the most common causes are an
AVERAGE function call against a range containing no number values or a
formula like N/D where D = 0. Those are better handled using

=IF(COUNT(range),AVERAGE(range),"N/A")

or

=IF(-D<0,N/D,"N/A") [-D rather than D intentional]

More generally, it's possible to trap only specific errors using

=IF(COUNT(1/(ERROR.TYPE(formula)={1;2})),"N/A",formula)

This example traps #NULL! and #DIV/0! errors.