SOLVED

Excel bubble chart: how to size bubbles based on no. of items with identical x and y values?

%3CLINGO-SUB%20id%3D%22lingo-sub-2258841%22%20slang%3D%22en-US%22%3EExcel%20bubble%20chart%3A%20how%20to%20size%20bubbles%20based%20on%20no.%20of%20items%20with%20identical%20x%20and%20y%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258841%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20set%20of%20data%20with%20x%20and%20y%20columns%20containing%20values%20from%201%20to%205.%20I've%20placed%20all%20the%20rows%2Fitems%20in%20a%20bubble%2Fscatter%20chart.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EThe%20challenge%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EHow%20can%20I%20size%20the%20bubbles%20relative%20to%20eachother%20by%20number%20of%20items%20registered%20%3CEM%3E%22per%20coordinate%3C%2FEM%3E%22%20%2F%20items%20with%20identical%20(x%20%3CU%3Eand%3C%2FU%3E%20y)%20values%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20data%20table%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2244.42909760589319%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3EProbability%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EImpact%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%231%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E%232%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%233%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%234%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20bubble%20chart%20(just%20a%20transparent%20bubble%20chart%20placed%20on%20top%20of%20coloured%20cells)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20669px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270742iA8DBF6841A762920%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%203%20items%20corresponds%20to%20the%20(4%2C%201)%20coordinate%2C%20while%20only%201%20item%20corresponds%20to%20(4%2C%202)%2C%20I%20want%20the%20bubble%20in%20(4%2C%201)%20to%20be%20bigger.%20As%20I%20add%20more%20items%20with%20other%20coordinates%2C%20I%20want%20the%20bubble%20chart%20to%20update%20automatically%20with%20relative%20sizes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2258841%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

I have a set of data with x and y columns containing values from 1 to 5. I've placed all the rows/items in a bubble/scatter chart.

 

The challenge:

How can I size the bubbles relative to eachother by number of items registered "per coordinate" / items with identical (x and y) values?

 

 

Example data table:

 ProbabilityImpact
#141
#241
#341
#442

 

Example bubble chart (just a transparent bubble chart placed on top of coloured cells):

image.png

 

Since 3 items corresponds to the (4, 1) coordinate, while only 1 item corresponds to (4, 2), I want the bubble in (4, 1) to be bigger. As I add more items with other coordinates, I want the bubble chart to update automatically with relative sizes.

2 Replies
best response confirmed by Tormod Solem Slupphaug (Contributor)
Solution

@Tormod Solem Slupphaug 

A bubble needs 3 argument. You have to add a counter column:

ProbabilityImpactn
413
413
413
421

Select row 2-5 (not the headers) and insert a bubble and i should be Ok (if you use a 2-D chart. In a 3D chart you will se that you have stacked 3 bubbles on top of each other).

You can create the 3:d column with 

=COUNTIFS($A$2:$A$5,A2,$B$2:$B$5,B2)

Anther way is to use a Pivot table to create something like:

ProbabilityImpactn
413
421

But you cant use it in a pivotChart so it's kind of pointless

 

Thanks, your COUNTIFS formula is just what I was looking for.