10 Replies Latest reply: Apr 15, 2016 4:59 PM by Stefan Wühl RSS

    Scatter chart - auto spacing the bubbles

    Paul Samarel

      I'm trying to get a bubble chart that will not overlap the bubbles.  Instead it should space and center the bubbles.

      And it should respect my filter (so I can't do it in the script)

       

      For example, lets say I want to plot countries by continent.

       

      My data would be:

       

      Country / Continent

      USA / North America

      Canada / North America

      Mexico / North America

      Venezuela / South America

      Peru / South America

      Argentina / South America

      Brazil / South America

      Ecuador / South America

      Italy / Europe

       

      I would imagine the solution would be:

      For Spacing:  Auto numbering the countries by continent (perhaps with a combinantion of Aggr/RowNo/Range).

      Then for Centering:  Adding (half of) the maximum count of countries per continent minus the max amount for that continent.

       

      So, since there are 3 in North Am., 5 in South Am., and 1 in Europe, I would get:

       

      Country / Continent / Position

      USA / North America = 1 + 5 - (5-3)/2 = 2

      Canada / North America = 2 + 5 - (5-3)/2 = 3

      Mexico / North America = 3 + 5 - (5-3)/2 = 4

      Venezuela / South America = 1 + 5 - (5-5)/2 = 1

      Peru / South America = 2 + 5 - (5-5)/2 = 2

      Argentina / South America = 3 + 5 - (5-5)/2 = 3

      Brazil / South America = 4 + 5 - (5-5)/2 = 4

      Ecuador / South America = 5 + 5 - (5-5)/2 = 5

      Italy / Europe = 1 + 5 - (5-1)/2 = 3

       

      Then I can plot this on a scatter.

      So if someone can help with that calculation, that would be great.

      Of course I would welcome any other way to get this chart.