Libreoffice Calc: How to rank numbers without skipping ranks?

Question

I have a table with Points/Game in one column and I want to rank the teams by relative place in the second column, but the RANK function does not do what I was hoping and adds all tied places to the count of the rank given to the next rank.

This is my table in which I have Points/Game in the first column (BX10:BX23).

In the second column, I would like each team ranked by their relative position within the list, with tying Pts/Game given the same ranks. This is working fine using =RANK($BX10, $BX$10:$BX$23), but this increments the next place by 1 for each item in the previous rank.

In other words, the first 5 teams all have the same Pts/Game, so all are correctly ranked at 1. However, the next team down gets rank 6 instead of 2, and so on.

I would like to have it simply show "these teams are tied at rank 1; the next ones down are tied at rank 2. . ." etc. So I want to make it so the "Place" column reads, from top to bottom: "1, 1, 1, 1, 1, 2, 3, 4, 4, 4, 5, 5, 5, 6" instead of what RANK gives, which is "1, 1, 1, 1, 1, 6, 7, 8, 8, 8, 11, 11, 11, 14".

Is there a reasonably simple way to do this?

Answer 1

I would have solved it with a formula inside the cells:

• put the value 1 into the cell BY10
• put the formula =if(BX11=BX10, BY10, BY10 + 1) into cell BY11
• drag BY11 down as far as you need.

This takes the rank of the previous row if the values are the same and adds one to the rank of the previous row otherwise.

To make the formula work, it is important that the values to rank after are already sorted as needed.