IF formula problem

[DazRogers]

Hi,

Please can somebody help me.

I run a football (soccer) predictions competition at work and need some help with an IF formula to calculate our points system rather than manually adding them up.

There is a 3 point scoring system that we use. For a correctly predicted scoreline (eg predict 1-1 and the actual score is 1-1) you are awarded 5 points, for a correct result and one correct number of goals for one of the teams (eg predicted 2-0 home win but the actual score was 1-0 home win) you are awarded 4 points and for a correct result only with neither of the number of goals for either team predicted correctly (eg home win, draw or away win) you are awarded 3 points.

The cells in use are:

A3 Home team name
B3 Actual Home team goals scored
C3 Versus
D3 Actual Away team goals scored
E3 Away team name
G3 Predicted home team goals scored
H3 Versus
I3 Predicted away team goals scored

I look forward to your help.

Many Thanks
Darren

 

[Juglaz]

Give this a whirl....

=IF(OR(AND(B3>D3,F3>H3),AND(B3 less than D3,F3 less than H3)),IF(AND(B3=F3,D3=H3),5,IF(OR(B3=F3,D3=H3),4,3)),0)

<D3,F3<H3)),IF(AND(B3=F3,D3=H3),5,IF(OR(B3=F3,D3=H3),4,3)),0)[ html QUOTE]<>