Forum Discussion
Conditional Formatting - formula for adjacent cells
Hello Community,
I am wanting to assign Conditional Formatting to a column adjacent to another column that already has conditional formatting assigned.
The 2 columns are divided into 2 Groups (Group 1 and Group 2).
To highlight the first, second and third highest values for Group 1 with different background colors and likewise for Group 2, I have assigned 3 separate conditional formatting rules to Group 1 of Column 2 and another 3 separate conditional formatting rules to Group 2 of Column 2.
In each rule I have used the LARGE function i.e. =LARGE($E$2:$E$9,1)
The Conditional Formatting that I am now struggling with is to highlight the highest value in all of Column 1 but is not adjacent to the first, second or third highest values in either Group 1 or Group 2.
I only want to highlight the highest cell and not the entire row.
In the attached I have manually formatted the value 21 of cell C6 in green but it is what I am trying to achieve with Conditional formatting.
Although the values 23 in cell C14 and 22 in cell C15 are greater than the value in C6, they should be excluded because they are adjacent to cells in Column 2 that are already assigned first, second or third.
Thank you in advance for any assistance.
Hi Golf,
The rule for the Column1 could be
=AGGREGATE(14,6,($C$11:$C$18)* ($C$11:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,1),$E$11:$E$18,0)))* ($C$11:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,2),$E$11:$E$18,0)))* ($C$11:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,3),$E$11:$E$18,0))),1)
for the green color, and similar for others.
If i understood your correctly.
Please see attached.
Thank you Sergei for your suggested solution.
The rule works well for retrieving the largest in range $C$2:$C$9 and also the largest in range $C$11:$C$18.
I am instead wanting to retrieve just the one largest from the entire range $C$2:$C$18.
I unsuccessfully attempted to modifying your rule with the following:
=AGGREGATE(14,6,($C$2:$C$18)*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$2:$E$9,1),$E$2:$E$9,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$11:$E$18,1),$E$11:$E$18,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$2:$E$9,2),$E$2:$E$9,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$11:$E$18,2),$E$11:$E$18,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$2:$E$9,3),$E$2:$E$9,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$11:$E$18,3),$E$11:$E$18,0))),1)Hi Golf,
If as I assumed when you shall to check each range separately, formula will be
=AGGREGATE(14,6,($C$2:$C$18)* ($C$2:$C$18<>INDEX($C$2:$C$9,MATCH(LARGE($E$2:$E$9,1),$E$2:$E$9,0)))* ($C$2:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,1),$E$11:$E$18,0)))* ($C$2:$C$18<>INDEX($C$2:$C$9,MATCH(LARGE($E$2:$E$9,2),$E$2:$E$9,0)))* ($C$2:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,2),$E$11:$E$18,0)))* ($C$2:$C$18<>INDEX($C$2:$C$9,MATCH(LARGE($E$2:$E$9,3),$E$2:$E$9,0)))* ($C$2:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,3),$E$11:$E$18,0))),1)
Alternatively you may compare not the numbers but exclude row there they are, when
=AGGREGATE(14,6,($C$2:$C$18)* ((ROW($C$2:$C$18)-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,1),$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,1),$E$11:$E$18,0))* ((ROW($C$2:$C$18)-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,2),$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,2),$E$11:$E$18,0))* ((ROW($C$2:$C$18)-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,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,3),$E$11:$E$18,0)),1)
Both formulas are in attached file, first one is applied to conditional formatting.
