Weighted Football Draft Order

[Boogamil]

I'm hoping that someone can help me with this minor issue. We have a football draft every year and the first year we generated the draft order through a random number formula.

In year two we want to use a weighted random number based on the place that you finished the previous year. If we were to do this through ping pong balls in a box, the tenth place team would have 10 balls, ninth would have 9 balls and so on. Therefore the first place team could still get the first pick in the draft, but they would only have a one in fifty-five chance.

Can this be done through a formula?

Any help would be greatly appreciated.

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I
1	Team	Place	Limit	Range			Drawing:	3
2	A	1	1	1
3	B	2	3	2-3
4	C	3	6	4-6
5	D	4	10	7-10
6
7
Sheet2

 

[Boogamil]

Mark W.

Thank you very much for responding to my post. I don't mean to appear thick, I may have no choice though. How does this arrive at a weighted draft order?

 

[ozNoz]

You know, I wondered that too, but what Mark has given indeed has all the information you need. In the example shown, the Drawing number in H1 indicates that Team B, who holds the range 2-3, has managed to score the first draft pick. If the Draw number had been 4, 5 or 6, the first pick would go to Team C. Recalculating (hit F9) will produce a new Draw number.
The only problem with this method is that repeated random generation is unlikely to produce the numbers 1-10 (the number of draft picks available in this case) without repetition.
It will still work, if you just ignore any draw number that has already been used, but let us know if you need more help or a more refined solution.

 

[yoyoPHIL]

Just horning in on Mark's unique reply. If you break down the range into two columns

D2 =IF(B2=1,1,C2-B2+1) copied down

E2 =IF(B2=1,1,C2-B2+1&"-"&C2) copied down

F2 =IF(AND($H$1>=D2,$H$1<=E2),A2,"") copied down

The formula in column F will look at the random number generated in H1 and highlight the team that the number represents.

yoyoPHIL

 

[Mark W.]

I was just shifting your paradigm a bit. Here's the entire "ping pong ball drawing" model employing sampling without replacement. Just sort the yellow highlighted cell range using the random values as the sort key.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Team	Place	Lower	Upper	Range		Draw		Random	Ball	Team
2	A	1	1	1	1		1		0.761047	10	D
3	B	2	2	3	2-3		2		0.087132	6	C
4	C	3	4	6	4-6		3		0.287331	5	C
5	D	4	7	10	7-10		4		0.921706	4	C
6							5		0.894756	2	B
7							6		0.935232	7	D
8							7		0.127029	8	D
9							8		0.760467	9	D
10							9		0.211148	1	A
11							10		0.606019	3	B
12
Sheet1

 

[Boogamil]

This works well, but how do you remove repetition of a team, as they can only draft once per round?

 

[Mark W.]

Okay, the pick order is highlighted in pink. Resort the yellow highlighted range for each round of the draft...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Team	Place	Lower	Upper	Range	Pick		Random	Ball	Team	Row
2	A	1	1	1	1	4		0.777959256	8	D	2
3	B	2	2	3	2-3	2		0.549178748	9	D
4	C	3	4	6	4-6	3		0.375564971	2	B	4
5	D	4	7	10	7-10	1		0.593072664	7	D
6								0.634906066	3	B
7								0.623803718	4	C	7
8								0.874367604	1	A	8
9								0.587644785	5	C
10								0.470764866	6	C
11								0.695807238	10	D
12
Sheet1

 

[Boogamil]

Thank you for your time, it works great.

 

[Ellionious]

I was trying to use this but the formulas no longer show. Could someone help?