Forum Discussion

tauqeeracma's avatar
tauqeeracma
Iron Contributor
Dec 07, 2021
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
mbaines's avatar
mbaines
Copper Contributor
Dec 07, 2021

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

 

 

 

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Dec 10, 2021

    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

    • mbaines's avatar
      mbaines
      Copper Contributor
      Dec 17, 2021
      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!
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Dec 17, 2021

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

         

Resources