Solved

# Microosoft Excel 2007 How to Convert a Column with some #NA values to numerics

Posted on 2011-02-20
Medium Priority
384 Views
Dear Experts,
I have the following annoying problem in Excel 2007.

I have a column of data that has some valid numeric values but where the value is missing for that particular row the cell is a text value "#NA".  I want to create a column that is a copy of this column except that I want all the "#NA" text values to be numeric zeroes.

I want to write a complex function call that will detect if the value in the cell is #NA and if so convert the value in the new cell to zero, otherwise leave the numeric value unchanged.   This looks like it should be easy but its a torture process.  Thanks Bill.

I have tried all sorts of variations using the IF function and the EXACT function but to no avail.  Can anyone help.
Best Wishes,

PT
0
Question by:PTRUSCOTT

LVL 2

Expert Comment

ID: 34940926
what type of column is the original, generating the #NA?

as in this suggestion http://www.mrexcel.com/td0060.html

the reason i suggest starting with the origin is that #NA is an error value and as such may not be as easily referenced as a variable in your IF functions
0

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 500 total points
ID: 34940955
Hello,

you could use this formula in another column

=if(iserror(A1),0,A1)

copy down all rows of  data.

Or,

=if(isnumber(A1),A1,0)

The first will exchange any error value for a zero, but will show text if the cell in column A is text. The second formula will only show numeric values, so if column A contains text, the result will be zero.

cheers, teylyn
0

LVL 11

Expert Comment

ID: 34941079
as you are using Excel 2007, you can simply use the following;

if the result of "YOUR FORMULA" is #N/A, then the above will return ZERO, otherwise, the result of your formula will be returned.

0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.