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

 

[Fluff]

How about

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

Cell Formulas
Range	Formula
A2:A16	A2	=XMATCH(B2:B16,TAKE(SORT(B2:K16,{6,7,10,8},-1),,1))
Dynamic array formulas.

Clear all cells in col A from A2 down & put the formula in A2 only.
This still does still show Orlando in 6th

 

[StaceyVECL]

How about

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

Cell Formulas
Range	Formula
A2:A16	A2	=XMATCH(B2:B16,TAKE(SORT(B2:K16,{6,7,10,8},-1),,1))
Dynamic array formulas.

Clear all cells in col A from A2 down & put the formula in A2 only.
This still does still show Orlando in 6th

This still gives me a SPILL error?

 

[Fluff]

Did you clear all cells from A2 down before putting in the formula?

 

[StaceyVECL]

Did you clear all cells from A2 down before putting in the formula?

Ah, I didn't but have now.

Thank you.

 

[Fluff]

You're welcome.