Conditional Formatting - formula for adjacent cells

Hi "Golf",

Here is another attempt to define the names for the calculations. Main reason that allows us to avoid array formulas which don't work with conditional formatting (more exactly significantly simplify the formulas).

All names you may find and edit if necessary in Formulas->Name Manager

First define cells where our ranges starts and ranges itself

// Cell on the top of Sales section
OnSales
=Sheet1!$A$1 

// Cell on the top of Admin section
OnAdmin
=Sheet1!$A$10

// Ranges with scores by sections
SalesScore1
=Sheet1!$C$2:$C$9

//similar for SalesScore2, AdminScore1, AdminScore2
// and the entire score ranges
Score1
=Sheet1!$C$2:$C$18
Score2
=Sheet1!$E$2:$E$18

and name for the condition which rows to exclude

// With this name we define condition which rows to exclude finding
// largest Score 1

RowsToExclude
=IF(
(ROW(Score1)<>(MATCH(LARGE(SalesScore2,1),SalesScore2,0)+ROW(OnSales)))*
(ROW(Score1)<>(MATCH(LARGE(SalesScore2,2),SalesScore2,0)+ROW(OnSales)))*
(ROW(Score1)<>(MATCH(LARGE(SalesScore2,3),SalesScore2,0)+ROW(OnSales)))*
(ROW(Score1)<>(MATCH(LARGE(AdminScore2,1),AdminScore2,0)+ROW(OnAdmin)))*
(ROW(Score1)<>(MATCH(LARGE(AdminScore2,2),AdminScore2,0)+ROW(OnAdmin)))*
(ROW(Score1)<>(MATCH(LARGE(AdminScore2,3),AdminScore2,0)+ROW(OnAdmin)))
,Score1)

With above names we may find values for first three largest scores

Large 1:	23	=LARGE(RowsToExclude,1)
// To simplify a bit define one more name
CountLarge1
=SUMPRODUCT((--RowsToExclude>0)*(Score1=LARGE(RowsToExclude,1)))

// and with help of it find next largests
Large 2:	18	=LARGE(RowsToExclude,1+CountLarge1)
Large 3:	17	=LARGE(RowsToExclude,1+ CountLarge1+
               SUMPRODUCT((--RowsToExclude>0)*(Score1=LARGE(RowsToExclude,1+CountLarge1))))

With this formulas we define conditional formatting rules checking if cell value = LargeN and cell row is not match the row for one of largest in Score 2

// First large conditional formatting rule 
=($C2=LARGE(RowsToExclude,1))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,1),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,2),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,3),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,1),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,2),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,3),AdminScore2,0)+ROW(OnAdmin)))

// Second large conditional formatting rule 
=($C2=LARGE(RowsToExclude,1+CountLarge1))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,1),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,2),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,3),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,1),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,2),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,3),AdminScore2,0)+ROW(OnAdmin)))

// Third large conditional formatting rule 
=($C2=LARGE(RowsToExclude,1+ CountLarge1+
               SUMPRODUCT((--RowsToExclude>0)*(Score1=LARGE(RowsToExclude,1+CountLarge1)))))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,1),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,2),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,3),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,1),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,2),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,3),AdminScore2,0)+ROW(OnAdmin)))

And in attached file

Posted previous message and recognized what LARGE returns same value for second and first if they are equal, i.e 

=LARGE({23,23,22},2)

returns 23, not 22.

It looks like another correction is required for second and third largest, perhaps with COUNTIF. Some later...

Hi again,

With this I'd suggest to use in conditional formatting not Cell value = rule, but formula rule which highlights the cell if the formula returns TRUE.

The formula is

=((ROW()-ROW($C2)+1)=
   MATCH(
      AGGREGATE(14,6,($C$2:$C$18)*
         ((ROW($C$2:$C$18)-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,OR(1,2,3)),$E$2:$E$9,0))*
         ((ROW($C$2:$C$18)-ROW($C$2)+1)<>COUNT($C$2:$C$11)+MATCH(LARGE($E$11:$E$18,OR(1,2,3)),$E$11:$E$18,0)),1),
   $C2:$C$18,0)
)*
((ROW()-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,1),$E$2:$E$9,0))*
((ROW()-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,2),$E$2:$E$9,0))*
((ROW()-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,3),$E$2:$E$9,0))*
((ROW()-ROW($C$2)+1)<>COUNT($C$2:$C$11)+ MATCH(LARGE($E$11:$E$18,1),$E$11:$E$18,0))*
((ROW()-ROW($C$2)+1)<>COUNT($C$2:$C$11)+ MATCH(LARGE($E$11:$E$18,2),$E$11:$E$18,0))*
((ROW()-ROW($C$2)+1)<>COUNT($C$2:$C$11)+ MATCH(LARGE($E$11:$E$18,3),$E$11:$E$18,0))

In first part we compare the current row number within the table with if it is equal to the row number of the first large value (returned by AGGREGATE) from ones which are not equal to largest three in second column (MATCH within AGGREGATE). Here we use OR(1,2,3) to compare three largest together. OR since conditional formatting doesn't allow array constants within the formula. On the other hand built-in array calculations within AGGREGATE correctly checks OR.

Next by AND conditions we check if received row number is not the same as row numbers for largest three in second column. Perhaps that part could be simplified, not sure right now.

Hi Sergei,

I have added more information in the attached to hopefully refine the required result.