Beginner help on a ranking formula

[mvbuccheri]

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. I'm struggling to build the formula itself with my lack of knowledge.

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 in AH7 of (Ryan-1, Dan-2, Bennett-3, Matt-4, Andrew-5, Bryan-6). Ryan through Matt have best records with points tiebreaker. Andrew/Bryan have the most points out of the rest.

This is the formula I have now but it's definitely wrong, and it's missing the point tiebreaker. I got to it using a lot of copying from other websites. I simply don't know how to create it.

=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]	[AH7]
Team	W	L	T	Points	W rank	Top 7	Rank
Ryan	3	0	0	209.10	1	1	#N/A
Dan	2	1	0	385.56	2	2	Dan
Bennett	2	1	0	386.26	2	2	Bennett
Matt	2	1	0	344.58	2	2	Bennett
Bryan	2	1	0	335.04	2	2	Bennett
Liam	2	1	0	324.94	2	2	Bennett
Andrew	1	2	0	391.42	7	7	Liam
Shane	1	2	0	387.26	7		Liam
Charley	1	2	0	306.18	7		Dan
Owen	1	2	0	348.22	7		Liam
Zac	1	2	0	318.28	7		Liam
Chris	0	3	0	367.66	12		Chris

 

[DRSteele]

Welcome to the Forum.

Ranking with ties is what you want. Here is an I article I wrote on the topic. Let us know how it goes for you.

 

[Fluff]

Hi & welcome to MrExcel.

Is this for Excel or for Google Sheets?

 

[mvbuccheri]

Hi & welcome to MrExcel.

Is this for Excel or for Google Sheets?

Hello, I've been using both to try formulating this

 

[Fluff]

But does it need to work it Sheets?

 

[mvbuccheri]

But does it need to work it Sheets?

Nope, either is fine

 

[Fluff]

Ok

 

[mvbuccheri]

Welcome to the Forum.

Ranking with ties is what you want. Here is an I article I wrote on the topic. Let us know how it goes for you.

Hi @DRSteele, your article was super helpful thank you. What would the approach be to adding a second rank into the remaining population? Use this to find the Top 4, and then create a second table for only 5-12 and then rerank?