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

[Kelvin Stott]

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

 

[Andrew Poulsom]

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

Bar-Line Combination Chart

 

[Kelvin Stott]

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!

 

[Jon Peltier]

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.

 

[Kelvin Stott]

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