Small & Large Function

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

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
 
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)
 
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
 
James8309 said:
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 !!!!!!!!!!!!!!!!!!!!!!!
Click to expand...

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