Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old September 12th 05, 07:00 PM
Stephen Bullen
 
Posts: n/a
Default

Hi Rmellison,

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...


OK, we just need to get creative. Firstly, you're going to want to use an XY
scatter chart, so we have to modify the X scale such that Excel actually plots
the range 60-140, but display 60-100-60 along the axis.

So the fake X scale is given by:
=A1 (for the top cells)
=200-A1 (for the bottom cells)

Plot each series using that scale to get the correct horizontal positioning of
your data points.

Now create a column of cells with the actual numbers that you want to display
along the x axis, e.g. 60, 70, 80, 90, 100, 90, 80, 70 60, with 10 alongside
them. Select those cells, copy them, click the chart, choose Paste Special and
say the the x axes values are in the first column, but don't replace existing
values. That should give you a horizontal line on the chart. Select it, change
the chart type to a line chart (Chart Chart Type Line) and elect to plot it
on the secondary axes (Double-click it Axis Secondary). Then use the chart
options to display the secondary X axis but not Y axis (Chart Chart Options
Axis).

Double-click the bottom axis, go to the scale tab and set the scale to go from
55 to 145 in steps of 5. That should give you the numbers 55-145 along the
bottom (from the XY chart) and 60-100-60 along the top (from the line chart),
with the numbers lining up correctly. Now double-click the bottom axis, go to
the patterns tab and choose not to display tick mark labels. Then double-click
the top axis, go to the patterns tab, choose to not display tick marks and
display the tick labels 'Low'.

Lastly, format the dummy series we used for the tick mark labels to have no line
style and no pattern.

It was a bit of work, but we're done!

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev



  #12   Report Post  
Old September 13th 05, 08:40 AM
rmellison
 
Posts: n/a
Default

Yep, you had it right all along! I didn't realise that you could have
different chart types for each individual series. Got it licked now, many
thanks for your help.

"bj" wrote:


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.

  #13   Report Post  
Old September 13th 05, 08:41 AM
rmellison
 
Posts: n/a
Default

Worked like a charm! Pretty much the same as what bj had already advised, I
just didn't realise that you could have different chart types for each
individual series! Cheers for the help!

"Stephen Bullen" wrote:

Hi Rmellison,

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...


OK, we just need to get creative. Firstly, you're going to want to use an XY
scatter chart, so we have to modify the X scale such that Excel actually plots
the range 60-140, but display 60-100-60 along the axis.

So the fake X scale is given by:
=A1 (for the top cells)
=200-A1 (for the bottom cells)

Plot each series using that scale to get the correct horizontal positioning of
your data points.

Now create a column of cells with the actual numbers that you want to display
along the x axis, e.g. 60, 70, 80, 90, 100, 90, 80, 70 60, with 10 alongside
them. Select those cells, copy them, click the chart, choose Paste Special and
say the the x axes values are in the first column, but don't replace existing
values. That should give you a horizontal line on the chart. Select it, change
the chart type to a line chart (Chart Chart Type Line) and elect to plot it
on the secondary axes (Double-click it Axis Secondary). Then use the chart
options to display the secondary X axis but not Y axis (Chart Chart Options
Axis).

Double-click the bottom axis, go to the scale tab and set the scale to go from
55 to 145 in steps of 5. That should give you the numbers 55-145 along the
bottom (from the XY chart) and 60-100-60 along the top (from the line chart),
with the numbers lining up correctly. Now double-click the bottom axis, go to
the patterns tab and choose not to display tick mark labels. Then double-click
the top axis, go to the patterns tab, choose to not display tick marks and
display the tick labels 'Low'.

Lastly, format the dummy series we used for the tick mark labels to have no line
style and no pattern.

It was a bit of work, but we're done!

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev



  #14   Report Post  
Old September 13th 05, 01:31 PM
Jon Peltier
 
Posts: n/a
Default

I was just setting up to suggest exactly this approach. Thanks, Stephen,
for saving me 20 minutes!

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


Stephen Bullen wrote:

Hi Rmellison,


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...



OK, we just need to get creative. Firstly, you're going to want to use an XY
scatter chart, so we have to modify the X scale such that Excel actually plots
the range 60-140, but display 60-100-60 along the axis.

So the fake X scale is given by:
=A1 (for the top cells)
=200-A1 (for the bottom cells)

Plot each series using that scale to get the correct horizontal positioning of
your data points.

Now create a column of cells with the actual numbers that you want to display
along the x axis, e.g. 60, 70, 80, 90, 100, 90, 80, 70 60, with 10 alongside
them. Select those cells, copy them, click the chart, choose Paste Special and
say the the x axes values are in the first column, but don't replace existing
values. That should give you a horizontal line on the chart. Select it, change
the chart type to a line chart (Chart Chart Type Line) and elect to plot it
on the secondary axes (Double-click it Axis Secondary). Then use the chart
options to display the secondary X axis but not Y axis (Chart Chart Options
Axis).

Double-click the bottom axis, go to the scale tab and set the scale to go from
55 to 145 in steps of 5. That should give you the numbers 55-145 along the
bottom (from the XY chart) and 60-100-60 along the top (from the line chart),
with the numbers lining up correctly. Now double-click the bottom axis, go to
the patterns tab and choose not to display tick mark labels. Then double-click
the top axis, go to the patterns tab, choose to not display tick marks and
display the tick labels 'Low'.

Lastly, format the dummy series we used for the tick mark labels to have no line
style and no pattern.

It was a bit of work, but we're done!

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev


  #15   Report Post  
Old September 13th 05, 03:45 PM
Tushar Mehta
 
Posts: n/a
Default

Clearly, the OP is happy. However, I still don't see how this works.
No, not a question of how to do it in XL, but one of concepts.

Given *two* data sets, one with values 98, 100, 98 and another with
values 98, 105, 98, how can one plot both and maintain the correct
horizontal spacing? The first requires a spacing of 4 units between
the 2 98s, the 2nd requires 14 units of spacing!

--
Regards,

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

In article ,
says...
Hi Rmellison,

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...


OK, we just need to get creative. Firstly, you're going to want to use an XY
scatter chart, so we have to modify the X scale such that Excel actually plots
the range 60-140, but display 60-100-60 along the axis.

So the fake X scale is given by:
=A1 (for the top cells)
=200-A1 (for the bottom cells)

Plot each series using that scale to get the correct horizontal positioning of
your data points.

Now create a column of cells with the actual numbers that you want to display
along the x axis, e.g. 60, 70, 80, 90, 100, 90, 80, 70 60, with 10 alongside
them. Select those cells, copy them, click the chart, choose Paste Special and
say the the x axes values are in the first column, but don't replace existing
values. That should give you a horizontal line on the chart. Select it, change
the chart type to a line chart (Chart Chart Type Line) and elect to plot it
on the secondary axes (Double-click it Axis Secondary). Then use the chart
options to display the secondary X axis but not Y axis (Chart Chart Options
Axis).

Double-click the bottom axis, go to the scale tab and set the scale to go from
55 to 145 in steps of 5. That should give you the numbers 55-145 along the
bottom (from the XY chart) and 60-100-60 along the top (from the line chart),
with the numbers lining up correctly. Now double-click the bottom axis, go to
the patterns tab and choose not to display tick mark labels. Then double-click
the top axis, go to the patterns tab, choose to not display tick marks and
display the tick labels 'Low'.

Lastly, format the dummy series we used for the tick mark labels to have no line
style and no pattern.

It was a bit of work, but we're done!

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev





  #16   Report Post  
Old September 13th 05, 07:32 PM
Stephen Bullen
 
Posts: n/a
Default

Hi Tushar,

Given *two* data sets, one with values 98, 100, 98 and another with
values 98, 105, 98, how can one plot both and maintain the correct
horizontal spacing? The first requires a spacing of 4 units between
the 2 98s, the 2nd requires 14 units of spacing!


Sure - whatever is plotted, how does the line correlate to the numbers
displayed on the axis? As we have to use a formula to decide where to
split the two halves of the chart, the lines can only be plotted
correctly where they have the same mid-point - so 90-100-90 would plot
OK alongside 80-100-80. I guess the only way to do it would be to plot
all of them as "% of max" rather than absolute figures.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev


  #17   Report Post  
Old September 14th 05, 10:54 AM
rmellison
 
Posts: n/a
Default

That is in fact why it works well for me. My x-values are speed values as a
percentage of the max speed for an acelerate/decelerate run, so 100% is
always the pivot value. See your point Tushar (btw thanks for earlier help as
well), but it doesn't affect my data. My philosophy (born of shear
frustration with excel), is if it looks right, it is right!

"Stephen Bullen" wrote:

Hi Tushar,

Given *two* data sets, one with values 98, 100, 98 and another with
values 98, 105, 98, how can one plot both and maintain the correct
horizontal spacing? The first requires a spacing of 4 units between
the 2 98s, the 2nd requires 14 units of spacing!


Sure - whatever is plotted, how does the line correlate to the numbers
displayed on the axis? As we have to use a formula to decide where to
split the two halves of the chart, the lines can only be plotted
correctly where they have the same mid-point - so 90-100-90 would plot
OK alongside 80-100-80. I guess the only way to do it would be to plot
all of them as "% of max" rather than absolute figures.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev



  #18   Report Post  
Old February 10th 10, 12:03 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 13
Default Line chart or scatter chart??



"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 03:11 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017