Forum Discussion
Conditional Formatting - formula for adjacent cells
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.
Hi Golf,
Let me clarify - you'd like to have the largest in C2:C18 which don't meet first three largest in E2:E9 and first three largest in E11:E18 (other words some six numbers in above range)?
And may we consider C2:C18 as one range or it shall be join of C2:C9 and C11:C18?
Hello Sergei,
This is correct, the largest in C2:C18 that does not meet largest 3 in E2:E9 and also does not meet largest 3 in E11:E18, those six numbers should be excluded from the calculation.
We may also consider C2:C18 as one range as C10 is blank and should not affect the result.
