Thanks:  0
Likes:  0

1. ## Soccer Formula

Hi all, Looking for some help with a formula that allocates 3 points for a predicting Home Win, Away Win or Draw, but also allocates 6 points should the person predict the correct score, in the instance below Susan predicted 3-3, which was correct, in turn gets a total of 9 points, 3 for predicting draw and 6 for correct score. Much obliged JJSB

 Name Matches Predicted Score Actual Score Points John Russia Saudi Arabia 1 0 3 3 0 Jim Russia Saudi Arabia 1 0 0 Peter Russia Saudi Arabia 2 0 0 Mary Russia Saudi Arabia 3 0 0 Susan Russia Saudi Arabia 3 3 0 Paul Russia Saudi Arabia 2 1 0 Mel Russia Saudi Arabia 3 2 0 Connor Russia Saudi Arabia 4 2 0

2. ## Re: Soccer Formula

Try:

=IF(AND(D2<>"",E2<>"",G2<>"",H2<>""),IF(OR(AND(D2 < E2,G2 < H2),AND(D2=E2,G2=H2),AND(D2>E2,G2>H2)),IF(AND(D2=G2,E2=H2),9,3),0),0)

3. ## Re: Soccer Formula

Hi Steve, No it's just populating each box with a zero, Thanks JJSB

4. ## Re: Soccer Formula

Yours has empty columns in your opening post! Anyway:

=IF(AND(D2<>"",E2<>"",F2<>"",G2<>""),IF(OR(AND(D2 < E2,F2 < G2),AND(D2=E2,F2=G2),AND(D2>E2,F2>G2)),IF(AND(D2=F2,E2=G2),9,3),0),0)

5. ## Re: Soccer Formula

Originally Posted by jayjaysb
Hi all, Looking for some help with a formula that allocates 3 points for a predicting Home Win, Away Win or Draw, but also allocates 6 points should the person predict the correct score, in the instance below Susan predicted 3-3, which was correct, in turn gets a total of 9 points, 3 for predicting draw and 6 for correct score. Much obliged JJSB

 Name Matches Predicted Score Actual Score Points John Russia Saudi Arabia 1 0 3 3 0 Jim Russia Saudi Arabia 1 0 0 Peter Russia Saudi Arabia 2 0 0 Mary Russia Saudi Arabia 3 0 0 Susan Russia Saudi Arabia 3 3 0 Paul Russia Saudi Arabia 2 1 0 Mel Russia Saudi Arabia 3 2 0 Connor Russia Saudi Arabia 4 2 0
As points column starts from H2 =IF(F2G2,IF(D2>E2,IF(AND(F2=D2,G2=E2),9,3),0)),IF(D2=E2,IF(AND(F2=D2,G2=E2),9,3),0))

I hope it will work.

6. ## Re: Soccer Formula

Here is my own solution, assuming your table's header row is 1 and your score columns are D, E, F and G:

= INT(AND(D2<>"",E2<>"",F2<>"",G2<>""))*(6*INT(AND(D2 = F2, E2 = G2)) + 3*INT(OR(AND(D2>E2,F2>G2),AND(D2

7. ## Re: Soccer Formula

Originally Posted by arpd123
Here is my own solution, assuming your table's header row is 1 and your score columns are D, E, F and G:

= INT(AND(D2<>"",E2<>"",F2<>"",G2<>""))*(6*INT(AND(D2 = F2, E2 = G2)) + 3*INT(OR(AND(D2>E2,F2>G2),AND(D2
This post (and the formula) got truncated somehow. When I clicked "reply with quote", it did not have the closing quote tag, either and I had to add it in manually. I don't have any editing privleges, unfortunately, as I assume I do not have a high enough post count.

8. ## Re: Soccer Formula

Steve, I am almost there, I input the formula into H2 and copied down, showed all zeros, but when I changed John's score to 3-3 it gave him 9 points (correctly) and then it changed Susan's to 9. Thanks JJSB

9. ## Re: Soccer Formula

Try this:

=IF(AND((F2=D2),(E2=G2)),9,IF(OR(AND(F2>G2,D2>E2),AND(G2>F2,E2>D2)),3,IF(AND((F2=G2),(E2=D2)),3,0)))

10. ## Re: Soccer Formula

Hi Final, that works, but only allocates 3 points for correct score and not 6, if you get me, thanks JJSB