|  | Simple Max/Min type question Help!! Urgent!! | | |
| | | James8309 |  |
|  Posted: Tue Aug 19, 2008 4:35 am Post subject: Simple Max/Min type question Help!! Urgent!! |  |
Hi everyone.
I have 5 numbers from cell A1 to A5
A1 = 5
A2 = 13
A3 = 4
A4 = 1
A5 = 12
If I want to re-arrange them in cell B1 to B5 from smallest to
largest. I know I can use sort function but.. how do I make it arrange
in such way automatically?
i.e.
B1 will always have the smallest value from A1 to A5 so I can use B1=>
=Min(A1:A5)
B2 will always have the 2nd smallest value from A1 to A5 => Now I have
a problem. Is there a formulae to find second smallest or biggest
value?
Please help
Thank you, |
| |
| | | Joerg Mochikun | |
| | Posted: Tue Aug 19, 2008 4:35 am Post subject: Re: Simple Max/Min type question Help!! Urgent!! | |
=SMALL(A1:A5,2)
Cheers,
JM
"James8309" <jaedong1221@gmail.com> wrote in message
news:5e2c3773-b759-460f-af2b-410f6f488dbf@z11g2000prl.googlegroups.com...
| Quote: | Hi everyone.
I have 5 numbers from cell A1 to A5
A1 = 5
A2 = 13
A3 = 4
A4 = 1
A5 = 12
If I want to re-arrange them in cell B1 to B5 from smallest to
largest. I know I can use sort function but.. how do I make it arrange
in such way automatically?
i.e.
B1 will always have the smallest value from A1 to A5 so I can use B1=
=Min(A1:A5)
B2 will always have the 2nd smallest value from A1 to A5 => Now I have
a problem. Is there a formulae to find second smallest or biggest
value?
Please help
Thank you, |
|
| |
| | | T. Valko | |
| | Posted: Tue Aug 19, 2008 4:35 am Post subject: Re: Simple Max/Min type question Help!! Urgent!! | |
Try this...
Enter this formula in B1 and copy down to B5:
=SMALL(A$1:A$5,ROWS(B$1:B1))
--
Biff
Microsoft Excel MVP
"James8309" <jaedong1221@gmail.com> wrote in message
news:5e2c3773-b759-460f-af2b-410f6f488dbf@z11g2000prl.googlegroups.com...
| Quote: | Hi everyone.
I have 5 numbers from cell A1 to A5
A1 = 5
A2 = 13
A3 = 4
A4 = 1
A5 = 12
If I want to re-arrange them in cell B1 to B5 from smallest to
largest. I know I can use sort function but.. how do I make it arrange
in such way automatically?
i.e.
B1 will always have the smallest value from A1 to A5 so I can use B1=
=Min(A1:A5)
B2 will always have the 2nd smallest value from A1 to A5 => Now I have
a problem. Is there a formulae to find second smallest or biggest
value?
Please help
Thank you, |
|
| |
| | | Rick Rothstein (MVP - VB) | |
| | Posted: Tue Aug 19, 2008 4:35 am Post subject: Re: Simple Max/Min type question Help!! Urgent!! | |
| |  | |
Use the formula Biff posted (it is in the message you replied to. To repeat
with the change required for the new range you posted...
Enter this formula in B1 and copy down to B6:
=SMALL(A$1:A$6,ROWS(B$1:B1))
Rick
"James8309" <jaedong1221@gmail.com> wrote in message
news:020aa1b1-1b5d-4924-9879-6711f7fa2d77@v1g2000pra.googlegroups.com...
On Aug 19, 2:58 pm, "T. Valko" <biffinp...@comcast.net> wrote:
| Quote: | Try this...
Enter this formula in B1 and copy down to B5:
=SMALL(A$1:A$5,ROWS(B$1:B1))
--
Biff
Microsoft Excel MVP
"James8309" <jaedong1...@gmail.com> wrote in message
news:5e2c3773-b759-460f-af2b-410f6f488dbf@z11g2000prl.googlegroups.com...
Hi everyone.
I have 5 numbers from cell A1 to A5
A1 = 5
A2 = 13
A3 = 4
A4 = 1
A5 = 12
If I want to re-arrange them in cell B1 to B5 from smallest to
largest. I know I can use sort function but.. how do I make it arrange
in such way automatically?
i.e.
B1 will always have the smallest value from A1 to A5 so I can use B1=
=Min(A1:A5)
B2 will always have the 2nd smallest value from A1 to A5 => Now I have
a problem. Is there a formulae to find second smallest or biggest
value?
Please help
Thank you,- Hide quoted text -
- Show quoted text -
|
Firstly, Thank you so much for your help.
It returns wrong results when there are same numbers
i.e.
A1 = 1
A2 = 2
A3 = 5
A4 = 3
A5 = 4
A6 = 4
I want it to return in ' 1 - 2 - 3 - 4 - 4 - 5 ' but instead when I
used small function it returned ' 1 - 2 - 3 - 4 - 5 - 5 '
I simply put in small(A1:A6,2) -> small(A1:A6,3) and so on.
What have I done wrong?
Your advice will be much appreciated.
Regards,
James |
| |
| | | Joerg Mochikun | |
| | Posted: Tue Aug 19, 2008 4:51 am Post subject: Re: Simple Max/Min type question Help!! Urgent!! | |
| | | |
It doesn't matter if you use your "simple" notation or the formula proposed
by Biff: the result should be the same. Carefully check your input again for
typos. The result will be as you expected: 1 - 2 - 3 - 4 - 4 - 5
JM
"James8309" <jaedong1221@gmail.com> wrote in message
news:020aa1b1-1b5d-4924-9879-6711f7fa2d77@v1g2000pra.googlegroups.com...
On Aug 19, 2:58 pm, "T. Valko" <biffinp...@comcast.net> wrote:
| Quote: | Try this...
Enter this formula in B1 and copy down to B5:
=SMALL(A$1:A$5,ROWS(B$1:B1))
--
Biff
Microsoft Excel MVP
"James8309" <jaedong1...@gmail.com> wrote in message
news:5e2c3773-b759-460f-af2b-410f6f488dbf@z11g2000prl.googlegroups.com...
Hi everyone.
I have 5 numbers from cell A1 to A5
A1 = 5
A2 = 13
A3 = 4
A4 = 1
A5 = 12
If I want to re-arrange them in cell B1 to B5 from smallest to
largest. I know I can use sort function but.. how do I make it arrange
in such way automatically?
i.e.
B1 will always have the smallest value from A1 to A5 so I can use B1=
=Min(A1:A5)
B2 will always have the 2nd smallest value from A1 to A5 => Now I have
a problem. Is there a formulae to find second smallest or biggest
value?
Please help
Thank you,- Hide quoted text -
- Show quoted text -
|
Firstly, Thank you so much for your help.
It returns wrong results when there are same numbers
i.e.
A1 = 1
A2 = 2
A3 = 5
A4 = 3
A5 = 4
A6 = 4
I want it to return in ' ' but instead when I
used small function it returned ' 1 - 2 - 3 - 4 - 5 - 5 '
I simply put in small(A1:A6,2) -> small(A1:A6,3) and so on.
What have I done wrong?
Your advice will be much appreciated.
Regards,
James |
| |
| | | James8309 | |
| | Posted: Tue Aug 19, 2008 6:00 am Post subject: Re: Simple Max/Min type question Help!! Urgent!! | |
| | | |
On Aug 19, 2:58 pm, "T. Valko" <biffinp...@comcast.net> wrote:
| Quote: | Try this...
Enter this formula in B1 and copy down to B5:
=SMALL(A$1:A$5,ROWS(B$1:B1))
--
Biff
Microsoft Excel MVP
"James8309" <jaedong1...@gmail.com> wrote in message
news:5e2c3773-b759-460f-af2b-410f6f488dbf@z11g2000prl.googlegroups.com...
Hi everyone.
I have 5 numbers from cell A1 to A5
A1 = 5
A2 = 13
A3 = 4
A4 = 1
A5 = 12
If I want to re-arrange them in cell B1 to B5 from smallest to
largest. I know I can use sort function but.. how do I make it arrange
in such way automatically?
i.e.
B1 will always have the smallest value from A1 to A5 so I can use B1=
=Min(A1:A5)
B2 will always have the 2nd smallest value from A1 to A5 => Now I have
a problem. Is there a formulae to find second smallest or biggest
value?
Please help
Thank you,- Hide quoted text -
- Show quoted text -
|
Firstly, Thank you so much for your help.
It returns wrong results when there are same numbers
i.e.
A1 = 1
A2 = 2
A3 = 5
A4 = 3
A5 = 4
A6 = 4
I want it to return in ' 1 - 2 - 3 - 4 - 4 - 5 ' but instead when I
used small function it returned ' 1 - 2 - 3 - 4 - 5 - 5 '
I simply put in small(A1:A6,2) -> small(A1:A6,3) and so on.
What have I done wrong?
Your advice will be much appreciated.
Regards,
James |
| |
|
|
