Football (soccer) predictions

[Mattcraig]

Hi,

I'd like to organize a prediction game for the group stage of the upcoming world cup.

I'd like to have a formula that would automatically attribute points. I don't want to do it manually as it would take me forever.
Could you help me create a formula in excel that would give:

1. 4 points for the exact score,
2. 2 points if the winner is found with the correct goal difference (ex 3-1 predicted vs 2-0 actual). Tie games would not result in 2 points.
3. 1 point if the winner or tie game was predicted.
4. 0 point for a bad prediction.

Thank you!!

Matt

[dominicb]

Good afternoon Mattcraig

Welcome to the forum.

Would you please upload what you already have - any formula you are given is going to rely on what cells contain what data.
To upload a file, click on "Go Advanced" below your reply window and go into Manage Attachments.

DominicB

[jason.b75]

On the assumption of

A3 Predicted home score
B3 Predicted away score
C3 Actual home score
D3 Actual away score

=IF(COUNT(A3:D3)=4,IF(AND(A3=C3,B3=D3),4,IF(AND((A3-B3)=(C3-D3),A3<>B3),2,IF(OR(AND(A3=B3,C3=D3),AND(A3>B3,C3>D3)),1,0))),"")

I'm not a fan of long nested IF formulas, but given the criteria, I think it might be the easiest way to do this.

[Mattcraig]

Hi DominicB,

thanks for the tip!

Matthias

[jason.b75]

Revised formula based on your sample file.

=IF(COUNT(D2:E2,Results!E2:F2)=4,IF(AND(Results!E2=D2,Results!F2=E2),4,IF(AND((Results!E2-Results!F2)=(D2-E2),Results!E2<>Results!F2),2,IF(OR(AND(Results!E2=Results!F2,D2=E2),AND(Results!E2>Results!F2,D2>E2)),1,0))),"")

File reattached with the formula in your commented cell. Note that the cell will stay blank until predicted score and actual result have been entered.

Hope this helps.

[Mattcraig]

Hi Jason,

it works perfectly, thanks so much!!

Matt

[Bbobb78]

Hi guys (new user here).

I am trying to set up something similar to the original post and found the formula most useful - thank you.

However I think there may be an error - if an away team wins and the prediction is an away win (but with a 2 or greater goal difference error) then 0 points are returned rather than 1 point.
Example:
Actual score 0 - 1
Predicted score 0 - 3

Zero points returned.

Thanks in advance for any help!

[dominicb]

Good morning Bbobb78

Welcome to the forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[jason.b75]

I disagree with you, dominincb.

Although presented as a question, Bbobb78 has highlighted a problem with the solution provided to the original question that had previously gone unnoticed, therefore providing a useful contribution to the thread.

For the benefit of the OP, or anyone else who may be looking at this thread for information, here is an updated formula

=IF(COUNT(D2:E2,Results!E2:F2)=4,IF(AND(Results!E2=D2,Results!F2=E2),4,IF(AND((Results!E2-Results!F2)=(D2-E2),Results!E2 <> Results!F2),2,IF(OR(AND(Results!E2=Results!F2,D2=E2),AND(Results!E2 > Results!F2,D2 > E2),AND(Results!E2 < Results!F2,D2 < E2)),1,0))),"")

[Bbobb78]

Hi dominicb, thanks for the welcome. Apologies for the confusion caused by my post, I will start a new thread and provide a link as you suggest in the future.

Hi jason.b75 - Thanks for the updated formula, works a treat....greatly appreciated (saved my hair from being torn out!!)