excel-lambda-RANK.DENSE – An Excel lambda function for DENSE_RANK from SQL

The gist for this lambda can be found here.

The goal

There are many reasons for ranking data. Excel provides a few native functions to do so. The two main ones are RANK.AVG and RANK.EQ.

Each function will return the integer rank of a number within a list of numbers, sorted either descending or ascending order.

To understand how each function works, take a look at this simple example:

You can see that when RANK.AVG encounters two identical numbers (the population in millions of Iran and Turkey), it takes the two ranks they would otherwise receive – 17 and 18 – and takes the average of them. Hence the result is 17.5. If there were three countries with the same population, then we would get the average of 17, 18 and 19 – 18.  The rank given after the averaged ranks is 19. In this example, 18 is skipped.

RANK.EQ gives each identical population the rank of the first – 17. The rank after these identical ranks is again 19 – 18 is skipped. 

Most SQL implementations offer an option known as DENSE_RANK. The simple explanation of DENSE_RANK is that in the example above, it would behave like RANK.EQ, except the following rank would be 18. 

So, the goal here is to create RANK.DENSE as an Excel lambda function for DENSE_RANK from SQL.

A solution

Here’s a lambda called RANK.DENSE. I have named the parameters in the same way as the two functions mentioned above, and they expect similar values.

=LAMBDA(Number,Ref,[Order],
  LET(
    _order,IF(ISOMITTED(Order),-1,IF(Order=0,-1,1)),
    _n,Number,
    _r,INDEX(IF(ROWS(Ref)=1,TRANSPOSE(Ref),Ref),,1),
    _d,SORT(_r,1,_order),
    _i,SEQUENCE(ROWS(_d)),
    _ranks,SCAN(0,_i,
          LAMBDA(a,b,
            IFS(
              b=1,1,
              INDEX(_d,b-1,1)=INDEX(_d,b,1),a,
              TRUE,a+1
            )
          )
         ),
    _out,MAP(_n,LAMBDA(x,XLOOKUP(x,_d,_ranks,"No rank"))),
    _out
  )
)

RANK.DENSE takes three parameters:

1. Number – A number or array of numbers to find the rank for from the ranks given by Ref sorted by Order
2. Ref – A list of numbers to be ranked, from which the rank of Number will be found
3. Order – An optional integer indicating whether the data should be ranked in descending order (0 – zero) or ascending order (1). If no value is provided, the default is zero (descending)

Here’s how it works:

If you want, you can grab the gist and go and use it right away.

How it works

This is quite a simple lambda, all told, but let’s break it down:

=LAMBDA(Number,Ref,[Order],
  LET(
    _order,IF(ISOMITTED(Order),-1,IF(Order=0,-1,1)),
    _n,Number,
    _r,INDEX(IF(ROWS(Ref)=1,TRANSPOSE(Ref),Ref),,1),
    _d,SORT(_r,1,_order),
    _i,SEQUENCE(ROWS(_d)),

We use LET to define some variables:

• _order – we provide a default value in case Order is omitted. This value is how the SORT function expects “Descending” to be encoded. If Order is provided, we convert the 0 to -1, otherwise we set _order to 1.
• _n – by convention, provide an internal name for the Number parameter
• _r – here we convert the array to a row-wise list (if it is originally a multi-column, single-row array), and take the first column of the transposed result.
• _d – here we are applying the specified sort order to the array from the Ref parameter. This prepares the data for ranking.
• _i – we create a sequence of integers as long as the array passed to Ref. We use this as an index to scan through the Ref array and assign a rank to each element

Next up:

    _ranks,SCAN(0,_i,
          LAMBDA(a,b,
            IFS(
              b=1,1,
              INDEX(_d,b-1,1)=INDEX(_d,b,1),a,
              TRUE,a+1
            )
          )
         ),
    _out,MAP(_n,LAMBDA(x,XLOOKUP(x,_d,_ranks,"No rank"))),
    _out
  )
)

• _ranks – here we SCAN through the index array _i. Remember that by convention, the parameters to SCAN’s lambda function – a and b – represent the accumulated value (a – this is just the result of the lambda for the previous row) and the current value in the scanned array (b).  The logic is that we assign the rank 1 to the first row regardless. For each other row, we test if the value in the sorted array on the current row is equal to the value on the prior row of the sorted array. If it is, we place the same rank that we assigned to the previous row. If it’s not, we increment the rank by 1.
• _out – here we use MAP and XLOOKUP to find the rank for each item in the Number parameter. Remember, Number can either be a single value, or it can be an array of values. By implementing in this way, RANK.DENSE can be used in Excel tables, where Number is an item in Ref, as well as in a dynamic array formula where Number=Ref.

Finally we return _out to the spreadsheet. 

In summary

We walked through how to create an Excel lambda function for DENSE_RANK from SQL.

This was simpler than some of the other lambdas I’ve created so far, but it is definitely useful. In fact, this lambda is an offshoot of the work I originally did for pd.qcut.

I hope this function is of use to you, or if not, that the technique of using an index array to scan through another array is useful.