Skip to content

Instantly share code, notes, and snippets.

Last active May 22, 2022 23:08
What would you like to do?
This Excel lambda function provides a ranking mechanism similar to SQL's DENSE_RANK, but follows the pattern of RANK.AVG and RANK.EQ
Ranks a one-dimensional array using the provided sort order. If adjacent items have the same value, they are all given the rank of the first of them.
Ranks following equally ranked groups increment by one from the most recent rank. This is as opposed to RANK.EQ where following ranks are given the rank
they would have received if the same-ranked items were given different ranks. As such, this function behaves similarly to SQL's DENSE_RANK
Number - A number or array of numbers to find the rank for from the ranks given by Ref sorted by Order
Ref - A list of numbers to be ranked, from which the rank of Number will be found
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.
An array the same size as Number, with each element being an integer representing the rank of the element of Number according to the ranks of Ref when sorted by Order
RANK.DENSE =LAMBDA(Number,Ref,[Order],
_out,MAP(_n,LAMBDA(x,XLOOKUP(x,_d,_ranks,"No rank"))),
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment