Using Conditional Formatting to Highlight an Entire Row based on Percentile Rank of a Single Cell

[paydog23]

I am familiar with how to apply conditional formatting to an entire row based on the value of a single cell within that row for simpler scenarios--for example, the cell contains a specific number or string. However, the same methodology does not appear to work for the percentile rank function. I tried the following formula:

=$F2 >=PERCENTILE($F$2:$F$44,0.9)

But it only highlighted the actual cells that exceeded the 90th percentile. My dataset looks like this:

XML Sampling TDT Migration Performance Job Duration Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Job ID	Dataset	Start_Time	End_Time	Total_Hours	Records_Per_Hour	Accepted_In_Staging	Rejected_In_Staging	Rejected_In_Migration	Changed_In_Migration
2	14736	FINDING_2017-01-01_to_2017-03-31_	3/10/20 11:17 AM	4/6/20 7:24 PM	656.12	391	256,749	191,711	218,063	0
3	14738	FINDING_2017-01-01_to_2017-03-31_	4/1/20 2:09 PM	4/7/20 3:08 AM	132.98	3,545	471,395	2,101	444,158	0
4	14743	FINDING_2017-01-01_to_2017-03-31_	4/6/20 2:32 PM	4/7/20 10:47 AM	20.25	23,405	473,955	18	473,478	0
5	14749	FINDING_2017-01-01_to_2017-03-31_	4/16/20 12:00 PM	4/17/20 12:19 AM	12.32	38,481	473,955	18	66,400	224
6	14792	FINDING_2011-01-01_to_2011-03-31_	6/15/20 2:38 PM	7/17/20 2:57 PM	768.32	534	410,385	369	1	359
7	14838	FINDING_2011-04-01_to_2011-06-30_	8/5/20 1:41 PM	8/5/20 2:54 PM	1.22	51,969	-	667	-	0
8	14840	FINDING_2011-04-01_to_2011-06-30_	8/6/20 10:06 AM	8/6/20 8:47 PM	10.68	45,552	486,643	667	63,230	460
9	14846	FINDING_2011-07-01_to_2011-09-30_	8/14/20 3:09 PM	8/14/20 10:26 PM	7.28	68,803	501,117	339	3	639
10	14848	FINDING_2011-10-01_to_2011-12-31_	8/19/20 10:57 AM	8/20/20 1:36 PM	26.65	15,541	414,178	367	1	395
11	14850	FINDING_2012-01-01_to_2012-03-31_	8/20/20 3:22 PM	8/20/20 9:32 PM	6.17	64,658	398,726	395	-	283
12	14852	FINDING_2012-04-01_to_2012-06-30_	8/21/20 8:08 AM	8/21/20 2:31 PM	6.38	72,808	464,757	529	1	544
13	14861	FINDING_2012-07-01_to_2012-09-30_	8/25/20 8:22 AM	8/25/20 3:47 PM	7.42	70,365	521,877	519	521,877	0
14	14863	FINDING_2012-10-01_to_2012-12-31_	8/25/20 4:18 PM	8/27/20 12:22 PM	44.07	9,257	407,913	242	1	454
15	14873	FINDING_2013-01-01_to_2013-03-31_	9/9/20 10:40 AM	9/9/20 8:00 PM	9.33	46,310	432,225	333	-	506
16	14875	FINDING_2013-04-01_to_2013-06-30_	9/10/20 8:07 AM	9/10/20 4:12 PM	8.08	56,850	459,541	329	1	556
17	14877	FINDING_2013-07-01_to_2013-09-30_	9/11/20 7:55 AM	9/11/20 4:54 PM	8.98	56,996	512,017	226	-	670
18	14879	FINDING_2013-10-01_to_2013-12-31_	9/11/20 6:36 PM	9/12/20 2:13 AM	7.62	52,655	401,052	81	3	469
19	14885	FINDING_2014-01-01_to_2014-03-31_	9/15/20 4:52 PM	9/16/20 6:43 AM	13.85	30,991	429,232	75	1	436
20	14887	FINDING_2014-04-01_to_2014-06-30_	9/16/20 9:34 AM	9/17/20 9:07 AM	23.55	21,805	513,505	198	1	527
21	14890	FINDING_2014-07-01_to_2014-09-30_	9/17/20 10:10 AM	9/18/20 8:11 AM	22.02	25,333	557,750	222	-	591
22	14904	FINDING_2014-10-01_to_2014-12-31_	9/22/20 8:25 AM	9/22/20 6:20 PM	9.92	42,543	421,883	176	139,432	358
23	14908	FINDING_2015-01-01_to_2015-03-31_	9/23/20 3:40 PM	9/24/20 4:42 AM	13.03	33,010	430,233	305	106,262	486
24	14913	FINDING_2015-07-01_to_2015-09-30_	9/25/20 9:26 AM	9/26/20 2:48 AM	17.37	28,911	502,088	87	1	393
25	14916	FINDING_2015-10-01_to_2015-12-31_	10/20/20 8:20 AM	10/20/20 3:27 PM	7.12	54,419	387,285	214	2	196
26	14918	FINDING_2016-01-01_to_2016-03-31_	10/20/20 3:33 PM	10/20/20 11:08 PM	7.58	55,499	420,870	21	-	161
27	14920	FINDING_2016-04-01_to_2016-06-30_	10/21/20 8:32 AM	10/21/20 8:20 PM	11.8	39,441	465,400	43	-	224
28	14925	FINDING_2016-07-01_to_2016-09-30_	10/27/20 10:05 AM	11/4/20 1:13 PM	195.13	2,712	506,472	95	280,838	365
29	14927	FINDING_2016-10-01_to_2016-10-31_	11/4/20 1:09 PM	11/5/20 10:40 AM	21.52	7,397	159,156	4	-	133
30	14930	FINDING_2016-11-01_to_2016-11-15_	11/6/20 3:53 PM	11/6/20 4:59 PM	1.1	82,540	90,794	-	-	37
31	14932	FINDING_2016-11-16_to_2016-12-31_	11/10/20 9:23 AM	11/10/20 11:28 AM	2.08	74,605	155,427	21	-	119
32	14935	FINDING_2017-01-01_to_2017-03-31_	11/13/20 3:03 PM	11/13/20 8:16 PM	5.22	90,374	471,450	18	467,095	0
33	14937	FINDING_2017-04-01_to_2017-06-30_	11/16/20 9:45 AM	11/16/20 7:12 PM	9.45	49,248	465,392	16	11,400	138
34	14939	FINDING_2017-07-01_to_2017-09-30_	11/17/20 11:32 AM	11/18/20 12:03 AM	12.5	44,231	552,892	84	-	169
35	14941	FINDING_2017-10-01_to_2017-12-31_	11/18/20 8:27 AM	11/18/20 10:07 AM	1.67	37,919	63,199	151	-	0
36	14943	FINDING_2018-01-01_to_2018-03-31_	11/18/20 10:11 AM	11/18/20 9:49 PM	11.63	41,538	483,227	37	1	146
37	14945	FINDING_2018-04-01_to_2018-06-30_	11/19/20 8:07 AM	11/19/20 9:00 PM	12.88	39,956	514,762	30	-	220
38	14948	FINDING_2011-01-03_to_2011-01-03_34671	11/24/20 2:44 PM	11/24/20 2:45 PM	0.02	720	12	-	12	0
39	14950	FINDING_2018-07-01_to_2018-09-30_	11/25/20 10:36 AM	11/25/20 8:39 PM	10.05	57,288	575,749	192	251,290	279
40	14952	FINDING_2018-10-01_to_2018-12-31_	11/30/20 7:24 AM	11/30/20 3:08 PM	7.73	57,998	448,521	15	3	128
41	14954	FINDING_2019-01-01_to_2019-03-31_	12/2/20 9:51 AM	12/3/20 3:51 AM	18	25,485	458,721	8	410,016	0
42	14959	FINDING_2019-07-01_to_2019-09-30_	12/18/20 1:48 PM	12/23/20 7:23 PM	125.58	4,344	545,506	608	-	171
43	14963	FINDING_2019-10-01_to_2019-12-31_	12/28/20 8:03 AM	12/28/20 12:50 PM	4.78	87,799	419,973	801	-	82
44	14965	FINDING_2020-01-01_to_2020-02-05_	12/28/20 1:15 PM	12/28/20 1:55 PM	0.67	81,185	54,123	162	-	0
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F44	Expression	="$F2 >=PERCENTILE($F$2:$F$44,0.9)"	text	NO

 

[Eric W]

You only have cells F2:F44 selected. You need to change that to A2:J44 and use the same formula.

 

[paydog23]

You only have cells F2:F44 selected. You need to change that to A2:J44 and use the same formula.

Thank you Eric--that solved my problem along with removing the double quotes.

 

[Eric W]

Happy to help! Thanks for the feedback.