returing more than one minimum

[foto]

I have a long, dynamic list of numbers from which I need to extract the lowest eight. The length of the list varies. Nested MIN() statements doesn't seem to do the trick. Thanks.

[zzydhf]

lookup the formula "small"

=small(a1:a100,n) where n is the nth smallest number.

[sen_edp]

hello foto

Microsoft Excel - HtmlMaker.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
A1		=	10
	A	B	C	D	E	F
1	10				:alert('2')>=SMALL($A$1:$A$10,1)
2	2				:alert('8')>=SMALL($A$1:$A$10,2)
3	55				:alert('9')>=SMALL($A$1:$A$10,3)
4	8				:alert('9')>=SMALL($A$1:$A$10,4)
5	78				:alert('10')>=SMALL($A$1:$A$10,5)
6	36				:alert('11')>=SMALL($A$1:$A$10,6)
7	9
8	11
9	9
10	22
11
Sheet4

You can see the value of cells only click each above hyperlinks


hope that helps

[Aladin Akyurek]

On 2002-05-22 06:51, foto wrote:
I have a long, dynamic list of numbers from which I need to extract the lowest eight. The length of the list varies. Nested MIN() statements doesn't seem to do the trick. Thanks.

Supposing that the numbers of interest are in A from A2 on.

In B2 enter:

=MATCH(9.99999999999999E+307,A:A)-(CELL("Row",$A$2)-1)

In B3 enter: 8

In C2 enter and copy down as far as needed:

=IF(ROW()-1<=MIN($B$2,$B$3),SMALL(OFFSET($A$2,0,0,$B$2,1),ROW()-1),"")

The foregoing takes into account that the number range in A can change, that is, the range is dynamic.