Forum Discussion
mbaines
Nov 22, 2021Copper Contributor
Excel Points Formula
Hi! I want to see if there's a formula which would work out a points score based off a score prediction matching the actual result: 5 points if it matches exactly, 3 if it matches the result or 1...
mbaines
Dec 17, 2021Copper Contributor
Hello! Your last point is correct: maximum 5 points for an exact correct score, or alternatively 3 points for the correct result (but no matching scores) plus 1 for any correct scores. I.e. as you said, a Prediction of 4-0 and a result of 3-0 would give 4 points (3 correct outcome plus 1 correct score)
Riny_van_Eekelen
Dec 17, 2021Platinum Contributor
mbaines See attached!
- Riny_van_EekelenNov 14, 2022Platinum Contributor
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.
- AmyWilsonNov 14, 2022Copper Contributor
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?
- mbainesDec 17, 2021Copper ContributorI have inputted it and that seems perfect! Thank you both very much