Thread: Replace #/Div
View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default 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.