bigdrulez3141
New Member
- Joined
- May 13, 2015
- Messages
- 3
Hey all. Thank you in advance for reading this ridiculously long question, which I'd assume has a pretty simple answer that I can't figure out. First, some background.
I run a sports league with statistics taken by me and entered into a template which automatically updates team stats and a statistical leaderboard. This all works fine, but for the players to be eligible to be on the leaderboard, I need it to be able to automatically sort out the player that has thrown less than 35 passes. The Column Headers and Formulas for the first place ranking are as follows:
Number-=INDEX('Spring 2015 Leaders'!$U$4:$U$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(E4)-ROW(E$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Name-=INDEX('Spring 2015 Leaders'!$S$4:$S$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(C4)-ROW(C$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Team-=INDEX('Spring 2015 Leaders'!$T$4:$T$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(D4)-ROW(D$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Attempts-=INDEX('Spring 2015 Leaders'!$U$4:$U$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(E4)-ROW(E$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Completions-=INDEX('Spring 2015 Leaders'!$V$4:$V$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(F4)-ROW(F$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Comp %-=INDEX('Spring 2015 Leaders'!$W$4:$W$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(G4)-ROW(G$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Yards-=INDEX('Spring 2015 Leaders'!$X$4:$X$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(H4)-ROW(H$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Yds/att-=INDEX('Spring 2015 Leaders'!$Y$4:$Y$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(I4)-ROW(I$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
TD-=INDEX('Spring 2015 Leaders'!$Z$4:$Z$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(J4)-ROW(J$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
TD%-=INDEX('Spring 2015 Leaders'!$AA$4:$AA$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(K4)-ROW(K$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
INT-=INDEX('Spring 2015 Leaders'!$AB$4:$AB$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(L4)-ROW(L$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
INT%-=INDEX('Spring 2015 Leaders'!$AC$4:$AC$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(M4)-ROW(M$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
QB Rating-=INDEX('Spring 2015 Leaders'!$AD$4:$AD$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(N4)-ROW(N$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
How do I adjust my formulas for them to skip to the next record that has at least 35 attempts?
I know you will probably have many questions for me, but thank you very much in advance!
I run a sports league with statistics taken by me and entered into a template which automatically updates team stats and a statistical leaderboard. This all works fine, but for the players to be eligible to be on the leaderboard, I need it to be able to automatically sort out the player that has thrown less than 35 passes. The Column Headers and Formulas for the first place ranking are as follows:
Number-=INDEX('Spring 2015 Leaders'!$U$4:$U$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(E4)-ROW(E$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Name-=INDEX('Spring 2015 Leaders'!$S$4:$S$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(C4)-ROW(C$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Team-=INDEX('Spring 2015 Leaders'!$T$4:$T$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(D4)-ROW(D$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Attempts-=INDEX('Spring 2015 Leaders'!$U$4:$U$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(E4)-ROW(E$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Completions-=INDEX('Spring 2015 Leaders'!$V$4:$V$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(F4)-ROW(F$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Comp %-=INDEX('Spring 2015 Leaders'!$W$4:$W$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(G4)-ROW(G$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Yards-=INDEX('Spring 2015 Leaders'!$X$4:$X$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(H4)-ROW(H$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
Yds/att-=INDEX('Spring 2015 Leaders'!$Y$4:$Y$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(I4)-ROW(I$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
TD-=INDEX('Spring 2015 Leaders'!$Z$4:$Z$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(J4)-ROW(J$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
TD%-=INDEX('Spring 2015 Leaders'!$AA$4:$AA$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(K4)-ROW(K$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
INT-=INDEX('Spring 2015 Leaders'!$AB$4:$AB$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(L4)-ROW(L$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
INT%-=INDEX('Spring 2015 Leaders'!$AC$4:$AC$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(M4)-ROW(M$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
QB Rating-=INDEX('Spring 2015 Leaders'!$AD$4:$AD$157,MATCH(LARGE($AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),ROW(N4)-ROW(N$4)+1),$AD$4:$AD$157-ROW($AD$4:$AD$157)/COUNT($AD$4:$AD$157),0))
How do I adjust my formulas for them to skip to the next record that has at least 35 attempts?
I know you will probably have many questions for me, but thank you very much in advance!