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:
=$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 |