Thank you benztown - could you please also send me the password? I'd like to use this for a prediction game for the people in my office this summer Many thanks
Thanks benztown - I'd also like to use this as a prediction game, so could I have the password too please? Thanks
Hi Benztown, can you PM me the password as well? Don't want to modify it or anything, just an excel buff who wants to see the formulas.
Hi can you please send me the password? I am not good in excel sheets.. I wanted to link the whole prediction thing to 15 persons... maybe you can explain some steps in PM thanks a lot!
can you send me the password please? i started making my own one but got stuck in the group ranking formulae
Thank you Benztown - could you please also send me the password? I'd like to use this for a prediction game for the 2 young boys football teams that I coach - they would love this.. Many thanks
Hi Benztown... is it possible for someone to do their own result predictions for every game and then get points awarded for each result, e.g. 6 for win and 3 for draw and a point for each goal correctly predicted?? This gives everybody a chance to score points even if their team is losing by still getting points for scoring goals. Not sure what formulas would be needed here?
If I understand you correctly, it shouldn't be a problem. My approach would be the following: Duplicate the prediction sheet once. The first one would then be the master sheet where the correct results are entered, the second one would be a prediction sheet that needs additional score keeping. For that, I'd add an additional column where the points are calculated by comparing the predicted result with the actual result on the master sheet. That's not particularly difficult but it could turn into a very long formula, so for the sake of clarity, I would split it up into several formulas that do their magic in hidden cells with only the ultimate result being visible. When it's all done, I'd create a duplicate of the prediction sheet for every participant.
Thks Benztown ..... would probably do 4 points for correct prediction...i.e. predict the win or draw.... And then one bonus point for each correct score of each team in the match. Trying to keep it s
[quote="ColinO, post: 30065428, member: 242o Thks Benztown ..... would probably do 4 points for correct prediction...i.e. predict the win or draw....l And then one bonus point for each correct score of each team in the match. Trying to keep it simple but also create interest in every game even if your overall result prediction is wrong. Anyways it's an idea only as I am useless at these formulas and wouldn't know how to create the prediction sheet even if I tried. I did this on paper many years ago for family but it was very time consuming, manually working the points system...but everybody loved it as even though their team would be losing they would be cheering them on to score as that was their goal prediction for their team in that game. Would need spreadsheet now as I have lots of boys in the club. Thks again Benztown..spreadsheet is great...
I'm sure you'll understand that I won't do customization for the spreadsheet, making this spreadsheet was work enough as it is. But as I said, adding an evaluation functionality isn't that complex as all it takes is to compare cells, so it's really only a bunch of IF functions. For instance, it could look like this: Let's call the first cell we're in S1 (S for score): =IF('master'!G1=G1, 1, 0) So here we would compare the cell G1 (which in this case would be goals scored by team A) in the master sheet with the same cell in the current sheet. If they're the same, the value of the cell will be 1, else it will be 0. The same would be done for cell S2, though now we'd compare the goals scored by team B: =IF('master'!G2=G2, 1, 0) In cell S3, we'd then see if the correct winner was predicted, in which case the value will be 4, otherwise it will be 0: IF(OR(AND('master'!G1>'master'!G2, G1>G2), AND('master'!G1<'master'!G2, G1<G2), AND('master'!G1='master'!G2, G1=G2)), 4, 0) Now all you have to do is to add up S1, S2 and S3 and you'll get your score for this game. If you put all that in the same row as is the game itself, all you have to do from here on is to copy and paste these formulas.
Hi benztown, how should one settle a win in extra time or with penalties? since if you put a draw from round of 16 onwards, it will not work.. please help!
Well, In order to keep it simple, I decided to only have the final score in the sheet. If you want more granularity, you'd have to add cells for the score in extra time and penalties for each of the knock-out games and adapt the formulas accordingly, but that should be rather simple as all you'd need to do is to use sums over three cells instead of just one cell. Alternatively, you could put in checkboxes one could mark for extra time and penalties in order to indicate what the final result represents. That wouldn't be quite as detailed and it would be more difficult to implement, but it wouldn't clutter the layout as much.
I filled in ONLY Group B. Final Standing : Holland-7, Spain-5, Chili-4 and Australia-0 Placed Spain Ok. BUT added Uruguay in game 52 as Winner of D! Should have had Holland in Game 51 Winner of B ?
Good catch. There was indeed an error in the "Winner Group D" cell. It didn't affect the actual results, so at the end of the day, the correct team would appear there, but it did fill in the leading team of Group D too early if there were games played in Group B (a bad copy and paste job on my part). I uploaded an updated version.
Thank you benztown - could you please also send me the password? I'd like to use this for a prediction game for the people in my office this summer Many thanks
Excellent spreadsheet Benztown. I too would like to set up a predictions game for the office. So could I also have the spreadsheet. I'd happily send the new version back if it helps as there appears to be a demand. Thanks