I have been given an excel document which colors a certain column based on a conditional format. If the cell's value is within the top 30%, it's colored Green, within the bottom 30% is Red, otherwise Yellow.



I need to export this color value, so I've been trying to create a formula to represent the value as either 'R', 'Y' or 'G'. I was able to get one of the other columns working (not the same formula), which was based on the cell's value:

= IF(I:I>0.45,"G",IF(I:I< 0.35,"R","Y"))

but for this column (J:J - formatted as currency) I don't know how to determine if the cell's value is within the upper or lower 30% for that column. Can someone help me with the proper formula to do this?

= IF(J:J...??,"G",IF(J:J...??,"R","Y"))

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.