Fantasy Football Draft Day Player Rankings Formula

Hey All,

I am trying to create a fantasy football draft day guide. I have copy and pasted off of the internet of all the players complete with hyperlinks. I have also gotten a hold of the average draft position of each player from mock drafts completed around the country. What I want to do is take that number and convert it to which corresponding round it equates to in a 12-team league. For instance, say a certain players average draft position (ADP) 13, i want to create a formula that would indicate convert the ADP to something like "Early 2nd Round", or perhaps "2.1" (meaning the 1st pick in the second round). Or say a certain players ADP is 26, an outcome of "Early 3rd round", or perhaps 3.2 (2nd pick in the 3rd round).

Seeing as though my list has over +250 players, it will be WAY to tedious to do it manually for each player, A nice click and drag formula would be awesome. I fiddled around with the If/Then function, but it is more than 7 outcomes, so it won't work. I also tried the LOOKUP function, but that doesn't seem to work so well as the Lookup_vector always rounds down to the closest Lookup_value and produces an incorrect result.

Perhaps a simple math formula, but I can't get my head wrapped around the arithmetic.

Any thoughts?

I know nothing of fantasy football, so help me out here.

1) How many drafts per round?
2) What column on your sheet lists "draft position"? I'm assuming that's a number from 1-250, right?

Any possibility you can provide a sheet to work from that lays out your collected data and then demonstrates manually the results you're after, at least the first few in the list?

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

Jbeaucaire,

Thanks for the response. I've attached the spreadsheet to give you a better idea of what I am trying to accomplish. Column A is the overall rank but is not important in what I am trying to accomplish. Column B is the player, and Column C is the ADP.

The ADP is just an overall number, and I want to know which round this ADP would correlate to in a 12 man league. The ADP doesn't take into account how many members are in a particular league, so I want to convert the data to how it would apply in my league. This data will help me forecast when I need to pick whom and where to target a certain player.

This would mean that each round is 12 picks. So... since the ADP come with decimals, pick 14.79 would equate to between the 2nd and 3rd pick in round two (round 1 = 1-12; round 2 = 13-24 and so on and so on). That's why I was thinking that the output could be "Rd. 1 - 1st 1/2" or "Rd. 2 - 2nd 1/2", or even X.X, (first x being the round, second x being the particular pick).

It's not that hard to do in your head at 0-24, but when you get up to 147.65.... well... the math gets a little confusing (especially considering I will be drinking

Thanks!

My idea is to just use:

1.1
1.2
.
.
1.11
1.12
2.1
2.2

etc...

In D1 enter the number of draft picks per round, 12 in your case.
In D2 enter this formula and copy downward:

=CEILING(A2/$D$1,1)&"."&IF(MOD(ROW()-1,$D$1)=0,$D$1, MOD(ROW()-1,$D$1))

Changing the value in D1 will instantly reassess the list.

THanks. It works, but unfortunately the formula is based off the "rank" in column "a" when the "adp" in column "c" is what is I an interested in.

I tried changing that aspect of your formula and the result doesn't work by changing A2 to C2. For instance, the player with an adp of 14.19 comes back as "2.12", when it should be "2.2".

It's the decimal point in column D that is causing the problem. Any work around?

Any thoughts?

Thanks for the help

Interestingly, I thought of using column C, but from what I can see column C is exactly the same order sequentially as column A. That's why I didn't use it. Since column C is already sorted from lowest to highest, I have to encourage you to keep the formula as given.

NOTE: I did make a tiny correction in the formula above.

Heh, starting to feel dense here. I just put a rank formula on your column C.

=RANK(C2,$C$2:$C$237,1)

...copied down. Even at around 130 the numbers stay sequential. I don't see any numbers that are "out of order". I fear I'm missing some thing here.

OK, I'm interested in that number now, too. So, how does the knowledge about what those numbers actually mean help change the list you wanted?

You asked for a numeric sequence 1.1-1.12 for the first 12 picks in that list, then 2.1-2.12 for the next 12. The formula I provided does that. It works the same whether you use column A or column C.

Is there something substantive that will change in sequence of numbers?