Team Position (1, 2, 3 etc) based on multiple criteria

[StaceyVECL]

Hi All,

I am running a basketball league and need to make the results and standings easier to see with minimal input.

I have all the results as they should be using the NBA format. However, I want the spreadsheet to tell me the position of the team automatically based on win %, conference wins and division wins. At the moment I am sorting the data and manually entering the position.

The result needs to show as 1, 2, 3, 4 etc, is there a way to do this?

Thanks, Stacey

 

[Joe4]

This question is very much data dependent. So I think we will need to see what your data looks like, how it is structured, and what you want your expected results to look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[StaceyVECL]

Thanks, Joe. The add-in isn't working. What I have so far is the below.

I am manually entering the "standing" but I need a formula to return the standing based on win%. If the win % is the same (see 5 + 6 below) then it uses conf. wins, if conf. wins is the same (see 5 + 6 below) it then uses div. wins to determine the higher position.

Standing	Team	Division	Conference	Total Wins	Total Loses	Win%	Conf. Wins	Conf. Loses	Div. Wins	Div. Loses
1 East	Boston Celtics	Atlantic	Eastern	62	17	0.785​	39	10	15	1
2 East	Milwaukee Bucks	Central	Eastern	49	31	0.613​	34	17	10	7
3 East	New York Knicks	Atlantic	Eastern	47	32	0.595​	32	17	10	5
4 East	Cleveland Cavaliers	Central	Eastern	47	33	0.588​	30	20	10	5
5 East	Orlando Magic	Southeast	Eastern	46	34	0.575​	31	19	9	7
6 East	Indiana Pacers	Central	Eastern	46	34	0.575​	31	19	11	5

 

[Joe4]

Based on your description, I think you have the last two record reversed, as they both have the same Win% and Conf. Wins, but the last records has more Div. Wins, so should be ranked higher.

So here is what I came up with:

Here is the formula I put in cell A2 and copied down to A7:

=RANK.EQ(G2,G$2:G$7)+MAX(COUNTIFS(G$2:G$7,G2,H$2:H$7,">" & H2),COUNTIFS(G$2:G$7,G2,J$2:J$7,">" & J2))

I think it should work for most tie-breaking situations, but am not sure if there are some that it might not work for.

And then I applied a Custom Format on cells A2:A7 to get the word "East" in there:

Note: The following article helped with the tie-breaker part of the RANK function: Rank Data In Microsoft Excel With Tie Breaking • Black Atlas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Standing	Team	Division	Conference	Total Wins	Total Loses	Win%	Conf. Wins	Conf. Loses	Div. Wins	Div. Loses
2	1	Boston Celtics	Atlantic	Eastern	62	17	0.785	39	10	15	1
3	2	Milwaukee Bucks	Central	Eastern	49	31	0.613	34	17	10	7
4	3	New York Knicks	Atlantic	Eastern	47	32	0.595	32	17	10	5
5	4	Cleveland Cavaliers	Central	Eastern	47	33	0.588	30	20	10	5
6	6	Orlando Magic	Southeast	Eastern	46	34	0.575	31	19	9	7
7	5	Indiana Pacers	Central	Eastern	46	34	0.575	31	19	11	5
Data

Cell Formulas
Range	Formula
A2:A7	A2	=XMATCH(B2:B7,TAKE(SORT(B2:J7,{6,7,9},-1),,1))
Dynamic array formulas.

 

[StaceyVECL]

Based on your description, I think you have the last two record reversed, as they both have the same Win% and Conf. Wins, but the last records has more Div. Wins, so should be ranked higher.

So here is what I came up with:
View attachment 109809

Here is the formula I put in cell A2 and copied down to A7:

=RANK.EQ(G2,G$2:G$7)+MAX(COUNTIFS(G$2:G$7,G2,H$2:H$7,">" & H2),COUNTIFS(G$2:G$7,G2,J$2:J$7,">" & J2))

I think it should work for most tie-breaking situations, but am not sure if there are some that it might not work for.

And then I applied a Custom Format on cells A2:A7 to get the word "East" in there:
View attachment 109810

Note: The following article helped with the tie-breaker part of the RANK function: Rank Data In Microsoft Excel With Tie Breaking • Black Atlas.

Thank you so much, this works perfectly.

 

[Joe4]

You are welcome!
Glad we were able to help!

Note that I think Fluff's formula will work a little better. There may be some weird tie-breaker scenarios that my formula could trip on (i.e. if two teams have the same in percentage, but one team has more conference wins, but the other team has more divisional wins).
So, I would probably go with his solution over mine.

 

[StaceyVECL]

You are welcome!
Glad we were able to help!

Note that I think Fluff's formula will work a little better. There may be some weird tie-breaker scenarios that my formula could trip on (i.e. if two teams have the same in percentage, but one team has more conference wins, but the other team has more divisional wins).
So, I would probably go with his solution over mine.

It works for now but will keep an eye on it

 

[Joe4]

OK. Here is an example where mine doesn't quite work and his does:

 

[StaceyVECL]

OK. Here is an example where mine doesn't quite work and his does:
View attachment 109813

Thank you. I have factored in some other things and made necessary adjustments but using the NBA information, I'm struggling to understand how they work it out too as currently they show as Orlando Magic as 5 and Indiana Pacers as 6. Maybe their spreadsheet is wrong .

I tried to use Fluff's formula but gives me a spill error.

After some online research, the standings go by Win%( G), Games Behind (H), Div. Wins (K), Conf. Wins (I). Which all works at present except for the aforementioned 5 and 6 spots.

The new formula is this =RANK.EQ(G4,G$4:G$18)+MAX(COUNTIFS(G$4:G$18,G4,H$4:H$18,">"&H4),COUNTIFS($G$4:$G$18,G4,$K$4:$K$18,">"&K4),COUNTIFS(G$4:G$18,G4,I$4:I$18,">"&I4))

A	B	C	D	E	F	G	H	I	J	K
Standing	Team	Division	Conference	Total Wins	Total Loses	Win%	GB	Conf. Wins	Conf. Loses	Div. Wins
1	Boston Celtics	Atlantic	Eastern	62	17	0.785	0.0	39	10	15
2	Milwaukee Bucks	Central	Eastern	49	31	0.613	13.5	34	17	10
3	New York Knicks	Atlantic	Eastern	47	32	0.595	15.0	32	17	10
4	Cleveland Cavaliers	Central	Eastern	47	33	0.588	15.5	30	20	10
6	Orlando Magic	Southeast	Eastern	46	34	0.575	16.5	31	19	9
5	Indiana Pacers	Central	Eastern	46	34	0.575	16.5	31	19	11
7	Philadelphia 76ers	Atlantic	Eastern	45	35	0.563	17.5	29	21	7
8	Miami Heat	Southeast	Eastern	44	36	0.550	18.5	30	20	13
9	Chicago Bulls	Central	Eastern	37	42	0.468	25.0	20	28	6
10	Atlanta Hawks	Southeast	Eastern	36	44	0.450	26.5	22	29	8
11	Brooklyn Nets	Atlantic	Eastern	32	48	0.400	30.5	24	26	5
12	Toronto Raptors	Atlantic	Eastern	25	55	0.313	37.5	18	32	1
13	Charlotte Hornets	Southeast	Eastern	20	60	0.250	42.5	13	37	6
14	Washington Wizards	Southeast	Eastern	15	65	0.188	47.5	11	39	4
15	Detroit Pistons	Central	Eastern	13	66	0.165	49.0	10	40	2