Lines in chart when no data is present

[RW]

I have found the trick to prevent 0's from showing in a chart when the data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to the
next datapoint instead of showing a gap. I've looked at Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is
selected.

The data is being charted on a secondary Y axis if this makes a difference.
Please help. I need to have gaps.

 

[ShaneDevenshire]

Hi RW,

I don't believe there is a straight forward way to do this.

 

[Jon Peltier]

Nothing straightforward, but Andy Pope has a technique for this:

http://andypope.info/charts/brokenlines.htm

Other alternatives include deleting the cells to form a gap (via macro which
can recreate the formula.

- Jon

 

[RW]

According to the following link,
http://office.microsoft.com/en-us/excel/HA010346081033.aspx

it states:
"By default, Excel does not plot a data point for an empty cell, which
causes a break in a data series. "

and even shows an example. Why does it not work for my data?

 

[Jon Peltier]

Because you don't have empty cells, you have formulas that return "", which
is a short piece of text. Unfortunately there is no function that simulates
a blank cell.

- Jon

 

[ShaneDevenshire]

By the way I also have a way to do it but its not pretty. There is also a
code solution which might be simplier but you would have to run it anytime
the data changes.

 

[Tushar Mehta]

That's the way MS designed the charting capability. If you are plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/software/na_discontinuity/index.html

It used to work with Line charts until MS made a change that messed up how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Tushar -

In which version of Excel did Microsoft make the change?

- Jon

 

[Tushar Mehta]

Hi Jon,

I just retested versions of XL from 2000 to 2007: created a Line chart and
then tried to format a single point to 'no line'. As of today, in 2002
(SP2?) it still shows the line. I am reasonably sure that was the same
behavior with 2003 when it first came out but today the line disappeared for
the single point. So, it was probably fixed in some 2003 SP.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

This is vaguely familiar (everything before yesterday is only vaguely
familiar).

I just tested Excel 2003 SP2, and the line can be formatted as no line
without a problem. I recall that the issue was that instead of making the
line segment vanish, what Excel did was draw the line from the previous
point to the subsequent point, so the point in the middle was still there
but not connected to either adjacent point. I wonder if that was how Excel
2003 came out, and they fixed it (i.e., changed it back) in an SP.

- Jon

 

[OssieMac]

I'm not sure if I am on the right track with the problem but if I am, it
appears to me that you only have to select the chart, select Tools, Options,
Chart Tab and set the Plot Empty Cells option.

 

[ShaneDevenshire]

Hi,

Well I test 2000 and it worked, but 2002 SP3 did not.

 

[Jon Peltier]

That only works with real blank cells, not cells in which a formula returns
"" or any other value.

- Jon

 

[ShaneDevenshire]

I should clarify, I test the idea of selecting the line segment that is
interpolated across the missing point and changed its color to None and it
worked in my copy of 2000 but not in 2002. These cells contained =NA(), not
a blank.

An alternate way to do this is to set the line color to match the background
color rather than to set it to None. With this approach there are issues,
but they can all be solved.

 

[Jon Peltier]

I know at some point this functionality was broken. However, it's fixed
again by Excel 2003 SP2; you can change a line segment to None, whether it
connects two regular points, spans a blank, or spans NA().

- Jon