Forum Discussion
Excel Points Formula
mbaines See attached!
mbaines Perhaps the attached file contains what you need. I went back to your very first post which was quite clear.
An exact match of the end result gives 5 points
Just guessing the winning team gives 3 points
In case neither of the first two happened, 1 point is awarded if a score at either side was guessed correctly, otherwise 0 points.
So the score is either 5, 3, 1, or 0.
But I became uncertain reading your last message "3 points for the correct result plus 1 point for the correct score". Say the result is 5-0 and the guess is 3-0. How many points would you award? 3 or 4?
If 4 (3 for the correct winner plus 1 for the fact the the zeroes matched), then the formula need to be altered. A guess of 5-0 would result in 5 points following the same logic (3+1+1).
AmyWilson Oh, this is an old one, but I believe that when you change the formula in L5 to:
=(SIGN(B5-C5)=SIGN($J5-$K5))*3+AND(B5=$J5,C5=$K5)*2
it does what you described. 3 points for the correct outcome (win or loose) plus two extra for the correct score.
Riny_van_Eekelen This is brilliant, how do I amend this to just show 5 points for correct score; 3 points for correct result and 0 points for anything else?
- I have inputted it and that seems perfect! Thank you both very much
mbaines See attached!
