Soccer Formula

[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

[TABLE="width: 604"]
<tbody>[TR]
[TD]Name[/TD]
[TD="colspan: 2"]Matches[/TD]
[TD="colspan: 2"]Predicted Score[/TD]
[TD][/TD]
[TD="colspan: 2"]Actual Score[/TD]
[TD][/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Mel[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Connor[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

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)<e2,g2<h2),and(d2=e2,g2=h2),and(d2></e2,g2<h2),and(d2=e2,g2=h2),and(d2>

 

[jayjaysb]

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

 

[steve the fish]

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)

 

[Legacy 143009]

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

[TABLE="width: 604"]
<tbody>[TR]
[TD]Name[/TD]
[TD="colspan: 2"]Matches[/TD]
[TD="colspan: 2"]Predicted Score[/TD]
[TD][/TD]
[TD="colspan: 2"]Actual Score[/TD]
[TD][/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Mel[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Connor[/TD]
[TD]Russia[/TD]
[TD]Saudi Arabia[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

As points column starts from H2 =IF(F2<G2,IF(D2<E2,IF(AND(F2=D2,G2=E2),9,3),0),IF(F2>G2,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.

 

[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<E2,F2<G2),AND(D2=E2,F2=G2))))

 

[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

<e2,f2<g2),and(d2=e2,f2=g2))))[ quote]
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.</e2,f2<g2),and(d2=e2,f2=g2))))[>

 

[jayjaysb]

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

 

[Finalfight40]

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)))

 

[jayjaysb]

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