Bubble size relative to a constant

[immanuel]

I am making a bubble chart with a dynamic range for z. As z changes, the
bubbles change sizes relative to the other elements in z. However, I'd like
them to change sizes relative to a constant.

At present, I'm including a constant in the each z range without a x or y.
Is this the recommended method?

/i.

 

[Tushar Mehta]

I imagine adding a dummy constant with missing x and y values is as good
a method as any. Any reason you don't like it?

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2004
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office

 

[immanuel]

It just doesn't seem very elegant. And someone looking at the source data
may not understand the reason for the dummy values in each column.

Regards,
Immanuel

 

[Jon Peltier]

? Excel + elegance = Excelegance
False

The thing with Excel is that you often have to use strange workarounds
to do seemingly simple things. You should adopt this philosophy:

Whatever works, works.

You could use insert a cell comment or use another kind of label to
alert the user that the cell is used to define the size of the bubbles
in the chart.

- Jon

 

[immanuel]

Well spoken. Speaking of strange workarounds, would you have one for
creating a combination chart with bubble and line chart subtypes?

Regards,
Immanuel

 

[Jon Peltier]

Yep, you can use any shape as a custom data point marker. Copy the
shape, select the series, or just the point, and paste, and the series
or point takes on the shape as its symbol. It's all here:

http://www.geocities.com/jonpeltier/Excel/ChartsHowTo/CustomSeriesFormatting.html

So carry it a step further, and use VBA to resize a circle (or whatever
shape) as required, then paste it onto the relevant point in a line or
scatter chart. Microsoft uses this technique in their knowledge base
when describing how to make a bubble chart in VBA:

http://support.microsoft.com/default.aspx?scid=kb;en-us;139662

I guess the author of the article couldn't find anyone who knew how to
make a regular bubble chart in VBA.

- Jon