CODEWITHSUNDEEP
excelexcel-formulaworksheet-functionconditional-formattingcountif
cleantone
cleantone

Reputation: 83

Top n conditional formatting - with a tie breaker

For each row in an Excel sheet I need to highlight the top six values. I tried using conditional formatting for Top 6 but I faced the following issue, for a row such as:

189 0 500 900 0 210 0 500 0 500 0 0 1060 883 883

the top six values are 1060, 900, 883, 883, 500 and 500. As you can see, there are two 500s in my top six but three 500s in the entire row. Conditional Formatting selects all values that are equal to values in Top 6, so it will highlight seven cells.

Is there a way to work around this, without adding any technical cells?

Upvotes: 1

Views: 908

Answers (1)

pnuts
pnuts

Reputation: 59475

The ties may be broken with a COUNTIF adjustment. For example a CF rule formula of the kind: 

=RANK(A1,$A$1:$O$1,0)+COUNTIF($A$1:A$1,A1)-1<7

<7 for the six you specified, though it depends upon when ranking ascending or descending. Ties are broken "in order" - the first found is accepted (count of 1 less 1 means no adjustment), others of the same value are down/up graded accordingly.

To apply this Conditional Formatting rule formula:

Select the range to which it is to apply (in this case taken as A1:O1).
Click on down arrow in HOME > Styles - Conditional Formatting.
Click on New Rule...
Click on Use a formula to determine which cells to format.
Under Format values where this formula is true: enter: 

 =RANK(A1,$A$1:$O$1,0)+COUNTIF($A$1:A$1,A1)-1<7

Click on Format....
Select choice of formatting.
Click OK.
Click OK.

Upvotes: 2

Related Questions