Weird behavior SMALL

[iKoenvD]

Hi,

I have a list of numbers and want to sort it while checking for criteria. My thought was to use

=SMALL(IF($A$2:$A$11>$C$1,$A$2:$A$11,99),COUNTA($B$1:B1)+1)

in cell B2 and extend it down. Without the IF function it works fine, but with it it acts real strange. It show a list that is in order, but the first number for example doesn't comply with criteria. Then the third and fourth cells show a #NUM! error. The real strange thing is when I select the formula and press F9 to preview the outcome, it show the right outcome.
Thoughts?

 

[RoryA]

Did you array enter it?

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option to consider that does not require array entry:

22 11 03.xlsm
	A	B	C
1			5
2	13	6
3	18	8
4	4	10
5	2	13
6	8	18
7	45	20
8	6	30
9	20	45
10	10
11	30
Sort

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(AGGREGATE(15,6,A$2:A$11/(A$2:A$11>=C$1),ROWS(B$2:B2)),"")

 

[iKoenvD]

Welcome to the MrExcel board!

Another option to consider that does not require array entry:

22 11 03.xlsm
	A	B	C
1			5
2	13	6
3	18	8
4	4	10
5	2	13
6	8	18
7	45	20
8	6	30
9	20	45
10	10
11	30
Sort

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(AGGREGATE(15,6,A$2:A$11/(A$2:A$11>=C$1),ROWS(B$2:B2)),"")

Thanks! I didn't realize it was an array entry, but the original dataset is quite large so I rather not use array. This works! Though I don't quite understand it yet.

 

[Peter_SSs]

the original dataset is quite large so I rather not use array.

This still does use an array, it just does not require the Ctrl+Shift+Enter confirmation.