I have an array of 22 numbers from 1 to 22 in a column. By pressing F9 i want
to be able to change their position randomly in the column. How can I do
this? In essence the numbers selected are not random number but what want to
be random is the numbers position within the range of cells

Hi Deal or No Deal,

In a helper column, insert the RAND() function and then sort on the helper
column. Each time you press F9, the helper column values will change.


---
Regards,
Norman



"Deal or No Deal game simulation" <Deal or No Deal game
wrote in message
...
I have an array of 22 numbers from 1 to 22 in a column. By pressing F9 i
want
to be able to change their position randomly in the column. How can I do
this? In essence the numbers selected are not random number but what want
to
be random is the numbers position within the range of cells

With a staging column:
In A1:A22, enter formula:
=RAND()*RAND()

in B1:B22, enter formula:
=RANK(A1,A$1:A$22)

Column B gives the 22 numbers in a random order.

There is an infinitesimal possibility that formula in A returns twice the
same number, in which case column B will also return twice the same number.

To check that eventuality, add a cell with following ARRAY formula:
=SUM(COUNTIF($B$1:$B$22,B1:B22))=ROWS(B1:B22)
(validate with Ctrl+Shift+Enter)

If that cell shows FALSE, press F9 again.

See example : http://cjoint.com/?fvkKq5ySSy

HTH
--
AP

"Deal or No Deal game simulation" <Deal or No Deal game
a écrit dans le message de news:
...
I have an array of 22 numbers from 1 to 22 in a column. By pressing F9 i
want
to be able to change their position randomly in the column. How can I do
this? In essence the numbers selected are not random number but what want
to
be random is the numbers position within the range of cells

You can create a random, without replacement list, without sorting or using
code.

In an out-of-the-way area of your sheet, say Column Z, enter the Rand()
function as far down as the amount of numbers you wish to draw from.

In your case, in Z1 enter:
=RAND()
And copy down to Z22.

NOW, if you wish to *just* use the numbers 1 to 22, enter this formula
anywhe

=INDEX(ROW($1:$22),RANK(Z1,$Z$1:$Z$22))
And copy down 22 rows.

You can use *any* 22 numbers *and/or* letters if you wish, by entering the
list to pick from in say A1 to A22, and then use this formula:

=INDEX($A$1:$A$22,RANK(Z1,$Z$1:$Z$22))
And copy down 22 rows.

Each hit of <F9 will produce a new random order.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Deal or No Deal game simulation" <Deal or No Deal game
wrote in message
...
I have an array of 22 numbers from 1 to 22 in a column. By pressing F9 i
want
to be able to change their position randomly in the column. How can I do
this? In essence the numbers selected are not random number but what want
to
be random is the numbers position within the range of cells