LARGE function for unique values

[mhenk]

in column A, I have a list of about 1000 numbers, however, only 9-11 of them are unique. I would like to have a simple function that returns the nth largest unique number. Here is an abbreviated example:

A1-A6
1
1
1.05
1.05
2
3


B1-B4
1
2
3
4

I would like C1-C4 to return
3
2
1.05
1

Any help? Thanks

 

[mhenk]

no, it seems like when I'm using a formula to calculate the values in column A, your formula does not work.

The data will always be the same length (in terms of number of entries)

 

[mhenk]

I know what the problem is, it seems to be that somewhere in the calculation leading up to column A's entries, I had to use a random number. If I take out the randomness, the formula works just fine.

 

[vane0326]

Input formula in cell C2 and copied down.

=IF(ROW($C2)=2,MAX($A$2:$A$12),MAX(IF($A$2:$A$12<C1,$A$2:$A$12)))

Must hold down:

Crtl,Shift,Enter

Large non-Duplicates.xls
	A	B	C	D
1
2	1		3
3	1		2
4	1.05		1.05
5	1.05		1
6	2		0
7	3		0
8			0
9
10
Sheet2