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

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
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/0B4rFQKgfCtswRUY0MXlpQnJMa1k/edit?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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
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:

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
Understandably, thanks. Great articles by the way, they have been enormously helpful! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,533
Messages
5,445,042
Members
405,307
Latest member
TINA31415

This Week's Hot Topics

Top