Discussion Re: Excel Points Formula in Excel
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041040#M126418
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.<BR /><BR />I massively appreciate all your help, thank you very much!Fri, 17 Dec 2021 09:39:29 GMTmbaines2021-12-17T09:39:29ZExcel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/2993016#M123247
<P>Hi!</P><P> </P><P>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?</P>Mon, 22 Nov 2021 14:15:28 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/2993016#M123247mbaines2021-11-22T14:15:28ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3031750#M124975
<P>Hi <LI-USER uid="1226236"></LI-USER> </P><P> </P><P>You may use the below logical formula to achieve your desired results:</P><LI-CODE lang="applescript">=IF(AND(B5=E5,C5=F5),5,IF(OR((AND(B5>C5,E5>F5)),(AND(B5<C5,E5<F5))),3,1))</LI-CODE><P>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.</P><P> </P><P>Please refer to the attached file for your reference. Please let me know if it works for you.</P><P> </P><P>Thanks</P><P>Tauqeer</P>Mon, 06 Dec 2021 17:47:26 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3031750#M124975tauqeeracma2021-12-06T17:47:26ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3032550#M125071
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.Tue, 07 Dec 2021 10:22:17 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3032550#M125071mbaines2021-12-07T10:22:17ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3032554#M125073
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.<BR /><BR />Thanks<BR />TauqeerTue, 07 Dec 2021 10:29:38 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3032554#M125073tauqeeracma2021-12-07T10:29:38ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3032598#M125080
<P><LI-USER uid="239215"></LI-USER> Here is an image of the table - M5 should equal 0, as none of the scores match correctly.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="mbaines_1-1638876815548.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/332191i2AA5D6499DC554FE/image-size/medium?v=v2&px=400" role="button" title="mbaines_1-1638876815548.png" alt="mbaines_1-1638876815548.png" /></span></P><P> </P><P> </P>Tue, 07 Dec 2021 11:34:11 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3032598#M125080mbaines2021-12-07T11:34:11ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3035607#M125495
<P>Hi <LI-USER uid="1226236"></LI-USER> </P><P> </P><P>I have modified the formula and updated the attached revised file for you. Hope this will help you.</P><LI-CODE lang="applescript">=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)))</LI-CODE><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="tauqeeracma_0-1639124544008.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/333101iE184BD8DD767102D/image-size/medium?v=v2&px=400" role="button" title="tauqeeracma_0-1639124544008.png" alt="tauqeeracma_0-1639124544008.png" /></span></P><P> </P><P>In case of different requirements, please specify it in the criteria section, I will adjust accordingly.</P><P> </P><P>Thanks</P><P>Tauqeer</P>Fri, 10 Dec 2021 08:26:43 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3035607#M125495tauqeeracma2021-12-10T08:26:43ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041040#M126418
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.<BR /><BR />I massively appreciate all your help, thank you very much!Fri, 17 Dec 2021 09:39:29 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041040#M126418mbaines2021-12-17T09:39:29ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041068#M126421
<P><LI-USER uid="1226236"></LI-USER> Perhaps the attached file contains what you need. I went back to your very first post which was quite clear.</P><P> </P><P><EM>An exact match of the end result gives 5 points</EM></P><P><EM>Just guessing the winning team gives 3 points</EM></P><P><EM>In case neither of the first two happened, 1 point is awarded if a score at either side was guessed correctly, otherwise 0 points.</EM></P><P> </P><P>So the score is either 5, 3, 1, or 0.</P><P> </P><P>But I became uncertain reading your last message <EM>"3 points for the correct result plus 1 point for the correct score"</EM>. Say the result is 5-0 and the guess is 3-0. How many points would you award? 3 or 4?</P><P> </P><P>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).</P><P> </P>Fri, 17 Dec 2021 11:08:51 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041068#M126421Riny_van_Eekelen2021-12-17T11:08:51ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041070#M126422
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)Fri, 17 Dec 2021 11:21:07 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041070#M126422mbaines2021-12-17T11:21:07ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041082#M126426
<P><LI-USER uid="1226236"></LI-USER> See attached!</P>Fri, 17 Dec 2021 11:53:10 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041082#M126426Riny_van_Eekelen2021-12-17T11:53:10ZRe: Excel Points Formula
https://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041127#M126438
I have inputted it and that seems perfect! Thank you both very muchFri, 17 Dec 2021 13:01:29 GMThttps://techcommunity.microsoft.com/t5/excel/excel-points-formula/m-p/3041127#M126438mbaines2021-12-17T13:01:29Z