Forum Discussion
Solved
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 See attached!
Thank you so much! I can see the formula works on the attached image but for some reason it is giving me incorrect data in the results column - it is giving me 1 when it should be 0. I have tried typing it out and copy/pasting it with the appropriate cells but it still doesn't seem to work for me.
Can you please attach the file (or some sample data) in which you are pasting the formula and getting incorrect data. I will try to fix it directly in your file.
Thanks
Tauqeer
Thanks
Tauqeer
tauqeeracma Here is an image of the table - M5 should equal 0, as none of the scores match correctly.
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
- That isn't quite right still, it is giving the wrong result. It would need to be 3 points for the correct result (i.e. home victory, draw, away victory) plus an additional 1 point if the score is correct. I don't really know how to word that in a format that would work as a criteria! So, 5 points for an exact score, 3 if just the result matches, 1 if just one score matches and 0 if it completely wrong.
I massively appreciate all your help, thank you very much!