Charts - formulas return #N/A but they still get charted

[bjw]

Here is my formula:
=IF(ISBLANK('Detail Qry'!E25),NA(),'Detail Qry'!E25)

It returns a #N/A like it is supposed to be it is still being charted on my
chart. I have tried ISERROR and ISERR but they don't work either.
I am actually using this formula to pull in a row of dates and then column A
is a work center. So really what i have is a crosstab.

Column A Column B Column C
5/29/07 5/30/07
L1 390 cs 502 cs
B4 425 cs 618 cs

When i run the query from the referenced sheet i will get the above crosstab
of data. But it will not always be the same dates and not always the same
work center in Column A. And there will not always be the same number of
rows or columns that come back. I have used Dynamic charts in the past but
not for something like this (not sure if it is possible). I only want to
chart what comes back in the query. Any help would be appreciated.

 

[Jon Peltier]

You didn't admit to using any particular chart type. #N/A is ignored only in
XY or line chart series, but will still be plotted as zero in other chart
types. In these types it's better to use "" so at least the label does not
appear.

- Jon

 

[bjw]

It is a line chart.

You didn't admit to using any particular chart type. #N/A is ignored only in
XY or line chart series, but will still be plotted as zero in other chart
types. In these types it's better to use "" so at least the label does not
appear.

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

 

[Jon Peltier]

What version of Excel? Where are the #N/A in relation to the rest of the
data (i.e., paste in a sample)?

The #N/A doesn't mean it will be completely ignored, just that no point will
be placed on the chart. Suppose your data looks like this:

5/29/2007 5/30/2007
L1 390 cs 502 cs
B4 425 cs 618 cs
#N/A #N/A #N/A
J3 270 cs 485 cs

If you plot by rows, you will get a series named #N/A which appears in the
legend but not in the chart. If you plot by columns, you will get four
category labels: L1, B4, #N/A, and J3, and the #N/A label will have no
points plotted above it.

- Jon

 

[bjw]

Excel 2003

5/29/2007 5/30/2007 #N/A
L1 390 cs 502 cs #N/A
B4 425 cs 618 cs #N/A
J3 270 cs 485 cs #N/A
#N/A #N/A #N/A #N/A

I need to have the dates as x axis and then the qty's as the data (Y axis).
Then each line in the graph represents a workcenter (L1, B4, etc). But i
don't want the NA's to show up. Is there a way with dynamic charts to get
that to work since the data is not always going to be the same?

 

[Jon Peltier]

Using dynamic ranges, you can create a chart whose series grow and shrink as
the range becomes longer and shorter. But you can't use dynamic ranges to
vary the number of series in the chart. That would require some VBA.

One question: the values in the table are appended with " cs". Is this
actual text that's appended to the values? If so, they will plot as zeros,
because Excel interprets mixed alphanumerics as text with zero value.

Assuming the data starts in cell A1, you could define a name that includes
the rows and columns up to the errors. Go to Insert menu > Name > Define:

Name: ChartDataRange
Refers To:
=offset($A$1,0,0,COUNTA($A$1:$A$36)-SUM(IF(ISNA($A$1:$A$36),1,0)),COUNT($1:$1)+1)

I arbitrarily chose A1:A36; pick a range long enough to get all the values
you may ever need.

Update the data, then run this macro:

Sub UpdateChart()
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("ChartDataRange"), _
PlotBy:=xlRows
End Sub

- Jon

 

[linda]

Hi!
how do you solve your problem?i face it too right now and dont know how to
do..
i've tried jon's suggestion,but still doesn't work..