Simple Max/Min type question Help!! Urgent!!

[James8309]

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]

=SMALL(A1:A5,2)

Cheers,
JM

 

[T. Valko]

Try this...

Enter this formula in B1 and copy down to B5:

=SMALL(A$1:A$5,ROWS(B$1:B1))

 

[James8309]

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











- 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

 

[Rick Rothstein \(MVP - VB\)]

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

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











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

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

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











- 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