Small Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shankidi - 19 Feb 2007 22:22 GMT

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!

Reply to this Message

Bob Phillips - 19 Feb 2007 22:50 GMT

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

Signature

---
HTH

Bob

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

>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
[quoted text clipped - 9 lines]
>
> Thank you!

Reply to this Message

Shankidi - 19 Feb 2007 23:24 GMT

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

> =SMALL(IF(MATCH(S1:S10,S1:S10,0)=ROW(S1:S10)-CELL("Row",S1:S10)+1,S1:S10),4)
>
[quoted text clipped - 11 lines]
> >
> > Thank you!

Reply to this Message

T. Valko - 19 Feb 2007 23:32 GMT

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

Biff

>I tried the formula but it ends up giving me #N/A.
>
[quoted text clipped - 14 lines]
>> >
>> > Thank you!

Reply to this Message

T. Valko - 19 Feb 2007 23:36 GMT

Any particular reason for using:

-CELL("Row",S1:S10)

versus

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

Biff

> =SMALL(IF(MATCH(S1:S10,S1:S10,0)=ROW(S1:S10)-CELL("Row",S1:S10)+1,S1:S10),4)
>
[quoted text clipped - 11 lines]
>>
>> Thank you!

Reply to this Message