Football Prediction help!!

NicWelsh

Hello everyone,

I’m trying to write a formula that will automatically take a group of football scores, compare them with a set of predictions and then calculate points based off a set point system.

Example:

Liverpool 4 – 1 Wigan
Predicted: 3 – 1
Points: 3

I’ve been searching online for weeks and week and have been trying to import the findings onto my spreadsheet but I’m failing miserably!!

I’ve attached the excel spreadsheet below and would be forever grateful if one of you could help me with this.

On the spreadsheet I’ve added the predictions, the point system and my attempt at the formula.

Would be grateful for all replies.

Thanks.

Aswerty

Just to note that the 5 pointer is redundant because it is just the accumulation of getting 1, 1 and 3.

The following formula I think calculates the scores right (this example is for L3):

=(IF(D3=I3,1,0)+IF(F3=J3,1,0)+IF(AND((D3-F3)>0,(I3-J3)>0),3,0)+IF(AND(D3-F3<0,I3-J3<0),3,0)+IF(AND(D3-F3=0,I3-J3=0),3,0))*IF(NOT(ISBLANK(H3)),2,1)

Where the first if statement calculates home side goals the second if statement calculates away side goals and then the next 3 if statements calculate the result. Of the result if statements only one will be true since they calculate the goal difference which is either greater than zero, less than zero or equal to zero. Goal difference here being from the home teams point of view.

The banker is tricky since using a cells colour in a formula isn't possible according to a quick google. So my last if statement checks if the cell is blank so an X or anything could be used to signify a banker instead. The banker obviously just multiplies the score by 2 if the banker cell is not blank and by 1 if it is blank.

Aswerty

Actually that is wrong, thought the point system was accumulation based (e.g. a correct result and correct home goals would give 4).