Formula. Way over my head

[Hicksey]

I have the task of manually filling in the results from rugby matches in a
spread sheet for a works guessing game. Please could someone help with a
formula to do the following and show the final score in column L2. I just
would not know where to start. Thank you

Points system
Home win 1 point. Away win 2 points. Correct winning/losing score 3
points. Draw 5 points. Correct game score 8 points. Correct score draw 10
points. Correct margin 1 point.( the tolerence of +10 to -10 of the actual
score.)

Only the highest appropriate points to be allocated (e.g you cannot be
awarded 3 points for correct winning score plus 1 point for home win). The
only exception is when a bonus point for correct margin is awarded..( the
tolerence of +10 to -10 of the actual score.)

A2 Home Team
B2 Home score
C2 Away score
D2 Away Team
E2 Points
F2 Margin
G2 = F2+10
H2 = F2-10
I2 Home Team Guess
J2 Away Team Guess
K2 Margin
L2 Points

 

[Toppers]

If I have understood the "rules" ... try this:

in L2:

=IF(AND(B2=C2,B2=I2),10,IF(AND(B2=I2,C2=J2),8,IF(AND(B2=C2,B2<>I2),5,IF(OR(B2=I2,C2=J2),3,IF(C2>B2,2,1)))))

in M2 (bonus point)

=IF(ABS(B2-C2)-ABS(I2-J2)<=10,1,0)

In N2

=L2+M2

I wasn't sure about "Correct winning/losing score 3 points" ... did you
mean predicting either the score of winners OR score of the losers?

And you don't need columns F,G,H or K for the calculation but I have assumed
that they are present.

And what are the E points?

HTH

 

[paul]

i dont understand your margin thing say the score in a game is 10-3 the
actual margin is 7 what is your +10-10 on that
ps I hope you got the right score in the All Blacks vs The Wallabies last
night,looked a bit dodgy at half time.

 

[Hicksey]

Hi Toppers
Thank you for responding

I wasn't sure about "Correct winning/losing score 3 points" ... did you
mean predicting either the score of winners OR score of the losers?
Yes that is what I meant

And what are the E points?
These are the points won by the teams playing not the points won by the
guessers.

Would it help if I sent you the spreadsheet?
Regards
Paul

 

[Hicksey]

Hi Paul
Thank you for responding.

i dont understand your margin thing say the score in a game is 10-3 the
actual margin is 7 what is your +10-10 on that
17 to -3

I dont actually have to understand the scoring. I only get to fill in the
numbers. Luckey me!
Regards
Paul

 

[paul]

one more question.To get three points you have to have either the winners
score or losers score correct AND have picked the wining team correctly?

 

[Hicksey]

Points System
Home win 1. Away win 2. Correct winning/losing score 3. Draw 5. Correct
game score 8. Correct score draw 10.
Correct margin 1 bonus point Only the highest appropriate points to be
allocated (e.g you cannot be awarded 3 points for correct winning score plus
1 point for home win).The only exception is when a bonus point for correct
margin is awarded In the event of a draw at the end of the season the person
who predicted the final Glaws leaque points most accurately will be declared
the winner.
Fines System
£1 fine, if the margin in any member's forecast is more than ten points
either way of the actual match margin.
Examples of margins
The margin is calculated by deducting the away score from the home score
Glos 24 Worcs 21 (24-21) = margin of 3
Worcs 21 Glos 24 (21-24) = margin of-3

The margin thing is only there to put money in the pot for the eventual
winner.

 

[Toppers]

Send to toppers<at>johntopley.fsnet.co.uk and it would help if you could show
some example (expected) results to help checking of the formula.