|  | Small & Large Function | | |
| | | James8309 |  |
|  Posted: Sun Aug 24, 2008 8:36 am Post subject: Small & Large Function |  |
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 | |
| | Posted: Sun Aug 24, 2008 8:36 am Post subject: Re: Small & Large Function | |
| |  | |
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)
"James8309" <jaedong1221@gmail.com> wrote in message
news:aa0e8682-79f1-467b-8d93-42dd07644830@25g2000prz.googlegroups.com...
| Quote: | 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 |
|
| |
| | | Rick Rothstein (MVP - VB) | |
| | Posted: Sun Aug 24, 2008 6:04 pm Post subject: Re: Small & Large Function | |
| | | |
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" <jaedong1221@gmail.com> wrote in message
news:aa0e8682-79f1-467b-8d93-42dd07644830@25g2000prz.googlegroups.com...
| Quote: | 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 |
|
| |
| | | Harlan Grove | |
| | Posted: Sun Aug 24, 2008 10:37 pm Post subject: Re: Small & Large Function | |
| | | |
James8309 <jaedong1...@gmail.com> wrote...
....
| Quote: | 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. |
| |
|
|
