I know what I want to do, I just can’t do it! So I need your help.

I need to plot multiple series on a scatter plot, but I want the series to be identified automatically. I am currently using Excel 2003 but will soon be using 2007.

WA_ID CGT1 CGT2 CGT3 Compliance CGT_Hits Scaled_Likelihood_Score Scaled_Consequence_Score
Person 1 0 0 0 Compliant 0 51.667002 32.636
Person 2 0 0 0 Not compliant 0 110.23166 0.4184
Person 3 0 0 1 Extreme non compliance 1 97.252165 8.7866
Person 4 1 0 0 Compliant 1 72.064143 64.0167
Person 5 0 0 1 Compliant 1 58.072604 6.2761
Person 6 0 1 0 Not compliant 1 99.833499 51.8828
Person 7 0 0 0 Compliant 0 97.372276 25.9414
Person 8 0 0 1 Not compliant 1 86.904757 19.2469
Person 9 0 1 0 Extreme non compliance 1 70.575758 106.9231
Person 10 1 0 0 Compliant 1 78.354254 119.2308
Person 11 1 0 0 Compliant 1 81.189668 17.5732
Person 12 0 0 0 Extreme non compliance 0 116.281853 36.4017
Person 13 1 0 1 Not compliant 2 107.142857 4.1841
Person 14 0 1 0 Extreme non compliance 1 161.583012 192.6923
Person 15 1 0 0 Extreme non compliance 1 99.101188 22.5941


This is some example data. I want two things:-

1. I want the columns headed CGT 1 –3 to be plotted as separate series IF there is a ‘1’ value in the row. The x & y values are in the Scaled Likelihood Score (x) and Scaled Consequence Score (y).

As an example, series CGT 1 would include the points Person 4, Person 10, Person 11, Person 13 & Person 15 while series CGT 2 would include Person 6, Person 9 & Person 14.

2. In the Column ‘Compliance’, there are three different classifications. I would like each of those to be plotted as separate series, again using the Scaled Likelihood Score (x) and Scaled Consequence Score (y) as the x & y values.