Quadrant Chart with Bubbles

[jasont]

Hi All

I have wracked my brain for a few days now trying to figure out how to get a Quadrant Graph with Bubbles to work.

I need a Quadrant Graph that plots Revenue (dollars) & Margin (%); with the bubbles being either Total Quantity Sold or a Price Range (5 different price ranges so only 5 different size bubbles.

The number of points in each quadrant could be in the 100's so I need a way to easily count that. The preference is to also have the axis dynamic so that I can move them and see the impact on the number of points in each quadrant.

My thought was to create a pivot out of the data and base the graph on that so that I could also view the Quadrant graph at different levels by just manipulating the pivot; ie SKU level of a product / a group of products / a family of products / different country / different region etc etc

Wow, for my little brain this is just too much. I am good with excel, but this is my first venture in to this type of Graph.

I have attached a shortened version of the basic data that I would like to graph and analyse; I am using Excel 2010

[Andy Pope]

You will not be able to plot a bubble chart directly from a pivot table as that chart style is not supported.

You could use a standard chart based on the data from a pivot table but you would need code to repopulate the chart when the table changes.

You could use Filters to reduce data content within a normal chart.

As to counting items in quadrent that would require formula or code and more detail about what defines a quadrent.
Adjustment of the scales would require code.

[jasont]

Hi Andy

thanks for your time. I think the bubble chart is going to be extremely messy.

The aim is to "box" items a bit like you would with a BCG matrix; but in this instance using revenue / margin as the measures.

I have a set of data (attached) where I can create an XY scatter, simple enough. But what I want to do is split the scatter in to 4 quandrants; There are no negative figures and the points are different types of measures (revenue / margin). I would like to be able to manipulate the intersection of the quadrant easily so that I can see the impact on the number of products in each quadrant if I choose to set my revenue and margin at specific minimum levels.

Thanks for your help, this is driving me nuts!

[Andy Pope]

Here is example code for linking scale parameters to cells
http://peltiertech.com/Excel/Charts/...nkToSheet.html

You could add some calculated values to draw the 2 dividing lines to form the quadrents.
And once you know the quadrent limits you can use formula to calculate number counts for each quadrent.

[jasont]

Thanks Andy for your help. I have just had some help from a senior colleague of mine who had an unbelievable simple solution. I thought I would share it in order to share the knowledge...your feedback on the solution is welcome...

cheers
Jason

[Andy Pope]

Along the lines of what I suggested. You can do it with a couple of pairs of coordinates.

In order to stop the scale expanding you will either need to fix the Min/Max values or use the VBA solution to alter them based on cell values.

[jasont]

hi andy

how do i incorporate having the data from book102 where the axis has 2 different measurements?

[Andy Pope]

Not sure I follow.

This example has input for min/max values for both axes.
The dividers will be plotted at the half way point.

[jasont]

hi andy

i dont have much hair so i am scraping my scalp away at the moment...could you do me a favour and put the count formula's back in for each quadrant...after that I am going to have to work my through it from the beginning and try to replicate it to learn something...

cheers
jason

[Andy Pope]

quadrent content formula included

[jasont]

you guys are brilliant!