I'm trying to conditionally format a group of cells where you take the top n from each region per period, ex. Top 5 from North, Top 3 from East, etc. The formula I used is =OR(AND($A2="North",B2>=LARGE(B$2:B$66,5)),AND($A2="South",B2>=LARGE(B$2:B$66,3)),AND($A2="East",B2>=LARGE(B$2:B$66,4)),AND($A2="West",B2>=LARGE(B$2:B$66,4))) but it seems to only pick up the top n of each period regardless of region. What am I doing wrong?
Conditional formatting with multiple criteria
- Thread starter reggie n.
- Start date
NARAYANK991
Excel Ninja
Hi Reggie ,
Try this :
=B2>=LARGE(IF($A$2:$A$66=$A2,B$2:B$66),INDEX($J$2:$J$5,MATCH($A2,$I$2:$I$5,0)))
This is to be applied only to column B ; if you want it to be applied to all 4 columns , check if the references are correctly specified.
Narayan
Try this :
=B2>=LARGE(IF($A$2:$A$66=$A2,B$2:B$66),INDEX($J$2:$J$5,MATCH($A2,$I$2:$I$5,0)))
This is to be applied only to column B ; if you want it to be applied to all 4 columns , check if the references are correctly specified.
Narayan
Thanks Narayan! If I wanted to separate the formatting, say one color for each region, would I still need the index match formula?
NARAYANK991
Excel Ninja
Hi ,
The INDEX / MATCH is needed because you need a different number of results for each region ; suppose you wanted the TOP 3 or TOP 5 for every region , then the table and the INDEX / MATCH are not required.
If you need formatting of each region toppers in a different colour , you will need 4 different rules , one for each colour.
Narayan
The INDEX / MATCH is needed because you need a different number of results for each region ; suppose you wanted the TOP 3 or TOP 5 for every region , then the table and the INDEX / MATCH are not required.
If you need formatting of each region toppers in a different colour , you will need 4 different rules , one for each colour.
Narayan
NARAYANK991
Excel Ninja
Hi ,
See this file.
I have introduced two rules ; one for colouring the top 5 in the North region green , and the other for colouring the top 5 in the South region brown.
If you need to colour the top 5 in the North , but only the top 3 in the South , then you need the INDEX / MATCH.
Since you need to colour each region a different colour , you need a separate rule for each colour , since only one format colour can be associated with one formula rule.
Narayan
See this file.
I have introduced two rules ; one for colouring the top 5 in the North region green , and the other for colouring the top 5 in the South region brown.
If you need to colour the top 5 in the North , but only the top 3 in the South , then you need the INDEX / MATCH.
Since you need to colour each region a different colour , you need a separate rule for each colour , since only one format colour can be associated with one formula rule.
Narayan
Thanks Naryan, that's what I needed!