Problem with BUBBLE CHART scale and legend

[keesberbee]

Lately I am working with Bubble Charts and I noticed two problems that I cannot solve.

(1) Comparing multiple Bubble Charts that have dissimilar bubble size data is not possible. The problem is that if you use the same size multiplier, the bubbles in each chart have the same size, despite the difference in bubble size values. For example the bubble sizes of the next 2 tables are similar:
X1: 2, 3, 4, 5; Y1: 4, 3, 2, 1; Bubble1: 4, 3, 2, 1
X2: 2, 3, 4, 5; Y2: 4, 3, 2, 1; Bubble2: 0.4, 0.3, 0.2, 0.1
Of course there are some ways to overcome this problem, but they cause a lot extra work or are not dynamic. I am looking for a Macro or VBA procedure that generates bubble sizes in multiple charts that are sized according to the same bubble size scale.

(2) I also noticed that Bubble Charts in Excel don’t have a proper legend: they only inform about the third dimension (e.g. revenues). I am looking for a legend that informs the user about the bubble sizes and their values or ranges. A fourth dimensions can even be included in the charts: ‘colour of the bubbles’. Also for this matter I would like to have a clear legend.

My question: is there any macro or VBA procedure for including information about bubble size and bubble colour in the legend of the bubble chart?
Hopefully, someone can help me: you will help me a lot!

[HEK]

Hi kees:
Even without VBA or a Macro you can get you want I think.

(1) Excel refers the bubble sizes to the lowestvalue in the chart. So if
you add a bubble with size 0.1 to the Bubble1 series you get bubble sizes
with the right comparable values. So, add the 0.1 buble and make this bubble
invisible by changing the pattern and line colour to "none". Apparently,
nothing has changed but now the 1 to 4 values are sized compared to this 0.1
bubble.

(2) Not sure you can get what you want, but you could add data labels to the
bubbles, appearing close to the bubbles. The data lables could reflect the
names of the series, or the size. This is preferable anyway as it wld take
less efforts from the user because the data are close to the visual display.
Also, you could define for all bubbles different colours.

HTH,
GL,
Henk

"keesberbee" wrote:

>
> Lately I am working with Bubble Charts and I noticed two problems that I
> cannot solve.
>
> (1) Comparing multiple Bubble Charts that have dissimilar bubble size
> data is not possible. The problem is that if you use the same size
> multiplier, the bubbles in each chart have the same size, despite the
> difference in bubble size values. For example the bubble sizes of the
> next 2 tables are similar:
> X1: 2, 3, 4, 5; Y1: 4, 3, 2, 1; Bubble1: 4, 3, 2, 1
> X2: 2, 3, 4, 5; Y2: 4, 3, 2, 1; Bubble2: 0.4, 0.3, 0.2, 0.1
> Of course there are some ways to overcome this problem, but they cause
> a lot extra work or are not dynamic. I am looking for a Macro or VBA
> procedure that generates bubble sizes in multiple charts that are sized
> according to the same bubble size scale.
>
> (2) I also noticed that Bubble Charts in Excel don’t have a proper
> legend: they only inform about the third dimension (e.g. revenues). I
> am looking for a legend that informs the user about the bubble sizes
> and their values or ranges. A fourth dimensions can even be included in
> the charts: ‘colour of the bubbles’. Also for this matter I would like
> to have a clear legend. My question: is there any macro or VBA
> procedure for the bubble legend?
> Hopefully, someone can help me: you will help me a lot!
>
>
> --
> keesberbee
> ------------------------------------------------------------------------
> keesberbee's Profile: http://www.excelforum.com/member.php...o&userid=35899
> View this thread: http://www.excelforum.com/showthread...hreadid=557235
>
>

[keesberbee]

Thanks Henk for your answer.

About comparing 2 independent bubble charts with different data i am convinced that the solution proposed by you is most effective and the best one to choose.

About the legend part, i know it is possible to include the bubble sizes into the bubbles but that solution is not the one i am looking for. I am looking for a legend in which fixed bubble sizes are showed and represent a range of numbers. For example the smallest fixed bubble size represent revenues between 0 and 1 million euros, a somewhat bigger fixed bubble size represent revenues between 1 million and 2 million euros, and so on. I know it is possible because i saw it one time but i dont know how to do it.

[Jon Peltier]

(1) See this page for a workaround:

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

(2) There is no way built into Excel to make this kind of legend. You would
have to carefully draw a bunch of circles and use text boxes for labels.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"keesberbee" <[email protected]> wrote
in message news:[email protected]...
>
> Lately I am working with Bubble Charts and I noticed two problems that I
> cannot solve.
>
> (1) Comparing multiple Bubble Charts that have dissimilar bubble size
> data is not possible. The problem is that if you use the same size
> multiplier, the bubbles in each chart have the same size, despite the
> difference in bubble size values. For example the bubble sizes of the
> next 2 tables are similar:
> X1: 2, 3, 4, 5; Y1: 4, 3, 2, 1; Bubble1: 4, 3, 2, 1
> X2: 2, 3, 4, 5; Y2: 4, 3, 2, 1; Bubble2: 0.4, 0.3, 0.2, 0.1
> Of course there are some ways to overcome this problem, but they cause
> a lot extra work or are not dynamic. I am looking for a Macro or VBA
> procedure that generates bubble sizes in multiple charts that are sized
> according to the same bubble size scale.
>
> (2) I also noticed that Bubble Charts in Excel don't have a proper
> legend: they only inform about the third dimension (e.g. revenues). I
> am looking for a legend that informs the user about the bubble sizes
> and their values or ranges. A fourth dimensions can even be included in
> the charts: 'colour of the bubbles'. Also for this matter I would like
> to have a clear legend. My question: is there any macro or VBA
> procedure for the bubble legend?
> Hopefully, someone can help me: you will help me a lot!
>
>
> --
> keesberbee
> ------------------------------------------------------------------------
> keesberbee's Profile:
> http://www.excelforum.com/member.php...o&userid=35899
> View this thread: http://www.excelforum.com/showthread...hreadid=557235
>

[Del Cotter]

On Fri, 30 Jun 2006, in microsoft.public.excel.charting,
keesberbee <[email protected]>
said:

>(2) I also noticed that Bubble Charts in Excel don’t have a proper
>legend: they only inform about the third dimension (e.g. revenues). I
>am looking for a legend that informs the user about the bubble sizes
>and their values or ranges.

A while ago I wanted to make a legend that explained and labelled the
error bars on the data, not just the data point, and I tried making a
drawing, but it wasn't very elegant: the drawing didn't look exactly
like the data, making the result a bit amateurish in appearance.

Then I realised I didn't have to put up with that; I could have a
drawing that, by definition, looked exactly like an Excel data point,
just by making the "legend" out of a custom range with data labels. The
result was much better.

I'm sure you could do the same with a bubble range whose content would
be something like this:

x-position y-position area explanatory text
---------- ---------- ---- ----------------
1.4 0.28 1 bubble area = 1
1.4 0.50 2 bubble area = 2
1.4 0.74 3 bubble area = 3

Example:

http://www.branta.demon.co.uk/excel/bubblelegend.xls

You also need one of the add-ins that gives you the ability to define a
range as the input to data labels (Excel has needed this capability
since the 1980s, and it's a scandal that it still han't got it in 2006)

--
Del Cotter
NB Personal replies to this post will send email to
[email protected], which goes to a spam folder-- please send your
email to del3 instead.

[Del Cotter]

On Fri, 30 Jun 2006, in microsoft.public.excel.charting,
keesberbee <[email protected]>
said:

>Lately I am working with Bubble Charts and I noticed two problems that I
>cannot solve.
>
>(1) Comparing multiple Bubble Charts that have dissimilar bubble size
>data is not possible. The problem is that if you use the same size
>multiplier, the bubbles in each chart have the same size, despite the
>difference in bubble size values.

>Of course there are some ways to overcome this problem, but they cause
>a lot extra work or are not dynamic. I am looking for a Macro or VBA
>procedure that generates bubble sizes in multiple charts that are sized
>according to the same bubble size scale.

No need for a macro or VBA, in my opinion. If you put a dummy bubble
range in both your charts which consists of two bubbles whose sizes are
the minimum and maximum of *all* the bubbles in both charts, then the
bubble size will be stable across charts, and will dynamically update as
you change the data. Make the bubble line color and fill color "none",
so that they don't appear on the chart.

>(2) I also noticed that Bubble Charts in Excel don’t have a proper
>legend: they only inform about the third dimension (e.g. revenues). I
>am looking for a legend that informs the user about the bubble sizes
>and their values or ranges.

A while ago I wanted to make a legend that explained and labelled the
error bars on the data, not just the data point, and I tried making a
drawing, but it wasn't very elegant: the drawing didn't look exactly
like the data, making the result a bit amateurish in appearance.

Then I realised I didn't have to put up with that; I could have a
drawing that, by definition, looked exactly like an Excel data point,
just by making the "legend" out of a custom range with data labels. The
result was much better.

You can do the same with a bubble range whose content would be something
like this:

x-position y-position area explanatory text
---------- ---------- ---- ----------------
1.4 0.28 1 bubble area = 1
1.4 0.50 2 bubble area = 2
1.4 0.74 3 bubble area = 3

Example:

http://www.branta.demon.co.uk/excel/bubblelegend.xls

You also need one of the add-ins that gives you the ability to define a
range as the input to data labels (Excel has needed this capability
since the 1980s, and it's a scandal that it still han't got it in 2006)

--
Del Cotter
NB Personal replies to this post will send email to [email protected],
which goes to a spam folder-- please send your email to del3 instead.

[Jon Peltier]

"Del Cotter" <[email protected]> wrote in message
news:[email protected]...
> On Fri, 30 Jun 2006, in microsoft.public.excel.charting,
> keesberbee <[email protected]> said:
>
>>Lately I am working with Bubble Charts and I noticed two problems that I
>>cannot solve.
>>
>>(1) Comparing multiple Bubble Charts that have dissimilar bubble size
>>data is not possible. The problem is that if you use the same size
>>multiplier, the bubbles in each chart have the same size, despite the
>>difference in bubble size values.
>
>>Of course there are some ways to overcome this problem, but they cause
>>a lot extra work or are not dynamic. I am looking for a Macro or VBA
>>procedure that generates bubble sizes in multiple charts that are sized
>>according to the same bubble size scale.
>
> No need for a macro or VBA, in my opinion. If you put a dummy bubble range
> in both your charts which consists of two bubbles whose sizes are the
> minimum and maximum of *all* the bubbles in both charts, then the bubble
> size will be stable across charts, and will dynamically update as you
> change the data. Make the bubble line color and fill color "none", so that
> they don't appear on the chart.
>
>>(2) I also noticed that Bubble Charts in Excel don't have a proper
>>legend: they only inform about the third dimension (e.g. revenues). I
>>am looking for a legend that informs the user about the bubble sizes
>>and their values or ranges.
>
> A while ago I wanted to make a legend that explained and labelled the
> error bars on the data, not just the data point, and I tried making a
> drawing, but it wasn't very elegant: the drawing didn't look exactly like
> the data, making the result a bit amateurish in appearance.
>
> Then I realised I didn't have to put up with that; I could have a drawing
> that, by definition, looked exactly like an Excel data point, just by
> making the "legend" out of a custom range with data labels. The result was
> much better.
>
> You can do the same with a bubble range whose content would be something
> like this:
>
> x-position y-position area explanatory text
> ---------- ---------- ---- ----------------
> 1.4 0.28 1 bubble area = 1
> 1.4 0.50 2 bubble area = 2
> 1.4 0.74 3 bubble area = 3
>
> Example:
>
> http://www.branta.demon.co.uk/excel/bubblelegend.xls

I remembered using this approach after I sent my reply, then I decided it
would be too complex to describe. Good job.

> You also need one of the add-ins that gives you the ability to define a
> range as the input to data labels (Excel has needed this capability since
> the 1980s, and it's a scandal that it still han't got it in 2006)

Or in 2007 for that matter.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______