• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

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
0
PTRUSCOTT
Asked:
PTRUSCOTT
1 Solution
 
synetronCommented:
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
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
RunriggerCommented:
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.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now