Fantasy Football Geek Stuff

[Jeramie]

OK...I am a FFball geek. I would like to automate our random draft pick process and not have to draw pieces of paper ("ping pong balls") out of a Crown Royal bag anymore to determine our draft order. I saw a previous post about a weighted random draft order...but my Excel skills are limited and I will not be able to make the other solution that I found work for my league.

Essentially, we have 12 teams and 14 rounds in the draft. Each team has 1 pick in each round. The even numbered round picks are just the opposite of the odd numbered round picks (ie. serpentine draft - the team with the last pick in the first round automatically gets the first pick in the second round).

The first round is weighted based on last year's finishing position. 1st through 6th finishers get 1 ping pong ball...7th place gets 2, 8th place gets 3, 9th place gets 4, 10th place gets 5, 11th place gets 6th, 12th place gets 7. The 3rd, 5th, 7th, 9th, 11th, and 13th rounds are completely random...no weighting.

Would it be possible to have a macro in excel generate "random" draft picks based on the info above and autopopulate the draft order cells with the appropriate owner names. I understand the code for this would be lengthy and I can't do it. Would anybody be willing to put something like this together for me for a reasonable fee? Please e-mail me if you are interested and let me know what would make it worth your time. Any help is greatly appreciated.

 

[Oaktree]

You don't need a macro.

Try this:

(Repeat columns F:H for rounds 3-14)

Book1
	A	B	C	D	E	F	G	H
1	Name	Weight	Randround1	Round1Choice	Round2Choice	RandRound3	Round3Choice	Round4Choice
2	Player1	1	0.086804987	12	1	0.220902879	7	6
3	Player2	1	0.723107007	6	7	0.6984971	2	11
4	Player3	1	0.84524155	5	8	0.151349297	9	4
5	Player4	1	0.328589821	9	4	0.612773093	3	10
6	Player5	1	0.291959373	10	3	0.255872325	6	7
7	Player6	1	0.544332517	7	6	0.133426177	10	3
8	Player7	2	0.201949784	11	2	0.589808301	4	9
9	Player8	3	2.65722896	2	11	0.718651662	1	12
10	Player9	4	0.372801026	8	5	0.089298799	12	1
11	Player10	5	2.655234144	3	10	0.214834461	8	5
12	Player11	6	3.733392024	1	12	0.100570335	11	2
13	Player12	7	1.062863791	4	9	0.409226105	5	8
Sheet1

 

[Jeramie]

Oaktree...that looks easy enough...however, could you give me a brief description of how/why it works...specifically the random weighted function in column C. It looks like you are multiplying the number of "ping pong" balls by the random number generated by Excel. I just can't figure if that equates to the odds generated by the "ping pong" set up...ie the 12th finisher has a 7 out of 33 chance of getting that first pick.

I'm no math genious....please help me understand

 

[Seti]

To simplify the even rounds, just use 13-(odd round choice) to get the serpentine effect.

 

[Oaktree]

To simplify the even rounds, just use 13-(odd round choice) to get the serpentine effect.

Wasn't sure if there would always be 12 players though...

 

[Seti]

Good Point.

 

[Oaktree]

One way to explain it is that a random % of 7 is, on average, 7x as high as a random % of 1. So, if I multiply player 12's random number by 7 and player 1's random number by 1, player 12 will, on average, be 6x more likely to pick before player 1 (or the equivalent of having 7 ping pong balls to his 1).

This all assumes, of course, that the randomness in your selection is the same as the randomness Excel uses. I didn't incorporate any "home court advantage" whereby you could give your enemies heavier ping pong balls that get buried at the bottom of the bag

 

[Jeramie]

OK...I buy that!

Thanks for your help!

 

[Jeramie]

Can you now tell me how I can get the random results to populate a draft order table? In this example, Hank has the #1 pick in the first round and I want his name to go to cell C2. I am thinking that I would use some lookup function in C2...but I have not been able to figure out how to make that work...I haven't used the lookup functions before. Any ideas?

Draft Order.xls
	AA	AB	AC	AD	AE
1	Owner	2004Finish	#ofPingPongballs	RandRound1	Rank
2	Jeramie	1	1	0.175860797	11
3	Don	2	1	0.340212265	9
4	Lara	3	1	0.435980283	8
5	Tom	4	1	0.973688702	5
6	Lee	5	1	0.808073645	6
7	Jeff	6	1	0.297318454	10
8	Andy	7	2	0.605182859	7
9	Ty	8	3	2.885267021	2
10	Dan	9	4	2.455191922	3
11	Kristina	10	5	1.606467994	4
12	Hank	11	6	3.814560016	1
13	Josh	12	7	0.072191874	12
Sheet1

 

[Oaktree]

The basic formula is:
=index(range_of_names, match(value,ranked_values,0))

Where value is one of the the ascending numbers (i.e. draft order).

As an example,

Book1
	A	B	C	D	E	F	G	H
1	Owner	2004Finish	#ofPingPongballs	RandRound1	Rank			Round1
2	Jeramie	1	1	0.878077	7		1	Josh
3	Don	2	1	0.807737	9		2	Hank
4	Lara	3	1	0.000572	12		3	Kristina
5	Tom	4	1	0.828383	8		4	Ty
6	Lee	5	1	0.757938	10		5	Dan
7	Jeff	6	1	0.916302	6		6	Jeff
8	Andy	7	2	0.426767	11		7	Jeramie
9	Ty	8	3	2.551928	4		8	Tom
10	Dan	9	4	1.495061	5		9	Don
11	Kristina	10	5	4.797163	3		10	Lee
12	Hank	11	6	4.79752	2		11	Andy
13	Josh	12	7	6.666409	1		12	Lara
Sheet1