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

  • Thread starter Thread starter James8309
  • Start date Start date
J

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,
 
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 -
Click to expand...

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
 
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 -
Click to expand...

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
 
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 -
Click to expand...

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
 
Back
Top