Small Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
=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)
 
You have to enter the formula as an array. Use the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff
 
Any particular reason for using:

-CELL("Row",S1:S10)

versus

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

Biff
 
Back
Top