Conditional Formatting -- Highlighting the Proper Percentile

[thedon986]

Hi all,

I have a question about conditional formatting and using a formula to highlight the correct percentile that a rate falls into. Additionally, my rates are in percentages while the percentiles are in number format. A data example is below. Can someone help me develop a formula that would highlight the correct percentile based on the current rate?? I have not been able to figure it out. In the example below the 84.48 is what I want to highlight to show that the rate falls into the 50th percentile, but should that rate increase I would want the conditional formatting to highlight the 75th percentile (and only that percentile) instead.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Current Rate[/TD]
[TD]10th[/TD]
[TD]25th[/TD]
[TD]50th[/TD]
[TD]75th[/TD]
[TD]90th[/TD]
[/TR]
[TR]
[TD]89.33%[/TD]
[TD]69.47[/TD]
[TD]77.13[/TD]
[TD]84.48[/TD]
[TD]89.35[/TD]
[TD]92.54[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Excel 2010

	A	B	C	D	E	F
current	10th	25th	50th	75thg	90th

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]89.33%
[/TD]
[TD="align: right"]69.47
[/TD]
[TD="align: right"]77.13
[/TD]
[TD="bgcolor: #0070C0, align: right"]84.48
[/TD]
[TD="align: right"]89.35
[/TD]
[TD="align: right"]92.54
[/TD]

</tbody>

Sheet5​

Try using this formula in conditional formatting

=IFERROR(MATCH($A$2*100,$B$2:$F$2,1),1)=COLUMN(B2)-1

 

[thedon986]

Hi Scott,

Thanks for the reply. This works for my data example, but what if the rate (A2) is not adjacent to the percentiles but separated by other columns? It is not working for that. I didn't provide all columns in my sheet but for reference, the rate is column J and the percentiles begin at column P. I think the COLUMN formula at the end is throwing it off.

 

[Scott T]

Adjust the number subtracted from Column(P2) where P2 is where the first percentile (69.47 in your example) is

Change the number so that this results in 1 for the first column (column P is the 16th column so this is 16-15=1).

COLUMN(P2)-15

=IFERROR(MATCH($J$2*100,$P$2:$T$2,1),1)=COLUMN(P2)-[COLOR=#ff0000]15[/COLOR]

 

[thedon986]

Okay, I get it now. That works. Thanks!!

 

[Scott T]

You're welcome.