Formula for Football (Soccer) Prediction League
 

I'm trying to write a formula that will automatically take a group of
football (soccer) scores, compare them with a set of predictions, and then
allocate points according to how close the predictions are to the actual
scores.

E.g.
Manchester United 3 - 0 Arsenal
Prediction: 4 - 1
Points: 1

I've found a formula online that works for most scores; the correct score
(e.g. 3 - 0), a correct win (e.g. 4 - 1), and in the case of a draw (e.g Man
Utd 1 - 1 Arsenal, and the prediction 2 -2), correct draw (but not the
correct score).

The points are as follows:
Correct draw (Prediction = result): 4
Correct win (Prediction = result): 3
Correct win, incorrect score (Winner correct, score not): 1
Correct draw, incorrect sco 1

The formula I have for all but the correct draw is:
=IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3 B3,C3D3),1,IF(AND(A3<B3,C3<D3),1,0)))))

Could someone help me work out how I can write a formula to add before the
above that will check the contents of four cells (the home score, away score,
home prediction, away prediction), and if the number in all 4 cells are the
same, allocate 4 points? I've tried using averages, but some combinations
would void this. I've also used the SUM function, but the same happens, as
with MULTIPLYING.

Something like =IF(A3:D3) are equal, 4,0

Below are a few examples of what I mean:

(Result) (Prediction) (Points)
A B C D E
3 3 3 3 4
2 1 3 1 1
1 0 1 0 3
3 3 2 2 1

Apologies in advance if I haven't explained this clearly enough.

Thanks

Formula for Football (Soccer) Prediction League
 

NB: The formula I have I found whilst Googling so thank you to whoever worked
it out originally, without it I'd still be staring blankly at the screen,
wondering where to start.

"genius7082" wrote:

I'm trying to write a formula that will automatically take a group of
football (soccer) scores, compare them with a set of predictions, and then
allocate points according to how close the predictions are to the actual
scores.

E.g.
Manchester United 3 - 0 Arsenal
Prediction: 4 - 1
Points: 1

I've found a formula online that works for most scores; the correct score
(e.g. 3 - 0), a correct win (e.g. 4 - 1), and in the case of a draw (e.g Man
Utd 1 - 1 Arsenal, and the prediction 2 -2), correct draw (but not the
correct score).

The points are as follows:
Correct draw (Prediction = result): 4
Correct win (Prediction = result): 3
Correct win, incorrect score (Winner correct, score not): 1
Correct draw, incorrect sco 1

The formula I have for all but the correct draw is:
=IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3 B3,C3D3),1,IF(AND(A3<B3,C3<D3),1,0)))))

Could someone help me work out how I can write a formula to add before the
above that will check the contents of four cells (the home score, away score,
home prediction, away prediction), and if the number in all 4 cells are the
same, allocate 4 points? I've tried using averages, but some combinations
would void this. I've also used the SUM function, but the same happens, as
with MULTIPLYING.

Something like =IF(A3:D3) are equal, 4,0

Below are a few examples of what I mean:

(Result) (Prediction) (Points)
A B C D E
3 3 3 3 4
2 1 3 1 1
1 0 1 0 3
3 3 2 2 1

Apologies in advance if I haven't explained this clearly enough.

Thanks