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.

 

[joel]

use an if statement

for no returned results
=if(error(your funcrtion),"",your function)

or
to return zero
=if(error(your funcrtion),0,your function)

 

[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

 

[joel]

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

????????? Efficient is an interesting word especially in this incident
What do you mean? Can you prove it?

 

[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

 

[joel]

A computer doesn't really doesn't care how big a formual is. th
simplier formula is easy to understand, but you ae maintaining tw
formulas instead of one formula. And then hidding a column will make i
more difficult for somebody unfamilar with the workbook to see what i
happening.

I don't believe in complicated formulas and often split formulas int
multiple cells. But to say this is more "efficient" is my only point.
I felt efficient was a poor choice of words.

 

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

 

[joel]

I see your point, but I consider what you are saying more "Good Desig
Practice". I usally think of efficency more as "operational" than as
part of the build process.