Football Prediction help!!

Lets assume the two scores are in cells B1 and C1
Lets assume the predictions are in cells B2 and C2

This formula works:
=IF(AND(B1=B2,C1=C2),5,IF(OR(AND(B1>C1,B2>C2),AND(C1>B1,C2>B2)),3,IF(AND(B2<>B1,C2<>C1),0,1)))
The first part of the formula is straightforward. Points = 5
The middle section is to determine the winning team. Points =3
The next section is when you don't pick either team correctly. Points = 0
What ever doesn't match any of the other conditions. Points = 1

Kirk

Because the data in your particular case are not in B1, B2, C1 and C2. Put this formula in cell L3:
=IF(AND($D3=I3,$F3=J3),5,IF(OR(AND($D3>$F3,I3>J3),AND($F3>$D3,J3>I3)),3,IF(AND($D3<>I3,$F3<>J3),0,1)))
Copy this formula from row 3 to row 12

The dollar sign ($) in front of the D and F is to make the column reference absolute so you can copy this formula to the Clive, Tommy, Stephen, Tom H and Tom S columns

Please mark the title as [SOLVED] if this solves your problem. Please check the star at the bottom of this message, if this was helpful

Kirk

Neither your formula nor mine addresses ties.

The formula I developed awards five points for a tie with the correct prediction on number of goals but zero points for ties with incorrect number of goals. For example, what should be awarded if you predict a tie 2-2 and the score is 1-1? Zero or some other amount?

Attached file updated with formulas


Need Help.xls

Change the formula in L3 to:
=IF(I3="","",IF(AND($D3=I3,$F3=J3),5,IF(OR(AND($D3>$F3,I3>J3),AND($F3>$D3,J3>I3),AND($F3=$D3,J3=I3)),3,IF(AND($D3<>I3,$F3<>J3),0,1))))
Copy formula down and to the other columns


Be sure to mark the subject as [SOLVED] if this solves your problem and click on the star below to indicate that I was able to help

Kirk

I don't understand what banker's points are. I don't know soccer lingo. If you can explain it I can do it.
How does one earn banker's points?

By the way, dis you notice, I added this to the front part of the equation =IF(I3="","",
Without it, the program "assumes" a zero in the cell and awards points accordingly

Put this in cell L3:
=IF(I3="","",IF(AND($D3=I3,$F3=J3),5,IF(OR(AND($D3>$F3,I3>J3),AND($F3>$D3,J3>I3),AND($F3=$D3,J3=I3)),3,IF(AND($D3<>I3,$F3<>J3),0,1)))*IF(M3<>"",2,1))
Copy down and copy to the other columns as appropriate


The is formula broken down this way:

=IF(I3="","", {Check to make sure there is some data, if not return a blank}

IF(AND($D3=I3,$F3=J3),5 {If both value are correct, award a 5}

IF(OR(AND($D3>$F3,I3>J3),AND($F3>$D3,J3>I3),AND($F3=$D3,J3=I3)),3 {Check to see if you predicted the winner or tie correctly. If so, award 3 points}

IF(AND($D3<>I3,$F3<>J3),0,1 {If you didn't get anything right, award zero or null points, else award one point}

*IF(M3<>"",2,1)) {Either multiply by two or one depending whether you put something in the Banker column}

Be sure to mark the subject as [SOLVED] if this solves your problem and click on the star below to indicate that I was able to help


Kirk








If you put anything in the banker column, the result is multiplied times two

If this helped change the title to [SOLVED]

Click on the star at the bottom left hand side of this message to increase my reputation points