How to create oval bubble chart based on scatter chart with XY error bars?

[Kelvin Stott]

Hello,

I have an XY scatter chart where each XY point has independent XY error bars. Is there any way to draw an oval around the error bars to create an oval bubble chart, where the bubble area represents the combined error?

Thanks in advance for any help.

Kelvin

 

[Jon Peltier]

You could draw oval shapes and position them manually... tedious.

You could write a VBA program to do this programmatically... tricky but once you get the hang of it, it'll be at least more fun than manually.

However, both approaches use shapes, and if there is any change to the size of the chart or the axis scales, the shapes will no longer line up where you/VBA so neatly placed them.

 

[Kelvin Stott]

Thanks, Jon,

If there's anyone who knows how to make funky charts then it's you!

In fact I was hoping to do this by VBA, and I was thinking I could use event handlers to call a sub that redraws the ovals when the data is changed. Main thing I am struggling with is how to draw ovals to fit the XY error bars on a scatter chart... Could I plot a few reference points (e.g., the four corners of a rectangle) as a series, and somehow convert (or delete and replace) these to give the reference points of an oval shape, similar to how you make your funky shape plots? Could you suggest any simple code that might do this, please? I don't need perfectly working code, just enough clues to get started, thanks!

Kelvin

 

[Jon Peltier]

Kelvin -

Know any algebra? You can get the plot area dimensions using PlotArea.InsideLeft, .Inside.Top, .InsideWidth, and .InsideHeight

You know where along the axes you are because you know X and Y, and you know the axis scale limits.

You can't get the error bar information directly from the chart (it's not exposed to VBA), but if the code you have built the chart and added the error bars, you can find the data.

 

[Kelvin Stott]

Thanks, I could try that, but isn't there an easier way/command to convert a plotted line into a shape, so I could just plot a rectangle, and convert that to a shape? Maybe by ungrouping the chart or something?

 

[Jon Peltier]

I'd think a little algebra is easier than trying to ungroup a chart (which hasn't produced expected results since Excel 2003) and deal with the pieces. Especially since you mentioned having an event procedure to update the shapes when the data changes.

How about this: You know X and Y, and you know error bars in the X and Y direction. Why not compute the X-Y coordinates of each oval in the worksheet, and plot them onto the chart? They will automatically recompute when the values change, and they will stay in the right place on the chart when the axis scales change.

 

[Kelvin Stott]

OK you've persuaded me, I'll give it a try, thanks!