Help with a Prediction League formula

[ladbroke]

Hi all,

I am running a Prediction League and was wondering if someone could help me with a macro that I could run that will help me.
I currently using formulas in various cells but I think (although I've hidden the columns) it looks very untidy still.

Basically, below is a screenshot of how I am wanting to redesign my layout.

In the first row I want the participants total points above their names.

Points are awarded as follows:

5 for correct score/result (ie Person B in game 1)
4 points for correct result with 1 team correct goals (ie Person A game 2 - home team scored 0, prediction 0)
3 points for correct result but not score line (ie Person A game 3)
1 point for correct goals scored for 1 team (ie Person B game 3)

I was once told that anything is possible with Excel.
I would need the formula to appreciate new games and new participants too if possible.

Many Thanks.

With the following example, Person A would have 7 points, whilst Person B would have 9.

excel help.png

I have already posted this in the VBA forum, in the hope/thinking that macro was to be the best option, but I seemingly have had no luck there, so maybe a formula is the best option. Please help.

[JohnTopley]

Please post a sample file showing expected results.

[ladbroke]

I have attached the sample sheet, but not sure what else I could add.

I've added a column to the right of each player, assuming a formula would need to be in place to work out points for individual games. I've added the points that each game would accumulate, just would like a formula to tell me the points rather than having to manually work it out as I currently have over 50 players and its hard to manually do it.

In Row A, where the total points is, I've also added a =sum formula as I know this would be the best way to add all the points together from each column.

Is this what you were referring to?