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

[Eric]

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]

On Fri, 30 Dec 2005 17:31:03 -0800, "Eric" <[email protected]>
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

[Jacob_F_Roecker]

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

[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

[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






"Eric" <[email protected]> wrote in message
news:[email protected]...
> 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

[juleeus]

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

--------------------------------------------------------------------------------




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)

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Fri, 30 Dec 2005 23:56:02 -0800, "Eric" <[email protected]>
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