I am working with this data set:
| ids | Cat | Val |
|---|---|---|
| u1 | u | 1 |
| u2 | u | 2 |
| g3 | g | 3 |
| g4 | g | 4 |
| g5 | g | 5 |
| i6 | i | 6 |
| i7 | i | 7 |
| i8 | i | 8 |
| i9 | i | 9 |
| i10 | i | 10 |
I need the the percentile ranks for categories of my IDS. I need to have this in a LET function, as I want to use it as a part of a bigger formula.
Here is what I tested:
Formula: =LET(ids,E2:E4,cats,XLOOKUP(ids,$A$2:$A$11,$B$2:$B$11,""),vals,XLOOKUP(ids,$A$2:$A$11,$C$2:$C$11,""),output,BYROW(cats,LAMBDA(x,PERCENTRANK(FILTER(vals,cats=x),val))),output)
Any ideas why LAMBDA does not work would be appreciated.
3
Match in Two Columns Using MAP
- This is a case when using
BYROWwould be far more complicated than usingMAP. - Note that MSDocs claims the new
PERCENTRANKfunctions offer more precision.
=LET(sData,A2:C11,siCol,1,scCol,2,svCol,3,
di,E2:E4,significance,1,if_not_found,"",
si,CHOOSECOLS(sData,siCol),
sc,CHOOSECOLS(sData,scCol),
sv,CHOOSECOLS(sData,svCol),
dc,XLOOKUP(di,si,sc),
dv,XLOOKUP(di,si,sv),
r,MAP(dc,dv,LAMBDA(c,v,
IFNA(PERCENTRANK.INC(FILTER(sv,sc=c),
v,significance),if_not_found))),
r)
0
Get percentile rank within each cat for the selected ids, using MAP:
- Filter data for cats for the selected ids
- Sort the filtered data by ids
- Use binary search to lookup ids (within the filtered rows)
v2: with reduced lookups
=LET(
for_ids, E2:E4,
data, A2:C11,
filterd_for_ids, CHOOSEROWS(data, XMATCH(for_ids, INDEX(data, , 1))),
all_cats, INDEX(data, , 2),
cats_for_selected_ids, INDEX(filterd_for_ids, , 2),
vals_for_selected_ids, INDEX(filterd_for_ids, , 3),
filtered, SORT(
FILTER(data, ISNUMBER(XMATCH(all_cats, cats_for_selected_ids)))
),
cats, INDEX(filtered, , 2),
vals, INDEX(filtered, , 3),
output, MAP(
cats_for_selected_ids,
vals_for_selected_ids,
LAMBDA(cat, val, PERCENTRANK.INC(FILTER(vals, cats = cat), val))
),
output
)
v1
=LET(
for_ids, E2:E4,
data, A2:C11,
all_ids, INDEX(data, , 1),
all_cats, INDEX(data, , 2),
cats_for_selected_ids, XLOOKUP(for_ids, all_ids, all_cats),
filtered, SORT(
FILTER(data, ISNUMBER(XMATCH(all_cats, cats_for_selected_ids)))
),
ids, INDEX(filtered, , 1),
cats, INDEX(filtered, , 2),
vals, INDEX(filtered, , 3),
output, MAP(
for_ids,
LAMBDA(x,
PERCENTRANK.INC(
FILTER(vals, cats = XLOOKUP(x, ids, cats, , , 2)),
XLOOKUP(x, ids, vals, , , 2)
)
)
),
output
)
3