Learning Rank Formula For Sports Competition

Sports competitions need to calculate standings points in order to determine the ranking of winner. In the attached Excel sheet there is a formula for RANK.

And it is also using VLOOKUP formula to display each participant at each rank.

In the full or half competition system, standings calculated by points, which are:

Win gets 3 points

Draw gets 1 point

Lose gets 0 point

If any of participants get the same points, the participant with the highest total score entitled to a higher rank. For example on football or futsal competitions, Barcol and Ajaks get the same 14 points, then their score will   determines the winner. On football or futsal score is the number of goals scored.

Look at the picture above. To apply that rules, the Excel program has a factor that is 10000, seen on the right side of the big table. This figure is used to divide the score number, and the results is summed to point. So that there will be a slight difference between those clubs with the same points. So the program can determine the ranking for Barcol (14.0008) and Ajaks (14.0001).

Number 10000 is choosen as it is big enough so that if the score is divided by 10000 will not make a huge difference in points but different enough for the program to determine the difference in rankings. As seen on POINT column, Barcol gets 14 points. As Barcol has 8 score, which is divided by 10000, then added to 14 and become 14.0008. Decimal number 0.0008 is sufficient to let the program decides that Barcol has higher ranking than Ajaks.

If using a small number like 10 as the factor. When a participant get 15 score 15 like in badminton. So 15 divided by 10 is 1.5, it would rise point to 1.5, it certainly does not fit the rules of the competition.

That FACTOR calculation may become confusing for some participants. So that is why it has grey font format. That column can be hidden to avoid misunderstanding.

RANK in the leftmost column automatically calculate the ranking of any participant or club. It is using RANK formula to check data in FACTOR column.

Seen on Function Arguments, as shown above, the RANK formula is very simple. Number is a cell that contains a certain number of data (Ref), formula will rank that Number of that Ref data. In the above picture, Number is cell I8, while Ref is cells I8 to I15 or FACTOR column. $ character in Ref is used to lock formula to see only (refer only) to row 8 to row 15, when formula is copied. Order is rank order, if typed 0 then the highest Number will be the winner, or descendant. Conversely, if typed other than 0, then rank order will be ascendant, the lowest Number will be the winner.

The small table on the right displays the rank ordered from smallest number (winner) to the largest number (most to lose). This table uses VLOOKUP formula. The formula will look for the participant ranking according to the ranking that is written in the RANK column in the WINNER table.

Tables can be modified in order to accommodate more participants by Copy-Paste Insert Cells on the row that contains the formula. Blue colored cells are containing formula.