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

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

<tbody>
</tbody>

 

[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

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

<tbody>
</tbody>

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