Help with Football Prediction spreadsheet formula

Hi. I do a football prediction competition at work and need help or the formula to calculate peoples scores.

The scoring works like this

10 points for a completely correct result and score for both teams e.g. 1-0 prediction and 1-0 result
7 points for a correct result but a correct score for only 1 team e.g. 1-0 prediction and 2-0 result
5 points for a correct result but no correct score for either team e.g. 1-0 prediction and 2-1 result
2 points for an incorrect result but a correct score for 1 team e.g. 1-0 prediction and 1-2 result

I have basic Excel knowledge but have no idea how to create a formula which will calculate the above and populate the correct scores for people in the spreadsheet.

Please can anyone help. I can provide the spreadsheet that I have created which is only missing this formula.

Many thanks in advance.

Here you go ...

It's the 2nd tab called week 1, it's the Pts column for each person that I need each formula. There is an existing formula there but it's for a completely different scoring system.

Also the I need the points automatically updated for each person on the Table tab which is the first tab.

Many thanks.

That formula has worked perfectly, thanks very much for that.

It's just the current week column H that I have a slight problem. I need it to automatically populate the current score. It does it for the majority of the lines but even though I've copied the formula to all lines, some of them show N/A.

Thats excellent. Thanks for your help on this DonkeyOte, much appreciated.

Originally Posted by DonkeyOte

No sample results with which to work but perhaps:

Please Login or Register  to view this content.

Check that first and then we can move on to the latter question.

Sorry, totally forgot something else ... in the Weekly Result tab of the spreadsheet there is a bonus column which may be populated with an 'x'. If this column is populated the points score will be doubled. Please can you add this into the formula.

Thanks

i've already got a poisson sheet.
but i want to put the results from it into another sheet automatically.
please help me know how i can do this.

thanks in advance.