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

"Hicksey" wrote:



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

"Toppers" wrote in message
...
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(A ND(B2=C2,B2<I2),5,IF(OR(B2=I2,C2=J2),3,IF(C2B2,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

"Hicksey" wrote:

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