World cup 2014 sweepstakes kit
NOTE FOR CURRENT PLAYERS:
- To get the spreadsheet to pull the results you need to click on DATA, and then REFRESH ALL.
- All Knockout matches will appear once the group stage matches are complete
Below some ideas and instructions for using our Brazil World Cup 2014 Sweepstakes Kit (Click to get a copy) to run an office pool or sweepstakes amongst your friends during the tournament. These are the only sweepstakes spreadsheets that will automatically update the results so that you don’t have to! There are some video clips which show you the steps as well (in related news England to win the world cup according to excel).
Receive the world cup spreadsheet files
Once you have the zip file with the spreadsheets in it, we suggest that you delegate the control of the master spreadsheet to one or two responsible individuals.
Setting up the World Cup sweepstakes or office pool
The organiser should then send out, via email, one of the files (2014-Soccer-World-Cup-Spreadsheet-v8-SweepstakesInput.xlsx) to all the people that will be involved. Ask them to enter their name and predictions for all the group stage matches in the blue cells. Once they have predicted the group stages, the knock out stages will be populated. The participants must also enter the scores for the knock outs all the way to the finals and predict the winner in that match. Note that during the knock-out stage, a match must have a winner, so they must either enter a winning score e.g. 2-1, or else if they want to enter a draw, they must use decimals to show the penalty shoot out results e.g. 2.5- 2.3 means a 2-2 draw in the match followed by a 5-3 win for team A in the penalty shoot-out. These spreadsheets must be sent back to the organiser via email (before the World Cup starts 🙂 )and it is recommended that these emails be kept for audit purposes.
Running the World Cup Office pool
The organiser must update the main spreadsheet (2014-Soccer-World-Cup-Spreadsheet-v8-Sweepstakes.xlsx) with all the participants predictions. To do this they must:
- Have both the main spreadsheet and the spreadsheet received from the participant open
- Go to the participants spreadsheet and firstly check that they have written their name into cell A1. If not, then fill it in for them.
- Highlight cell F10 to J91 (this purposefully includes the names of the teams)
- Copy this area.
- Go to the main spreadsheet and choose one of the empty yellow sheets currently labelled Player 1, 2 etc.
- Click on T10 and Paste as Values (very important it is done this way)
- Do this for all the players and don’t forget to save along the way.
- The black sheet called Masterfile is a calculation sheet and should not be changed.
If you want to test the spreadsheet prior to the tournament, you need to trick it into believing the matches are playing. You do this by going to the last sheets in the main spreadsheet (Actual Results Web & KnockOutWeb). This is where the results will appear, but for now, you can capture some ‘results’ to see that everything is working fine. The video clip below shows how to use RANDBETWEEN to get Excel to guess some scores instead of you working through all the cells. Please note that only when all the actual results from the group stages are captured will the knock-out matches flow through to the scoring. This is explained better in the video clip below. As the tournament progresses the scores will be updated automatically into this master file (2014-Soccer-World-Cup-Spreadsheet-v8-Sweepstakes.xlsx) when it is opened. If the organiser wants to manually update the scores you can go to the last sheets (ActualResultsWeb) and enter the scores in the blue cells (please don’t change anything else here). All the participants sweepstakes points will be calculated and you can see a summary of the rankings on the Overall Pnts sheet (beginning of the spreadsheet).
I was wondering, the points for correctly predicting the knock out stage participants say points for quarter finals, semi and final but nothing for the last 16 (second round?) is this the same as quarter final?
- Yes, this is a typo. The points for last 16 and quarters are the same
Is there points awarded for actually predicting the scores of the knock out games or just the teams playing?
- It is just for the teams playing. Getting the scores right does not add to the points
Lastly, I put in some results just to see how it worked and I put a draw for some of the knock out games. It seemed to randomly select one of the teams to go though. How does this work?
- For the knock out matches you need to have a winner. You can either choose a winning score e.g. 2-1 or else if you want to indicate a penalty shoot out, use for example 1.5- 1.3 indicating a 1-1 draw with a 5-3 penalty shoot out win.
I am using Excel 2011 on an Apple Mac and it is very slow and continually thinking.
- The version you get now by downloaded in updated but for older users the picture links on the Mac are very slow. Email us and we will send a new copy or we can tell you how to correct this.
Can you use the spreadsheet on Google Docs
- It wasn’t built with Google Docs in mind, but some users have tried it and say it works. As an idea get all the information via Excel and then upload the masterfile to Google Docs and allow the players to see what is happening. Only limitation is that it won’t automatically update so you will need update the scores
Will you score points for correct team through to the finals (1/8, 1/4, 1/2 and final) regardless of if the team advanced from a first or second place in the group stage? Or do you have to predict the correct placing in the group stage to score points?
- You get points for your chosen teams getting to the next stage. How they got there is not important
Can you confirm that for the knockout stages, we can enter decimal places and the master file will be able to recognise that because the actual scores are going to update themselves so will it be done in a the format to give points correctly etc
- Yes, use a decimal if you want to indicate that there was a penalty shootout. See the note below about decimals though
Hello. I’m having issues opening the spreadsheet on my Mac. It gives me an error message and just opens as a “read-only” file. How can I go about fixing this issue?
- Try and expand it out of the zip files and save it in a separate location
Just one thing, I’ve been having issues with the participants spreadsheet not opening in excel 2003
- Please send us an email and we will send a version that works with Excel 2003. We used the IFERROR function which was not available at that time.
Do I need to change the formats in the cells? I get dates(01.03.2014) when typing 1.3 or 2.5. I have tried with commaes 1,3 also, but it did not help
- Use the character that indicates a decimal on your computer. Some use commas and some use full stops so use whatever you normally use for a decimal
Hope that helps. If you have any questions please feel free to email us at info@AuditExcel.co.za. Below another overview