JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,525
- Office Version
- 365
- Platform
- Windows
I did a search for ranking on multiple columns. There were several solutions. This one seems like the best for 2 columns, Points & Assists, both high-to-low.
I then modified it for the case where the first column is high-to-low (Points) and the second is low-to-high (Fouls).
Is there a better way?
Next I want to generalize it so that the first column can also be low-to-high.
Ranking.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
2 | Points | Assists | Rank↓↓ | Fouls | Rank↓↑ | ||
3 | 12 | 2 | 1 | 4 | 3 | ||
4 | 12 | 1 | 2 | 2 | 1 | ||
5 | 12 | 1 | 2 | 3 | 2 | ||
6 | 10 | 5 | 4 | 5 | 5 | ||
7 | 10 | 4 | 5 | 3 | 4 | ||
8 | 10 | 3 | 6 | 5 | 5 | ||
9 | 8 | 10 | 7 | 0 | 7 | ||
10 | 8 | 7 | 8 | 0 | 7 | ||
11 | 8 | 7 | 8 | 2 | 9 | ||
Rank on 2 Columns |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3:D11 | D3 | =RANK.EQ([@Points],[Points]) + COUNTIFS([Points],[@Points],[Assists],">" & [@Assists]) |
F3:F11 | F3 | =RANK.EQ([@Points],[Points]) + COUNTIFS([Points],[@Points],[Fouls],"<" & [@Fouls]) |
I then modified it for the case where the first column is high-to-low (Points) and the second is low-to-high (Fouls).
Ranking.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
2 | Points | Assists | Rank↓↓ | Fouls | Rank↓↑ | ||
3 | 12 | 1 | 2 | 2 | 1 | ||
4 | 12 | 1 | 2 | 3 | 2 | ||
5 | 12 | 2 | 1 | 4 | 3 | ||
6 | 10 | 4 | 5 | 3 | 4 | ||
7 | 10 | 5 | 4 | 5 | 5 | ||
8 | 10 | 3 | 6 | 5 | 5 | ||
9 | 8 | 10 | 7 | 0 | 7 | ||
10 | 8 | 7 | 8 | 0 | 7 | ||
11 | 8 | 7 | 8 | 2 | 9 | ||
Rank on 2 Columns |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3:D11 | D3 | =RANK.EQ([@Points],[Points]) + COUNTIFS([Points],[@Points],[Assists],">" & [@Assists]) |
F3:F11 | F3 | =RANK.EQ([@Points],[Points]) + COUNTIFS([Points],[@Points],[Fouls],"<" & [@Fouls]) |
Is there a better way?
Next I want to generalize it so that the first column can also be low-to-high.