how to remove #N/A
I have a large spreadhsheet (21k rows) that creates alot of #N/A as designed.
I would like to change the #N/A to zero removing the formula. This is not a one time deal as I need to re-create the spreadsheet weekly. I do not want to value out the entire spreadsheet. |
how to remove #N/A
=IF(ISNA(blah),"",blah)
=IF(ISNA(blah),0,blah) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Charlie_Brown" wrote in message ... I have a large spreadhsheet (21k rows) that creates alot of #N/A as designed. I would like to change the #N/A to zero removing the formula. This is not a one time deal as I need to re-create the spreadsheet weekly. I do not want to value out the entire spreadsheet. |
how to remove #N/A
Select the range to fix (include other cells if you want).
Edit|Goto|special check formulas and errors (and uncheck all the other options) The selection should be just the cells with formulas that evaluate to #n/a's. Now type 0 and hit ctrl-enter. ====== Another option would be to change the formula that returns the #n/a. =if(isna(yourformula),0,yourformula) Charlie_Brown wrote: I have a large spreadhsheet (21k rows) that creates alot of #N/A as designed. I would like to change the #N/A to zero removing the formula. This is not a one time deal as I need to re-create the spreadsheet weekly. I do not want to value out the entire spreadsheet. -- Dave Peterson |
how to remove #N/A
Hi Charlie
Use below formula: =IF(ISNA(VLOOKUP(A2,$B$2:$B$15,1,0))=TRUE,"0",VLOO KUP(A2,$B$2:$B$15,1,0)) Change your reference cells. This formula will convert all your #N/A values to Zero There is one more way of doing: Ctrl+H Replace what : #N/A Replace with : 0 "Charlie_Brown" wrote: I have a large spreadhsheet (21k rows) that creates alot of #N/A as designed. I would like to change the #N/A to zero removing the formula. This is not a one time deal as I need to re-create the spreadsheet weekly. I do not want to value out the entire spreadsheet. |
how to remove #N/A
Minor point. =TRUE is unneeded:
=IF(ISNA(VLOOKUP(A2,$B$2:$B$15,1,0)),"0",VLOOKUP(A 2,$B$2:$B$15,1,0)) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "singh" wrote in message ... Hi Charlie Use below formula: =IF(ISNA(VLOOKUP(A2,$B$2:$B$15,1,0))=TRUE,"0",VLOO KUP(A2,$B$2:$B$15,1,0)) Change your reference cells. This formula will convert all your #N/A values to Zero There is one more way of doing: Ctrl+H Replace what : #N/A Replace with : 0 "Charlie_Brown" wrote: I have a large spreadhsheet (21k rows) that creates alot of #N/A as designed. I would like to change the #N/A to zero removing the formula. This is not a one time deal as I need to re-create the spreadsheet weekly. I do not want to value out the entire spreadsheet. |
All times are GMT +1. The time now is 05:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com