| 0 | A | B |
|---|---|---|
| 1 | 100 | start |
| 2 | 30 | 8 |
| 3 | 50 | rows |
| 4 | 60 | 4 |
| 5 | 60 | |
| 6 | 60 | 30 |
| 7 | 40 | 30 |
| 8 | 10 | 10 |
| 9 | 30 | |
| 10 | 30 |
In Cell B6:B9 I want to
- sort the
Column Ain a descending oder and - display always the number of rows entered in
Cell B4starting from the row entered inCell B2.
So far I have been able to develop this formula:
=CHOOSEROWS(SORT(A1:A10,,-1),SEQUENCE(C4,,C2))
This formula almost gives me the result.
However, once you reach the point that there are no more than 4 rows below the starting point – which is the case in the example above – the formula returns error #VALUE!.
The idea is that the formula in this case displays the remaining 3 rows and just cuts out the fourth one.
How do I need to modify it to make it work?
One possible way :
=TOCOL(INDEX(SORT(A1:A10,,-1),SEQUENCE(B4,,B2)),2)
And considering the formula used in the context of the OP, then bit verbose:
=CHOOSEROWS(SORT(A1:A10,,-1),SEQUENCE(MIN(B4, ROWS(A1:A10)-B2+1),,B2))