Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading