charting that ignores non numeric/date cells

  • Thread starter Thread starter Giacomo
  • Start date Start date
G

Giacomo

Is it possible for charting to ignore parts of data
ranges which are neither numbers nor dates? (So far as I
can tell it reads them as 0s.)
Regards,
giacomo
 
Giacomo -

You should delete the non numeric cells. Excel can at least skip a blank
cell. It can't skip an apparently blank cell produced by "" in a
formula, because "" is a text entry even though it's zero characters
long. If you change "" to NA() in a formula, you get an ugly error in
the sheet, but the chart doesn't plot it.

If you're stuck displaying the text within the data table, you could
make another table elsewhere, linked to this one, but without the text.
Use the new table as your chart's data range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
thanks Jon,
I've (almost completely) succeded via filtering out of
the data range blank cells (nonblank). I don't like the
fact that it hides all the rows corresponding to the
blank column cells in the data range and I haven't been
able to fix that (help!). But charting wise it works
perfectly.
By the way substituting "" with na() does not work as the
chart still puts the na values on the axis.
Thanks for your time,
gicomo
 
Giacomo -

What you could do is filter the data, copy it, use Paste Special from
the Edit menu to Paste-Link it elsewhere in the worksheet (where it
won't be seen), and use this new range for the chart's data source.
Since you linked it, updating the original data will change the chart.

What kind of chart are you making? The NA() doesn't appear on a value
axis, but it does on a category axis. So a line chart will have an #N/A
category, but with no marker if the Y value is also #N/A.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top