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

# Re: Excel Points Formula

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

# Re: Excel Points Formula

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.

# Re: Excel Points Formula

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

# Re: Excel Points Formula

@tauqeeracma Here is an image of the table - M5 should equal 0, as none of the scores match correctly. # Re: Excel Points Formula

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

# Re: Excel Points Formula

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!

# Re: Excel Points Formula

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

# Re: Excel Points Formula

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

# Re: Excel Points Formula

@mbaines See attached!

# Re: Excel Points Formula

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

# Re: Excel Points Formula

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

# Re: Excel Points Formula

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