Discussion in 'Windows & Other Software' started by Participant, 11 Jun 2010.

1. Participant

Location: Warwickshire

Hi all

We've got a World Cup results prediction spreadsheet, whereby you get one point for guessing correct number of home goals, one for correct number of away goals, and one for the correct result.

My prediction is in the first section, the actual results are in the second section, and the number of points scored I want to be automatically calculated when the game's result is entered.

For the correct number of goals for each team, I've just done =IF(AND and it works fine. However, I can't work out a formula that will check whether the result was guessed correctly, whether win lose or draw.

For example, if I had South Africa to draw 1-1 with Mexico and the score was actually 2-2, I'd get no points for number of goals but one point for guessing a draw. It's the last part I can't figure out.

Thanks!

Edit - to hopefully make it more obvious what I mean:

Last edited: 11 Jun 2010
2. Meeko

Location: Dunfermline

Use the following formula mate :

=IF(AND(B1-C1>0,E1-F1>0),1,IF(AND(B1-C1=0,E1-F1=0),1,IF(AND(B1-C1<0,E1-F1<0),1,0)))

B1 = predicted home team goals
C1 = predicted away team goals
E1 = actual home team goals
F1 = actual away team goals

This is simply deducting the away goals from the home goals for both prediction and actual results

A home win will always result in a +ve number, hence the first AND function saying >0
A draw will always result in a 0, hence the second AND function saying =0
An away win will always result in a -ve number, hence the last AND function saying <0

Last edited: 11 Jun 2010
3. Participant

Location: Warwickshire

Works perfectly, much appreciated.