Dealing with #N/A results

S

SandyLACA

Is there a way to replace all the #N/A results from any formula? If you try
to use #N/A in the replace function, Excel doesn't recognize it. The only
work around I could come up with is to sort the column with the #N/A's and
then delete or copy over them.
Thanks, Sandy
 
R

RichardM

Wrap your formula in an error handler such as:

if(isna(your formula),0,your formula)

in simple terms, if your formula returns the #N/A error return a zero
(you could use "" instead and leave the cell blank if you prefer). If
it returns a valid value return that value.

I use this method to trap errors in vlookups (I use iserror because it
traps all errors) e.g.

=if(iserror(vlookup(A1,$B:$C,2,0)),0,vlookup(A1,$B:$C,2,0))
 
Top