MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Office Pool Spreadsheet


Posted by Hogger on September 06, 2001 10:42 AM

I am wondering how I would set up a spreadsheet for an office pool where each particpant selects one winner from one of 15 possible games.

Each week each participant would look at the 15 games to pick from and select one game that they would like to pick a winner from. The pool would run for 17 weeks and one wrong pick would disqualify you from picking in the following weeks until there was only one participant left. The problem as you can probably already see is that each week participants have a choice of 15 games to pick from for a total of 30 possible selections.

My question is what function would be the right one to accomplish this task. I would like excel to look at the picks for each participant and then compare these picks to a list of the winning teams for that specific week and return a number value of 1 for a correct pick and a text value of "out" for an incorrect pick. If it is also possible I would also like to keep a total tabulated at the end of each week, where a participant could see the number of correct picks they have made thus far in the pool. Now I don't want to confuse anyone as to the number of picks allowed per person per week... it is only one pick per participant per week.

Thank You
Hogger


Posted by IML on September 06, 2001 11:23 AM

Not real sure of your format, but lets say you have names in col A and week 1 picks in colB and you want to evaluate in colC
First list the winners someone on your sheet (spelling counts). in this example, I put them in B26:B33
Your formula for C could be
=IF(COUNTIF($B$25:$B$32,B1)=1,1,"OUT")
and copy it down as far as you need to.

You should be able to just use a sum formula for you totals as "OUT" and team names would influence this.

good luck