If Statement for football scoring help.

[lavster]

I have a spreadsheet to calculate the predictions of all the fixtures for a weekend. However the guy running it has decided to change the scoring and I cant figure out the formula. If someone can help i'd be greatful.
the scoring is as follows
100% match - result and score = 3 points
correctly predict a draw but get the score wrong = 2 points
correctly predict the winner but get the score wrong = 1 point
wrong score and winner = 0 points

at the moment it all works except the correctly predict a draw part.

here is the formula.

Please Login or Register  to view this content.

so columns E / F / G are the predictors guesses and columns B / C / D are the actual results.

The calculates 3 points for exactly right, 1 point for correct prediction and 0 for wrong so its just the 2 points for draw predicting i need help with.

Thanks guys

[Glenn Kennedy]

It might help if you told us what was meant to be in each of the columns B, C & D

[lavster]

Really sorry
Column B is the predicted result i.e D, HW or AW
Column C is predicted Home Score i.e 1,2,3
Column D is predicted Away Score i.e 1,2,3
Column E is the users guessed result i.e D, HW or AW
Column C is users guessed Home Score i.e 1,2,3
Column D is users guessed Away Score i.e 1,2,3

[lavster]

Here is the spreadsheet, the sheet i've been working on is GW1

Thanks again

[Glenn Kennedy]

I'm not sure if I've understood your request, but....

For E14, drag down:
=IF(AND($B2=E2,$C2=F2,$D2=G2),"100% Prediction",IF(AND(C2=D2,F2=G2),"Draw Predicted - Wrong Score",IF(AND($C2=F2,$D2=G2),"Score Predicted",IF(OR(ISBLANK(F2), ISBLANK(G2)),"No Entry",IF(SIGN($C2-$D2)=SIGN(F2-G2),"Game Result Predicted","No Match")))))

For F14, drag down:
=IF(E14="100% Prediction",3,IF(E14="No Match",0,IF(E14="Score Predicted",2,IF(E14="Game Result Predicted",1,IF(E14="Draw Predicted - Wrong Score",2)))))

[lavster]

works a treat! thank you very much!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.