Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-20
3
Medium Priority
?
381 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 (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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

609 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