Soccer pools league table

[gabtab]

I am looking to create a football pools league table for work and have come across a problem. I want to award 3 points if a person predicts the score exactly which is the easy part. I just enter the score in a cell and do an if statement =IF(B2=C2,3,0). My problem is I want to award 1 point if the result is predicted but not the exact score. So if I say predict 2-2 and the result is 3-3 or if I predict 1-2 and the score is 2-4 I want to award a point. Can someone please help me? Thanks

 

[circledchicken]

Hi,

To make it simpler you can split your score across 4 columns (i.e. B/C with the Home/Away predicted goals, and D/E with the actual goals, and then try this:

=IF(AND(B2=D2,C2=E2),3,IF(AND(B2<C2,D2<E2),1,IF(AND(B2>C2,D2>E2),1,IF(AND(B2=C2,D2=E2),1,0))))

The first if statement returns 3 points for an exact match, the second two return 1 point for a correct result (but not exact score) where one of the teams won, and the third for a correct result (but not exact score) where they drew.

 

[gabtab]

thanks a million for that. thats exactly what i was looking for

 

[barry houdini]

You could also use SIGN function to simplify that further, i.e.

=IF(AND(B2=D2,C2=E2),3,IF(SIGN(B2-C2)=SIGN(D2-E2),1,0))

 

[circledchicken]

Cool - SIGN - never used that before, thats great - thanks!