Excel 2000 Chart Problems

[Bill]

I have a very simple worksheet (12 data lines, 6 columns) and have a
line chart displaying 3 series of data point results.

Problem 1: Series 1 plots my 12 data points perfectly! Series 2 plots
only 2 of the 12 data points and Series 3 plots only 3 data points!
I'm at wits end trying to find out what is wrong! Please note that
some cells are blank in 2 of the series because there was no reading
was taken, however, Series 1 plotted perfectly even with one blank
cell! ALL data points are within the range limits specified!

Problem 2: The legend on my chart (3 lines of text, one for each
series) are out-of-sequence, that is, I need to reorder it so that the
series are in order 1, 2, 3 instead of 3, 2, 1.

Can someone please resolve my delema? I've plotted many graphs before
and never encountered this much of a problem. Thanks!

Bill

 

[Ed Ferrero]

Hi Bill,

I have a very simple worksheet (12 data lines, 6 columns) and have a
line chart displaying 3 series of data point results.

Problem 1: Series 1 plots my 12 data points perfectly! Series 2 plots
only 2 of the 12 data points and Series 3 plots only 3 data points!
I'm at wits end trying to find out what is wrong! Please note that
some cells are blank in 2 of the series because there was no reading
was taken, however, Series 1 plotted perfectly even with one blank
cell! ALL data points are within the range limits specified!

Check that the data does not contain numbers stored as text.
Try using a custom number format like
0;-0;0;[Red]@
to quickly check that you do not have text values (they will show up in
red).

Problem 2: The legend on my chart (3 lines of text, one for each
series) are out-of-sequence, that is, I need to reorder it so that the
series are in order 1, 2, 3 instead of 3, 2, 1.

Click on a data series to select it. See the formula in the formula bar?
It will look something like this:

=SERIES(Label,X-Range,Y-Ranges,Series No)

Label is the series label, can be a cell reference or a label enclosed in
quotes

X-Range is a range, usually given in the form Sheet1!$C$5:$M$5 but it could
also be a series of numbers such as {5,6,7} or a reference to a range name
such as Sheet1!Sales

Y-Range is similar

Series No is an integer giving the z-order of the series

You can change the Series No to change the order of the series in the
legend, Try changing 3 to 1 and see what happens.

Ed Ferrero
www.edferrero.com

 

[Bill]

Hi Bill,

I have a very simple worksheet (12 data lines, 6 columns) and have a
line chart displaying 3 series of data point results.

Problem 1: Series 1 plots my 12 data points perfectly! Series 2 plots
only 2 of the 12 data points and Series 3 plots only 3 data points!
I'm at wits end trying to find out what is wrong! Please note that
some cells are blank in 2 of the series because there was no reading
was taken, however, Series 1 plotted perfectly even with one blank
cell! ALL data points are within the range limits specified!

Check that the data does not contain numbers stored as text.
Try using a custom number format like
0;-0;0;[Red]@
to quickly check that you do not have text values (they will show up in
red).

Problem 2: The legend on my chart (3 lines of text, one for each
series) are out-of-sequence, that is, I need to reorder it so that the
series are in order 1, 2, 3 instead of 3, 2, 1.

Click on a data series to select it. See the formula in the formula bar?
It will look something like this:

 =SERIES(Label,X-Range,Y-Ranges,Series No)

Label  is the series label, can be a cell reference or a label enclosedin
quotes

X-Range  is a range, usually given in the form Sheet1!$C$5:$M$5 but it could
also be a series of numbers such as {5,6,7} or a reference to a range name
such as Sheet1!Sales

Y-Range  is similar

Series No is an integer giving the z-order of the series

You can change the Series No to change the order of the series in the
legend, Try changing 3 to 1 and see what happens.

Ed Ferrerowww.edferrero.com

Ed,

I apologize, I sent a reply to the author instead of to this site, so
if you get it, disregard. I have fixed my Problem 2, but I should give
you some more informatiom than I did previously.

In the formula bar you referenced, this is what's there now:

=SERIES("~2 Hrs. Post-Breakfast",Data!$A$3:$A$15,Data!$C$3:$C$15,1)
=SERIES("~2 Hrs. Post-Lunch",Data!$A$3:$A$15,Data!$C$3:$C$15,2)
=SERIES("~2 Hrs. Post-Dinner",Data!$A$3:$A$15,Data!$C$3:$C$15,3)

The worksheet (13 data rows, 7 columns) has three data series. Column
1 is the "Date". Now, Series 1 plots only 3 of 13 data points; Series
2 plots 10 of the 13 data points; Series 3 plots 10 of 13 data points!
Some cells are blank in two of the series because no reading was
taken. The data cells are all formatted for "Number", per your earlier
instructions. My Y-Axis limits are 40 to 240.

When I view the chart and select a point in Series 2, six of the data
points appear outside of the chart area at the top! Two of them are
plotted in the wrong place; two of them are in the correct place. For
example, one data point indicates (in a drop down window) that the
value is "79", which is correct, but is plotted as "202"! Another data
point has the value of "83", which is correct, but is plotted as
"230"! What could cause this?

Bill

 

[Ed Ferrero]

Hi Bill,

When I view the chart and select a point in Series 2, six of the data
points appear outside of the chart area at the top! Two of them are
plotted in the wrong place; two of them are in the correct place. For
example, one data point indicates (in a drop down window) that the
value is "79", which is correct, but is plotted as "202"! Another data
point has the value of "83", which is correct, but is plotted as
"230"! What could cause this?

Beats me!

But it does sound interesting. If you want, send me a copy of the workbook
and I'll take a look at it.

BTW to reply to me personally you need to remove the bit that says _remove_
from my email address

Ed Ferrero
www.edferrero.com