Why does this not sort correctly?

[_dave]

Using the following:
=IF(ISBLANK(A2),"",LARGE(OFFSET($A$2:$A$1000,0,0,$B$2,1),A2))

It does not sort correctly, what am I not seeing here?

Bad Sorting-1.ods (17.6 KB)

[karolus]

@_dave

=IF(ISBLANK(A2);"";LARGE(OFFSET($A$2:$A$1000;0;0;$B$2;1);A2))

perhaps because you actually want:

=IF(ISBLANK(A2);"";LARGE(OFFSET($A$2;0;0;$B$2;1);ROW(A1)))

[_dave]

So, by dropping $A$1000, you still are able to define column A as a full range?
And by starting in A1, it does not take a non number, correct?
Why row and not column?

I’m still learning and trying to understand what going on.

Thank you Karolus for all your help in learning this “stuff”.

[joshua4]

@_dave

you still are able to define column A as a full range?

Here you are using offset from a cornerstone…a single cell, not a column. Then you are resizing that offset using the contents of $B$2. Roughly speaking, don’t confuse the way OFFSET works with how INDEX works.

@_dave

Why row and not column?

The ROW(A1) gets you the first largest, then second largest, then third largest, etc. It’s just a stand-in for a sequence of numbers: 1,2,3,4,… since it becomes ROW(A2), etc., as you drag down the formula in column C. Since you are dragging down, you need ROW(), not COLUMN().

[_dave]

Thank you, for clearing that up for me. I see now why ROW and not columns.
One learns something everyday.