SOLVED

Excel Points Formula

Occasional Contributor

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?

12 Replies

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

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

@tauqeeracma Here is an image of the table - M5 should equal 0, as none of the scores match correctly.

 

mbaines_1-1638876815548.png

 

 

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)))

tauqeeracma_0-1639124544008.png

 

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!

@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).

 

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)
best response confirmed by Hans Vogelaar (MVP)
Solution

@mbaines See attached!

I have inputted it and that seems perfect! Thank you both very much

@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?

@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.