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!