Excel Bubble Chart with Upper & Lower bounds

[tranhaithang]

Hi all,
Here's my problem:
- I'm making bubble chart to see the impact of forecast on TO performance:

• X-Axis: TO Change
• Y-Axis: Forecast bias (%)
• Size: TO Value

- I want to add in one more criteria, which is the upper & lower bound for forecast bias:

• -2.5% to +2.5% is the bound where the forecast bias should be. Forecast bias higher or lower than this bound needs to investigate
• Every categories with TO decrease while forecast bias is within range (-2.5+2.5%) is considered bad performance

I want to highlight these upper & lower bound with area colored (like below pictures)
bubble chart example.png
Is there anyway to make this automatically in bubble chart?
One of my thought is to make another chart behind this with the selected criteria. the problems is the axis is not matching with each other & it is troublesome to change when the data change

Could you help me with any better solution?
Thank you

[Andy Pope]

Bubble charts do not allow for combination with other chart types so all the usual work arounds will not work.

You could play with the gradient fill of the plot area, but this will only allow for a single coloured band.

Using a second chart behind the first may be the simplest solution.

You might be able to build the pattern using VBA and shapes, then copy the grouped shape and use as the plot area fill.

[tranhaithang]

Hi Andy,
Thank man.
Hmm, how do I define the stop position to stop at 2.5%. I see that you do that on manually, but when the chart is updated with another table, the distribution will change, then it will now follow through

Another thought, can we set excel formula to adjust the minimum/maximum axis? if so I can set both charts (front & behind) to update accordingly

Thanks in advance
Thang Tran

[Andy Pope]

You would have to write vba code to update axis parameters or set the gradient positions. None of these can be linked directly to cells.

[tranhaithang]

Hmm,

This is a simple one time analysis so I think I will stay with the temporary solution instead.

Thank for the help!

Thang Tran