Functions & Formulas

[Armand]

Hello,

I like to see if anyone is able to help me come up with a formula or
function that will do the following for me:

I have a set of 9 computer generated (somewhat random) numbers. THESE
NUMBERS WILL NOT BE IN AN ASSENDING OR A DECENDING ORDER. Example is
something like the following set of numbers: 18.34, 19.37, 20.4, 19.38,
17.96, and so on, up to nine numbers. I CAN ORDER THESE NUMBERS IN A ROW OR A
COLUMN, EACH IN A SEPARATE CELL. HOWEVER, THIS IS HALF OF THE PROBLEM!.

NOW I have another number, I WILL CALL THIS NUMBER MY CONTROL NUMBER. This
control number (19.11) is not equal to any of the numbers in the nine
somewhat random numbers I mentioned above. You can consider this my 10th
number in the universe of number we will consider. NOW THE ISSUE / QUESTION:

HOW CAN I ASK EXCELL TO LOOK AT MY CONTROL NUMBER AND ORDER THE NINE NUMBERS
IN MY FIRST SET IN AN ASSENDING AND A DECENDING ORDER - ABOVE AND BELOW MY
CONTROL NUMBER. TO CLARIFY CONSIDER MY EXAMPLE BELOW:

19.11 THIS IS MY CONTROL NUMBER.
18.34, 19.37, 20.4, 19.38, 17.96 THIS IS MY RANDUM NUMBER LIST
I NEED TO HAVE THE LIST LIKE SO:

20.40
19.38
19.37
19.11 The control number must always appear in the same exact
cell.
18.34
17.96

NOTE: EACH NUMBER IS GOING TO BE IN A SEPARATE CELL. ALL I
WANT IS TO START WITH MY CONTROL NUMBER AND HAVE THE REST OF THE NUMBERS
INCREASE IN AN ASSENDING ORDER GOING UP AND DECENDING ORDER GOING DOWN. I can
not have the control number move up or down. The control number will have a
specified location, the point is to have the other numbers be classified in
ascending and descending order around the control number.

I APPRECIATE ANY HELP ANYONE CAN GIVE ME. I AM STUCK !!!

 

[Tom Hutchins]

Here is one way...

If your 9 numbers are in A1:A9 in any order, and your control number is in
E10:

In A10 enter =E10

In E1 enter
=IF(OR(RANK($E$10,$A$1:$A$10,0)-(ROW(F$10)-ROW(F1))<=0,RANK($E$10,$A$1:$A$10,0)-(ROW(F$10)-ROW(F1))>10,),"",LARGE($A$1:$A$10,RANK($E$10,$A$1:$A$10,0)-(ROW(F$10)-ROW(F1))))

(that is all one formula).
Copy E1 to E2:E9
Copy E9 to E11:E19

Hope this helps,

Hutch

 

[Tom Hutchins]

Let's change the column F references to column E:

=IF(OR(RANK($E$10,$A$1:$A$10,0)-(ROW(E$10)-ROW(E1))<=0,RANK($E$10,$A$1:$A$10,0)-(ROW(E$10)-ROW(E1))>10,),"",LARGE($A$1:$A$10,RANK($E$10,$A$1:$A$10,0)-(ROW(E$10)-ROW(E1))))

Hutch