I am looking for a formula or something similar to output 5 best results.
The table looks like this.
| Name | Column B | Column C |
|---|---|---|
| a | 4 | 0 |
| b | 29 | 28 |
| c | 30 | 32 |
| d | 26 | 26 |
| e | 16 | 14 |
| f | 40 | 42 |
| g | 10 | 16 |
| h | 2 | 0 |
much more data
Column B may not exceed a total of 100 for 5 results. The maximum with 5 results from column C need to be determined. The 5 best names should be displayed.
I hope you understand my problem. Thanks!
I tried with LARGE function but failed.
CodePudding user response：
RE-EDIT: In order to filter when C is greater than B I wrapped it in LAMBDA not to repeat lots of times the same filter:
=LAMBDA(filtered;QUERY({SEQUENCE(ROWS(filtered))\filtered};"SELECT Col2,Col3,Col4 WHERE Col1 >= "& MATCH(1;MAP(SEQUENCE(ROWS(filtered));LAMBDA(c;IF(SUM(QUERY({SEQUENCE(ROWS(filtered))\filtered};"Select Col3 Where Col1 >= "&c&" limit "&J2))>100;0;1)));0)&" limit "&J2))(FILTER(SORT(A2:C;3;0);A2:A<>0;B2:B<C2:C))
You can try with this, with MATCH & MAP I find the index of the first 5 numbers that don't sum more than 100, and the SEQUENCE let me filter it in the QUERY:
=QUERY({SEQUENCE(COUNTA(A2:A)),FILTER(SORT(A2:C,3,0),A2:A<>0)},"SELECT Col2,Col3,Col4 WHERE Col1 >= "& MATCH(1,MAP(SEQUENCE(COUNTA(A2:A)),LAMBDA(c,IF(SOMME(QUERY({SEQUENCE(ROWS(A2:C)),SORT(A2:C,3,0)},"Select Col3 Where Col1 >= "&c&" limit 5"))>100,0,1))),0)&" limit 5")
EDIT: Locale "translation"
=QUERY({SEQUENCE(COUNTA(A2:A))\FILTER(SORT(A2:C;3;0);A2:A<>0)};"SELECT Col2,Col3,Col4 WHERE Col1 >= "& MATCH(1;MAP(SEQUENCE(COUNTA(A2:A));LAMBDA(c;IF(SUM(QUERY({SEQUENCE(ROWS(A2:C))\SORT(A2:C;3;0)};"Select Col3 Where Col1 >= "&c&" limit 5"))>100;0;1)));0)&" limit 5")
