Line chart with #N/A values

[dF]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

In the previous version of Excel, in which I originally created the graph in question, Excel would leave a gap in a scatterplot for a cell that contained "#N/A". The current version handles this differently, now it draws a straight line from the previous valid point to the next one. Unfortunately, I want it to leave the gaps - that's what it was supposed to look like.

So, I found another thread that called out an option for telling Excel to skip 0 values - which I could adapt to. Unfortunately, the option to have it skip the zero values was grayed out when I selected my chart and went to that option.

So, here I am stuck with a broken plot that used to work just fine. Can anyone tell me how to get Excel to skip #N/As or tell me how to get the grayed out option to skip 0 values activated?

 

[Mike Middleton]

dF -

Mac Excel 2008 will leave a gap in a Line chart or a line-connected XY
(Scatter) chart if a Y-data-point cell is clear (Edit | Clear Contents).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[dF]

dF -

Mac Excel 2008 will leave a gap in a Line chart or a line-connected XY
(Scatter) chart if a Y-data-point cell is clear (Edit | Clear Contents).

- Mike Middleton

Mike,
The cell contents are being generated by a formula. How can the formula make the cell appear to be blank?

 

[Mike Middleton]

dF -

A formula can make a cell appear to be blank by returning an empty string,
two double-quote marks, ="". But the cell is not empty, so you won't get a
gap as you would if the cell is truly clear, i.e., Edit | Clear Contents.

In both Mac Excel 2004 and Mac Excel 2008, select the chart, and choose
Excel | Preferences | Chart to see: Empty cells plotted as (1) Not plotted
(leave gaps), (2) Zero, (3) Interpolated. These alternatives apply only to
empty cells, not cells containing an empty string or NA().

I don't know what you're referring to by "the option to have it skip the
zero values was grayed out when I selected my chart and went to that
option." What is the menu path to see that option?

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

Mike,
The cell contents are being generated by a formula. How can the formula
make the cell appear to be blank?

dF -

Mac Excel 2008 will leave a gap in a Line chart or a line-connected XY
(Scatter) chart if a Y-data-point cell is clear (Edit | Clear Contents).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Carl Witthoft]

The answer is: you can't get this done automatically unless you write a
macro which invokes the command to ClearAll in the cells of interest.

It was things like that which made me very happy indeed to invest the
time to learn to use GnuPlot or the plotting routines in R .

 

[Jose]

Mike I also have problems with this. I have a formula that makes some cell
appear to be blank (with ="") But the cell is not empty because of the
formula. My problem is that because of this in the chart that I'm making
these cells go to 0, and I would like to leace a gap.

Thank you

 

[Bob Greenblatt]

Mike I also have problems with this. I have a formula that makes some cell
appear to be blank (with ="") But the cell is not empty because of the
formula. My problem is that because of this in the chart that I'm making
these cells go to 0, and I would like to leace a gap.

Thank you

You answered it in the thread title. Instead of ="", use =NA(). NA values
are not plotted.