Formula for football league

[athullovzu]

Hello everyone..

i was making one excel sheet for football score prediction league. the rule is to to award 10 points for the exact correct result. ( ex Correct result 2-1, Predicted result 2-1)
then 8 points for correct match result and correct goal difference (ex Correct result 3-2, Predicted result 2-1, since the match result is same and the goal difference is also same, 8 points)

3 points for the correct match result ( ex Correct result 3-2, Predicted result 1-0)

0 for wrong result

please help me in this... i need excel formula for this...

if correct result is predicted in B2 AND C2, and Predicted result in B3 AND C3... What FORMULA'S CAN I USE??

[Richard Buttrey]

Hi,

Perhaps

Please Login or Register  to view this content.

[JBeaucaire]

Try this: =IF(B2&"-"&C2=B3&"-"&C3, 10, IF(OR(AND(B2>C2,B3>C3),AND(B2<C2,B3<C3)), IF(B2-C2=B3-C3, 8, 3), 0))

[athullovzu]

Thanks for the help to both Richard Buttrey and JBeaucaire ... but i found some error in both the formula's..

in the first one suggested by Buttrey,
=IF(AND(B2=B3,C2=C3),10,IF(AND(B2-C2=B3-C3,AND(B2>-C2,B3>=C3)),8,IF(AND(B2>C2,B3>C3),3,0)))

if the correct result is 1-2 and the predicted result is 0-1, according to your formula 0 points is being awarded instead of 8 points..

and in the second one suggested by JBeaucaire
=IF(B2&"-"&C2=B3&"-"&C3, 10, IF(OR(AND(B2>C2,B3>C3),AND(B2<C2,B3<C3)), IF(B2-C2=B3-C3, 8, 3), 0))

if the correct result is 2-2 and the predicted result is 3-3 since both predicted and actual results are draw, 8 points should be awarded.. but according to your formula 0 points has been awarded..

hope both can sort out this easily.. waiting for the reply..

[micope21]

Try this one

=IF(AND(B2=B3,C2=C3),10,IF(OR(AND(B2>C2,B3>C3,ABS(B2-C2)=ABS(B3-C3)),OR(AND(C2>B2,C3>B3,ABS(B2-C2)=ABS(B3-C3)),AND(B2=C2,B3=C3,ABS(B2-B3)=1))),8,IF(OR(AND(B2>C2,B3>C3),AND(C2>B2,C3>B2),AND(B2=C2,B3=C3,ABS(B2-B3)<>1)),3,0)))

[JBeaucaire]

Correction to mine:

=IF(B2&"-"&C2=B3&"-"&C3, 10, IF(OR(AND(B2>=C2,B3>=C3),AND(B2<=C2,B3<=C3)), IF(B2-C2=B3-C3, 8, 3), 0))

That seems to give 8pts for draws.

[JBeaucaire]

If that resolves your need, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[athullovzu]

thanks for the help micope21 and JBeaucaire .. but still its not getting right

with your formula micope21
=IF(AND(B2=B3,C2=C3),10,IF(OR(AND(B2>C2,B3>C3,ABS(B2-C2)=ABS(B3-C3)),OR(AND(C2>B2,C3>B3,ABS(B2-C2)=ABS(B3-C3)),AND(B2=C2,B3=C3,ABS(B2-B3)=1))),8,IF(OR(AND(B2>C2,B3>C3),AND(C2>B2,C3>B2),AND(B2=C2,B3=C3,ABS(B2-B3)<>1)),3,0)))

by using this formula , if the actual score is 3-3 and the predicted score is 1-1, 8 points should be awarded.. but by using your formula only 3 points have been given. (same situation id actual score is 1-1 and predicted score is 3-3)

with your formula JBeaucaire
=IF(B2&"-"&C2=B3&"-"&C3, 10, IF(OR(AND(B2>=C2,B3>=C3),AND(B2<=C2,B3<=C3)), IF(B2-C2=B3-C3, 8, 3), 0))

if the actual score is 2-0 and the predicted score is 1-1 or 2-2... 0 points should be awarded, but according to your formula 3 points have been awarded.

hope both can sort this out

[JBeaucaire]

Try this:

=IF(B2&"-"&C2=B3&"-"&C3, 10, IF(B2-C2=B3-C3, 8, IF(OR(AND(B2>C2,B3>C3),AND(B2<C2,B3<C3)), 3, 0)))

[athullovzu]

thanks a lot JBeaucaire ..that really worked..

but is there any possibility of adding one more condition?? its like there is 2 rules in this score predictor game. it is that you can place double bank and safe bet on 1 match each in every game week. if you place double bank you will get double the points.
for ex. actual score is 2-1 and the predicted score is also 2-1 (Double Bet), you will get 20 points (10*2)
actual score is 2-1 and the predicted score is 1-0 (Double Bet), you will get 16 points (8*2)
actual score is 2-1 and the predicted score is 3-1 (Double Bet), you will get 6 points (3*2)
actual score is 2-1 and the predicted score is 1-2 (Double Bet), you will get -10 points (since you placed double bet on completely opposite scoreline)
actual score is 2-2 and the predicted score is 2-1 (Double Bet) , you will get 0 points
actual score is 2-1 or 1-2 and the predicted score is 2-2 (Double Bet) , you will get 0 points

now the safe bet rule, if you place safe bet on a match, and the result goes completely opposite, you will get 5 points

for ex . actual score is 2-1 and the predicted score is 1-2 (safe bet), you will get 5 points
actual score is 2-2 and the predicted score is 1-2 (Safe Bet), you will get 0 points.

note- 5 points will be awarded only if prediction goes completely wrong. and it can't be placed on draws.

can anyone find a condition formula for this rule. i can add some symbols or something else to cell below or on the same cell to accommodate this condition.

i think this is bit difficult though, will be very very happy if some one can find a solution for this.

[micope21]

Hi

Reason I did with this formula is If predicted score 1-1. Near to 1-1 like 2-2 or 0-0 would be 8 point. If 3-3 or more from predicted score 1-1 would be 3 point reason 2 space away.
reason what you say from the 1st post,

If that not a case. Then use JBeaucaire formula.

[athullovzu]

thanks for the reply micope21

btw can you help me with this, its like there is 2 rules in this score predictor game. it is that you can place double bank and safe bet on 1 match each in every game week. if you place double bank you will get double the points.
for ex. actual score is 2-1 and the predicted score is also 2-1 (Double Bet), you will get 20 points (10*2)
actual score is 2-1 and the predicted score is 1-0 (Double Bet), you will get 16 points (8*2)
actual score is 2-1 and the predicted score is 3-1 (Double Bet), you will get 6 points (3*2)
actual score is 2-1 and the predicted score is 1-2 (Double Bet), you will get -10 points (since you placed double bet on completely opposite scoreline)
actual score is 2-2 and the predicted score is 2-1 (Double Bet) , you will get 0 points
actual score is 2-1 or 1-2 and the predicted score is 2-2 (Double Bet) , you will get 0 points

now the safe bet rule, if you place safe bet on a match, and the result goes completely opposite, you will get 5 points

for ex . actual score is 2-1 and the predicted score is 1-2 (safe bet), you will get 5 points
actual score is 2-2 and the predicted score is 1-2 (Safe Bet), you will get 0 points.

note- 5 points will be awarded only if prediction goes completely wrong. and it can't be placed on draws.

can anyone find a condition formula for this rule. i can add some symbols or something else to cell below or on the same cell to accommodate this condition.

i think this is bit difficult though, will be very very happy if some one can find a solution for this.

[JBeaucaire]

I'm going to let this thread continue without me. Sorry for the inconvenience, but "feature creep" like this gets my goat a little, so I'm going to let you folks continue on ad nauseum.

[micope21]

Hi

Please click on attachment.

Let me know that what you looking for?

[athullovzu]

FCPL SCORE PREDICTION 12-13.xlsxThat was so awesome micope21 u are one genius!!
but just tell me how to get that to sheet calculation.. suppose i want that that to cell C7, E7, G7 etc...

[micope21]

Hi

Please click on Attachment.

Good luck
Cheer

[athullovzu]

FCPL SCORE PREDICTION 12-13.xlsxFCPL SCORE PREDICTION 12-13.xlsx

that almost worked , but it still has some errors.. i have uploaded the excel file and i colored the mistakes with yellow.

for ex. In cell l19, since the actual score is 1-3 and the predicted score is 1-0( safe bet), 5 points should be awarded instead of 0

in cell o19, since the actual score is 0-0 and the predicted score is 3-0 (DB) , 0 POINTS should be awarded since the result is not completely wrong!!

note- no negative points will be given if the result turned out to be a draw on double bet

Whereas if you had predicted a draw and double banked on it and if either of the team wins, then no points are deducted.

[athullovzu]

and some players missed to predict the scores this week, and i have colored their names with yellow.. but they have been awarded 8 points on draws!! how can i stop that!!

[micope21]

Ok No problem

Sort it.

Have a look attachment workbook.

Any problem let me know.

Cheer