+ Reply to Thread
Results 1 to 17 of 17

Line chart or scatter chart??

  1. #1
    rmellison
    Guest

    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. #2
    bj
    Guest

    RE: Line chart or scatter chart??

    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. #3
    rmellison
    Guest

    RE: Line chart or scatter chart??

    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. #4
    bj
    Guest

    RE: Line chart or scatter chart??

    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. #5
    rmellison
    Guest

    RE: Line chart or scatter chart??

    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. #6
    rmellison
    Guest

    RE: Line chart or scatter chart??

    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. #7
    Tushar Mehta
    Guest

    Re: Line chart or scatter chart??

    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 <[email protected]>,
    [email protected] 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. #8
    rmellison
    Guest

    Re: Line chart or scatter chart??

    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 <[email protected]>,
    > [email protected] 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. #9
    Tushar Mehta
    Guest

    Re: Line chart or scatter chart??

    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 <[email protected]>,
    [email protected] 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 <[email protected]>,
    > > [email protected] 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. #10
    Stephen Bullen
    Guest

    Re: Line chart or scatter chart??

    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



  11. #11
    bj
    Guest

    RE: Line chart or scatter chart??


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


  12. #12
    rmellison
    Guest

    RE: Line chart or scatter chart??

    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. #13
    rmellison
    Guest

    Re: Line chart or scatter chart??

    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. #14
    Jon Peltier
    Guest

    Re: Line chart or scatter chart??

    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. #15
    Tushar Mehta
    Guest

    Re: Line chart or scatter chart??

    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 <[email protected]>, [email protected]
    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. #16
    Stephen Bullen
    Guest

    Re: Line chart or scatter chart??

    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. #17
    rmellison
    Guest

    Re: Line chart or scatter chart??

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1