Football Predictions - 3 for score, 1 for result

**Mike.G** · 08-14-2010, 09:13 AM

Hi all,

I was wondering whether someone with more superior excel skills could help me in the correct formula for a football predictions spreadsheet.

Each week we predict the scores on the premiership matches, if the correct score is predicted, 3 points should be awarded, if the correct result is predicted (i.e. predicted 3-1, but came out 2-1) then 1 point should be awarded, and if neither then 0 points.

Could someone help me out with this formula?

Many thanks,

Mike

EDIT: Also, we predict two goal scorers, which can only be used twice a season, if they score, an additional 2 points are awarded? Would it be easier to have separate columns for bonus points and then add these together with points for the score predictions?

**Boweringboy** · 08-14-2010, 04:53 PM

Hi, this is exactly what im looking for aswell but again iam also looking for the formulas so i can just enter the correct scores and all updates itself.

**BenRoylance** · 08-17-2010, 04:55 AM

This assumes the actual score is inputted in B2 and C2 and the predicted score is in B3 and C3

=IF(AND(B2=B3,C2=C3)=TRUE,3,IF(AND(B2>C2,B3>C3)=TRUE,1,IF(AND(B2<C2,B3<C3)=TRUE,1,IF(AND(B2=C2,B3=C3)=TRUE,1,0))))

Any problems, let me know

**BenRoylance** · 08-17-2010, 04:57 AM

As for your 2nd question, do you mean them two scorers can be only use twice a season, or the chance for the bonus point can only be used twice a season? If you clarify that a bit I'll be able to help as I'm creating a similar spreadsheet

**daddylonglegs** · 08-17-2010, 05:18 AM

Given that you have scores listed in a single cell you could add columns for Scores, e.g. "Mike's Scores" in column I, "Stuart's Scores" in column J etc. then you can use this formula in I4 copied across and down

=IF(COUNTA($B4,C4)=2,IF($B4=C4,3,IF(SIGN(RIGHT($B4)-LEFT($B4))=SIGN(RIGHT(C4)-LEFT(C4)),1,0)),"")

This formula only returns a value once both prediction and result have been entered....then you get 3,1 or 0 as required. Note that all scores should be entered as h-a, the above doesn't cater for double digit scores/predictions.....although you could alter it to do so if you wish....

See attached