Home 
Search 
Today's Posts 
#1




Line chart or scatter chart??
I have many sets of data for which the xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#2




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 xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#3




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 xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#4




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 xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#5




I think that's cracked it!
Seems to me to be a bit of a shortfall in Excel for it to require dummy xaxes and whathaveyou. 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 xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#6




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 xvalues of one don't correspond to the xvalues 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 xaxis is 60 to 140, using my 'helper' column. The problem is the secondary axis will not display as 6010060 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 xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#7




What you have to do is 'merge' the xvalues of the 2 data sets. In the
example below the xvalues 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.tusharmehta.com Excel, PowerPoint, and VBA addins, tutorials Custom MS Office productivity solutions In article , says... I have many sets of data for which the xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#8




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 xvalues 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 xvalues, and it wouldn't work as i want it to beacause each value would be a category and the actual xvalues 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 xvalue 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 xaxis of a scatter graph, it'd make things a whole lot simpler... "Tushar Mehta" wrote: What you have to do is 'merge' the xvalues of the 2 data sets. In the example below the xvalues 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.tusharmehta.com Excel, PowerPoint, and VBA addins, tutorials Custom MS Office productivity solutions In article , says... I have many sets of data for which the xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#10




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 xvalues of one don't correspond to the xvalues 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 xaxis is 60 to 140, using my 'helper' column. The problem is the secondary axis will not display as 6010060 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 xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#11




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 xaxis 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 60140, but display 6010060 along the axis. So the fake X scale is given by: =A1 (for the top cells) =200A1 (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 (Doubleclick it Axis Secondary). Then use the chart options to display the secondary X axis but not Y axis (Chart Chart Options Axis). Doubleclick 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 55145 along the bottom (from the XY chart) and 6010060 along the top (from the line chart), with the numbers lining up correctly. Now doubleclick the bottom axis, go to the patterns tab and choose not to display tick mark labels. Then doubleclick 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




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 xvalues of one don't correspond to the xvalues 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 xaxis is 60 to 140, using my 'helper' column. The problem is the secondary axis will not display as 6010060 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 xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
#13




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 xaxis 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 60140, but display 6010060 along the axis. So the fake X scale is given by: =A1 (for the top cells) =200A1 (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 (Doubleclick it Axis Secondary). Then use the chart options to display the secondary X axis but not Y axis (Chart Chart Options Axis). Doubleclick 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 55145 along the bottom (from the XY chart) and 6010060 along the top (from the line chart), with the numbers lining up correctly. Now doubleclick the bottom axis, go to the patterns tab and choose not to display tick mark labels. Then doubleclick 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




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 xaxis 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 60140, but display 6010060 along the axis. So the fake X scale is given by: =A1 (for the top cells) =200A1 (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 (Doubleclick it Axis Secondary). Then use the chart options to display the secondary X axis but not Y axis (Chart Chart Options Axis). Doubleclick 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 55145 along the bottom (from the XY chart) and 6010060 along the top (from the line chart), with the numbers lining up correctly. Now doubleclick the bottom axis, go to the patterns tab and choose not to display tick mark labels. Then doubleclick 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




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.tusharmehta.com Excel, PowerPoint, and VBA addins, 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 xaxis 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 60140, but display 6010060 along the axis. So the fake X scale is given by: =A1 (for the top cells) =200A1 (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 (Doubleclick it Axis Secondary). Then use the chart options to display the secondary X axis but not Y axis (Chart Chart Options Axis). Doubleclick 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 55145 along the bottom (from the XY chart) and 6010060 along the top (from the line chart), with the numbers lining up correctly. Now doubleclick the bottom axis, go to the patterns tab and choose not to display tick mark labels. Then doubleclick 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




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 midpoint  so 9010090 would plot OK alongside 8010080. 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




That is in fact why it works well for me. My xvalues 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 midpoint  so 9010090 would plot OK alongside 8010080. 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
Posted to microsoft.public.excel.charting




Line chart or scatter chart??
"rmellison" wrote: I have many sets of data for which the xvalues 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 xvalues 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 xvalues. Using a scatter graph uses numerical xvalues rather than categories, so my xvalues 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 xaxis? Or similary can I create a scatter graph with xvalues running from 60 to 100 to 60? Any help greatly appreciated. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Line chart or scatter chart??  Excel Discussion (Misc queries)  
Label an average line in a dynamic chart  Charts and Charting in Excel  
Line chart in Excel  trendline incomplete  Charts and Charting in Excel  
Line chart  date line association gone mad!  Charts and Charting in Excel  
pivot table multi line chart  Charts and Charting in Excel 