Results 1 to 5 of 5

How to add mean/median points to horizontal bar chart with VBA?

This is a discussion on How to add mean/median points to horizontal bar chart with VBA? within the Excel Questions forums, part of the Question Forums category; Hello, I have managed to write VBA code to create horizontal boxplots from scratch, based on stacked bar charts as ...

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Posts
    306

    Default How to add mean/median points to horizontal bar chart with VBA?

    Hello,

    I have managed to write VBA code to create horizontal boxplots from scratch, based on stacked bar charts as described here: Excel Box and Whisker Diagrams (Box Plots) | Peltier Tech Blog | Excel Charts

    However, I am now struggling to add points to this chart by adding a new dataseries with a different chart type, again using only VBA code. I have managed to plot the points correctly along the horizontal value axis with the xlXYScatter chart type, but I can't plot them correctly along the vertical category axis so that they lign up with the horizontal bars:

    https://docs.google.com/file/d/0B4rF...it?usp=sharing

    So how can I get the points to lign up with the horizontal bars along the vertical axis?

    Am I using the right chart type (xlXYScatter), and how should I set .Values and .XValues for my data series? Do I need to configure .ChartGroups(2) in some way?

    Thanks for any help!

    Best regards,
    Kelvin

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092

    Default Re: How to add mean/median points to horizontal bar chart with VBA?

    This shows how to combine a bar chart and an xy scatter chart:

    Bar-Line Combination Chart
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Oct 2010
    Posts
    306

    Default Re: How to add mean/median points to horizontal bar chart with VBA?

    Perfect, thank you. I see now that I have to set the values of secondary vertical axis (.Values) to N - 0.5, where N is the point number, and then invert/reverse the axis. I don't know why I missed that in Peltier's boxplot article which I cited above.

    Tested and working perfectly fine, thanks!
    Last edited by Kelvin Stott; Apr 2nd, 2013 at 02:14 PM.

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

    Default Re: How to add mean/median points to horizontal bar chart with VBA?

    Quote Originally Posted by Kelvin Stott View Post
    I don't know why I missed that in Peltier's boxplot article which I cited above.
    It's a lot of details all at once.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  5. #5
    Board Regular
    Join Date
    Oct 2010
    Posts
    306

    Default Re: How to add mean/median points to horizontal bar chart with VBA?

    Understandably, thanks. Great articles by the way, they have been enormously helpful!

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