Forum Discussion
Beginner help on a ranking formula
Hello,
First time posting here so hoping I'm doing this correctly. I'm trying to rank the below table using this kind of formula (it's for fantasy football). The top row marks the actual cell in excel, meaning 'Ryan' starts in M7.
The Top 6 names advance. Top 4 are determined solely by number of W. Any ties then go to Points to decide who is higher. The Top 5-6 will be those remaining who have the highest points in ranking order.
For this example, I would expect an output of (Ryan-1, Dan-2, Bennett-3, Matt-4, Andrew-5, Bryan-6).
This is the formula I have now but it's definitely incomplete. I got to it using a lot of copying from other websites.
=ArrayFormula(INDEX($M$7:$M$18, MATCH(1, ($AF$7:$AF$18=SMALL($AF$7:$AF$18, AF7)) * (COUNTIF(AH$7:AH7, $M$7:$M$18)=0), 0)))
| [M5] | [Q5] | [S5] | [T5] | [Y5] | [AF7] | [AG7] |
| Team | W | L | T | Points | W rank | Top 7 |
| Ryan | 3 | 0 | 0 | 209.10 | 1 | 1 |
| Dan | 2 | 1 | 0 | 385.56 | 2 | 2 |
| Bennett | 2 | 1 | 0 | 386.26 | 2 | 2 |
| Matt | 2 | 1 | 0 | 344.58 | 2 | 2 |
| Bryan | 2 | 1 | 0 | 335.04 | 2 | 2 |
| Liam | 2 | 1 | 0 | 324.94 | 2 | 2 |
| Andrew | 1 | 2 | 0 | 391.42 | 7 | 7 |
| Shane | 1 | 2 | 0 | 387.26 | 7 | |
| Charley | 1 | 2 | 0 | 306.18 | 7 | |
| Owen | 1 | 2 | 0 | 348.22 | 7 | |
| Zac | 1 | 2 | 0 | 318.28 | 7 | |
| Chris | 0 | 3 | 0 | 367.66 | 12 |
2 Replies
=ArrayFormula(INDEX($M$7:$M$18, MATCH(1, ($AF$7:$AF$18=SMALL($AF$7:$AF$18, AF7)) * (COUNTIF(AH$7:AH7, $M$7:$M$18)=0), 0)))
What is in AH7?
I'm not sure if I understood the question correctly, but wouldn't it be possible to create this as a table and filter to get the results you want?
Attached is a table with your formula.
Thank you for your understanding and patience
I know I don't know anything (Socrates)
Hi NikolinoDE,
AH7 was a typo, that should be AF7. I believe my formula is incorrect too since it isn't outputting what I would expect the results to be
