Small Function

[Guest]

I have a lot of numbers in a column and I want Excel to return the 4th
smallest number so I'm using the formula small(range,4). However, the small
function works in this way: if I have the numbers (1,1,2,2,3,4) the function
small(range,4) returns 2, but I want it to return 4 since that is the 4th
smallest number.

Any advice on how to tweak the small function to get it to work in this way,
or an alternative function that I can use?

Thank you!

 

[Bob Phillips]

=SMALL(IF(MATCH(S1:S10,S1:S10,0)=ROW(S1:S10)-CELL("Row",S1:S10)+1,S1:S10),4)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I tried the formula but it ends up giving me #N/A.

 

[T. Valko]

You have to enter the formula as an array. Use the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[T. Valko]

Any particular reason for using:

-CELL("Row",S1:S10)

versus

-ROW(S1:S10) or -MIN(ROW(S1:S10))

Biff