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.

Answer 2

When I find that I can't remember the built-in function I need or the formula is too cumbersome, I write my own user-defined function that will do the job. "If you want it done well, do it yourself"

In this case, the code turned out like this:

Option Explicit 
Function placeInSeq(aData As Variant, Optional asAscending As Integer) As Variant
Dim aUniqueVal() As Variant 
Dim i As Long, j As Long 
    If IsMissing(asAscending) Then asAscending=0
    For i = LBound(aData) To UBound(aData)
        For j = LBound(aData,2) To UBound(aData,2)
            collectUnique(aData(i,j), aUniqueVal)
        Next j
    Next i
    For i = LBound(aData) To UBound(aData)
        For j = LBound(aData,2) To UBound(aData,2)
            aData(i,j) = getIndexOfUnique(aData(i,j), aUniqueVal)
            If asAscending Then
                aData(i,j) = aData(i,j) + 1
            Else 
                aData(i,j) = UBound(aUniqueVal) - aData(i,j) +1
            EndIf 
        Next j
    Next i
    placeInSeq = aData
End Function

Sub collectUnique(key As Variant, aData As Variant)
Dim l&, r&, m&, N&, i&
    l = LBound(aData)
    r = UBound(aData) + 1
    N = r
    While (l < r)
        m = l + Int((r - l) / 2)
        If aData(m) < key Then l = m + 1 Else r = m
    Wend
    If r = N Then
        ReDim Preserve aData(0 To N)
        aData(N) = key
    ElseIf aData(r) = key Then
'       Nothing todo
    Else
        ReDim Preserve aData(0 To N)
        For i = N - 1 To r Step -1
            aData(i + 1) = aData(i)
        Next i
        aData(r) = key
    End If
End Sub

Function getIndexOfUnique(key As Variant, aData As Variant)
Dim l&, r&, m&, N&, i&
    l = LBound(aData)
    r = UBound(aData) + 1
    N = r
    While (l < r)
        m = l + Int((r - l) / 2)
        If aData(m) < key Then l = m + 1 Else r = m
    Wend
    If r = N Then
        getIndexOfUnique = -1
    ElseIf aData(r) = key Then
        getIndexOfUnique = r
    Else
        getIndexOfUnique = -1
    End If
End Function

Function placeInSeq() takes one required parameter - an array of values from a range of cells and returns an array of the same size. The second optional parameter has the same meaning as the Type parameter in the RANK() function - if 0 or not specified, then the values are ranked in descending order, otherwise - in ascending order.

Simply enter =PLACEINSEQ(BX10:BX23) into BY10 and complete the entry with Ctrl+Shift+Enter to show Calc that this is an array formula.

Yes, this is not an ideal solution. But it works and the result is as expected.