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.

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.

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" <(E-Mail Removed)> wrote in message
news:97F43545-939D-4FA6-BF74-(E-Mail Removed)...
> 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.

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>