Football Prediction Formula needed: Fairly complicated

Hello to all on the site. This is my first post.

I run a small English football prediction game; and I want to automate the scoring to avoid human error and to be able to effectively upscale the event to more numbers at work without causing me too much extra admin. I have attached the template for how it scores currently as it is different to others in presentation and scoring that have been looked at on this site.

Scores are awarded as follows:-
3 points for a correct score – ie 3-0 to Arsenal and it finishes 3-0 to Arsenal.
2 points for a correct margin ie 3-0 to Arsenal and it finishes 4-1 to Arsenal.
1 point for a correct result ie 3-0 to Arsenal and it finishes 2-1 to Arsenal.
0 points for anything for else
Please note predicting draws cannot score 2 points. Either it is correctly scored ie 3 points, or any other draw will score 1 point. ie Arsenal and Liverpool to draw 1-1 will score 3 points for a pundit picking 1-1, but only 1 point for a pundit selecting either 0-0, 2-2 or any other scoring draw.

Is there anyway I can easily automate this with a formula?



In the attached template, columns D and E record the actual score. Columns G and H record the first pundits prediction. Column I should be able to record a score of 3,2,1 or 0 depending on correlation between G&H with D&E

I hope someone is able to point me in the right direction. I am ok with formulas but struggling here.

Thanks
Stuart

Thanks for quick response.

No. The most you can score is 3 points for getting the overall correct score exactly right.
The only scores that can be recorded in column I for Alex is 0, 1, 2 or 3

Thanks

Hi again. Many thanks for this.

There is something in there which doesnt quite work (for my scoring) but I dont think we are far from it. Where a 1-1 draw is recorded and a pundit picks 1-1 he/she should get 3points and does. However if they pick 2-2 they get 2 points. I want to discourage them from sitting on the fence and picking draws therefore need any draw predicted that gets the exact score wrong to get 1 point instead of 2. Is this easy to add?


Thanks

I managed to solve this today. Many thanks for the help with this

=IF(AND($E3="",$F3=""),0,IF(AND(H3=$E3,I3=$F3),3,IF(AND(H3-I3=$E3-$F3,$E3=$F3),1,IF(H3-I3=$E3-$F3,2,IF(OR(AND($E3>$F3,H3>I3),AND($F3>$E3,I3>H3)),1,IF(AND(H3=I3,H3<>$E3),0,0))))))