Thanks:  0
Likes:  0
Dislikes:  0

# Thread: Any way in excel to replace the#N/A val with 0

1. ## Any way in excel to replace the#N/A val with 0

Hello All,
I am trying to replace #N/A value with 0. Is there any forumal to do that. please help. Thanks.

2. ## Re: Any way in excel to replace the#N/A val with 0

Yes

=IF(ISNA(A1), 0, A1)

3. ## Re: Any way in excel to replace the#N/A val with 0

Thanks Tarun, I am getting syntax error.
= IF(ISNA(A1),0,A1)).

I am trying to do this with lookup function, and getting syntax error too. may be it's the same mistake i am making everywhere. If someone can take a look and help. Thanks for all your help.

objWS2.range("F2:F6").formula = "=VLOOKUP(D2,'Current Source'!\$C\$2:\$E\$113,3,0)" '' current position

= IF (ISNA(VLOOKUP(D2,'Current Source'!\$C\$2:\$E\$113,3,0)), "",VLOOKUP(D2,'Current Source'!\$C\$2:\$E\$113,3,0)

4. ## Re: Any way in excel to replace the#N/A val with 0

You are missing a bracket at the end

=IF(ISNA(VLOOKUP(D2,'Current Source'!\$C\$2:\$E\$113,3,0)),"",VLOOKUP(D2,'Current Source'!\$C\$2:\$E\$113,3,0))

5. ## Re: Any way in excel to replace the#N/A val with 0

Thanks Tarun. Just needed one more clarification:
If I have a column D (from WS2)which contains #NA values and I want to replace them with 0, Will this be correct syntax?
objWS2.range("D150")= "=IF(ISNA(D1), 0, D1)) -- it is working fine , just to confirm

6. ## Re: Any way in excel to replace the#N/A val with 0

Well if it works fine then i doubt there is a confirmation needed. But I just had one doubt isn't this formula referring to D1 only?

7. ## Re: Any way in excel to replace the#N/A val with 0

Thank you Tarun for your time. I really apprecitae. Actually that's my confusion is now: about the range. How do I give the range. I have the entire D column which cotains 50 rows of data where 20 values are #N/A so how do I give the range? It did work fine but I just noticed that it is replacing the entire D column all 50 values with 0 which means range is not given right. So how do i give the range ?

8. ## Re: Any way in excel to replace the#N/A val with 0

Any suggestions ?

9. ## Re: Any way in excel to replace the#N/A val with 0

There is a relative formula technique that you would need to use in this

objWS2.range("E1:E50").FormulaR1C1 = "=IF(ISNA(RC[-1]), 0, RC[-1]))"

RC means row and col
R[1]C[-1] would mean next row and previous column
R[-1]C[2] would mean previous row and next to next column from current column

10. ## Re: Any way in excel to replace the#N/A val with 0

Hi Tarun,
I am sorry but I didnot understand. how do I Substitute the values in FormulaR1C1? Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 12.00%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 03:59 PM.

Copyright BetaSoft Inc.