Avoiding #N/A result
 

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.

Avoiding #N/A result
 

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)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198752

http://www.thecodecage.com/forumz

Avoiding #N/A result
 

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()


--
Jacob (MVP - Excel)


"Wes_A" wrote:

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.

Avoiding #N/A result
 

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


--
Regards
Dave Hawley
www.ozgrid.com
"Wes_A" wrote in message
...
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.

Avoiding #N/A result
 

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?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198752

http://www.thecodecage.com/forumz

Avoiding #N/A result
 

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 :)

--
Regards
Dave Hawley
www.ozgrid.com
"joel" wrote in message
...

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?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=198752

http://www.thecodecage.com/forumz

Avoiding #N/A result
 

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198752

http://www.thecodecage.com/forumz

Avoiding #N/A result
 

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 ****ing contest and to be drawn into by
nit-picking.


--
Regards
Dave Hawley
www.ozgrid.com


"joel" wrote in message
...

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=198752

http://www.thecodecage.com/forumz

Avoiding #N/A result
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198752

http://www.thecodecage.com/forumz