Showing Significance Testing in Bubble Charts

[Guest]

We are preparing a bubble chart in which the x-axis shows scores from a
survey, the y-axis shows relative importance, and the z-dimension shows the
percentage point change in the score from the previous period. We have set
the default color of the bubbles to grey (which means no significant change),
and would like to make the significant changes colored in red (negative) or
green (positive). We have already done the stat testing, and can mark the
significance testing with a -1 (negatively significant), 0 (insignificant),
and +1 (positively significant). We would like to be able to automate this
(i.e., not do manually), as we have at least 100 different charts we need to
create. Is there any way to automate this? The data array looks like this:

2006 Score Importance %point change
Significant?
54 .27 -2
0
77 .59 8
1
82 .47 3
0
68 .78 -5
-1

So we would have a grey bubble for row 1 and 3, a red bubble for row 4, and
a green bubble for row 2. Any suggestions?

 

[Andy Pope]

Hi,

This is what I suggested to a very similar post a couple of days ago.

The normal work around for making a charts formatting conditional is to
use multiple series. Then via formula only plot those points
(bubble/columns/markers/bars) that are valid.

See Jon's page for examples.
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Cheers
Andy