Results 1 to 5 of 5

Thread: High-low lines on scatterplot

  1. #1

    Default High-low lines on scatterplot

    I have two data series, showing the ranking of a number of factors before and after a vote.

    I want to show how the position of each factor changes after the vote. So far, I have put on high-low lines, which links the relevant points clearly. However, I want them to be arrows, always pointing from one series to the other, regardless of whether it moves higher or lower.
    At the moment, I can only add arrow heads in one direction (up or down).

    Does anyone know a way of adding appropriate arrows?

    My table is set out:

    Factor Pre-vote rank position Post-vote rank position
    A 1 3
    B 5 6
    C 7 4
    D 10 2

    I want the arrows to always point from the pre-vote data set to the post-vote data set, regardless of whether it's an increase or decrease.

    Any help very much appreciated.

    Caitlin

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    2,921

    Default Re: High-low lines on scatterplot

    Hello
    What Excel version are you using? Anyway, the page below may prove useful; let me know if you need further help.

    Connect Two XY Series with Arrows (VBA) | Peltier Tech Blog | Excel Charts
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)
    Read Forum Rules here

  3. #3

    Default Re: High-low lines on scatterplot

    Hi,

    Thanks very much for the reply.

    I'm using Excel 2010.

    However, my issue is made a bit more complicated because i'm actually using a line plot with the lines removed rather than a real scatterplot, because the x-axis categories are labels rather than numerical values. Therefore, the joining lines are high-low lines. Because the datasets don't have a numerical x value, i can't use this approach: Connect Two XY Series | Peltier Tech Blog | Excel Charts

    If you have any further ideas about how I might do this, please do let me know. Otherwise, I will just have to do some explicit labelling of the figure.

    Many thanks.

  4. #4
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    2,921

    Lightbulb Re: High-low lines on scatterplot

    Here is a possible solution:

    • Create a scatter plot with numeric values in place of your labels.
    • Insert an arbitrary x axis as described here:


    Arbitrary Axis Scale

    This will allow you to get rid of the temporary numeric values and go back to the original text labels, while still having a XY chart.

    • Run the VBA code mentioned at post #2

    Completing the steps above will generate a chart like the one below. If you have some trouble with the procedure, feel free to ask.

    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)
    Read Forum Rules here

  5. #5
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,375

    Default Re: High-low lines on scatterplot

    I know it's an old post, but someone may find this solution useful.

    Here's some simple data, alpha and beta as before and after, plus a column labeled "change", which is beta (after) minus alpha (before).

    Plot the data as a line chart, and format to hide the lines (top left chart). Select series alpha and add error bars (top right chart). Format the error bar values to use custom values, click Specify Value, then select the change values for Positive Error Value and change the ={1} to ={0} for Negative Error Value (dialog image bottom left, bottom left chart). Finally, format the error bars to use no end cap, then format its line to have an end arrow of the desired type and size.



    Coincidentally, I just published a new blog post, Connect Two XY Series with Arrows, which has a non-VBA approach to connecting plotted points with arrows, and updated code if you still need that. It includes detailed instructions for formatting line segments with arrowheads.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com