Excel Bubble Chart. Want radius relative to x-axis.

[Chris]

Hi,
I am trying to create a bubble chart that sets the radius/diameter of
the bubble so that it is on the same scale of as the x-axis.
I am overlaying these on maps and need the bubble to act as a radius
from a center point in km.

Thanks,
-Chris

 

[Jon Peltier]

Chris -

This is really tricky, since (a) the X axis scale isn't tied too strongly to
physical units, and (b) the bubble diameter isn't tied to any axis units.
(a) means that changing font size or the number of digits in the axis labels
will affect the X axis length, but you can tie it down using code related to
this:

http://peltiertech.com/Excel/Charts/SquareGrid.html

(b) is also tough, because you can specify a default maximum bubble size in
percent, where 100% is approximately 25% of the smaller of the plot area
inside height or width. This is the size of the largest bubble, regardless
of the order of magnitude of the bubble size values. You have to lock down
the axis scales first, as above, then determine a default bubble size, then
relate your desired bubble size to the input bubble size measurements. It
will require some trial and error.

An alternative that will give better precision of bubble sizes (without
improving in any way the precision of the axis scale) is to draw circles in
the worksheet, then use these as custom chart data markers:

http://peltiertech.com/Excel/ChartsHowTo/CustomMarkers.html

- Jon

 

[Chris]

Jon,
Thanks for the very quick response.

I have alreay used your code to make the plot square and I have written
code so that the x-axis and y-axis are always on the same scale.

From what it sounds like, if I use a bubble plot there is no way to

have a bubble taking up more than 25% of the plot area.
I would prefer that the bubbles could range from a tiny dot to a huge
bubble that takes up the entire plot area. Is this going to be
possible with custom markers?

-Chris

 

[Jon Peltier]

Chris -

Actually, your default size can range from a small value (0) to 300%. At
100%, the largest bubble diameter is 1/4 the length of the smaller axis. At
200%, it's not 1/2 the axis length, but more like 40%. At 300% it's not 3/4
but more like 55%. I don't know why the percentage isn't proportional.

You can use a shape that's larger than the chart for a custom data marker,
and it will obscure everything.

In one of the beta editions of Excel 2007, the custom marker capability had
been incorrectly implemented, and I have not tested it in the RTM version
yet. I don't know how the bubble features have changed in Excel 2007.

- Jon

 

[Del Cotter]

I am trying to create a bubble chart that sets the radius/diameter of
the bubble so that it is on the same scale of as the x-axis.
I am overlaying these on maps and need the bubble to act as a radius
from a center point in km.

Is it feasible to abandon the idea of a bubble chart, and just create
short pieces of X-Y series that draw circles to scale?