natesteiner21
New Member
- Joined
- Jul 10, 2012
- Messages
- 35
- Office Version
- 365
- Platform
- Windows
I put together a NCAA Tournament Pool for a friend of mine last year and it seemed to work just fine, except, there were some ties that I should have realized would happen before I gave her the completed sheet. I will do my best to describe what I'm doing below.
Column A-Ticket # (Means Nothing Here)
Column B-Total
Column C-Rank
Column D-Rank 2
Column H-Group One (Team)
Column I-Pts_1/2 (Total Points Scored by Column H)
Column K,N,Q,T,W,Z,AC-Group 2-8 (Teams)
Column L,O,R,U,X,AA,AD-Pts_3/4_15/16 (Total Points Scored by Columns K-AC)
What I'm trying to do is whoever has the highest total in Column B is the winner, which would obviously show up in Column C. Some of the numbers in Column B are going to be the same and I have to use the tiebreaker for this, which is, total points scored in Column AD (1st TB), AA (2nd TB), X (3rd TB), U (4th TB), R (5th TB), O (6th TB), L (7th TB), I (8th TB).
The reason that I have 2 Rank Columns is that Rank is just =RANK(B2,B$2:B$3000,0)
While Rank_2 is: =IF(COUNTIF(C:C,C2)=1,C2,RANK($B2,$B$2:$B$3000)+SUMPRODUCT(($B$2:$B$3000=B2)*($AD$2:$AD$3000>$AD2))+SUMPRODUCT(($B$2:$B$3000=$B2)*($AA$2:$AA$3000>$AA2))+SUMPRODUCT(($B$2:$B$3000=B2)*($X$2:$X$3000>$X2))+SUMPRODUCT(($B$2:$B$3000=B2)*($U$2:$U$3000>$U2))+SUMPRODUCT(($B$2:$B$3000=B2)*($R$2:$R$3000>$R2))+SUMPRODUCT(($B$2:$B$3000=B2)*($O$2:$O$3000>$O2))+SUMPRODUCT(($B$2:$B$3000=B2)*($L$2:$L$3000>$L2))+SUMPRODUCT(($B$2:$B$3000=B2)*($I$2:$I$3000>$I2))+COUNTIF($C$2:C2,C2)-1)
The issue is that it isn't working correctly for every single Ticket #. Most of them it works for but not all of them.
Any help would be appreciated. Hopefully I just screwed something up typing it out.
Thanks.
Column A-Ticket # (Means Nothing Here)
Column B-Total
Column C-Rank
Column D-Rank 2
Column H-Group One (Team)
Column I-Pts_1/2 (Total Points Scored by Column H)
Column K,N,Q,T,W,Z,AC-Group 2-8 (Teams)
Column L,O,R,U,X,AA,AD-Pts_3/4_15/16 (Total Points Scored by Columns K-AC)
What I'm trying to do is whoever has the highest total in Column B is the winner, which would obviously show up in Column C. Some of the numbers in Column B are going to be the same and I have to use the tiebreaker for this, which is, total points scored in Column AD (1st TB), AA (2nd TB), X (3rd TB), U (4th TB), R (5th TB), O (6th TB), L (7th TB), I (8th TB).
The reason that I have 2 Rank Columns is that Rank is just =RANK(B2,B$2:B$3000,0)
While Rank_2 is: =IF(COUNTIF(C:C,C2)=1,C2,RANK($B2,$B$2:$B$3000)+SUMPRODUCT(($B$2:$B$3000=B2)*($AD$2:$AD$3000>$AD2))+SUMPRODUCT(($B$2:$B$3000=$B2)*($AA$2:$AA$3000>$AA2))+SUMPRODUCT(($B$2:$B$3000=B2)*($X$2:$X$3000>$X2))+SUMPRODUCT(($B$2:$B$3000=B2)*($U$2:$U$3000>$U2))+SUMPRODUCT(($B$2:$B$3000=B2)*($R$2:$R$3000>$R2))+SUMPRODUCT(($B$2:$B$3000=B2)*($O$2:$O$3000>$O2))+SUMPRODUCT(($B$2:$B$3000=B2)*($L$2:$L$3000>$L2))+SUMPRODUCT(($B$2:$B$3000=B2)*($I$2:$I$3000>$I2))+COUNTIF($C$2:C2,C2)-1)
The issue is that it isn't working correctly for every single Ticket #. Most of them it works for but not all of them.
Any help would be appreciated. Hopefully I just screwed something up typing it out.
Thanks.
