how to conver #n/a's to 0

[ray500]

Hi,

I am a newbie to Excel programming and would like to know asap how to have on all formulas where I get '#N/A's to the number 0. The problem with this is that these N/As are screwing up totals and my boss would like to put just 0s. Most of the N/As come from most formulas dealing with VLOOKUP such as this formula:
=VLOOKUP(TRIM($B43&$D43),PLUTCommCatPTDyn,8,0)

Can somebody give me the syntax so that when I get a n/a, it will default to 0, otherwise let the formula display the legitamate value is comes up with. My boss needs this by the end of the day, so any quick help is really appreciated. Thanks.

[Chip Pearson]

You have to use and IF function and two VLOOKUPS. E.g.,

=IF(ISNA(VLOOKUP(....)),0,VLOOKUP(...))

This tests VLOOKUP for NA. If it is NA, the formula returns a 0.
If it is not an NA, it calls VLOOKUP(...) again to get the
result. The obvious disadvantage of this approach is that you're
typically calling VLOOKUP twice.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"ray500" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> I am a newbie to Excel programming and would like to know asap
> how to
> have on all formulas where I get '#N/A's to the number 0. The
> problem
> with this is that these N/As are screwing up totals and my boss
> would
> like to put just 0s. Most of the N/As come from most formulas
> dealing
> with VLOOKUP such as this formula:
> =VLOOKUP(TRIM($B43&$D43),PLUTCommCatPTDyn,8,0)
>
> Can somebody give me the syntax so that when I get a n/a, it
> will
> default to 0, otherwise let the formula display the legitamate
> value is
> comes up with. My boss needs this by the end of the day, so
> any quick
> help is really appreciated. Thanks.
>
>
> --
> ray500
> ------------------------------------------------------------------------
> ray500's Profile:
> http://www.excelforum.com/member.php...o&userid=32942
> View this thread:
> http://www.excelforum.com/showthread...hreadid=527689
>

[Paul W Smith]

Look up the ISNA function

If ISNA({your formula},{your formula},0)

PWS


"ray500" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I am a newbie to Excel programming and would like to know asap how to
> have on all formulas where I get '#N/A's to the number 0. The problem
> with this is that these N/As are screwing up totals and my boss would
> like to put just 0s. Most of the N/As come from most formulas dealing
> with VLOOKUP such as this formula:
> =VLOOKUP(TRIM($B43&$D43),PLUTCommCatPTDyn,8,0)
>
> Can somebody give me the syntax so that when I get a n/a, it will
> default to 0, otherwise let the formula display the legitamate value is
> comes up with. My boss needs this by the end of the day, so any quick
> help is really appreciated. Thanks.
>
>
> --
> ray500
> ------------------------------------------------------------------------
> ray500's Profile:
> http://www.excelforum.com/member.php...o&userid=32942
> View this thread: http://www.excelforum.com/showthread...hreadid=527689
>

[Chip Pearson]

Paul
> If ISNA({your formula},{your formula},0)

You've got it backwards. It should be

=IF(ISNA(your formula),0,(your formula))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Paul W Smith" <[email protected]> wrote in message
news:[email protected]...
> Look up the ISNA function
>
> If ISNA({your formula},{your formula},0)
>
> PWS
>
>
> "ray500" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> Hi,
>>
>> I am a newbie to Excel programming and would like to know asap
>> how to
>> have on all formulas where I get '#N/A's to the number 0.
>> The problem
>> with this is that these N/As are screwing up totals and my
>> boss would
>> like to put just 0s. Most of the N/As come from most formulas
>> dealing
>> with VLOOKUP such as this formula:
>> =VLOOKUP(TRIM($B43&$D43),PLUTCommCatPTDyn,8,0)
>>
>> Can somebody give me the syntax so that when I get a n/a, it
>> will
>> default to 0, otherwise let the formula display the legitamate
>> value is
>> comes up with. My boss needs this by the end of the day, so
>> any quick
>> help is really appreciated. Thanks.
>>
>>
>> --
>> ray500
>> ------------------------------------------------------------------------
>> ray500's Profile:
>> http://www.excelforum.com/member.php...o&userid=32942
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=527689
>>
>
>

[ray500]

I'll give your suggestions a try and will let u know the results. If doing Vlookup 2x doesn't significantly affect performance, I'm fine.

[ray500]

thanks, it works!

The thing is I have to do this to more than 15 columns. Is there a cleaner way to do this? In other words, is there some way to save a generic function like this in the Workbook names(Insert->Names Menu option) so that I don't have to type every function twice for each column. Maybe something like this:
MyIfNAFunction(VALOOKUP....)...
thanks for all your help.

[ray500]

thanks, it works!

The thing is I have to do this to more than 15 columns. Is there a cleaner way to do this? In other words, is there some way to save a generic function like this in the Workbook names(Insert->Names Menu option) so that I don't have to type every function twice for each column. Maybe something like this:
MyIfNAFunction(VALOOKUP....)...
thanks for all your help.