Nov 22 2021 06:15 AM
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 point if just one side matches (i.e. if the prediction was 3-0 and the score was 3-0 = 5 points, if the score was 2-1 = 3 points, if the score was 0-0 = 1 point). Any ideas?
Dec 06 2021 09:47 AM
Hi @mbaines
You may use the below logical formula to achieve your desired results:
=IF(AND(B5=E5,C5=F5),5,IF(OR((AND(B5>C5,E5>F5)),(AND(B5<C5,E5<F5))),3,1))
As per my understanding, if predictions for the winning team and scores exactly match, 5 points will be given. If predictions for the winning team matches but with a different score, 3 points will be given. Lastly, if predictions for the winning team do not match, only 1 point will be given.
Please refer to the attached file for your reference. Please let me know if it works for you.
Thanks
Tauqeer
Dec 07 2021 02:22 AM
Dec 07 2021 02:29 AM
Dec 07 2021 03:34 AM
@tauqeeracma Here is an image of the table - M5 should equal 0, as none of the scores match correctly.
Dec 10 2021 12:26 AM
Hi @mbaines
I have modified the formula and updated the attached revised file for you. Hope this will help you.
=IF(AND(C5=$K5,D5=$L5),5,IF(AND(C5<>$K5,D5<>$L5),0,IF(OR(AND(C5=$K5,$K5>$L5),AND(D5=$L5,$K5<$L5)),3,1)))
In case of different requirements, please specify it in the criteria section, I will adjust accordingly.
Thanks
Tauqeer
Dec 17 2021 01:39 AM
Dec 17 2021 03:08 AM
@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).
Dec 17 2021 03:21 AM
Dec 17 2021 05:01 AM
Nov 14 2022 06:54 AM
@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?
Nov 14 2022 08:05 AM
@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.