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.

=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.

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

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.

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.