| 0 | A | B |
|---|---|---|
| 1 | 100 | 60 |
| 2 | 50 | 50 |
| 3 | 80 | 40 |
| 4 | 10 | |
| 5 | 20 | |
| 6 | 70 | |
| 7 | 30 | |
| 8 | 90 | |
| 9 | 40 | |
| 10 | 60 |
In Column B I want to
- sort the values in
Column Adescending and - then only display the values in rank 5,6,7 of the descending order.
So far I have been able to solve the first step using this formula:
=TAKE(SORT(A1:A10;;-1);5)
However, I have now clue how to implement the second step. It should be something like this
=TAKE(SORT(A1:A10;;-1);5-7)
Do you know which formula I need to only show the values in rank 5,6,7?
NOTE: The 5,6,7 is just an example for this question.
It would be great to have formula in which this range can be defined flexible.
>Solution :
You could try using the CHOOSEROWS() as well:
=CHOOSEROWS(SORT(A1:A10,,-1),5,6,7)
Or, may be using INDEX()
=INDEX(SORT(A1:A10,,-1),{5;6;7})
Addendum: Also, if you ever want to have those list from a filtered data then one possible way:
=LET(α, A2:A11, INDEX(SORT(FILTER(α,MAP(α,LAMBDA(δ,SUBTOTAL(103,δ)))),,-1),SEQUENCE(3,,5)))

