I think I figured it out... almost. The following works in most cases

=IF(J:J>=PERCENTILE(J2:J96,0.7),"G",IF(J:J<=PERCEN TILE(J2:J96,0.3),"R","Y"))

But for some reason, there are 2 rows which don't match the color of the cells using the conditional formatting. Is the 'Top 30%' and 'Bottom 30%' used for the conditions calculated differently?

I tried using 'Rank' as well to determine the values, but got the same results (I set a column to the RANK of column J:J, then used the above formula using that column - the value was the same as the original formula)

Any suggestions? I need to do the same for another excel sheet forthcoming, and I would like to get this working before that one is required.