Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
keesberbee
 
Posts: n/a
Default Problem with BUBBLE CHART scale and legend


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

  #2   Report Post  
Posted to microsoft.public.excel.charting
HEK
 
Posts: n/a
Default Problem with BUBBLE CHART scale and legend

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 dont 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


  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default Problem with BUBBLE CHART scale and legend


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.


--
keesberbee
------------------------------------------------------------------------
keesberbee's Profile: http://www.excelforum.com/member.php...o&userid=35899
View this thread: http://www.excelforum.com/showthread...hreadid=557235

  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default Problem with BUBBLE CHART scale and legend

(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" wrote
in message ...

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



  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 560
Default Problem with BUBBLE CHART scale and legend

On Fri, 30 Jun 2006, in microsoft.public.excel.charting,
keesberbee
said:

(2) I also noticed that Bubble Charts in Excel dont 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
, which goes to a spam folder-- please send your
email to del3 instead.


  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 560
Default Problem with BUBBLE CHART scale and legend

On Fri, 30 Jun 2006, in microsoft.public.excel.charting,
keesberbee
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 dont 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 ,
which goes to a spam folder-- please send your email to del3 instead.
  #7   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default Problem with BUBBLE CHART scale and legend


"Del Cotter" wrote in message
...
On Fri, 30 Jun 2006, in microsoft.public.excel.charting,
keesberbee 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
_______


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Scale to Chart gti_jobert Excel Discussion (Misc queries) 1 March 27th 06 03:13 PM


All times are GMT +1. The time now is 09:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"