G
genius7082
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 score: 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,C3>D3),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(A33) 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
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 score: 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,C3>D3),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(A33) 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