Small & Large Function

[James8309]

Hi everyone..

Ive been trying to figure this problem that I am having for more than
an hour and it is driving me crazy.

1. I have 5 different cells from A1:E1.

2. Random number from 1 to 13 gets inputted into each cell randomly
e.g. A1 can be anything from 1 to 13.

3. I have 5 different cells from A2:E2 which suppose to rank the
numbers in A1:E1 and place them correctly.
i.e. A2 = Smallest Number from A1:E1, B2 = 2nd Smallest, C2 = 3rd
Smallet and of course E2 = biggest number from A1:E1.

Question
I just put in SMALL(A1:E1,2) in B2 and SMALL(A1:E1,3) in C2 and yes it
works fine if A1:E1 is like this
A1 = 13
B1 = 4
C1 = 2
D1 = 5
E1 = 1

which will give
A2 = 1
B2 = 2
C2 = 4
D2 = 5
E2 = 13

BUT it there are repeating numbers my formulae doesn't work for me.
i.e.;
A1 = 2
B1 = 2
C1 = 2
D1 = 1
E1 = 5

it gives me
A2 = 1
B2 = 2
C1 = 5 !!!!!!!!!!!!!!!!!!!!!!!


How do I fix it so it returns 1 - 2 - 2 - 2 - 5?

Thank you for your help in advance!

Regards,


James

 

[Bob Phillips]

This works fine for me

=SMALL($A$1:$E$1,COLUMN(A1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

I don't know why you got the results you did... your typed-in formulas
worked fine for me. Bob's formula is easier to implement as you only have to
type the formula once (in A2) and then copy it down to A6.

Rick

 

[Harlan Grove]

I just put in SMALL(A1:E1,2) in B2 and SMALL(A1:E1,3) in C2 and yes it
works fine if A1:E1 is like this
A1 = 13
B1 = 4
C1 = 2
D1 = 5
E1 = 1

which will give
A2 = 1
B2 = 2
C2 = 4
D2 = 5
E2 = 13

BUT it there are repeating numbers my formulae doesn't work for me.
i.e.;
A1 = 2
B1 = 2
C1 = 2
D1 = 1
E1 = 5

it gives me
A2 = 1
B2 = 2
C1 = 5 !!!!!!!!!!!!!!!!!!!!!!!

I'm going to guess you didn't use the same formula in these two
examples. You may have entered the actual formulas

B2: =SMALL(A1:E1,2)
C2: =SMALL(A1:E1,3)

for the first set of numbers, but I strongly suspect you dragged and/
or copied and pasted the column B formula into column C for the second
set of numbers. That is, I suspect you entered

A2: =SMALL(A1:E1,1)

then filled the A2 formula into B2:E2 and only changed the last
argument, so

B2: =SMALL(B1:F1,2)
C2: =SMALL(C1:G1,3)

which, given your second set of numbers, would correctly return 1 for
A2, 2 for B2 and 5 for C2.