ADP Round, Pick #

bjoak · Post by **bjoak** » Sat Mar 01, 2008 7:46 am

The ADP's are inconveniently listed as whole numbers rather than round and pick numbers.

Here is how to convert them on excel:

For round #

1. Make a new column with the formula =E4/15+.5

2. Paste the results to the rest of the column.

3. Set column settings to 0 decimal places. (I recommend you use the list manager.)

For pick #

1. Make another new column with the formula =MOD(E4, 15)

2. Paste the results to the rest of the column.

3. Set column settings to 0 decimal places.

You can repeat the same process for the earliest and latest picks.

The only problem is that half of the 15th picks will show up as 0, i.e. Round 2, pick 15 will come up as 3, 0. If anyone knows how to fix that, post here.

RedRyder · Post by **RedRyder** » Sat Mar 01, 2008 7:57 am

Thanks for posting this info!

King of Queens · Post by **King of Queens** » Sat Mar 01, 2008 8:00 am

Originally posted by bjoak:

The only problem is that half of the 15th picks will show up as 0, i.e. Round 2, pick 15 will come up as 3, 0. If anyone knows how to fix that, post here. Have Desmond tell Faraday to set the device to 2.342 oscillating at 11 hertz. That should fix the problem.

Asumijet · Post by **Asumijet** » Sat Mar 01, 2008 8:30 am

Originally posted by King of Queens:

QUOTE]Have Desmond tell Faraday to set the device to 2.342 oscillating at 11 hertz. That should fix the problem. [/QB]

I have felt sideways ever since last Thursday. I hope I don't go drafting Pedro Guerrero on Monday.

Captain Crunch · Post by **Captain Crunch** » Sat Mar 01, 2008 10:20 am

This will get rid of the 0 problem:

For the round #, use =ROUNDUP(E4/15,0) instead of =E4/15+.5

For pick # use =IF(MOD(E4,15)=0,15,MOD(E4,15)) instead of =MOD(E4, 15)

Make sure you get the brackets correct.

bjoak · Post by **bjoak** » Sat Mar 01, 2008 10:37 am

Originally posted by CaptainCrunch:

This will get rid of the 0 problem:

For the round #, use =ROUNDUP(E4/15,0) instead of =E4/15+.5

For pick # use =IF(MOD(E4,15)=0,15,MOD(E4,15)) instead of =MOD(E4, 15)

Make sure you get the brackets correct. Nope, I still have exactly the same problem. Look down to 103 Jose Valverde.

Captain Crunch · Post by **Captain Crunch** » Sat Mar 01, 2008 10:42 am

It works ok for me. Try selecting the top round and pick cells that you have created (should be F4 and G4) and drag to fill.

Captain Crunch · Post by **Captain Crunch** » Sat Mar 01, 2008 10:52 am

I see the difference. Don't use the ADP column, as Valverde's average position is 105.18 which is greater than 105 (the last pick of round 7. But his actual average pick rank is 103, i.e. 6-13.

The easiest thing to do is to get rid of the bracket in the first column and use that data as the source (A4 instead of E4).

Captain Crunch · Post by **Captain Crunch** » Sat Mar 01, 2008 11:08 am

Hmmm, that breaks down near the bottom of the list because of the wide ranges. So that's no good either.

I think the best you can do is your original method with the modifications that I suggested ... and living with the odd player like Valverde that will be wrong.

Oh well, I tried. I'll give it some more thought tomorrow, but I have a draft in a couple of hours, so that'll be it for today.

JohnZ · Post by **JohnZ** » Sat Mar 01, 2008 11:10 am

Just do more mocks so it becomes like riding a bike.

bjoak · Post by **bjoak** » Sat Mar 01, 2008 11:55 am

Originally posted by CaptainCrunch:

Hmmm, that breaks down near the bottom of the list because of the wide ranges. So that's no good either.

I think the best you can do is your original method with the modifications that I suggested ... and living with the odd player like Valverde that will be wrong.

Oh well, I tried. I'll give it some more thought tomorrow, but I have a draft in a couple of hours, so that'll be it for today. You can round off the E column in a new column and then use those values with the equation you suggested. It's really not worth the effort for such a small problem.

King of Queens · Post by **King of Queens** » Sat Mar 01, 2008 12:00 pm

Originally posted by UFS:

Just do more mocks so it becomes like riding a bike. Have to say, it's becoming pretty easy for me to convert "209" to "14.14".

By March 15th, we'll all be experts in numbers -- but only up to 450, of course.

GOD Loves You · Post by **GOD Loves You** » Sat Mar 01, 2008 1:16 pm

Originally posted by King of Queens:

quote:Originally posted by UFS:

Just do more mocks so it becomes like riding a bike. Have to say, it's becoming pretty easy for me to convert "209" to "14.14".

By March 15th, we'll all be experts in numbers -- but only up to 450, of course. [/QUOTE]come join Asumijet, Childs, and myself over at poopytooth's "Big Game" and you will be an expert up to 625 players .

www.fantasybaseballbiggame.com

Heck, I wish we could get Tom and Greg to come join us too!

[ March 01, 2008, 07:17 PM: Message edited by: GOD Loves You ]

Captain Crunch · Post by **Captain Crunch** » Sun Mar 02, 2008 9:50 am

Well just in case anyone is desperate to make this work:

for the round # use: =ROUNDUP(E4/15,0)

and for the pick # use: =ROUNDUP(IF(MOD(E4,15)=0,15,MOD(E4,15)),0)

The last 56 players will say round 31. Somehow, their ADP has been calculated to be greater than 450. Not worth bothering to fix that one ... fersure.

[ March 02, 2008, 03:55 PM: Message edited by: CaptainCrunch ]

Navel Lint · Post by **Navel Lint** » Sun Mar 02, 2008 10:32 am

I'm not sure why, but ADP has been based off 455 instead of 450

Ratbeer · Post by **Ratbeer** » Sun Mar 02, 2008 10:34 am

I believe this is easier. Leaving the file just as it came through. I am using Columns I, J and K for "Round", "Pick" and "Round/Pick"

For Round

Type the "1" in cells I4:I18, (Short cut - highlight the cells I4:I18 type 1 and then hit CTRL+Enter, this will put the 1 in all 15 cells.)

In cell I19 FORMULA: =I4+1 copy this all the way down, just kill after round 30

For Pick

Fill in the 1-15 in cells J4:J18

In cell J19 FORMULA: =J4 copy this all the way down

For Round Pick

in cell K4 FORMULA: =I4&"."&J4 and copy down

Hope this helps.