excelexcel-formula

Only display the values in a certain rank position from filtered and sorted list

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

  1. sort the values in Column A descending and
  2. 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:

    enter image description here

    =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:

    enter image description here

    =LET(α, A2:A11, INDEX(SORT(FILTER(α,MAP(α,LAMBDA(δ,SUBTOTAL(103,δ)))),,-1),SEQUENCE(3,,5)))