Bubble chart questions

[gingerdip]

Hi there,
I am looking to span out the formulas automatically, like attached, what I did but only to few suppliers in col C
I find that I need to manually edit the formula to include the values in col L and M for my axis values.
Is there a way to pull or duplicate for all my suppliers in Col C is a faster way?

segmentation sample.xlsx

[Pete_UK]

I'm not sure exactly what you mean, but if you change the formula in F8 to this:

=IFERROR(AVERAGE(D8:E8),"")

and the one in K8 to this:

=IFERROR(AVERAGE(G8:I8),"")

and then copy both formulae down the columns, that will remove your errors.

Hope this helps.

Pete

[gingerdip]

I have changed the formula as advised, but issue is, how do I select the series such that I have all suppliers in col C in bubbles on my chart.
As you can see in the pic, my suppliers are few and until the supplier: GMM ONE TV COMPANY LIMITED
HOw should I change my data to show all suppliers in col C, their X and Y axis in col L and M. and bubble size in Col O

segm select data.JPG

segmentation sample.xlsx

[gingerdip]

hi, any help here?

[MrShorty]

HOw should I change my data to show all suppliers in col C, their X and Y axis in col L and M. and bubble size in Col O

I'm not sure we understand your question. Perhaps to promote discussion, I will ask this question: Why not simply have one data series? X values L8:L62, Y values M8:M62, bubble size O8:O62.

[gingerdip]

Attachment 449807

HI
I will try to explain what I am trying to achieve here.
I created a questionnaire in row 7. This allows the user to rate the supplier based on my questions.
The ratings in col D , E, will be averaged out in col F
Ratings in col G H I J will be averaged out in col K.

So each supplier in col C should have both ratings in X and Y axis. Bubble size from Col M, which denotes the spend size. Attached refer

[gingerdip]

segmentation sample forum.xlsx
HI
I will try to explain what I am trying to achieve here.
I created a questionnaire in row 7. This allows the user to rate the supplier based on my questions.
The ratings in col D , E, will be averaged out in col F
Ratings in col G H I J will be averaged out in col K.

So each supplier in col C should have both ratings in X and Y axis. Bubble size from Col M, which denotes the spend size. Attached refer

[gingerdip]

to simplify my qns, attached.
How do I add a legend to my bubble chart where I will see different colors, each bubble represent the supplier in col B?

segmentation sample FORUM 1.xlsx

[MrShorty]

to simplify my qns, attached.
How do I add a legend to my bubble chart where I will see different colors, each bubble represent the supplier in col B?

I think I understand the question now.

You can use this latest example, and select the "vary colors by point" option (in format series -> fill -> vary colors by point checkbox). If it creates a legend, then the legend entries will be the x values and the not the company names. I do not see a way to change this, so the legend will not be useful with this approach.

The easiest approach is probably the approach you started with -- a separate series for each data point. This will be somewhat tedious to put together the first time. Once it is put together, it should be easy to enter or paste new data into the spreadsheet and the chart will update accordingly. Or save the original spreadsheet as a template for future use. Is there some other reason you are avoiding this approach?