How to Remove text values from graphs

J

JAbels001

I have several graphs put together from data on another tab, however
sometimes this data will come up "N/a" or "No Data" which the graph
recognizes as ZERO. Can this be avoided? I only want the graph to graph
numberical values. Please assist. Thanks!
 
L

Luke M

Have your formulas return the #N/A error
Example:
=IF(A1=B1,A1+B1,NA())

XL graphs ignore the NA error, as it is, by definition, not applicable.
 
J

JAbels001

No can do... They must return the values they are.. "N/a" or "No Data" must
come up in order for my workbook to meet other criteria... Suggestions?
 
L

Luke M

Could you setup a hidden table somewhere that duplicates your original data
using formulas similar to:
=IF(ISNUMBER(A1),A1,NA())

And then create your graph from this hidden table?
 
J

Jon Peltier

As Luke points out, it's very easy to set up multiple data ranges. They can
all be linked to a central table, so they update when the underlying data
changes. The drawbacks? You use extra worksheet space. This hasn't been a
practical issue since we stopped using COBOL in the mid-80s. The benefits is
that you can make ranges which are optimized for charting, display, and
further analysis, and save the time it might take to try to make one range
fit all needs.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top