?
Solved

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

Posted on 2011-02-20
3
Medium Priority
?
384 Views
Last Modified: 2012-05-11
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
Comment
Question by:PTRUSCOTT
3 Comments
 
LVL 2

Expert Comment

by:synetron
ID: 34940926
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
 
LVL 50

Accepted Solution

by:
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

by:Runrigger
ID: 34941079
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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.

Join & Write a Comment

Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
This article presents several of my favorite code snippets.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question