The following are some email discussions regarding office pools in general as well the World Cup Office Pool in particular and a question or two on the workings of my bracket calculators. Enjoy!

On May 25, 2010, at 7:02 PM, Brian wrote:

Greetings,

Great work on putting together such an elaborate spreadsheet. This will definitely make things easier when keeping track of things. I just had a quick question. Do the participants make the picks for the entire tournament right away? Or do they make the group stage picks and then make picks again before the knockout stage starts?

Either way, this latest edition I created was with the thought that participants would make their picks all the way through.

I think it kind of depends on how many people you have participating in the pool and how well you know them. If it is a small group or it is people you know pretty well and you're pretty sure you can reach them easily a two stage picking format might be more fun. That is people pick the group phase and then they re-pick the knock out stage after the group phase. To me this format will maintain more interest in the tournament all the way to its conclusion as people will have more teams to pull for if you let them re-pick after the group phase. But if you have a large group and people you may have trouble reaching it may be better to just have them pick all the way through. If it weren't for awarding points for correct picks in the wrong slot I don't think this would make for a very exciting game/pool as the knock-out stage would likely not have many people garnering points as the chance of picking many of the correct finishes is slight. But since the calculator awards points for a correct pick in the wrong slot as well as in the right slot I think there is a good chance people will still have a fair amount of teams left in the knock-out stage to maintain interest in the game/pool. And I feel that correct picks in the wrong slot should be equal in point value to correct picks in the wrong slot as one has already penalized the participant in the group phase for rewarding less points for picking the wrong finish. I don't think that should count against people twice. Plus if you don't have many points for picks in the knock-out stage it will be greatly out weighed by points gathered in the group phase hence making picks in the knock-out stage meaningless. (This only applies if you decide to pick all the way through before the start of the tournament).

ESPN and FoxSoccer.com both have bracket pools. ESPN awards points for correct picks in the wrong slot but they give bonus points if you get the right team in the right slot. I do not think Fox pool awards points for correct pick in the wrong slot.

The pool I am hosting is being done in two stages with about 15 participants I hope. But mine is mostly with family and close friends. What I hope to achieve with the amount of points I award for the picks in the two stages is about equal weighting/points for the two stages i.e. the average points garnered by a participant in the Group Stage will be about equal to the Knock-out Stage. If you want to change the amount of points awarded for the picks that can be done easily enough by just entering a new number in the box at the bottom of the page. I typed in the points in the heading box for the point totals so I could come back to those if I changed the points in the smaller box.

Remember if you do the two stage pool you will have about 24 hours to get people's picks as the knock-out stage I believe starts the day after the group stage finishes.

My BASIC DRAFT has you pick teams and tracks points all the way through the group stage and continues into stage 2. If your team does not make stage 2 then you are out of luck and get no points. I like this format except for the rare occasion you lose all your teams (I will be posting tips on how to relieve the stress of bad brackets later). With this system you don’t have to worry about the 24 hour turnaround from the group stage to stage 2.

I would avoid having to re-pick for stage 2 because many people will not get their picks in.

On May 26, 2010, at 11:33 AM, Tse wrote:

Hi Andy,

I downloaded your 'fifa world cup table 5.xls' and its awesome.

I have some questions as to how the Round of 16 is played. Your spreadsheet picks up the participants selection for round of 16, but what if their pick was wrong, what happens to their Round of 16 points?

For example Round of 16 1A vs 2B, the actual is France vs S Korea but I didn't pick France as 1A. That means this Round of 16 is of no interest to me?

Not sure if i missed out any instructions on your page or in the spreadsheet. Please enlighten.

If you picked France as 2A and they finish 1A you will get credit (points) for that pick equal to the amount that you enter at the bottom of the table where it says 3 points for a correct pick in the wrong slot. Maybe correct pick is the wrong way to say it but what I mean is as long as you picked france to finish first or second they will garner points for you in the knock-out stage whether you picked there finish/rank correctly.

You can adjust how many points you give just by typing in the small box next to the heading for points for certain picks. I have the points set as it currently is to try and make the points garnered in the group phase about equal to the points gathered in the knock out stage. See the question on the second page of my site for more thoughts on this.

Did this answer your question? Please write back if I did not answer what you were asking.

My question is with Round 2 and beyond - how does it work? Do we pick teams for round 2 and beyond? It seems your spreadsheet takes my first round picks and place them as my second round picks (E45 and E46 are auto populated). What happens if my first round picks are wrong? What do I fill in for E47/48?

Ahh I see your question now. Yes your right it autopopulates your picks to round two but once you enter the actual results for the group phase the advancing teams will be auto-populated to the appropriate places on the left side of the calculator hence allowing you to pick from the actual participants that advance to the knock-out stage. Hence you can just ignore the advancement of your picks from the group phase. This particular calculator is made more for picking all the way through to the final but can certainly be used for a two stage picking pool. I like it for the two stage picking pool as it does show the picks for all the participants from the group phase which allows a little better comparison than just a bunch of numbers. But if you don't like this particular calculator for the two stage picking process just download the older calculator its download link is just below the first download link on the left of the webpage.

As you enter the tournament results in round 1 the first and second place finshers will autopopulate to B46 and B47 for first from group A and second from group B respectively then you can pick from these cells to fill in E46/47 of D46/47 whichever column is yours.

I really appreciate your questions. Let me know if you have any other questions or if I did or did not get you the answer you were looking for.

On May 26, 2010, at 11:43 PM, Tse wrote:

Thanks for the explanation, I understand fully now.

Many thanks again, AWESOME spreadsheet. We'll enjoy our World Cup much more with your spreadsheet.

2) How difficult is it to improve your spreadsheet to allow prediction of 3 / 4 placing in each group?

I am thinking to allow my friends to predict not only the top 2 teams but all the teams in each group. Predicting only the top 2 teams is very likely to end up with many same predictions, so to include rankings for all teams will show who really knows the teams well.

Question 1: The points are calculated/accumulated and auto-populated as the results of the tournament are entered (is that what you mean by real time?). But to get the proper order for the rankings does require some manipulation on your part. If your using Microsoft Excel you have to go under tools menu bar and sort the columns labeled participant, points and paid in descending order. Do not sort the column labeled rank, if you sort it with the other columns then their current rank moves with them. You will want to resort after the group stage and you could resort after every game in the knock-out stage if you wanted. If you are using Apple Numbers just put your pointer at the top of the column labeled rank and click the drop down menu chevron and go to sort descending. With Apple Numbers I put a Table with the rankings 1,2,3,4,etc. overlying the ranking table so that these numbers are fixed while the table underneath it is sorted.

Question 2: Oh boy, I think that would take quite a bit of doing. It is doable though. Is your main objective to score their 3rd and 4th place picks or just show their 3rd and 4th place picks?

On May 27, 2010, at 12:35 PM, Tse wrote:

Many thanks again for your prompt reply.

FYI, my proficiency with excel is prob medium level.

Q1) If I sort only the Participants, Points and Paid columns without sorting Rank, then the link between the 3 columns -> Rank will be lost?

I am really lost here, can't figure out your logic for this tab. Why did you put '1' at A51? All the other cells above A51 are '+1' of other 'A' cells but there doesn't seem to be a pattern for cells between A2 and A19.

Q2) I want to score their 3rd and 4th picks. The points awarded for 3rd will be the same as 4th, and preferably different value from E125 and E127, if possible.

Sorry, I think I see what is happening. I have sorted the document using Apple Numbers and that has mixed up the ranking table for the Excel document. When I work on the table with my Apple Computer it is with Apple's program and then I save a copy as an Excel document.

So what you will need to do is sort the rank column in your excel document in ascending order that should bring 1 back up to the top (to cell A2) and the order should go 1,2,3,4,etc down the column. The reason column A is jumbled up in the Excel document is b/c I sorted it in Numbers and Numbers does not allow you to disassociate columns in the same table when you sort them. That why I have to put a second table overlying the ranking sheet in the Numbers document with the 1,2,3,4,etc. column. You want column A to stay fixed reading 1,2,3,4,etc. as you go down the column and you want to sort column C (labeled points) in descending order so the person with the most points is at the top. You want the data in columns B (labeled participant) and column D (labeled paid) to move with the data in column C as you sort column C.

Does that make sense? Great questions keep them coming.

Regarding question 2, I've been pondering on question #2 I think I have thought of a way to do it that may not be so labor intensive. I will see if I can work on it this weekend. If it works what I will do is award the highest points for picking correct rank of an advancing team, a in the middle amount of points for picking an advancing team but incorrect rank and the lowest amount of points for a correct rank of a non-advancing team.