how to conver #n/a's to 0
 

Hi,

I am a newbie to Excel programming and would like to know asap how t
have on all formulas where I get '#N/A's to the number 0. The proble
with this is that these N/As are screwing up totals and my boss woul
like to put just 0s. Most of the N/As come from most formulas dealin
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 wil
default to 0, otherwise let the formula display the legitamate value i
comes up with. My boss needs this by the end of the day, so any quic
help is really appreciated. Thanks

--
ray50
-----------------------------------------------------------------------
ray500's Profile: http://www.excelforum.com/member.php...fo&userid=3294
View this thread: http://www.excelforum.com/showthread.php?threadid=52768

how to conver #n/a's to 0
 

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"
wrote in message
...

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

how to conver #n/a's to 0
 

Look up the ISNA function

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

PWS


"ray500" wrote in
message ...

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

how to conver #n/a's to 0
 

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" wrote in message
...
Look up the ISNA function

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

PWS


"ray500"
wrote in message
...

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

how to conver #n/a's to 0
 

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
------------------------------------------------------------------------
ray500's Profile: http://www.excelforum.com/member.php...o&userid=32942
View this thread: http://www.excelforum.com/showthread...hreadid=527689

how to conver #n/a's to 0
 

:) thanks, it works!

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

--
ray50
-----------------------------------------------------------------------
ray500's Profile: http://www.excelforum.com/member.php...fo&userid=3294
View this thread: http://www.excelforum.com/showthread.php?threadid=52768

how to conver #n/a's to 0
 

:) 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
------------------------------------------------------------------------
ray500's Profile: http://www.excelforum.com/member.php...o&userid=32942
View this thread: http://www.excelforum.com/showthread...hreadid=527689