asked on

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

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

synetron

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

you may want to start with the origin, not the destination:

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

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Runrigger

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

=IFERROR(<<YOUR FORMULA>>,0)

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