Replace #/Div

[Alfred90210]

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!!

 

[T. Valko]

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.

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Alfred90210]

THANK YOU!!!!!

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.

 

[Alfred90210]

Thank you!!!!!

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

If this post helps click Yes

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

THANK YOU!!!!!

 

[Harlan Grove]

=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.