Skip to content

Instantly share code, notes, and snippets.

@ncalm
Last active June 15, 2023 09:42
Show Gist options
  • Save ncalm/696b49f7b00f64c37cd1b26971b2f1df to your computer and use it in GitHub Desktop.
Save ncalm/696b49f7b00f64c37cd1b26971b2f1df to your computer and use it in GitHub Desktop.
This Excel lambda function provides a ranking mechanism similar to SQL's DENSE_RANK, but follows the pattern of RANK.AVG and RANK.EQ
/*
RANK.DENSE
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
Inputs:
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.
Returns:
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],
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
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment