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

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" < XXXX@XXXXX.COM >

=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" < XXXX@XXXXX.COM >

Look up the ISNA function

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

PWS

"ray500" < XXXX@XXXXX.COM > wrote in

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

PWS

"ray500" < XXXX@XXXXX.COM > wrote in

Paul

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

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

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.yqcomputer.com/

View this thread: http://www.yqcomputer.com/

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

:) 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.yqcomputer.com/

View this thread: http://www.yqcomputer.com/

1. can '1' be conver to true ,and the '0' conver to false ?

3. Weird Problem -- Repeating C's and N's

5. Multiline tooltip problem... shows the \n's

6. index n's maximun values in an array

7. How to change the "N's" in proc report across time

9. deleting empty lines from a variable containing \n's

10. Is is possible to count the Y/N's in a record?

11. Mofify data values of n's variables

13. 2 fields have embedded \n's, how to get rid of these?

15. How to conver an especific Format to DateTime

7 post • Page:**1** of **1**