Excel formula help for my World Cup spreadsheet please

Caporegime
Joined
13 May 2003
Posts
33,939
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:

dfgdfg.jpg
 
Last edited:
Associate
Joined
3 Apr 2003
Posts
442
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:
Back
Top Bottom