Bubble Chart - Venn Diagram

[Bill Hall]

I am using three points to create a Venn Diagram with the Excel Bubble Chart.

At the moment, the bubbles have no FILL; if I use a different colour FILL in
each circle, how can I use the TRANSPARANCY so that I might be able to see
the common, and triple overlapping area?

Is there any relationship between the scale of the grid numbers (x,y), and
the radius number (r)? (There doesn't appear to be!)

What does the "scale the bubble size nnn% to the default" mean?

Thanks in anticipation

Bill Hall

 

[Jon Peltier]

Bill -

Unfortunately, you cannot change the transparency of the bubbles in the
bubble chart. However, you can still get this effect by using custom
markers for the bubble chart. Draw come circles in the worksheet (any
size, they will scale to the size of the bubbles). Color them
appropriately and assign a transparency value; old Excel versions allow
only Yes (50%??) or No to transparency, while Excel 2003 offers any
value between 0 and 100%. Copy a circle, select the chart series, then
the point, then use Ctrl-V to paste the circle as the custom marker for
that bubble. Repeat for the other colors.

In a bubble chart, Excel has a default maximum size for a bubble, which
it uses for the largest bubble value in the chart. The default is about
25% of the smaller of the height or width of the plot area of the chart.
So there is no direct correlation with the axis scales. If you change
the default percentage (Excel accepts 0 to 300%), you merely tell Excel
how large to make the largest bubble.

More about bubble charts and custom chart markers:

http://peltiertech.com/Excel/ChartsHowTo/HowToBubble.html
http://peltiertech.com/Excel/Charts/ControlBubbleSizes.html
http://peltiertech.com/Excel/ChartsHowTo/CustomMarkers.html

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

 

[Bill Hall]

Hi Jon

Many thanks for your advice and help.

I was able to produce the type of graph that I had intended. The idea about
the hidden, scaling circle worked perfectly.

Again, thanks

Bill

 

[kalan]

Hi Jon,

a question on this response related to bubble sizes. Say I have 10 data
points that represent averages. The differences among the averages are not
very big and therefore bubble size differences are not that evident. The size
represents one of my 4 factors on a chart and I'd really like to keep it.

The question is: Is there any way to "tell excel" to increase bubble size at
a greater rate from one point to another. So, if you have one data point with
an average of 2 and another with an average of 3, you want excel to augment
the circle size for the data point with an average of 3 more than what it
normally would do as compared to the size of the point with an average of 2.
Can I do it manually?

Many thanks for your help.
Kalan

 

[Jon Peltier]

Put a formula into the range that defines the bubble sizes, and have the
formula transform the small change in variable into a large change in bubble
size. Bubbles aren't really good for showing a continuous variable, but you
could also make a series with three bubble sizes, 1 for small, 2 for medium,
and 4 for large, or similar values.

- Jon