Hi,

I have a chart I am trying to create, however, i not want to plot the
empty ranges.
I have used the NA() option in my formulas so that it does not get
plotetd on my graphs but it is not working.

How can I fix this error?

Hi,

What exactly does 'not working' mean? What type of chart?

The use of NA() in a line chart will only suppress the displaying of a data
marker. The line will be interpolated between real data points.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
wrote in message
...
Hi,

I have a chart I am trying to create, however, i not want to plot the
empty ranges.
I have used the NA() option in my formulas so that it does not get
plotetd on my graphs but it is not working.

How can I fix this error?

Hi Andy,

I have sometimes felt that this should be labelled as a bug in Excel. Column
charts correctly leave gaps, but line and scatter charts do not. As you say,
only the markers are not plotted, but the lines are still there.

A possible workaround (which works only in Excel 2007) is to use a 3d line
chart with angles and perspective set to zero. This correctly leaves a gap
in the middle, and looks pretty close to a 2d line chart.

Ed Ferrero
www.edferrero.com

Hi,

What exactly does 'not working' mean? What type of chart?

The use of NA() in a line chart will only suppress the displaying of a
data marker. The line will be interpolated between real data points.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel

On Jul 11, 8:44*pm, "Ed Ferrero" wrote:
Hi Andy,

I have sometimes felt that this should be labelled as a bug in Excel. Column
charts correctly leave gaps, but line and scatter charts do not. As you say,
only the markers are not plotted, but the lines are still there.

A possible workaround (which works only in Excel 2007) is to use a 3d line
chart with angles and perspective set to zero. This correctly leaves a gap
in the middle, and looks pretty close to a 2d line chart.

Ed Ferrerowww.edferrero.com



Hi,

What exactly does 'not working' mean? What type of chart?

The use of NA() in a line chart will only suppress the displaying of a
data marker. The line will be interpolated between real data points.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel- Hide quoted text -

- Show quoted text -

Hi Andy,

I am trying to create a column graph.
I have made my formula to return NA() rather then a "" value if the
result is 0.
However, even though I am using NA() which supposedly shoul not plot
on a graph the data range is still plotting as 0.

Would you be able to help me with this, as I am trying to create a
dynamic set of charts and it is difficult to always manually select
the correct data ranges.

Thanks for your help!!

Hi,

Sounds like you want the data point removed completely if it is NA.
As I said the NA only suppresses the data marker.

Easiest way to remove the data point from the chart is to hide the row. You
can use auto filter to do this.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
wrote in message
...
On Jul 11, 8:44 pm, "Ed Ferrero" wrote:
Hi Andy,

I have sometimes felt that this should be labelled as a bug in Excel.
Column
charts correctly leave gaps, but line and scatter charts do not. As you
say,
only the markers are not plotted, but the lines are still there.

A possible workaround (which works only in Excel 2007) is to use a 3d line
chart with angles and perspective set to zero. This correctly leaves a gap
in the middle, and looks pretty close to a 2d line chart.

Ed Ferrerowww.edferrero.com



Hi,

What exactly does 'not working' mean? What type of chart?

The use of NA() in a line chart will only suppress the displaying of a
data marker. The line will be interpolated between real data points.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel- Hide quoted text -

- Show quoted text -

Hi Andy,

I am trying to create a column graph.
I have made my formula to return NA() rather then a "" value if the
result is 0.
However, even though I am using NA() which supposedly shoul not plot
on a graph the data range is still plotting as 0.

Would you be able to help me with this, as I am trying to create a
dynamic set of charts and it is difficult to always manually select
the correct data ranges.

Thanks for your help!!

On Jul 14, 5:34*pm, "Andy Pope" wrote:
Hi,

Sounds like you want the data point removed completely if it is NA.
As I said the NA only suppresses the data marker.

Easiest way to remove the data point from the chart is to hide the row. You
can use auto filter to do this.

Cheers
Andy

--

Andy Pope, Microsoft MVP - wrote in message

...
On Jul 11, 8:44 pm, "Ed Ferrero" wrote:





Hi Andy,

I have sometimes felt that this should be labelled as a bug in Excel.
Column
charts correctly leave gaps, but line and scatter charts do not. As you
say,
only the markers are not plotted, but the lines are still there.

A possible workaround (which works only in Excel 2007) is to use a 3d line
chart with angles and perspective set to zero. This correctly leaves a gap
in the middle, and looks pretty close to a 2d line chart.

Ed Ferrerowww.edferrero.com

Hi,

What exactly does 'not working' mean? What type of chart?

The use of NA() in a line chart will only suppress the displaying of a
data marker. The line will be interpolated between real data points.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel- Hide quoted text -

- Show quoted text -

Hi Andy,

I am trying to create a column graph.
I have made my formula to return NA() rather then a "" value if the
result is 0.
However, even though I am using NA() which supposedly shoul not plot
on a graph the data range is still plotting as 0.

Would you be able to help me with this, as I am trying to create a
dynamic set of charts and it is difficult to always manually select
the correct data ranges.

Thanks for your help!!- Hide quoted text -

- Show quoted text -

Hi Andy,

Thanks for your reply.
Is there any other way of doing this?
With the way the table is set up, is that the data point might be NA
in some columns but is a value in another.

For example:
Class 1 Class 2 Class 3

John 2 1 NA
Michelle 3 NA 6
Lisa NA 6 5

I have 3 charts, one for Class 1 one for Class 2 and one for Class 3.
For example for class 1 John would be plotted with 2, and Michelle
with 3 and I would not want a data point at all for Lisa.
I can't exactly hide Lisa's row as she is participating in Class 2 and
Class 3 that need to be plotted on their respective graphs.

It would be difficult to separate this table to separate tables as in
reality this table is huge and it would not be practical to have 3
tables rather then 1.

Do you know another solution to this as hiding rows will not work??

Thanks again for all of your help!!