Excluding Error Values

[Guest]

In an Excel chart I have data linked to an MS Access query. The number of
rows and columns for the query may vary. To cover this, I have pasted over a
larger area of cells that I am likely to receive rows/columns in Access. I
now want to plot this data on a chart.

When selecting the data source for the graph as the full potential range of
cells from the Access query, the graph plots the #N/A values which result
from the cell range being large than the Access query. How can I get the
chart to ignore the #N/A values like it can be set to ignore zero values?

 

[Jon Peltier]

First, the chart cannot be set to ignore zero values, only true blanks
(i.e., not a formula that returns a zero length string ""). Second, if it's
a marker-type chart (line or XY), the #N/A values are not plotted, although
the series internally counts them as points. Third, you could use dynamic
ranges to define the data, using something like COUNT(Sheet1!$A:$A) to
determine how many rows have values instead of errors (presumably it's all
values above a cutoff row and all errors below).

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon