What function to select the last 3 small values from a list ?

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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

 

[Guest]

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

 

[R.VENKATARAMAN]

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

 

[juleeus]

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

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

 

[Guest]

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 :>

 

[Ron Rosenfeld]

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