Is it possible to plot this kind of data in a (line) chart w/ preferred axis? How?

[pgtr]

How would I plot this data in a line chart?

(MS Excel 03)

(Samples below should be viewed in non-proportional font or formatting
will not be clear)


Raw data (below):

A B C D E
0 @ 1000 0 @ 1000 0 @ 1060 0 @ 865 0 @ 1200
10 @ 1700 10 @ 1700 2 @ 1340 2 @ 1325 2 @ 1460
na na 17 @ 2400 11 @ 2400 12 @ 2200
26 @ 5000 26 @ 5000 24 @ 4800 18 @ 4200 20 @ 5000

The catch is I need to have the 2nd # be the X axis (0...6000).


I can put the data in the spreadsheet something like this and tell it
to ignore empty values (below):

A B (etc)
+-----------------------
0 | 1000 1060
1 |
2 | 1340
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10| 1700
11|
12|
13|
14|
15|
16|
17| 2400
18|
(etc)

Or I can put the data in the sheet in another format...?


But ultimately I need to have the line chart look something like this
(below):

etc...
10|
9|
8|
7|
6|
5|
4|
3|
2|
1|
0+------------------------>
1K 2K 3K 4K 5K 6K

And I can't figure out how to get Excel to do this chart from this
data! Surely this is possible? Appreciate any help or tips!

 

[Stephen Bullen]

Hi Pgtr,

How would I plot this data in a line chart?

(MS Excel 03)

(Samples below should be viewed in non-proportional font or formatting
will not be clear)

Raw data (below):

A B C D E
0 @ 1000 0 @ 1000 0 @ 1060 0 @ 865 0 @ 1200
10 @ 1700 10 @ 1700 2 @ 1340 2 @ 1325 2 @ 1460
na na 17 @ 2400 11 @ 2400 12 @ 2200
26 @ 5000 26 @ 5000 24 @ 4800 18 @ 4200 20 @ 5000

The catch is I need to have the 2nd # be the X axis (0...6000).

I'm not sure I'm interpreting the data correctly, but is this five data
series, each with their own x and y values:

A: (1000,0),(1700,10),...,(5000,26)
B: (1000,0),(1700,10),...,(5000,26)
C: (1060,0),(1340,2),...
etc.?

If so, you can arrange your data like the following:

A B C D E F etc.
1 SeriesA SeriesB SeriesC
2 X Y X Y X Y
3 1000 0 1000 0 1060 0
4 1700 10 1700 10 1340 2
5
...

In other words, but the X and Y data for each series in their own
column, with the X values to the left of the Y's.

Now create an XY chart using just the values for SeriesA (columns A and
B). Then select the two columns for SeriesB and Edit > Copy. Select
the chart and Edit > Paste Special. In that dialog, choose to paste as
a new series, with X values in the first column, but don't replace the
existing X values. Repeat for the rest of the series to get the chart I
think you want <g>.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[pgtr]

Hi Pgtr,

I'm not sure I'm interpreting the data correctly, but is this five data
series, each with their own x and y values:

Yes, precisely. But I must have the larger #s 0...5000 to be in the X
axis.

A: (1000,0),(1700,10),...,(5000,26)
B: (1000,0),(1700,10),...,(5000,26)
C: (1060,0),(1340,2),...
etc.?
Yes.

If so, you can arrange your data like the following:

A B C D E F etc.
1 SeriesA SeriesB SeriesC
2 X Y X Y X Y
3 1000 0 1000 0 1060 0
4 1700 10 1700 10 1340 2
5
...

Interesting. OK got it.

In other words, but the X and Y data for each series in their own
column, with the X values to the left of the Y's.

OK. Let's just work w/ a single series pairing for simplicity...

A B
Series 'X'
1060 8
1340 10
2400 25
4800 32

Desired graph format:
(B)
etc
25| *
24|
23|
22|
21|
20|
19|
18|
17|
16|
15|
14|
13|
12|
11|
10| *
9|
8| *
7|
6|
5|
4|
3|
2|
1|
0+------------------------>(A)
1K 2K 3K 4K 5K 6K

THen if I can get that to work I'll expand it to include all the data.

Now create an XY chart using just the values for SeriesA (columns A and
B).

But when I graph... all I can do is create 2 SEPARATE INDEPENDENT
plots of the 'paired' data. Eg. one plot uses 1060...1340...2400...etc
as Y data as a function of 1,2,3... and the other uses 8,10,25... also
as a function of 1,2,3. E.g. it's forcing both 'paired' columns into
SEPARATE and unrelated Y values. They aren't 'connected' as one being
the function of another... (in the data above, B is a function of A is
in A(B)).

In the graph: Y is still 0...5000 and X is simply 1,2,3,4. X should be
1000...2000... up to 6000 or so. And Y should be 0 to 26.

Then select the two columns for SeriesB and Edit > Copy. Select
the chart and Edit > Paste Special. In that dialog, choose to paste as
a new series, with X values in the first column, but don't replace the
existing X values.

OK. All this seems to accomplish is add a 3rd series to the 2 series
already shown (see above) when I really just want a SINGLE line for a
given series.

Repeat for the rest of the series to get the chart I
think you want <g>.

Interesting approach about separating the data out in this form but I
still seem stuck. Any add'l suggestions or did I take any mis-steps
above?

Thanks very much for taking the time to respond!

 

[Stephen Bullen]

Hi Pgtr,

But when I graph... all I can do is create 2 SEPARATE INDEPENDENT
plots of the 'paired' data. Eg. one plot uses 1060...1340...2400...etc
as Y data as a function of 1,2,3... and the other uses 8,10,25... also
as a function of 1,2,3. E.g. it's forcing both 'paired' columns into
SEPARATE and unrelated Y values.

OK:

(a) Make sure you select an XY (Scatter) chart, not a Line chart as the
chart type. I get what you're seeing if I choose the Line chart type,
but not if I choose XY (Scatter).

(b) In Step 2 of the Chart Wizard, click on the Series tab and check
the
ranges that Excel is using for each of your series. Correct them if
they're wrong. The 'X Values' should point to the range containing the
big numbers (1-5000), while the 'Y Values' should point to the range
containing the small numbers.

Regards

Stephen Bullen

 

[pgtr]

OK:

(a) Make sure you select an XY (Scatter) chart, not a Line chart as the
chart type. I get what you're seeing if I choose the Line chart type,
but not if I choose XY (Scatter).

DOH! That was it! SCATTER CHART - thank you VERY MUCH it is now
working exactly the way I want it too. This is shaping up to be a
perfect image for an article I'm working on.