Friday Challenge – Conditionally Control Excel Bubble Chart Colors

Hi All,

Sorry this is a little late.  But better late then never Smile.

Here is this week’s Friday Challenge (on Sunday.  I will post my results on Tuesday to give you more time):

The challenge is how can you create an Excel chart that conditionally changes the colors in an Excel Bubble chart.  Digest the request below:

“I’ve used Excel 2010 to create a bubble chart plotting the risk-reward analyses of a portfolio of research projects. X-axis is the potential reward of the project, y-axis the probability of success, z-axis (bubble size) is the budget.
I would like to control the color of the bubbles by a fourth, qualitative text attribute of the project type. So each project (bubble) would be characterized as either “New Product”, “Optimization” or “Tech Transfer”.
Can this be done by conditional formatting? If you could point me in the right direction, it would be most appreciated.   – Cheers,  John”

Here is our “fake” data set that I created based on the request:

2 Type X Reward Y Probability Z Bubble Size
3 New Product 8 8 80
4 New Product 9 10 29
5 Optimization 1 9 13
6 New Product 10 0 62
7 Tech Transfer 9 9 52
8 Optimization 7 7 12
9 Tech Transfer 9 10 22
10 New Product 2 0 0
11 New Product 4 7 29
12 Tech Transfer 8 2 30

So give this some thought and think of how you can set this up in an Excel spreadsheet.

Leave me comments on how you would handle this problem.  Also, if you have a challenge that you are looking to see if it can be done in Excel, leave a detailed request in the comments below.