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

Kelvin Stott

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

MrExcel MVP
Joined
May 14, 2003
Messages
4,730
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

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

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,730
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

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

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,730
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.
 

Forum statistics

Threads
1,082,269
Messages
5,364,145
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top