Forum Discussion

nq003524's avatar
nq003524
Copper Contributor
Nov 04, 2024

How to make your formula jump rows

I'm the percentile function to calculate 20 and 80 percentiles from a list of data registered to a date. I need to work it out month by month. However when I drag my formula down it only goes down on...
excel
Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 06, 2024

This may not be what you're looking for but without a sample data set I must create one on spec.

=LET(
    month_name, MONTH(DemoTbl[Date]),
    fn, LAMBDA(v, Thunk(PERCENTILE.INC(v, {0.2, 0.8}))),
    agg, GROUPBY(month_name, DemoTbl[Val], fn, , 0),
    i, TAKE(agg, , 1),
    thunks, TAKE(agg, , -1),
    header, {"Month", "20%", "80%"},
    unpack, LAMBDA(acc, v, VSTACK(acc, HSTACK(v, INDEX(thunks, v, 1)()))),
    result, REDUCE(header, i, unpack),
    result
)

 

Resources