Forum Discussion

Jannesu8's avatar
Jannesu8
Copper Contributor
Jun 13, 2023

Formula point system 40 team tournament

Anyone who can help me out with a formula using lots of 'IF CONDITIONS'?

 

I am currently making a sheat for a tournament with 40 teams who are divided over 8 groups (5 teams/group) and the point system is +3 if you win, +1 if you draw and +0 if you lose a match.

I have all the matches listed next to the group tables and scores are to be filled in next to the teams (so for example in column M a score is given in, in N team name 1, in O sits 'vs', in P sits team name 2 and in Q the score for team 2)

So the current formula i use to calculate the points for each team in their group is currently this:

=IF.CONDITIONS(M4<Q4;"+3";M4>Q4;"+0";M4=Q4;"+1")+IF.CONDITIONS(Q7<M7;"+3";Q7>M7;"+0";Q7=M7;"+1")+IF.CONDITIONS(M10>Q10;"+3";M10<Q10;"+0";M10=Q10;"+1")+IF.CONDITIONS(M13>Q13;"+3";M13<Q13;"+0";M13=Q13;"+1")

 

The 4 IF CONDITIONS are for the 4 matches 

The problem is that i want matches that arent played yet (and thus where scores are not filled in yet) to not count yet or be +0 so that we have a correct counting of points throughout the tournament (with the current formula it only is correct if all scores are filled in). Now all these matches that aren't played yet count as a draw (because the scores are equal with nothing in the cells) and count for +1 point.

Someone who can help me out with some advice? 🙂

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 14, 2023

    Jannesu8 

    To modify your formula to only count matches with filled-in scores, you can add an additional condition to check if the scores are not blank.

    Here's an updated version of your formula that includes this condition:

    =IF(NOT(ISBLANK(M4)), IF(M4<Q4, "+3", IF(M4>Q4, "+0", "+1")), 0) + IF(NOT(ISBLANK(Q7)), IF(Q7<M7, "+3", IF(Q7>M7, "+0", "+1")), 0) + IF(NOT(ISBLANK(M10)), IF(M10>Q10, "+3", IF(M10<Q10, "+0", "+1")), 0) + IF(NOT(ISBLANK(M13)), IF(M13>Q13, "+3", IF(M13<Q13, "+0", "+1")), 0)

    This formula checks if the scores in cells M4, Q7, M10, and M13 are not blank using the ISBLANK function. If a score is not blank, it applies the respective points calculation based on the comparison with the opposing team's score. If a score is blank, it returns 0.

    Make sure to adjust the cell references in the formula based on the actual location of your scores.

    I hope this helps!

Resources