Does anyone know how to select the last 3 small values from a list? such as
a list {1,2,3,4,5,6,7,8,9}
then reture the last 3 small value {1,2,3}
Thank you for any suggestion
Eric

On Fri, 30 Dec 2005 17:31:03 -0800, "Eric"
wrote:

Does anyone know how to select the last 3 small values from a list? such as
a list {1,2,3,4,5,6,7,8,9}
then reture the last 3 small value {1,2,3}
Thank you for any suggestion
Eric

The SMALL worksheet function will do that.

The specifics depend on the nature of your list and the result specifications.



--ron

This assumes that your values are in A1-A9

=SMALL(A1:A9,1)
=SMALL(A1:A9,2)
=SMALL(A1:A9,3)

Each one of these sells will return a 'smallest' value. You'll have the
smallest, 2nd smallest, and third smallest

There's some good help understanding this function in the help file.

Good Luck


"Eric" wrote:

Does anyone know how to select the last 3 small values from a list? such as
a list {1,2,3,4,5,6,7,8,9}
then reture the last 3 small value {1,2,3}
Thank you for any suggestion
Eric

Does it have a single function to perform the similar task?
Given Lists {1,2,3,4,5,6,7,8,9}
Given number {2,3,7}
Condition: If any given number equals to any last 3 small integers, such as
{1,2,3} in this case, then TRUE.
2 is TRUE
3 is TRUE
7 is FALSE
Does anyone have any idea?
Thank you
Eric

"Jacob_F_Roecker" wrote:

This assumes that your values are in A1-A9

=SMALL(A1:A9,1)
=SMALL(A1:A9,2)
=SMALL(A1:A9,3)

Each one of these sells will return a 'smallest' value. You'll have the
smallest, 2nd smallest, and third smallest

There's some good help understanding this function in the help file.

Good Luck


"Eric" wrote:

Does anyone know how to select the last 3 small values from a list? such as
a list {1,2,3,4,5,6,7,8,9}
then reture the last 3 small value {1,2,3}
Thank you for any suggestion
Eric

may not be elegant solution but serves the purpose

suppose a11,a12,a13 are 2,3,7,
the list 1 to 7 are in A2 to A8

in B11 type
=IF(OR(A11=SMALL($A$2:$A$8,1),A11=SMALL($A$2:$A$8, 2),A11=SMALL($A$2:$A$8,3))
,TRUE,FALSE)
copy B11 down to b12 and B13






"Eric" wrote in message
...
Does it have a single function to perform the similar task?
Given Lists {1,2,3,4,5,6,7,8,9}
Given number {2,3,7}
Condition: If any given number equals to any last 3 small integers, such
as
{1,2,3} in this case, then TRUE.
2 is TRUE
3 is TRUE
7 is FALSE
Does anyone have any idea?
Thank you
Eric

"Jacob_F_Roecker" wrote:

This assumes that your values are in A1-A9

=SMALL(A1:A9,1)
=SMALL(A1:A9,2)
=SMALL(A1:A9,3)

Each one of these sells will return a 'smallest' value. You'll have the
smallest, 2nd smallest, and third smallest

There's some good help understanding this function in the help file.

Good Luck


"Eric" wrote:

Does anyone know how to select the last 3 small values from a list?
such as
a list {1,2,3,4,5,6,7,8,9}
then reture the last 3 small value {1,2,3}
Thank you for any suggestion
Eric

Thank you for your reply, it works for limited conditions, but if there are
many conditions involved, the code will be very lengthy, and there is a
length limitation for conditional formating, which I am working on.
Thank you for your suggestion
Eric :

"R.VENKATARAMAN" wrote:

may not be elegant solution but serves the purpose

suppose a11,a12,a13 are 2,3,7,
the list 1 to 7 are in A2 to A8

in B11 type
=IF(OR(A11=SMALL($A$2:$A$8,1),A11=SMALL($A$2:$A$8, 2),A11=SMALL($A$2:$A$8,3))
,TRUE,FALSE)
copy B11 down to b12 and B13






"Eric" wrote in message
...
Does it have a single function to perform the similar task?
Given Lists {1,2,3,4,5,6,7,8,9}
Given number {2,3,7}
Condition: If any given number equals to any last 3 small integers, such
as
{1,2,3} in this case, then TRUE.
2 is TRUE
3 is TRUE
7 is FALSE
Does anyone have any idea?
Thank you
Eric

"Jacob_F_Roecker" wrote:

This assumes that your values are in A1-A9

=SMALL(A1:A9,1)
=SMALL(A1:A9,2)
=SMALL(A1:A9,3)

Each one of these sells will return a 'smallest' value. You'll have the
smallest, 2nd smallest, and third smallest

There's some good help understanding this function in the help file.

Good Luck


"Eric" wrote:

Does anyone know how to select the last 3 small values from a list?
such as
a list {1,2,3,4,5,6,7,8,9}
then reture the last 3 small value {1,2,3}
Thank you for any suggestion
Eric

A1:A9 is the range from where u want to select

=+IF(COUNT(A1:A9)=COUNT(A1:A9),CONCATENATE(SMALL(A 1:A9,1),",",SMALL(A1:A9,2),",",SMALL(A1:A9,3)),0)


--
juleeus
------------------------------------------------------------------------
juleeus's Profile: http://www.excelforum.com/member.php...o&userid=18356
View this thread: http://www.excelforum.com/showthread...hreadid=497096

On Fri, 30 Dec 2005 23:56:02 -0800, "Eric"
wrote:

Does it have a single function to perform the similar task?
Given Lists {1,2,3,4,5,6,7,8,9}
Given number {2,3,7}
Condition: If any given number equals to any last 3 small integers, such as
{1,2,3} in this case, then TRUE.
2 is TRUE
3 is TRUE
7 is FALSE
Does anyone have any idea?
Thank you
Eric

list = the range where your numbers 1..9 are listed.

Your test number is in A14.

The following formula will do what you specify:

=SUMPRODUCT(-(SMALL(list,{1,2,3})=A14))<0


--ron