Football prediction formula

alsmonkeymagic

New Member
Joined
Nov 2, 2019
Messages
2
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.

ABCDEFGHI
Actual ResultPredictionDouble pointsPoints Scored
BournemouthvMan Utd0303Y6
ArsenalvWolves21311
Aston VillavLiverpool11210

<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
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
Welcome to Mr Excel :biggrin:

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
Joined
Nov 2, 2019
Messages
2
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 :biggrin:

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
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top