Is this a bug in excel 2007 charting

[Brian Murphy]

This one is really throwing me for a loop.
Paste the data below on a blank sheet, and select it.
This, of course, has to be done with Excel 2007.
Use the ribbon to insert an XY scatter chart with straight lines
connecting the points.
What I get is a chart without any visible data series.
When I delete the pair of zeros in the 4th line from the bottom,
suddenly the two data series appear.
Or changing the chart type to "smooth lines" makes them appear.
I would appreciate it if someone could confirm what I'm getting.
This had me going until nearly 1 am last night.

Thanks,

Brian

0 0 0
0 0.5 -0.5
2 0.5 -0.5
2 0 0
0 0 0

2 0 0
2 0.5 -0.5
3 0.5 -0.5
3 0 0
2 0 0

4 0 0
4 0 0
0 0
0 0
4 0 0

 

[Patrick Molloy]

bizarre, and it seems file in Excel 2003

 

[Martin Brown]

This one is really throwing me for a loop.
Paste the data below on a blank sheet, and select it.
This, of course, has to be done with Excel 2007.
Use the ribbon to insert an XY scatter chart with straight lines
connecting the points.
What I get is a chart without any visible data series.

I get all the dots but without the lines joining them up.

When I delete the pair of zeros in the 4th line from the bottom,
suddenly the two data series appear.
Or changing the chart type to "smooth lines" makes them appear.
I would appreciate it if someone could confirm what I'm getting.
This had me going until nearly 1 am last night.

Thanks,

Brian

0 0 0
0 0.5 -0.5
2 0.5 -0.5
2 0 0
0 0 0

2 0 0
2 0.5 -0.5
3 0.5 -0.5
3 0 0
2 0 0

4 0 0
4 0 0
0 0
0 0
4 0 0

Deleting the blank lines makes the graph lines appear. I suspect that an
XY graph may not like having blank values in the X column.

There are plenty of worse bugs in XL charting. My personal favourite was
the two ticks at 10^8 in log scale plots in the out of the box version.

Regards,
Martin Brown

 

[Peter T]

What do you get if you do (with the chart selected) -

Design, Select Data, Hidden and Empty cells, select Zero

Regards,
Peter T

 

[Bernard Liengme]

I followed your instructions except that I used a chart with markers and
lines and got the expected result: the B data series was displayed with
lines while the C was only markers. Then I formatted the C data series and
in Chart Tools | Design | Select Data I opened the Hidden & Empty Cells
dialog where I used Connect Data Points with Lines. Now both data series are
displayed with markers and lines.


Next I did as you said and made a chart with just lines (Unsmoothed). As
expected the C series was invisible but its legend told me it was present.
Then I again used Chart Tools | Design | Select Data and opened the Hidden
& Empty Cells dialog where I used Connect Data Points with Lines. Now both
data series are displayed with markers and lines.

From my view point, Excel is working as advertized.
best wishes

 

[Jon Peltier]

From my view point, Excel is working as advertized.

Maybe, I don't know. But it's different than in 2003. And past
performance is an important piece of advertising.

Brian -

Your problem seems to be those two blank X values. However, I used
different data:

1 2 3
2 3 4
3 4 5

5 5 6
6 6 7

8 7 8
9 8 9
9 10
10 11
12 11 12

and got exactly what I expected in both 2003 and 2007. For some reason
it doesn't like your data. If I select the first two blocks plus the
first line of the third block, I get the expected display, but if I
select the first two lines of the third block, I lose the connecting lines.

Even more intriguing, if I use your whole data set except for the last
line, and I change your blanks and fours in the first column of the
third block so that the blanks are in the 2d and 3d row, it draws as
expected.

If that's a feature, I'd love to sneak a peek at the spec.

- Jon

 

[Brian Murphy]

When I change that option setting, the lines do appear.

Brian

 

[Brian Murphy]

I guess I'm glad to hear it's not just me.

There's more to the wierdness. The default data series line thickness
in my Excel 2007 is 2.25 points. If edit one of the invisible series
and reduce this to 1.25 points, the series becomes visible. However,
in print preview it's gone, and printing the chart to a pdf file it's
gone. If I reduce it to 0.25 points, it does show up in print
preview, but not in a pdf file. That's what had me spending hours
looking for some chart formatting setting that was triggering this. I
finally just stumbled onto the thing with the zeros.

Are we having fun, yet?

Brian

 

[Peter T]

Your problem seems to be those two blank X values. However, I used
different data:

Normally a set of XY values would not include a Y without a corresponding X,
even if the Y was zero - ?

I think easier to see what's going on to start with a small single XY series

x y
4 0
0
0
4 0

Change each of the values in turn, including the missing X values. Also
toggle 'Show empty cells as Gaps/Zeros'

If anything maybe the 2007 chart displays more logically than the 2003
version. Even so, the data should not include an unmatched XY pair.

Regards,
Peter T

 

[Brian Murphy]

I think you're right, Peter.

In a little bit of testing just now, the wierdness seems to stop if I
put values in those two empty cells of the X data column. I need to
check my code that creates these data sets and fix whatever is causing
the empty cells in the X data column. Could be I'm guilty of not
seeing the forest through the trees.

Cheers,

Brian