+ Reply to Thread
Results 1 to 3 of 3

Bubble Chart Scaling (Probability Circle?)

  1. #1
    Registered User
    Join Date
    11-10-2016
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    9

    Bubble Chart Scaling (Probability Circle?)

    Hi,

    Maybe this is more of a math question but I'm trying to re-create the below (except with more accurate circles rather than all being the same size). My data would change month to month and the bubbles would update accordingly but if I could replicate this than I can populate it with my data.

    Probability Circle.png

    The problem I'm having is that the scaling is throwing me off and I'm not sure how to use it to adjust my axis positions.

    Right now, I have the X-axis and Y-axis at 0-100.
    My bubble size is the total of each category (28 for Cats, 19 for Dogs, 15 for Birds). I'm playing around with it but have had no avail. Not sure if this is necessarily an "excel" question but if I could better understand the scaling works, maybe I could figure it out myself.

    Thanks,
    Dan

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,765

    Re: Bubble Chart Scaling (Probability Circle?)

    As far as how Excel scales the size of the circles in a bubble chart, my copy of Excel gives me options for having the size of the circle be related to "area" (size=f(r^2)) or width (size=f(r)) where r is the radius of each circle. A quick test shows how this works:
    1) I set up a fixed grid chart like yours, based on fixed (non-overlapping) positions of the 3 circles. I used random numbers for the circle size sorted in descending order (so the first circle would always be largest).
    2) Then I compared the size of the three circles in both area and width options. My observations:
    3a) The largest circle was always the same size, and this was the same for both area and width options.
    3b) In width/radius mode, the radius of each smaller circle was proportional to the max circle size -- r(i)/r(max)=z(i)/z(max)
    3c) In area mode, the area (pi*r^2) of each circle was proportional to the max circle size -- a(i)/a(max)=z(i)/z(max) -> r^2(i)/r^2(max)=z(i)/z(max)
    3d) I cannot tell how Excel decides how large to make the largest circle, but there is a "scale bubble size to" option that allows me to make the bubbles larger or smaller.

    It's not clear to me what key elements of the chart you are trying to make larger/smaller here. I would expect that, with that information, it should mostly be an algebra/geometry problem to figure out where the center of each circle should be and how large to make each bubble to get the desired effect.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,173

    Re: Bubble Chart Scaling (Probability Circle?)

    You might find it easier to draw the circles.
    Use series and plot circles using the value as a radius.

    Here is an example to get ou started. You will need to add extra series to plot the values within each sector.
    Link data labels to values and the x and y value will not be the one you want to display.

    You may also need to adjust min/max values of the axis if your data was to change.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1