# Football prediction formula

#### alsmonkeymagic

##### New Member
Hi

I am trying to write a formula that will work out scores for me based on a set of predictions against foot ball scores - I have set out the table below. For each correct result, so for example you predict a 1-0 win and its a 2-1 win you get 1 point. For each correct result and score, so for example you predict a 1-0 win and its a 1-0 win you get 3 points. Also each week you can choose 1 game that you want to double your points on, so if you get the result right 2 points and a correct score and result 6 points. I just cannot seem to get my head around it, any help, please!!! I am using Excel 2016.

 A B C D E F G H I Actual Result Prediction Double points Points Scored Bournemouth v Man Utd 0 3 0 3 Y 6 Arsenal v Wolves 2 1 3 1 1 Aston Villa v Liverpool 1 1 2 1 0

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### jasonb75

##### Well-known Member
Welcome to Mr Excel

See if this does what you need, I've assumed that the same rules apply to a draw (1 point for predicting 2-2 against a result of 1-1, 3 points for correct score).

=IF(COUNT(D2:G2)<4,"",IF(AND(D2=F2,E2=G2),3,IF(SIGN(D2-E2)=SIGN(F2-G2),1))*IF(H2="Y",2,1))

There are 4 IF's in the formula, the 1st checks that the scores for both result and prediction have been entered to prevent points being awarded incorrectly in the event of a draw predicted on a game not yet played.
The 2nd IF checks if the predicted score is an exact match for the result. The 3rd checks checks the actual winner is the same as the predicted winner when the score is different. The last one doubles the points where applicable.

#### alsmonkeymagic

##### New Member
Thank you for the welcome and the reply, I have used the formula but I am only getting a blank cell. I assume it would not matter what the cell format is?

Welcome to Mr Excel

See if this does what you need, I've assumed that the same rules apply to a draw (1 point for predicting 2-2 against a result of 1-1, 3 points for correct score).

=IF(COUNT(D2:G2)<4,"",IF(AND(D2=F2,E2=G2),3,IF(SIGN(D2-E2)=SIGN(F2-G2),1))*IF(H2="Y",2,1))

There are 4 IF's in the formula, the 1st checks that the scores for both result and prediction have been entered to prevent points being awarded incorrectly in the event of a draw predicted on a game not yet played.
The 2nd IF checks if the predicted score is an exact match for the result. The 3rd checks checks the actual winner is the same as the predicted winner when the score is different. The last one doubles the points where applicable.

#### jasonb75

##### Well-known Member
The format does make a difference, I noticed in your example that the scores were aligned to the left which I thought was just the way the forum software had formatted the copy and paste of your table. However, it also suggests that the numbers in those cells are formatted as text, in which case the first part of the formula would not count them correctly.

It should work by removing the 1st IF, however this could potentially mean that incorrect points are awarded with a predicted draw.

=IF(AND(D2=F2,E2=G2),3,IF(SIGN(D2-E2)=SIGN(F2-G2),1))*IF(H2="Y",2,1)

I would suggest not using a text format for cells that contain numbers, it is is rarely necessary and can break even the most simple formulas.

Replies
3
Views
83
Replies
0
Views
155
Replies
2
Views
1K
Replies
2
Views
3K
Replies
1
Views
52

1,129,667
Messages
5,637,669
Members
416,979
Latest member
juliegeorge792

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back