bubbasbrew
New Member
- Joined
- Dec 23, 2011
- Messages
- 3
I have a College Bowl contest going and I want the players to see how they rank in order 1-33 with the other players in the contest. Her is my formula for the players:
=INDEX(A$1:CR$1,1,LARGE(IF($H$83:$CR$83=D89,COLUMN($H$83:$CR$83)),COUNTIF($D$89:$D89,D89))*1)
*A1-CR1 are the names of the players
*H83-CR83 are their scores
*D89-D121 is how their point are stacked in this Large Formula: =LARGE(H$83:CQ$83,F89)
*F89-F121 are just the numbers 1-33
The Formula for the Rankings is: =RANK(D89,$D$89:$D$118,0)
Now this all works but only if there are scores entered. If there is just a 0 for all of the scores, then this is what I get:
[TABLE="width: 382"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Zerbel, Tim[/TD]
[TD]WI[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Wheaton, Christine[/TD]
[TD]TX[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]
The 0's should have team members names and States in them. This is what it looks like with scores in them:
[TABLE="width: 545"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Bowers, Bill[/TD]
[TD]AZ[/TD]
[TD]34[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Fraize, Larry[/TD]
[TD]MA[/TD]
[TD]27[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grzanna, Troy[/TD]
[TD]WI[/TD]
[TD]23[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Skinner, Phillip[/TD]
[TD]GA[/TD]
[TD]21[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Bowers, Mike[/TD]
[TD]FL[/TD]
[TD]20[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ellis, Jerry[/TD]
[TD]VA[/TD]
[TD]19[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Leahy, Chris[/TD]
[TD]WI[/TD]
[TD]18[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Bowers, Carolyn[/TD]
[TD]FL[/TD]
[TD]18[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
So.........How close am I to getting this right?
Mike
=INDEX(A$1:CR$1,1,LARGE(IF($H$83:$CR$83=D89,COLUMN($H$83:$CR$83)),COUNTIF($D$89:$D89,D89))*1)
*A1-CR1 are the names of the players
*H83-CR83 are their scores
*D89-D121 is how their point are stacked in this Large Formula: =LARGE(H$83:CQ$83,F89)
*F89-F121 are just the numbers 1-33
The Formula for the Rankings is: =RANK(D89,$D$89:$D$118,0)
Now this all works but only if there are scores entered. If there is just a 0 for all of the scores, then this is what I get:
[TABLE="width: 382"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Zerbel, Tim[/TD]
[TD]WI[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Wheaton, Christine[/TD]
[TD]TX[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]
The 0's should have team members names and States in them. This is what it looks like with scores in them:
[TABLE="width: 545"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Bowers, Bill[/TD]
[TD]AZ[/TD]
[TD]34[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Fraize, Larry[/TD]
[TD]MA[/TD]
[TD]27[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grzanna, Troy[/TD]
[TD]WI[/TD]
[TD]23[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Skinner, Phillip[/TD]
[TD]GA[/TD]
[TD]21[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Bowers, Mike[/TD]
[TD]FL[/TD]
[TD]20[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ellis, Jerry[/TD]
[TD]VA[/TD]
[TD]19[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Leahy, Chris[/TD]
[TD]WI[/TD]
[TD]18[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Bowers, Carolyn[/TD]
[TD]FL[/TD]
[TD]18[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
So.........How close am I to getting this right?
Mike
