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

paydog23

New Member
Joined
Jul 12, 2017
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
1Job IDDatasetStart_TimeEnd_TimeTotal_Hours Records_Per_Hour Accepted_In_Staging Rejected_In_Staging Rejected_In_Migration Changed_In_Migration
214736FINDING_2017-01-01_to_2017-03-31_3/10/20 11:17 AM4/6/20 7:24 PM656.12391256,749191,711218,0630
314738FINDING_2017-01-01_to_2017-03-31_4/1/20 2:09 PM4/7/20 3:08 AM132.983,545471,3952,101444,1580
414743FINDING_2017-01-01_to_2017-03-31_4/6/20 2:32 PM4/7/20 10:47 AM20.2523,405473,95518473,4780
514749FINDING_2017-01-01_to_2017-03-31_4/16/20 12:00 PM4/17/20 12:19 AM12.3238,481473,9551866,400224
614792FINDING_2011-01-01_to_2011-03-31_6/15/20 2:38 PM7/17/20 2:57 PM768.32534410,3853691359
714838FINDING_2011-04-01_to_2011-06-30_8/5/20 1:41 PM8/5/20 2:54 PM1.2251,969-667-0
814840FINDING_2011-04-01_to_2011-06-30_8/6/20 10:06 AM8/6/20 8:47 PM10.6845,552486,64366763,230460
914846FINDING_2011-07-01_to_2011-09-30_8/14/20 3:09 PM8/14/20 10:26 PM7.2868,803501,1173393639
1014848FINDING_2011-10-01_to_2011-12-31_8/19/20 10:57 AM8/20/20 1:36 PM26.6515,541414,1783671395
1114850FINDING_2012-01-01_to_2012-03-31_8/20/20 3:22 PM8/20/20 9:32 PM6.1764,658398,726395-283
1214852FINDING_2012-04-01_to_2012-06-30_8/21/20 8:08 AM8/21/20 2:31 PM6.3872,808464,7575291544
1314861FINDING_2012-07-01_to_2012-09-30_8/25/20 8:22 AM8/25/20 3:47 PM7.4270,365521,877519521,8770
1414863FINDING_2012-10-01_to_2012-12-31_8/25/20 4:18 PM8/27/20 12:22 PM44.079,257407,9132421454
1514873FINDING_2013-01-01_to_2013-03-31_9/9/20 10:40 AM9/9/20 8:00 PM9.3346,310432,225333-506
1614875FINDING_2013-04-01_to_2013-06-30_9/10/20 8:07 AM9/10/20 4:12 PM8.0856,850459,5413291556
1714877FINDING_2013-07-01_to_2013-09-30_9/11/20 7:55 AM9/11/20 4:54 PM8.9856,996512,017226-670
1814879FINDING_2013-10-01_to_2013-12-31_9/11/20 6:36 PM9/12/20 2:13 AM7.6252,655401,052813469
1914885FINDING_2014-01-01_to_2014-03-31_9/15/20 4:52 PM9/16/20 6:43 AM13.8530,991429,232751436
2014887FINDING_2014-04-01_to_2014-06-30_9/16/20 9:34 AM9/17/20 9:07 AM23.5521,805513,5051981527
2114890FINDING_2014-07-01_to_2014-09-30_9/17/20 10:10 AM9/18/20 8:11 AM22.0225,333557,750222-591
2214904FINDING_2014-10-01_to_2014-12-31_9/22/20 8:25 AM9/22/20 6:20 PM9.9242,543421,883176139,432358
2314908FINDING_2015-01-01_to_2015-03-31_9/23/20 3:40 PM9/24/20 4:42 AM13.0333,010430,233305106,262486
2414913FINDING_2015-07-01_to_2015-09-30_9/25/20 9:26 AM9/26/20 2:48 AM17.3728,911502,088871393
2514916FINDING_2015-10-01_to_2015-12-31_10/20/20 8:20 AM10/20/20 3:27 PM7.1254,419387,2852142196
2614918FINDING_2016-01-01_to_2016-03-31_10/20/20 3:33 PM10/20/20 11:08 PM7.5855,499420,87021-161
2714920FINDING_2016-04-01_to_2016-06-30_10/21/20 8:32 AM10/21/20 8:20 PM11.839,441465,40043-224
2814925FINDING_2016-07-01_to_2016-09-30_10/27/20 10:05 AM11/4/20 1:13 PM195.132,712506,47295280,838365
2914927FINDING_2016-10-01_to_2016-10-31_11/4/20 1:09 PM11/5/20 10:40 AM21.527,397159,1564-133
3014930FINDING_2016-11-01_to_2016-11-15_11/6/20 3:53 PM11/6/20 4:59 PM1.182,54090,794--37
3114932FINDING_2016-11-16_to_2016-12-31_11/10/20 9:23 AM11/10/20 11:28 AM2.0874,605155,42721-119
3214935FINDING_2017-01-01_to_2017-03-31_11/13/20 3:03 PM11/13/20 8:16 PM5.2290,374471,45018467,0950
3314937FINDING_2017-04-01_to_2017-06-30_11/16/20 9:45 AM11/16/20 7:12 PM9.4549,248465,3921611,400138
3414939FINDING_2017-07-01_to_2017-09-30_11/17/20 11:32 AM11/18/20 12:03 AM12.544,231552,89284-169
3514941FINDING_2017-10-01_to_2017-12-31_11/18/20 8:27 AM11/18/20 10:07 AM1.6737,91963,199151-0
3614943FINDING_2018-01-01_to_2018-03-31_11/18/20 10:11 AM11/18/20 9:49 PM11.6341,538483,227371146
3714945FINDING_2018-04-01_to_2018-06-30_11/19/20 8:07 AM11/19/20 9:00 PM12.8839,956514,76230-220
3814948FINDING_2011-01-03_to_2011-01-03_3467111/24/20 2:44 PM11/24/20 2:45 PM0.0272012-120
3914950FINDING_2018-07-01_to_2018-09-30_11/25/20 10:36 AM11/25/20 8:39 PM10.0557,288575,749192251,290279
4014952FINDING_2018-10-01_to_2018-12-31_11/30/20 7:24 AM11/30/20 3:08 PM7.7357,998448,521153128
4114954FINDING_2019-01-01_to_2019-03-31_12/2/20 9:51 AM12/3/20 3:51 AM1825,485458,7218410,0160
4214959FINDING_2019-07-01_to_2019-09-30_12/18/20 1:48 PM12/23/20 7:23 PM125.584,344545,506608-171
4314963FINDING_2019-10-01_to_2019-12-31_12/28/20 8:03 AM12/28/20 12:50 PM4.7887,799419,973801-82
4414965FINDING_2020-01-01_to_2020-02-05_12/28/20 1:15 PM12/28/20 1:55 PM0.6781,18554,123162-0
Master
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F44Expression="$F2 >=PERCENTILE($F$2:$F$44,0.9)"textNO
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,746
You only have cells F2:F44 selected. You need to change that to A2:J44 and use the same formula.
 
Solution

paydog23

New Member
Joined
Jul 12, 2017
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,166
Messages
5,623,119
Members
415,956
Latest member
Footballtend

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top