Random Number (not repeating a number)

G

Guest

I have made a table and i am trying to have 20 cells have a random number
between 1 and 20, i can do this with =INT(RAND()*(20-1)+1), but the problem
is i don't want to have repeated numbers i only want to have each number
once. Is there an easy way of doing this in Excel 2000?

Thanks Julian.
 
M

Myrna Larson

Put the numbers 1 to 20 in a column. In the column to the right, put the
formula =RAND(). Sort on this 2nd column. This will put the 20 numbers in
random order.
 
R

Ragdyer

So, what you're actually looking for is not a random number, but a random
*order* of pre-determined numbers.

One approach to accomplish this without constant sorting, is to use an
"out-of-the-way" column, say Column Z, and enter
=RAND()

Drag down to copy, say to Z100.

Now, enter this array formula where you wish to *start* displaying the
random order, say C1:

=INDEX(ROW(A1:A20),RANK(Z1:Z20,Z1:Z20))
But *just* use <ENTER>.

Now, click in C1, and drag down the *selection* to C20.
*Don't* use the fill handle!
Then, hit <F2>, *then* CSE (<Ctrl> <Shift> <Enter>.

This will now give you a display of your 1 to 20 numbers, in random order.
To display a *new* order, all you have to do now, is simply hit <F9>.
 
M

Myrna Larson

Nice, one!

But it may have a down-side, namely the automatic recalculation.
When calculation is set to Automatic, you can't enter any data on this sheet
without getting a new list. And that seems to be true even if there are no
formulas in any cells. You don't have a copy of the original list unless you
copy it somewhere else and Paste special/Values.

I wonder if that's always wanted. If you use the more common technique,
requiring sorting, the user controls when a new list is produced.
 
R

RagDyer

You bring up a very valid point Myrna.

But, let's have the tail wag the dog, and do an exercise, that by *no* means
is easier or better then the sort technique.
It just keeps the brain working in order to delay the onset of Alzheimer's
in guys of my age.

Suppose the Rand() function is placed in a separate WB, whose only job is to
store the random numbers.
So, when this WB is closed, since Index and Rand are not volatile, the
existing results are static, with no error messages.
Then, all you have to do is open, and then immediately close this random WB
in order to obtain a new set of returns.

Admittedly, sorting a couple of columns may be a lot simpler !
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


Nice, one!

But it may have a down-side, namely the automatic recalculation.
When calculation is set to Automatic, you can't enter any data on this sheet
without getting a new list. And that seems to be true even if there are no
formulas in any cells. You don't have a copy of the original list unless you
copy it somewhere else and Paste special/Values.

I wonder if that's always wanted. If you use the more common technique,
requiring sorting, the user controls when a new list is produced.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top