Formula Question: Fantasy Football Related

[srs310]

Here's my dilemma. I am trying to experiment with letting excel tell me who to draft for my fantasy team based on the average draft position and ranking of the remaining players on the board.

I'm in need of a formula that would check the availability status (whether it is yes or no: which will be updated manually). If the return value is "no" then the player will be factored out of any equations. If the value returns "Yes" as in he is still available, it would search through the position category for anyone of that same position category (i.e Running Back, Tight End, Quarterback, etc) and search for the best available player in that category (lowest rank). As an end result I would like the formula to return the word "Draft" for the highest ranked player in each particular category. Thanks for any help / advise and sorry if the wording is confusing, I tried to keep it as simple as i could in the explanation. - Below is a snippet of what my data fields are made up of.

[TABLE="width: 1539"]
<colgroup><col style="text-align: center;"><col span="4" style="text-align: center;"><col span="8" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Rank[/TD]
[TD="align: center"]Player[/TD]
[TD="align: center"]Team[/TD]
[TD="align: center"]Availabilty Status (Yes / No)[/TD]
[TD="align: center"]Position Category[/TD]
[TD="align: center"]POS[/TD]
[TD="align: center"]Yahoo[/TD]
[TD="align: center"]ESPN[/TD]
[TD="align: center"]CBS[/TD]
[TD="align: center"]FFC[/TD]
[TD="align: center"]NFL[/TD]
[TD="align: center"]Fantrax[/TD]
[TD="align: center"]AVG[/TD]
[TD="align: center"]Draft [/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Adrian Peterson[/TD]
[TD="align: center"]MIN[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1.7[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Eddie Lacy[/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2.7[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Jamaal Charles[/TD]
[TD="align: center"]KC[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2.8[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Le'Veon Bell[/TD]
[TD="align: center"]PIT[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Marshawn Lynch[/TD]
[TD="align: center"]SEA[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4.8[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Antonio Brown[/TD]
[TD="align: center"]PIT[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Wide Receiver[/TD]
[TD="align: center"]WR1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6.2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Matt Forte[/TD]
[TD="align: center"]CHI[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB6[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10.2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Dez Bryant[/TD]
[TD="align: center"]DAL[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Wide Receiver[/TD]
[TD="align: center"]WR2[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10.3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Andrew Luck[/TD]
[TD="align: center"]IND[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Quarterback[/TD]
[TD="align: center"]QB1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]11.3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Demaryius Thomas[/TD]
[TD="align: center"]DEN[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Wide Receiver[/TD]
[TD="align: center"]WR3[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]11.7[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]C.J. Anderson[/TD]
[TD="align: center"]DEN[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB7[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]11.8[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Aaron Rodgers[/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Quarterback[/TD]
[TD="align: center"]QB2[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]12.2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]DeMarco Murray[/TD]
[TD="align: center"]PHI[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB8[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]12.3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Rob Gronkowski[/TD]
[TD="align: center"]NE[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Tight End[/TD]
[TD="align: center"]TE1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Odell Beckham Jr.[/TD]
[TD="align: center"]NYG[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Wide Receiver[/TD]
[TD="align: center"]WR4[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13.7[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Jeremy Hill[/TD]
[TD="align: center"]CIN[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Running Back[/TD]
[TD="align: center"]RB9[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]14.7[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi srs310,
if your data starts in A1, this is the formula for N2: {=MIN(IF($E$2:$E$17=$E2;IF($D$2:$D$17="Yes";$M$2:$M$17)))} , press CTRL+SHIFT+ENTER as it's an array formula (more about array formulas: Array Formulas ). It will return the lowest AVG for that player type where the Availability Status = Yes. The column next to it could have a simple formula comparing the AVG with that column.
Ciao,
Koen