Options for using SMALL()

[Conan Kelly]

Hello all,

I would like to use the SMALL() function to look through a range (A2:A243)
to pick out a certain value.

The problem is that the values in that range are from 1 to 10 with each
value repeating multiple times (1, 2, 3.1, 3.2, 3.3, 4.1, 4.2, 4.3, 5, 6.1,
6.2, 7.1, 7.2, 7.3, 8, 9, & 10). So SMALL() would need to be used in
conjunction with a function that would return an array of unique values.

Is there such a function?

If not, is there any websites out there with good tutorials for writing
UDF's?

I've written UDF's before, but I don't know how I would go about writing a
UDF that would return an *array* of values.

Thanks for any help anyone can provide,

Conan Kelly

 

[T. Valko]

So, what you want to do is find the nth smallest number but use only the
unique numbers?

1 = 1
1
2 = 2
2
3 = 3
4 = 4
5 = 5

Try this entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=SMALL(IF(ISNUMBER(rng),IF(MATCH(rng,rng,0)=ROW(rng)-MIN(ROW(rng))+1,rng)),n)

Where n = nth value you want

Biff