Find and Replace #N/A with 0

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Greene - 19 Feb 2006 00:53 GMT

After a vlookup etc, isn't there a way of simply changing unmatched
or #N/A to 0 so the addition formulas are still good. this would be a
great tool to have. thanks.

Reply to this Message

CyberTaz - 19 Feb 2006 01:49 GMT

One option - You can probably do so if you combine an IF function with the
ISERROR function with your error-prone function nested within:

=IF((ISERROR,YourFunction),0,YourFunction)

HTH |:>)

On 2/18/06 7:53 PM, in article uqiUP7ONGHA.812@TK2MSFTNGP10.phx.gbl, "David
Greene" <dgreene@anywhere.com> wrote:

> After a vlookup etc, isn't there a way of simply changing unmatched
> or #N/A to 0 so the addition formulas are still good. this would be a
> great tool to have. thanks.

Reply to this Message

JE McGimpsey - 19 Feb 2006 17:31 GMT

> One option - You can probably do so if you combine an IF function with the
> ISERROR function with your error-prone function nested within:
>
> =IF((ISERROR,YourFunction),0,YourFunction)

just a consideration:

In general it's better when designing spreadsheets to trap only the most
limiting error. In addition to #N/A, ISERROR() will return 0 for the
#VALUE!, #REF!, #DIV/0!, #NUM!, and #NAME errors. This could lead to
trouble if, say, the user inadvertently deleted the enough columns in
the lookup range to cause VLOOKUP to return a #REF! - the
IF(ISERROR(...),0,...) construction would return 0 for all further
calculations without any indication that there's an error.].

I'd recommend trapping only #NA, using:

=IF(ISNA(VLOOKUP(x,rng,FALSE)), 0, VLOOKUP(x,rng,FALSE))

Additionally, while computer and calculation speed have reduced the need
for efficiency, in a calculation intensive worksheet, I would use
MATCH() for the ISNA() test, since there's no reason to return the value
from the other column during the test:

=IF(ISNA(MATCH(x,rng1,FALSE)),0,VLOOKUP(x,rng,FALSE))

where rng1 is the first column of rng.

Reply to this Message

CyberTaz - 19 Feb 2006 19:10 GMT

Excellent point - I didn't consider trapping just the #N/A.

Regards |:>)

On 2/19/06 12:31 PM, in article
jemcgimpsey-DCD1CE.10312419022006@msnews.microsoft.com, "JE McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>> One option - You can probably do so if you combine an IF function with the
>> ISERROR function with your error-prone function nested within:
[quoted text clipped - 23 lines]
>
> where rng1 is the first column of rng.

Reply to this Message

Mac Forum / Applications / Excel / February 2006

Find and Replace #N/A with 0