Replace #/Div

  • Thread starter Thread starter Alfred90210
  • Start date Start date
A

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!!
 
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.
 
THANK YOU!!!!!

T. Valko said:
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.
Click to expand...
 
T. Valko said:
=IF(ISERROR(your_formula),"N/A",your_formula)

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

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.
 
Back
Top