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 one row where I need it to go down to the next month. Is it possible to jump down 30 or so rows or is there another way of doing it?

 

excel

8 Replies

  • 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
)

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Nov 06, 2024

      Patrick2788 

      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
)

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Nov 06, 2024

        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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 04, 2024

    nq003524 

    You can use the OFFSET or perhaps INDEX function for this.

    Without knowing what the data look like it is impossible for me to provide more specific help.

    • nq003524's avatar
      nq003524
      Copper Contributor
      Nov 04, 2024

      HansVogelaar  this is the dataset. I want to take the month, in this example January, and calculate the 20% and 80% percentile. After this I want to calculate February and so on. But when I drag down my calculation under 20% and 80% it only goes down one row in the left table. Instead I want it to skip around 30 rows to the date beginning 01/02/1972. Are you able to help?

Resources