entering matching data for line charts

I'm sure this is naive. In Excel 2003 I'm trying to construct a line graph
in which there is is more than one set of matching x and y values. For
example, the data looks like this:
x1 y1 x2 y2
1/1/08 1 1/15/08 5
2/1/08 2 2/15/08 6
3/1/08 3 3/15/08 7
4/1/08 4

If I were drawing a scatter plot, I could enter matching x and y values, but
with a line graph I can plot the data only by rearranging it to look like
this.
x y1 y2
1/1/08 1
1/15/08 6
2/1/08 2
2/15/08 7
3/1/08 3
3/15/08 8
4/1/08 4

I'm sure there must be a simpler way, particularly if one has multiple data
sets. Any help would be appreciated.

Thanks,
johnston

If you are plotting Y against X values, XY (confusingly named as scatter) is
the option you should be using (and you can, of course, include lines,
either with or without point markers).
Line graph (named to cause confusion) is not usually the right option in
those circumstances.
--
David Biddulph

"johnston" wrote in message
...
I'm sure this is naive. In Excel 2003 I'm trying to construct a line
graph
in which there is is more than one set of matching x and y values. For
example, the data looks like this:
x1 y1 x2 y2
1/1/08 1 1/15/08 5
2/1/08 2 2/15/08 6
3/1/08 3 3/15/08 7
4/1/08 4

If I were drawing a scatter plot, I could enter matching x and y values,
but
with a line graph I can plot the data only by rearranging it to look like
this.
x y1 y2
1/1/08 1
1/15/08 6
2/1/08 2
2/15/08 7
3/1/08 3
3/15/08 8
4/1/08 4

I'm sure there must be a simpler way, particularly if one has multiple
data
sets. Any help would be appreciated.

Thanks,
johnston

Dave, thank you for the reply. If I use a scatter plot, is there a way to
adjust the x-axis so that the major divisions align with the first of each
month as opposed to constant intervals? In previous attempts, when I set the
divisions in a scatter plot for example to 30 days, they fall at odd
intervals such as 1/1/08, 1/30/08, etc.

"David Biddulph" wrote:

If you are plotting Y against X values, XY (confusingly named as scatter) is
the option you should be using (and you can, of course, include lines,
either with or without point markers).
Line graph (named to cause confusion) is not usually the right option in
those circumstances.
--
David Biddulph

"johnston" wrote in message
...
I'm sure this is naive. In Excel 2003 I'm trying to construct a line
graph
in which there is is more than one set of matching x and y values. For
example, the data looks like this:
x1 y1 x2 y2
1/1/08 1 1/15/08 5
2/1/08 2 2/15/08 6
3/1/08 3 3/15/08 7
4/1/08 4

If I were drawing a scatter plot, I could enter matching x and y values,
but
with a line graph I can plot the data only by rearranging it to look like
this.
x y1 y2
1/1/08 1
1/15/08 6
2/1/08 2
2/15/08 7
3/1/08 3
3/15/08 8
4/1/08 4

I'm sure there must be a simpler way, particularly if one has multiple
data
sets. Any help would be appreciated.

Thanks,
johnston

Johnston -

There is a simpler rearrangement of the data:

x y1 y2
1/1/08 1
2/1/08 2
3/1/08 3
4/1/08 4
1/15/08 6
2/15/08 7
3/15/08 8


A line chart internally sorts the data by date in a line chart with a date
axis. The chart at first will not connect the points with lines, because by
sorting it introduces blanks within the ranges of Y values. Go to Tools menu
Options Chart tab, and choose interpolate in the top section.

The other thing you could do is create the chart with the X1-Y1 data, add
the second series with the X2-Y2 data, convert the second series to an XY
series, and format the XY series to plot on the primary axis. The XY series
will plot nicely on the line chart's date axis.

Note, this XY-Line combination chart will not work in 2007. I've reported
the bug and can only hope it's fixed in Excel 14.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"johnston" wrote in message
...
I'm sure this is naive. In Excel 2003 I'm trying to construct a line
graph
in which there is is more than one set of matching x and y values. For
example, the data looks like this:
x1 y1 x2 y2
1/1/08 1 1/15/08 5
2/1/08 2 2/15/08 6
3/1/08 3 3/15/08 7
4/1/08 4

If I were drawing a scatter plot, I could enter matching x and y values,
but
with a line graph I can plot the data only by rearranging it to look like
this.
x y1 y2
1/1/08 1
1/15/08 6
2/1/08 2
2/15/08 7
3/1/08 3
3/15/08 8
4/1/08 4

I'm sure there must be a simpler way, particularly if one has multiple
data
sets. Any help would be appreciated.

Thanks,
johnston

Jon,
Your arrangement of the data is much simpler. Thank you. However, I didn't
quite understand your instructions. Do I create a line chart with the x1 y1
data and add the y2 data as a new series? (Excel doesn't allow me to enter
the x2 data.) Can you explain more fully what to do?

Thanks,
johnston

"Jon Peltier" wrote:

Johnston -

There is a simpler rearrangement of the data:

x y1 y2
1/1/08 1
2/1/08 2
3/1/08 3
4/1/08 4
1/15/08 6
2/15/08 7
3/15/08 8


A line chart internally sorts the data by date in a line chart with a date
axis. The chart at first will not connect the points with lines, because by
sorting it introduces blanks within the ranges of Y values. Go to Tools menu
Options Chart tab, and choose interpolate in the top section.

The other thing you could do is create the chart with the X1-Y1 data, add
the second series with the X2-Y2 data, convert the second series to an XY
series, and format the XY series to plot on the primary axis. The XY series
will plot nicely on the line chart's date axis.

Note, this XY-Line combination chart will not work in 2007. I've reported
the bug and can only hope it's fixed in Excel 14.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"johnston" wrote in message
...
I'm sure this is naive. In Excel 2003 I'm trying to construct a line
graph
in which there is is more than one set of matching x and y values. For
example, the data looks like this:
x1 y1 x2 y2
1/1/08 1 1/15/08 5
2/1/08 2 2/15/08 6
3/1/08 3 3/15/08 7
4/1/08 4

If I were drawing a scatter plot, I could enter matching x and y values,
but
with a line graph I can plot the data only by rearranging it to look like
this.
x y1 y2
1/1/08 1
1/15/08 6
2/1/08 2
2/15/08 7
3/1/08 3
3/15/08 8
4/1/08 4

I'm sure there must be a simpler way, particularly if one has multiple
data
sets. Any help would be appreciated.

Thanks,
johnston

Create the line chart with the X1-Y1 data. Copy the X2-Y2 data, select the
chart, and paste special to add the data as a new series, with categories in
the first column. Change the series to an XY type (right click - chart
type - select a line type), and move it back to the primary axis. Note to
Excel 2007 users: the second series will lose its X values by creating a
line chart series, so when you convert it to an XY series you will have to
add the X2 data as its X values..

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"johnston" wrote in message
...
Jon,
Your arrangement of the data is much simpler. Thank you. However, I
didn't
quite understand your instructions. Do I create a line chart with the x1
y1
data and add the y2 data as a new series? (Excel doesn't allow me to
enter
the x2 data.) Can you explain more fully what to do?

Thanks,
johnston

"Jon Peltier" wrote:

Johnston -

There is a simpler rearrangement of the data:

x y1 y2
1/1/08 1
2/1/08 2
3/1/08 3
4/1/08 4
1/15/08 6
2/15/08 7
3/15/08 8


A line chart internally sorts the data by date in a line chart with a
date
axis. The chart at first will not connect the points with lines, because
by
sorting it introduces blanks within the ranges of Y values. Go to Tools
menu
Options Chart tab, and choose interpolate in the top section.

The other thing you could do is create the chart with the X1-Y1 data, add
the second series with the X2-Y2 data, convert the second series to an XY
series, and format the XY series to plot on the primary axis. The XY
series
will plot nicely on the line chart's date axis.

Note, this XY-Line combination chart will not work in 2007. I've reported
the bug and can only hope it's fixed in Excel 14.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"johnston" wrote in message
...
I'm sure this is naive. In Excel 2003 I'm trying to construct a line
graph
in which there is is more than one set of matching x and y values. For
example, the data looks like this:
x1 y1 x2 y2
1/1/08 1 1/15/08 5
2/1/08 2 2/15/08 6
3/1/08 3 3/15/08 7
4/1/08 4

If I were drawing a scatter plot, I could enter matching x and y
values,
but
with a line graph I can plot the data only by rearranging it to look
like
this.
x y1 y2
1/1/08 1
1/15/08 6
2/1/08 2
2/15/08 7
3/1/08 3
3/15/08 8
4/1/08 4

I'm sure there must be a simpler way, particularly if one has multiple
data
sets. Any help would be appreciated.

Thanks,
johnston

Jon, thank you for the explanation. You saved me hours of aggravation.

johnston

"Jon Peltier" wrote:

Create the line chart with the X1-Y1 data. Copy the X2-Y2 data, select the
chart, and paste special to add the data as a new series, with categories in
the first column. Change the series to an XY type (right click - chart
type - select a line type), and move it back to the primary axis. Note to
Excel 2007 users: the second series will lose its X values by creating a
line chart series, so when you convert it to an XY series you will have to
add the X2 data as its X values..

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"johnston" wrote in message
...
Jon,
Your arrangement of the data is much simpler. Thank you. However, I
didn't
quite understand your instructions. Do I create a line chart with the x1
y1
data and add the y2 data as a new series? (Excel doesn't allow me to
enter
the x2 data.) Can you explain more fully what to do?

Thanks,
johnston

"Jon Peltier" wrote:

Johnston -

There is a simpler rearrangement of the data:

x y1 y2
1/1/08 1
2/1/08 2
3/1/08 3
4/1/08 4
1/15/08 6
2/15/08 7
3/15/08 8


A line chart internally sorts the data by date in a line chart with a
date
axis. The chart at first will not connect the points with lines, because
by
sorting it introduces blanks within the ranges of Y values. Go to Tools
menu
Options Chart tab, and choose interpolate in the top section.

The other thing you could do is create the chart with the X1-Y1 data, add
the second series with the X2-Y2 data, convert the second series to an XY
series, and format the XY series to plot on the primary axis. The XY
series
will plot nicely on the line chart's date axis.

Note, this XY-Line combination chart will not work in 2007. I've reported
the bug and can only hope it's fixed in Excel 14.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"johnston" wrote in message
...
I'm sure this is naive. In Excel 2003 I'm trying to construct a line
graph
in which there is is more than one set of matching x and y values. For
example, the data looks like this:
x1 y1 x2 y2
1/1/08 1 1/15/08 5
2/1/08 2 2/15/08 6
3/1/08 3 3/15/08 7
4/1/08 4

If I were drawing a scatter plot, I could enter matching x and y
values,
but
with a line graph I can plot the data only by rearranging it to look
like
this.
x y1 y2
1/1/08 1
1/15/08 6
2/1/08 2
2/15/08 7
3/1/08 3
3/15/08 8
4/1/08 4

I'm sure there must be a simpler way, particularly if one has multiple
data
sets. Any help would be appreciated.

Thanks,
johnston