I have a list of numbers in 9 adjacent cells. The numbers in them are unique
1-9 but in random order. I need to select a consecutive range of 7 of these
cells and find out the order of the nunbers in the cells. When I find out
the lowest number in the range, I need to assign a 1 to a corresponding cell.
When I find the next lowest number in the range, I need to asign a 2 to the
corresponding cell, etc. Until I have a new 7-cell range that contains 1-7
(unique, but random).

For instance, here are the numbers in the 9 cells:
8,5,9,2,4,7,1,6,3

If the range I choose is the first 7 cells, then the cells corresponding to
the first 7 cells in the list would be:
6,5,7,2,3,6,1

Any ideas. I'm open to writing a function if necessary.

I think you can use the RANK function to do this.


"fedude" wrote in message
...
I have a list of numbers in 9 adjacent cells. The numbers in
them are unique
1-9 but in random order. I need to select a consecutive range
of 7 of these
cells and find out the order of the nunbers in the cells. When
I find out
the lowest number in the range, I need to assign a 1 to a
corresponding cell.
When I find the next lowest number in the range, I need to
asign a 2 to the
corresponding cell, etc. Until I have a new 7-cell range that
contains 1-7
(unique, but random).

For instance, here are the numbers in the 9 cells:
8,5,9,2,4,7,1,6,3

If the range I choose is the first 7 cells, then the cells
corresponding to
the first 7 cells in the list would be:
6,5,7,2,3,6,1

Any ideas. I'm open to writing a function if necessary.

PERFECT! Thanks Chip



"Chip Pearson" wrote:

I think you can use the RANK function to do this.


"fedude" wrote in message
...
I have a list of numbers in 9 adjacent cells. The numbers in
them are unique
1-9 but in random order. I need to select a consecutive range
of 7 of these
cells and find out the order of the nunbers in the cells. When
I find out
the lowest number in the range, I need to assign a 1 to a
corresponding cell.
When I find the next lowest number in the range, I need to
asign a 2 to the
corresponding cell, etc. Until I have a new 7-cell range that
contains 1-7
(unique, but random).

For instance, here are the numbers in the 9 cells:
8,5,9,2,4,7,1,6,3

If the range I choose is the first 7 cells, then the cells
corresponding to
the first 7 cells in the list would be:
6,5,7,2,3,6,1

Any ideas. I'm open to writing a function if necessary.

Look up the rank function in Help. I think you should be able to make that
work...
--
HTH...

Jim Thomlinson


"fedude" wrote:

I have a list of numbers in 9 adjacent cells. The numbers in them are unique
1-9 but in random order. I need to select a consecutive range of 7 of these
cells and find out the order of the nunbers in the cells. When I find out
the lowest number in the range, I need to assign a 1 to a corresponding cell.
When I find the next lowest number in the range, I need to asign a 2 to the
corresponding cell, etc. Until I have a new 7-cell range that contains 1-7
(unique, but random).

For instance, here are the numbers in the 9 cells:
8,5,9,2,4,7,1,6,3

If the range I choose is the first 7 cells, then the cells corresponding to
the first 7 cells in the list would be:
6,5,7,2,3,6,1

Any ideas. I'm open to writing a function if necessary.

It's like the RANK function was developed for my need...

PERFECT! Thanks Jim


"Jim Thomlinson" wrote:

Look up the rank function in Help. I think you should be able to make that
work...
--
HTH...

Jim Thomlinson


"fedude" wrote:

I have a list of numbers in 9 adjacent cells. The numbers in them are unique
1-9 but in random order. I need to select a consecutive range of 7 of these
cells and find out the order of the nunbers in the cells. When I find out
the lowest number in the range, I need to assign a 1 to a corresponding cell.
When I find the next lowest number in the range, I need to asign a 2 to the
corresponding cell, etc. Until I have a new 7-cell range that contains 1-7
(unique, but random).

For instance, here are the numbers in the 9 cells:
8,5,9,2,4,7,1,6,3

If the range I choose is the first 7 cells, then the cells corresponding to
the first 7 cells in the list would be:
6,5,7,2,3,6,1

Any ideas. I'm open to writing a function if necessary.