Avoiding #N/A result

[Wes_A]

Is there a way to avoid getting the error result "#N/A" and rather having ""
or 0 returned as the result?
It's not a display issue, I don't want the error in the cell at all.
Thanks for any suggestion.

 

[Jacob Skaria]

Handle that using ISNA() and IF()

=IF(ISNA(yourformula),"",yourformula)
OR
=IF(ISNA(yourformula),0,yourformula)

If you are using XL 2007 check out help for IFERROR()

 

[ozgrid.com]

It's far more efficient to let the #N/A happen, hide the column/row and
reference the cell with;

=IF(ISNA(A1),0,A1)

http://www.ozgrid.com/Excel/stop-na-vlookup.htm

 

[ozgrid.com]

Less typing, less overhead, more efficient re-calculations. Common sense
dictates it's more efficient for both Excel and the user.

No doubt you disagree..and we will have to agree to disagree

 

[ozgrid.com]

Maybe not the PC but Excel certainly does care how big a formula is. I have
seen many a Workbook forced to switch calculations to manual because of poor
design. That's a false reading waiting to happen and catering to bad design
when they should fix it. By doubling up the VLOOKUP with an IF and ISNA
Function you doubling the calculation needed and the recalculation time. Not
very prudent spreadsheet design. My way, is as I said, less typing, far less
calculation time and hence more efficient for both Excel and the user. You
wont notice the difference until it's too late. If that doesn't warrant the
word "efficient" you must use a different Dictionary to me.

But hey, I'm not here for a pissing contest and to be drawn into by
nit-picking.