Lambda Function to return bottom "N" as part of larger formula

Question

I'm dipping my toes into the LAMBDA world in Excel, to solve a super common table I create.

Given a range of Data, I want to create a table that pulls the Top N, and all the respective values. At the end, I like to have an "All Others" line that returns the sum of the non-top N values.

Data:

Name    Value     Percent
A   100   10%
B   99    0.23
C   98    0.29
D   97    0.29
E   96    0.39
F   95    0.19
G   94    0.13
H   93    0.12
I   92    0.31
J   91    0.23
K   90    0.32
L   89    0.23
M   88    0.11

I want to take the Top N (e.g. Top 10) values, and pull them in to their own table.

The formula I can use is below (which I then put in a Named Range/Lambda function):

=FILTER(A2:C14,RANK(INDEX(A2:C14,,2),INDEX(A2:C14,,2),0)<=10)  
   

=LAMBDA(tableRng,rankCol,topN,FILTER(tableRng,RANK(INDEX(tableRng,,rankCol),INDEX(tableRng,,rankCol),0)<=topN))

This successfully creates the top N (e.g. Top 10) table. Is there a way I can have this same Lambda function also add a row for the remaining values?

So far, I've been able to return those last 3 (in my example) by changing the <=TopN to simply >topN, but they still spill in a table form, instead of combining the values.

If I wrap that formula (changing >= to <), in SUM(), I get 267.666 which is the two columns of remaining data combined...so I'm getting closer, but not there yet.

Context: I'm really trying to do this with a single Lambda function, but if this request is really pushing the bounds of Lambda, or would be an incredibly convoluted formula, I understand and would be open to a second formula that does simply return the "remaining" values in a single line "All Others".

Answer 1

I have tried something like this, hope this accomplishes the desired output:

=LAMBDA(_Data,_TopN,
 LET(
     _a, SORT(_Data, 2, -1),
     _b, TAKE(_a, _TopN),
     _c, DROP(_a, _TopN),
     _d, HSTACK("All Other", DROP(BYCOL(_c, SUM), , 1)),
     VSTACK(_b, _d)))(A2:C14, 10)

This should take care of the ties as well:

=LAMBDA(_Data,_TopN,
 LET(
     _a, SORT(_Data, 2, -1),
     _b, CHOOSECOLS(_a, 2),
     _c, FILTER(_a, _b>=LARGE(_b, _TopN), ""),
     _d, HSTACK("All Others", BYCOL(DROP(_a, ROWS(_c), 1), SUM)),
     VSTACK(_c, _d)))(A2:C14, 10)