Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Scale to Chart | Excel Discussion (Misc queries) |