Solved

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

Posted on 2011-02-20
3
377 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 earned 125 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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