Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Better Scatter/Bubble Charts

Latest post Thu, Feb 21 2008 8:22 PM by Jim Thomlinson. 0 replies.
  • Thu, Feb 21 2008 8:22 PM

    • Jim Thomlinson
    • Top 10 Contributor
    • Joined on Thu, Jan 10 2008
    • Vancouver, Canada
    • Posts 84
    • Points 1,256

    Better Scatter/Bubble Charts

    Scatter and bubble charts are great when you want to relate two different items. Shame they are so poorly implimented. For example here is what I would like to do.

    I work  in the Grocery industry. In the produce department we track the Days of Supply, the shrink rate and the margin. If your days of supply are too high (for example I have enough bananas to last 15 days) then stuff starts to go bad and you have to throw it out. When you throw it out your overall margin decreases. We have over 100 locations. The best way to track something like this is with a pivot table. I would like to have a scatter chart created from this pivot table with the Days of Supply across the bottom and the shrink % up the side. Most of the locations will be clustered along the diagonal. If I convert that to a bubble chart then I can have the bubble size indicate the margin %. In this scenario all of the large bubbles should be near the origin and the bubbles will get smaller as you move away from the axis. If I show that chart to someone and give a quick explanation they will be able to relate how ordering too much stuff increases their shrink and decreases their margin. Additionally by looking at the outlyers I will be able to determine who has inventory problems. For this to be usefull I would like to be able to hover over a point and have it return the location number. Since our locations are broken up into regions I would also like to be able to have the point color coded by region if I add that dimension into my pivot table. Double clicking a point should drill down to the source data for further analysis.

    That is just one example. I could probably come up with dozens.

    • Post Points: 5
Page 1 of 1 (1 items) | RSS
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.