IF, AND, BUT and ON-THE-OTHER-HAND formula :)

[JockW]

I'm unsure if this is do-able, but I know some of you will relish the
challenge....
I'm creating a spreadsheet showing points gained from predicting soccer
scores.
For the correct result (ie home win, away win or draw) - 3 points,
For getting home team score correct - 1 point,
For getting away team score correct - 1 point.
Therefore, the max possible points per game would be 5.
So, for instance:

Home Away
3 1

john's prediction
2 1 4 points - 3 for the result and 1 for the correct
away score
Jane's prediction
1 0 3 points for the correct result
Jack's prediction
0 3 0 points

I think there may be too many variables but any/all help greatly appreciated.

 

[Toppers]

=((A2>B2)*(C2>D2))*3+(A2=C2)+(B2=D2)

A2=Actual Home
B2=Actual way

C2=Predicted Home
D2=Predicted Away

HTH

 

[Elkar]

There's probably a more elegant solution out there, but this should work:

=IF(A2=$A$1,1,0)+IF(B2=$B$1,1,0)+IF(OR(AND(A2>B2,$A$1>$B$1),AND(A2<B2,$A$2<$B$2),AND(A2=B2,$A$1=$B$1)),3)

This assumes your actual score is in A1 and B1, with your 1st guess scores
in A2 and B2.

HTH,
Elkar

 

[David Biddulph]

If the actual result is in cells B2 and C2 (home & away), and the
predictions from your contestants are in the same columns in rows 4 onwards,
then in D4 you can put the formula
=3*(SIGN(B4-C4)=SIGN(B$2-C$2))+(B4=B$2)+(C4=C$2), and copy down as required.

 

[Toppers]

For all correct results:

=(OR((A2>B2)*(C2>D2),(B2>A2)*(D2>C2),(A2=B2)*(C2=D2))*3+(A2=C2)+(B2=D2))

 

[Elkar]

Oops, just noticed a typo. Try this instead:

=IF(A2=$A$1,1,0)+IF(B2=$B$1,1,0)+IF(OR(AND(A2>B2,$A$1>$B$1),AND(A2<B2,$A$1<$B$1),AND(A2=B2,$A$1=$B$1)),3)

HTH,
Elkar

 

[T. Valko]

See this screencap:

http://img119.imageshack.us/img119/5356/scoresdb9.jpg

 

[David Biddulph]

This doesn't give the 3 points for correct predicttion of a draw?

 

[T. Valko]

In the formula, you can do without the 1* and still get the correct result.

 

[JockW]

Wow!
I'm overwhelmed guys. Many thanks for all your input on this, it's very much
appreciated.

 

[JockW]

I don't get it, but it works....

thanks.

If the actual result is in cells B2 and C2 (home & away), and the
predictions from your contestants are in the same columns in rows 4 onwards,
then in D4 you can put the formula
=3*(SIGN(B4-C4)=SIGN(B$2-C$2))+(B4=B$2)+(C4=C$2), and copy down as required.

 

[David Biddulph]

Easy. When used in a calculation, a logical value of TRUE will count as 1,
and a FALSE counts as zero.

 

[JockW]

Thanks for the update
--

In the formula, you can do without the 1* and still get the correct result.

 

[Trevor Shuttleworth]

=IF(OR(AND($A$2>$B$2,A4>B4),AND($A$2<$B$2,A4<B4),AND($A$2=$B$2,A4=B4)),3,0)+--($A$2=A4)+--($B$2=B4)

Assume Actual Home and Away scores are an A2 and B2 and predicted scores are
in A4 and B4 down

Regards

Trevor

 

[T. Valko]

Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks for the update

 

[Jock]

Thanks Trevor, brilliant up to a point.
A4 and B4 can be absolutely anything (I.E.Z and Z) and the formula gives 3
points if the actual score was a draw. I discovered this when one entry had
no score entered for a particular match (which was a draw) and the formula
returned 3 for a game with no prediction.
Is there a work around?

 

[Trevor Shuttleworth]

You said there'd be numbers ...

OK, users can't be trusted.

Try:

=IF(OR(NOT(ISNUMBER(A10)),NOT(ISNUMBER(B10))),"null
point",IF(OR(AND($A$2>$B$2,A10>B10),AND($A$2<$B$2,A10<B10),AND($A$2=$B$2,A10=B10)),3,0)+--($A$2=A10)+--($B$2=B10))

Change the text to meet your requirements ... "cheat" maybe ?

Did the other solutions have similar outcomes when you forecast rubbish ?

Regards

Trevor

 

[Harlan Grove]

OK, users can't be trusted.

The fundamental rule of application programming from which all good UI
coding flows.

Try:

=IF(OR(NOT(ISNUMBER(A10)),NOT(ISNUMBER(B10))),"null point",
IF(OR(AND($A$2>$B$2,A10>B10),AND($A$2<$B$2,A10<B10),
AND($A$2=$B$2,A10=B10)),3,0)+--($A$2=A10)+--($B$2=B10))

+-- bits unnecessary, + alone would be sufficient.

However, too long.

=IF(COUNT(A$2:B$2,A10:B10)=4,3*(SIGN(A$2-B$2)=SIGN(A10-B10))
+(A10=A$2)+(B10=B$2),"TBD")

 

[JockW]

Hi Trevor,
Thanks for getting back. All solutions supplied (except your modified one
and Harlans - neither tested) had the exactly the same outcome if non numeric
characters (and even if cells were left blank) were used in that, because the
formula took <blank> and <blank> as being the same, it awarded 3 points. If
no actual score was entered, then 5 points were clocked up as all criteria
were met.
I tried to get around the issue by putting =IF( at the beginning so it
ignored blank cells. This worked up to a point. I finally ran with a slightly
modified version of David Biddulphs proposal:
=(IF(AND(F7="",G7=""),"",3*(SIGN(F7-G7)=SIGN($D7-$E7))+(F7=$D7)+(G7=$E7)))
Thank you all for your time and effort - I just need to predict the corect
scores now so I can retire.....lol