Forum Discussion

tauqeeracma's avatar
tauqeeracma
Iron Contributor
Dec 06, 2021

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

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

Resources