Forum Discussion
How to make your formula jump rows
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
)
OK, I'm impressed! Sorting through the code does take a bit of time and effort. I came up with some alternatives, not necessarily improvements, that exploit the fact that there are only two data columns to be returned. The first uses MAP for each data column
=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%"}, pcntl₁, MAP(thunks, LAMBDA(ϑ, INDEX(ϑ(), 1))), pcntl₂, MAP(thunks, LAMBDA(ϑ, INDEX(ϑ(), 2))), result, HSTACK(i, pcntl₁, pcntl₂), VSTACK(header, result) )The second goes back to the calculation of percentiles and calculates them separately so that thunking is avoided.
=LET( month_name, HSTACK(MONTH(DemoTbl[Date]), TEXT(DemoTbl[Date], "mmm")), fn₁, LAMBDA(v, PERCENTILE.INC(v, 0.2)), fn₂, LAMBDA(v, PERCENTILE.INC(v, 0.8)), header, {"Seq", "Month", "20%", "80%"}, agg, GROUPBY(month_name, DemoTbl[Val], HSTACK(fn₁, fn₂), , 0), result, VSTACK(header, DROP(agg, 1)), result )I like it! With a larger data set it's undoubtedly faster than using thunks. I was determined to use 1 function in GROUPBY and had to remind myself Excel is limited to returning a scalar (a missed opportunity!) in this situation. Felt like a good opportunity for some practice.
There is still a lot of room for exploration. Your approach would have worked with an array of percentiles but was overkill for two. Such matters are often easier to evaluate in hindsight!
