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]

Another one:

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

If the actual score is 3-3 shouldnt Susan be 9?

 

[jayjaysb]

Yes Steve, 3 for the win and 6 bonus for predicting correct score, I still cant get it to work, Thanks JJSB

 

[Finalfight40]

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

Ah did you want 6 points even if lets say Russia win 2-1 but someone predicted 2-1 to Saudi Arabia?

 

[Finalfight40]

If so then maybe:

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

Just read post below:

ignore this formula

 

[jayjaysb]

Ah did you want 6 points even if lets say Russia win 2-1 but someone predicted 2-1 to Saudi Arabia?

No Final, If Russia wins 2-1 and someone predicts 2-1 then they get 3 points because Russia win, they will then get an additional 6 points for correctly predicting 2-1, noone will get any points if Saudi beat Russia 2-1 as noone predicted it, I might not be explaining myself too well. Thanks JJSB

 

[steve the fish]

Are you only entering the actual score in cells F2 and G2 and not copying it down??

 

[steve the fish]

If so its:

=IF(AND(D2<>"",E2<>"",$F$2<>"",$G$2<>""),IF(AND(D2=$F$2,E2=$G$2),9,IF(SIGN(D2-E2)=SIGN($F$2-$G$2),3,0)),0)

but will be a right pain for you to manage for each game as would need a formula change. Think id either drag the result down for each row or create a lookup table of results.

 

[jayjaysb]

Are you only entering the actual score in cells F2 and G2 and not copying it down??

Steve, I think I might be confusing you all. In cell f2 and g2 thats where the REAL correct score sits. H2 is where I am putting the formula and then dragging it down to H9. Apologies if I am annoying you all. Thanks JJSB

 

[jayjaysb]

if so its:

=if(and(d2<>"",e2<>"",$f$2<>"",$g$2<>""),if(and(d2=$f$2,e2=$g$2),9,if(sign(d2-e2)=sign($f$2-$g$2),3,0)),0)

but will be a right pain for you to manage for each game as would need a formula change. Think id either drag the result down for each row or create a lookup table of results.

it works!!!!!!! Thanks a million. JJSB

 

[krykm]

Another approach;

=(SIGN($F$2-$G$2)=SIGN(D2-E2))*3+(D2&E2=$F$2&$G$2)*6