Office Pool Spreadsheet Help

meatus

New Member
Joined
Jun 2, 2008
Messages
4
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 Workbook
ABCDEFGHIJ
1NAMEOVERALLWeek 1
2CorrectTotal%WeeksPts DiffCorrectTotalPointsPts Diff
3Master-45--1221644
4Player 1314568.89%33410162123
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I used to run a pool. Man, it's a gigantic PITA trying to get everyone to make their picks on time!

Not real sure what you're trying to do but since I'm an Excel dweeb *and* a huge football fan I'll help you as much as I can.

I think I would need to see your file to help me understand what you're wanting to do.

I'll send you a private message with my email address and you can contact me if you'd like.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top