I've been doing a weekly NFL office pool for a few years. I use excel to do all of the sheets and to keep track of everyones picks. This year I decided to offer a prize for the person who had the overall best year. In order to qualify they needed to play at least 13 of the 17 weeks. For the ones that played all the weeks or more than 13 I'm only counting their best 13 weeks. Well we just finished week 14 so I need to start going through and counting their best 13. I want to have excel do the work for me so I don't have to each week. I can't think of a way to do it though. Is there a formula that can total the top 13 cells of a range? Another problem/kink is that with the byes in the NFL there are a different amount of total games from week to week. So for a person on one week they got 12 of 13 right and another they have 13 of 16 right I need it to show the 12 as the higher number. I'm pretty sure that I'll need to add another column in each week for a percentage so that is what is counted but I need to have it total that weeks correct picks in the overall section at the first.
Hopefully that makes sense. I've probably been thinking it's way to hard than it is and I'm missing an easy way of accomplishing this. I've only included the overall section and the first week to give an example of how I have it set up. I've also included a link to an example on Google Docs to see a better example.
The master row is where I enter the number of games for that week and how many total points were scored for the tie breaker game.
Excel 2003
Google Docs example:
http://spreadsheets.google.com/ccc?key=0Asb0et5M7kbMdEstaDIzZDVzZHpiOUN5akRDb3BNRnc&hl=en
Hopefully that makes sense. I've probably been thinking it's way to hard than it is and I'm missing an easy way of accomplishing this. I've only included the overall section and the first week to give an example of how I have it set up. I've also included a link to an example on Google Docs to see a better example.
The master row is where I enter the number of games for that week and how many total points were scored for the tie breaker game.
| Excel Workbook | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | NAME | OVERALL | Week 1 | |||||||||
| 2 | Correct | Total | % | Weeks | Pts Diff | Correct | Total | Points | Pts Diff | |||
| 3 | Master | - | 45 | - | - | 122 | 16 | 44 | ||||
| 4 | Player 1 | 31 | 45 | 68.89% | 3 | 34 | 10 | 16 | 21 | 23 | ||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3 | =SUM(G3,K3,O3,S3,W3,AA3,AE3,AI3,AM3,AQ3,AU3,AY3) | |
| C4 | =SUM(H4,L4,P4,T4,X4,AB4,AF4,AJ4,AN4,AR4,AV4,AZ4,BD4,BH4,BL4,BP4,BT4) | |
| F3 | =SUM(I3,M3,Q3,U3,Y3,AC3,AG3,AK3,AO3,AS3,AW3,BA3,BE3,BI3,BM3,BQ3,BU3) | |
| F4 | =SUM(J4,N4,R4,V4,Z4,AD4,AH4,AL4,AP4,AT4,AX4,BB4,BF4,BJ4,BN4,BR4,BV4) | |
| B4 | =SUM(G4,K4,O4,S4,W4,AA4,AE4,AI4,AM4,AQ4,AU4,AY4,BC4,BG4,BK4,BO4,BS4) | |
| D4 | =B4/C4 | |
| E4 | =COUNTA(G4,K4,O4,S4,W4,AA4,AE4,AI4,AM4,AQ4,AU4,AY4,BC4,BG4,BK4,BO4,BS4) | |
| H4 | =IF(G4>0,$G$3,0) | |
| J4 | =IF(I4>0,ABS($I$3-I4),0) | |
Google Docs example:
http://spreadsheets.google.com/ccc?key=0Asb0et5M7kbMdEstaDIzZDVzZHpiOUN5akRDb3BNRnc&hl=en
