[stealofhome]

I run this report using the same file as the Free Agent report. I paste these formulas right next to those of the Free Agent columns, so starting at CZ1.

Nxt_Yr_age: =(697347-DF2)/365.25
Level: =VLOOKUP(C2,[Fixes.xlsx]DraftList!$E$2:$F$32,2,0)
Unsigned: =IF(CZ2>=18.82,IF(DA2="HS","Y",IF(CW2=19,IF(DA2="F A","Y","N"),"N")),"N")
HittingTalent: =IF(ISNA(VLOOKUP(A2,INDIRECT("players_value.csv!$A $2:$S$"&COUNT(players_value.csv!$A:$A)-1),7,0)),0,VLOOKUP(A2,INDIRECT("players_value.csv! $A$2:$S$"&COUNT(players_value.csv!$A:$A)-1),7,0))
PitchingTalent: =IF(ISNA(VLOOKUP(A2,INDIRECT("players_value.csv!$A $2:$S$"&COUNT(players_value.csv!$A:$A)-1),11,0)),0,VLOOKUP(A2,INDIRECT("players_value.csv !$A$2:$S$"&COUNT(players_value.csv!$A:$A)-1),11,0))
Max: =MAX(DC2:DD2)
DOB: =DATE(DG2,DH2,DI2)

The first thing is determine what age the player will be when the NCAA season begins the following year. Since Excel uses December 30, 1899 as day 0 of the universe, I have to do some calculations to figure out how old the players are that were born in the 1800s. This involves adding 1900 to the calculated dates. Once I turn the player's actual birth date to an Excel date, it says they were born in AD 3700 something - 5/6/1889 turns into 5/6/3789. I compensate for that by saying that the NCAA season starts on 3/26/3809 or whatever. The 697347 value changes every year based on the NCAA season start date.

The level field turns the level value into an actual level. This was determined for my game from the leagues_basic file from which I created a static table. The Unsigned field determines which players fit the proper criteria: Draft eligible last year (that's the 18.82 bit), still in HS or listed as a FA but only 19. Depending on how old the player is at draft time, the team will either hold onto him or release him if he is drafted but unsigned.

I use the Talent level to determine the player ranking and then select the top 20 to manually move to college. It is important to leave the players_value sheet open at all times while running these calculations or else it won't work.

Finally, I copy the date_of_birth field from column J and paste it in column DG, then run Text to Columns, using a hyphen as the delimiter. This will separate the date into year, month, day which will allow the DOB to calculate the proper age.

I sort by the Max field and filter on Unsigned = Y