Excel Points Formula

[mbaines]

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?

[#2]

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

[#3]

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.

[#4]

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

[#5]

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

[#6]

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

[#7]

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!

[#8]

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

[#9]

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)

[#10 · Accepted solution]

@mbaines See attached!

[#11]

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

[#12]

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

[#13]

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