Football Predictor

Hi,

Similar to other threads that I have seen on here, I run a football predictor competition at work and would like to set up a spreadsheet that will automatically calculate each individuals score.

The scoring works like this:

5 points for a correct result and correct score for e.g. 1-0 prediction and 1-0 result
3 points for a correct result but wrong score e.g. 3-2 prediction and 2-0 result
1 bonus point for a correct part score for either team but only if the overall result is correct result e.g. 3-2 prediction and 3-1 result, therefore bonus point is available for correctly predicting the 3 score correctly, giving an overall 4 points (3+1). Unlike others, I only give bonus points for if the overall score is correct and not if half a score is correct, e.g. 3-2 prediction and 0-2 result gets 0 points (apologies if I'm over stating how the scoring works).

I have altered this formula that I saw in another thread =IF(COUNT($X308:$Y308,Z308:AA308)=4,(1*OR($X308=Z308,$Y308=AA308))+(3*(SIGN($X308-$Y308)=SIGN(Z308-AA308)))+(1*AND($X308=Z308,$Y308=AA308)),"")

It just about works but gives a bonus point even with the overall wrong score, how do I go about changing what I am looking for?

I am happy to 'tinker' about with the spreadsheet but this type of formula is new to me and I don't understand how it works, could someone also explain what the formula is doing?

I can provide a spreadsheet with the results that I am looking for if that makes it easier.

Thanks.

Hi Micope 21,

thank you very much, I appreciate the help and persistence - you've stopped a potential argument within my team as I thought I was going to have to change the scoring system!

EMcK

Hi,

Trying to complete the spreadsheet for everything I would like it to do, I have a separate tab in the workbook that has a running total of points scored over the season that includes a break down of points scored for each week; I have this set to read the total score for each from the score predictions tab. I send out a weekly update on the scores to those playing so I used to manually sort my points table however I have now recently added another tab called leaderboard to automatically sort the table into the correct numerical order. That seems fine however I have noticed that if I have two players on the same number of total points it removes the 2nd person from my sorted table – is there a way round this. Ideally I would like to sort by total number of points then highest number of points scored in any one week over the weeks we play, is this something that you could help with?

Also, if I expand the number of players playing, is there a quick way to ‘copy cell reference’ from the scores prediction sheet onto the points table? When I try to copy the formula excel doesn’t appear to ‘learn’ the pattern so I have to manually tell it what cells to look at which is more likely to cause an error.

Any help will very much be appreciated.

Thanks,
EMcK