Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 9th 05, 10:28 AM
rmellison
 
Posts: n/a
Default Line chart or scatter chart??

I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.


  #2   Report Post  
Old September 9th 05, 12:45 PM
bj
 
Posts: n/a
Default

there can be two x axis in a scatter chart.
first select one of the data series and <axis select secondary
next in <chart<chart options<axis select secondary x axis.

"rmellison" wrote:

I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.

  #3   Report Post  
Old September 9th 05, 01:54 PM
rmellison
 
Posts: n/a
Default

Thanks, but not what I'm looking for. Need to be able to plot two sets of
data against the same scale, but the scale has to be from 60 up to 100 then
back to 60. Don't know how to do that in a scatter chart, and a line chart
won't match the data to the scale because it works on the number of
categories.


"bj" wrote:

there can be two x axis in a scatter chart.
first select one of the data series and <axis select secondary
next in <chart<chart options<axis select secondary x axis.

"rmellison" wrote:

I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.

  #4   Report Post  
Old September 9th 05, 03:38 PM
bj
 
Posts: n/a
Default

I missunderstood what you wanted to do.

It is not as complex as it sounds but

I think you will need to set up an artificial x axis
in a helper column for each data set
set up one of the two equations
= the value
=200- the value (for those going back down towards sixty)
plot the two data sets in xy against the helper columns as the x axis
select the axis and make the min be 60 and the max be 140

in another column
enter 60,70,80,90,100,90,80,70,60
and next to it
1,1,1,1,1,1,1,1,1
add this data set to the chart
select this data set and change axis to secondary
then <chart< chart type select line

in <chart<chart options<axis select secondary x axis
format the axis such that you deselect the y axis crosses between catagories

hide the othe X axis



"rmellison" wrote:

Thanks, but not what I'm looking for. Need to be able to plot two sets of
data against the same scale, but the scale has to be from 60 up to 100 then
back to 60. Don't know how to do that in a scatter chart, and a line chart
won't match the data to the scale because it works on the number of
categories.


"bj" wrote:

there can be two x axis in a scatter chart.
first select one of the data series and <axis select secondary
next in <chart<chart options<axis select secondary x axis.

"rmellison" wrote:

I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.

  #5   Report Post  
Old September 9th 05, 04:06 PM
rmellison
 
Posts: n/a
Default

I think that's cracked it!

Seems to me to be a bit of a shortfall in Excel for it to require dummy
x-axes and what-have-you. Still, it works for my purposes, many thanks for
your assistance!

"bj" wrote:

I missunderstood what you wanted to do.

It is not as complex as it sounds but

I think you will need to set up an artificial x axis
in a helper column for each data set
set up one of the two equations
= the value
=200- the value (for those going back down towards sixty)
plot the two data sets in xy against the helper columns as the x axis
select the axis and make the min be 60 and the max be 140

in another column
enter 60,70,80,90,100,90,80,70,60
and next to it
1,1,1,1,1,1,1,1,1
add this data set to the chart
select this data set and change axis to secondary
then <chart< chart type select line

in <chart<chart options<axis select secondary x axis
format the axis such that you deselect the y axis crosses between catagories

hide the othe X axis



"rmellison" wrote:

Thanks, but not what I'm looking for. Need to be able to plot two sets of
data against the same scale, but the scale has to be from 60 up to 100 then
back to 60. Don't know how to do that in a scatter chart, and a line chart
won't match the data to the scale because it works on the number of
categories.


"bj" wrote:

there can be two x axis in a scatter chart.
first select one of the data series and <axis select secondary
next in <chart<chart options<axis select secondary x axis.

"rmellison" wrote:

I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.



  #6   Report Post  
Old September 12th 05, 09:38 AM
rmellison
 
Posts: n/a
Default

Hmmm,

Seems that this method hasn't worked after all. On Friday I had it sorted
for one data set, but have tried to add a second today and i come back to the
same problem. The two data sets are plotted against their categories, such
that the x-values of one don't correspond to the x-values of the other; it
merely aligns the nth point of one set with the nth point of the other set.

I can't see away around this one. I think to get the desired plot I'll have
to maintain a scatter type chart, but the best I can hope for on the x-axis
is 60 to 140, using my 'helper' column. The problem is the secondary axis
will not display as 60-100-60 if the type is kept as a scatter chart!

Any other suggestions? If not, I may have to admit defeat on this one....


"bj" wrote:

I missunderstood what you wanted to do.

It is not as complex as it sounds but

I think you will need to set up an artificial x axis
in a helper column for each data set
set up one of the two equations
= the value
=200- the value (for those going back down towards sixty)
plot the two data sets in xy against the helper columns as the x axis
select the axis and make the min be 60 and the max be 140

in another column
enter 60,70,80,90,100,90,80,70,60
and next to it
1,1,1,1,1,1,1,1,1
add this data set to the chart
select this data set and change axis to secondary
then <chart< chart type select line

in <chart<chart options<axis select secondary x axis
format the axis such that you deselect the y axis crosses between catagories

hide the othe X axis



"rmellison" wrote:

Thanks, but not what I'm looking for. Need to be able to plot two sets of
data against the same scale, but the scale has to be from 60 up to 100 then
back to 60. Don't know how to do that in a scatter chart, and a line chart
won't match the data to the scale because it works on the number of
categories.


"bj" wrote:

there can be two x axis in a scatter chart.
first select one of the data series and <axis select secondary
next in <chart<chart options<axis select secondary x axis.

"rmellison" wrote:

I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.

  #7   Report Post  
Old September 12th 05, 02:50 PM
Tushar Mehta
 
Posts: n/a
Default

What you have to do is 'merge' the x-values of the 2 data sets. In the
example below the x-values go from 10 to 40 to 10 in DataSet 1 and from
15 to 45 to 15 in DataSet2. I reorganized the data so that the 2 sets
'interleave.'

Data set 1:
10 1.061231423
20 1.861363713
30 6.561589331
40 3.4964915
40 9.683049858
30 20.00872277
20 11.95088416
10 11.62636711

Data set 2:
15 3.4709576
25 2.725999451
35 16.9991303
45 18.1101212
35 21.63843686
25 12.27002799
15 35.20802415

Merged data set:
10 1.061231423
15 3.4709576
20 1.861363713
25 2.725999451
30 6.561589331
35 16.9991303
40 3.4964915
45 18.1101212
40 9.683049858
35 21.63843686
30 20.00872277
25 12.27002799
20 11.95088416
15 35.20802415
10 11.62636711

Plot the merged data set and it will give you exactly what you want.

It isn't pretty to set up but if you have a lot of data (whether 200 is
a lot is up to you) and are comfortable with VBA you may want to
automate the process.

--
Regards,

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

In article ,
says...
I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.


  #8   Report Post  
Old September 12th 05, 03:26 PM
rmellison
 
Posts: n/a
Default

I had thought about merging the data, but unfortunately it's not quite as
straight forward as 10,20,30,20,10 merging with 15,25,35,25,15 etc. Each set
is 200 points, and for example, one has a range of 60 to 100 to 60 and
another is 65 to 100 to 65. The x-values can be any value within those
ranges, and are not integer values. Also, I would still have to plot as a
line chart to get the increase/decrease x-values, and it wouldn't work as i
want it to beacause each value would be a category and the actual x-values
for each data set would not align, if you see what I mean.

I suppose I could create a formula to merge the two sets to one x-value
range, but when I first thought about it I didn't try becasue it would take
me too long to get it right! My VBA isn't really up to the challenge
either....

If thats the only way to do it, I'll have to try and fudge something
together. But if anybody knows a way to get increasing/decreasing values (eg.
60 to 100 to 60) on the x-axis of a scatter graph, it'd make things a whole
lot simpler...

"Tushar Mehta" wrote:

What you have to do is 'merge' the x-values of the 2 data sets. In the
example below the x-values go from 10 to 40 to 10 in DataSet 1 and from
15 to 45 to 15 in DataSet2. I reorganized the data so that the 2 sets
'interleave.'

Data set 1:
10 1.061231423
20 1.861363713
30 6.561589331
40 3.4964915
40 9.683049858
30 20.00872277
20 11.95088416
10 11.62636711

Data set 2:
15 3.4709576
25 2.725999451
35 16.9991303
45 18.1101212
35 21.63843686
25 12.27002799
15 35.20802415

Merged data set:
10 1.061231423
15 3.4709576
20 1.861363713
25 2.725999451
30 6.561589331
35 16.9991303
40 3.4964915
45 18.1101212
40 9.683049858
35 21.63843686
30 20.00872277
25 12.27002799
20 11.95088416
15 35.20802415
10 11.62636711

Plot the merged data set and it will give you exactly what you want.

It isn't pretty to set up but if you have a lot of data (whether 200 is
a lot is up to you) and are comfortable with VBA you may want to
automate the process.

--
Regards,

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

In article ,
says...
I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.



  #9   Report Post  
Old September 12th 05, 04:39 PM
Tushar Mehta
 
Posts: n/a
Default

I came up with a way to simulate the effect for one data set before
realizing that...

Actually, no, it doesn't make any sense for multiple data sets, at
least not to me. How do you envision the spacing along the x-axis for
the following?

Data set 1 partial x-values: 96, 97, 100, 98, 95.

Data set 2 partial x values: 95, 98, 100, 101, 105, 103, 102, 101, 94.

Here's a simple solution for one data set. As long as the data have
only one maximum, just subtract Max(X-values) from all the actual x-
values! Use these new values as the x-axis values. Also create a new
column of data with all zeros. Plot this as a new series. Remove the
displayed x-values (double-click the axis, select the Patterns tab, set
'Tick mark labels' to None). For the new dummy series add the
*original* x-values as data labels (use XY Chartlabeler from
www.appspro.com or Chart Tools from www.j-walk.com).

--
Regards,

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

In article ,
says...
I had thought about merging the data, but unfortunately it's not quite as
straight forward as 10,20,30,20,10 merging with 15,25,35,25,15 etc. Each set
is 200 points, and for example, one has a range of 60 to 100 to 60 and
another is 65 to 100 to 65. The x-values can be any value within those
ranges, and are not integer values. Also, I would still have to plot as a
line chart to get the increase/decrease x-values, and it wouldn't work as i
want it to beacause each value would be a category and the actual x-values
for each data set would not align, if you see what I mean.

I suppose I could create a formula to merge the two sets to one x-value
range, but when I first thought about it I didn't try becasue it would take
me too long to get it right! My VBA isn't really up to the challenge
either....

If thats the only way to do it, I'll have to try and fudge something
together. But if anybody knows a way to get increasing/decreasing values (eg.
60 to 100 to 60) on the x-axis of a scatter graph, it'd make things a whole
lot simpler...

"Tushar Mehta" wrote:

What you have to do is 'merge' the x-values of the 2 data sets. In the
example below the x-values go from 10 to 40 to 10 in DataSet 1 and from
15 to 45 to 15 in DataSet2. I reorganized the data so that the 2 sets
'interleave.'

Data set 1:
10 1.061231423
20 1.861363713
30 6.561589331
40 3.4964915
40 9.683049858
30 20.00872277
20 11.95088416
10 11.62636711

Data set 2:
15 3.4709576
25 2.725999451
35 16.9991303
45 18.1101212
35 21.63843686
25 12.27002799
15 35.20802415

Merged data set:
10 1.061231423
15 3.4709576
20 1.861363713
25 2.725999451
30 6.561589331
35 16.9991303
40 3.4964915
45 18.1101212
40 9.683049858
35 21.63843686
30 20.00872277
25 12.27002799
20 11.95088416
15 35.20802415
10 11.62636711

Plot the merged data set and it will give you exactly what you want.

It isn't pretty to set up but if you have a lot of data (whether 200 is
a lot is up to you) and are comfortable with VBA you may want to
automate the process.

--
Regards,

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

In article ,
says...
I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.




  #10   Report Post  
Old September 12th 05, 06:14 PM
bj
 
Posts: n/a
Default


The two data sets should be plotted again the new numbers as XY scatter
chart and the secondary axis should be a line chart

"rmellison" wrote:

Hmmm,

Seems that this method hasn't worked after all. On Friday I had it sorted
for one data set, but have tried to add a second today and i come back to the
same problem. The two data sets are plotted against their categories, such
that the x-values of one don't correspond to the x-values of the other; it
merely aligns the nth point of one set with the nth point of the other set.

I can't see away around this one. I think to get the desired plot I'll have
to maintain a scatter type chart, but the best I can hope for on the x-axis
is 60 to 140, using my 'helper' column. The problem is the secondary axis
will not display as 60-100-60 if the type is kept as a scatter chart!

Any other suggestions? If not, I may have to admit defeat on this one....


"bj" wrote:

I missunderstood what you wanted to do.

It is not as complex as it sounds but

I think you will need to set up an artificial x axis
in a helper column for each data set
set up one of the two equations
= the value
=200- the value (for those going back down towards sixty)
plot the two data sets in xy against the helper columns as the x axis
select the axis and make the min be 60 and the max be 140

in another column
enter 60,70,80,90,100,90,80,70,60
and next to it
1,1,1,1,1,1,1,1,1
add this data set to the chart
select this data set and change axis to secondary
then <chart< chart type select line

in <chart<chart options<axis select secondary x axis
format the axis such that you deselect the y axis crosses between catagories

hide the othe X axis



"rmellison" wrote:

Thanks, but not what I'm looking for. Need to be able to plot two sets of
data against the same scale, but the scale has to be from 60 up to 100 then
back to 60. Don't know how to do that in a scatter chart, and a line chart
won't match the data to the scale because it works on the number of
categories.


"bj" wrote:

there can be two x axis in a scatter chart.
first select one of the data series and <axis select secondary
next in <chart<chart options<axis select secondary x axis.

"rmellison" wrote:

I have many sets of data for which the x-values run up from approx 60 to 100,
and back down to 60ish. Each set of data consists of 200 points. I can
happily display each set individually on a scatter chart, with the categories
from, 60 (at point i=1)to 100 (i=100) to 60 (i=200). My probably occurs when
I want to display two sets of data on the same graph. For each set, the
x-values vary, for example another set could run from 65 to 95 then back to
65, but it still contains 200 points. Excel will only allow one set of values
on the category axis, so only one data set will be correctly represented by
the x-values. Using a scatter graph uses numerical x-values rather than
categories, so my x-values only go from 60ish to 100ish, which is not what i
want.

Is there any way to use numerical values on a line chart, and have each data
set represented correctly against the x-axis? Or similary can I create a
scatter graph with x-values running from 60 to 100 to 60?

Any help greatly appreciated.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Line chart or scatter chart?? rmellison Excel Discussion (Misc queries) 2 September 7th 05 12:04 PM
Label an average line in a dynamic chart John Cordes Charts and Charting in Excel 3 June 17th 05 09:42 AM
Line chart in Excel - trendline incomplete [email protected] Charts and Charting in Excel 7 May 13th 05 01:21 PM
Line chart - date line association gone mad! Johannes Czernin Charts and Charting in Excel 5 January 17th 05 09:48 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM


All times are GMT +1. The time now is 08:33 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017