software

Hi,

I have looked at the Random Number Generator Add-in and the =RAND function
but can't get either to do what I want (although I'm sure they can)

I have a range of cells in a single column (A2:A3667), and I want a list of
random numbers to fill that range using the values 1:3666, using each value
once and once only. Effectively randomising the row numbers.

How can I do this?

Cheers,

MattMatt,

In A2, enter

=RAND()

In B2, enter

=RANK(A2,$A$2:$A$3667)

then copy A2:B2 down to row 3667.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Matt D Francisquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I have looked at the Random Number Generator Add-in and the =RAND function
gt; but can't get either to do what I want (although I'm sure they can)
gt;
gt; I have a range of cells in a single column (A2:A3667), and I want a list
of
gt; random numbers to fill that range using the values 1:3666, using each
value
gt; once and once only. Effectively randomising the row numbers.
gt;
gt; How can I do this?
gt;
gt; Cheers,
gt;
gt; Matt
gt;
Hi,

Well that worked - wasn't quite the way I was expecting to be able to do it,
but thanks! Can this guarantee that the numbers in column A will be unique?

Also, don't know if this is normal but the random numbers I generate in Col
A are all decimals, why's that?

quot;Bob Phillipsquot; wrote:

gt; Matt,
gt;
gt; In A2, enter
gt;
gt; =RAND()
gt;
gt; In B2, enter
gt;
gt; =RANK(A2,$A$2:$A$3667)
gt;
gt; then copy A2:B2 down to row 3667.
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Matt D Francisquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; I have looked at the Random Number Generator Add-in and the =RAND function
gt; gt; but can't get either to do what I want (although I'm sure they can)
gt; gt;
gt; gt; I have a range of cells in a single column (A2:A3667), and I want a list
gt; of
gt; gt; random numbers to fill that range using the values 1:3666, using each
gt; value
gt; gt; once and once only. Effectively randomising the row numbers.
gt; gt;
gt; gt; How can I do this?
gt; gt;
gt; gt; Cheers,
gt; gt;
gt; gt; Matt
gt; gt;
gt;
gt;
gt;

Because RAND() returns values from [0...1).

Another way, without the helper column:

www.mcgimpsey.com/excel/udfs/randint.htmlIn article gt;,
quot;Matt D Francisquot; gt; wrote:

gt; Also, don't know if this is normal but the random numbers I generate in Col
gt; A are all decimals, why's that?

I cannot guarantee that the numbers in A will be unique, but in all the
times I have used it I have never seen a duplicate.

If you want to be sure, use

A2: =RAND()
B2: =RANK(A2,$A$2:$A$3667) ROW()/10^7
C2: =RANK(B2,$B$2:$B$3667,1)

and copy down

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Matt D Francisquot; gt; wrote in message
...
gt; Hi,
gt;
gt; Well that worked - wasn't quite the way I was expecting to be able to do
it,
gt; but thanks! Can this guarantee that the numbers in column A will be
unique?
gt;
gt; Also, don't know if this is normal but the random numbers I generate in
Col
gt; A are all decimals, why's that?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Matt,
gt; gt;
gt; gt; In A2, enter
gt; gt;
gt; gt; =RAND()
gt; gt;
gt; gt; In B2, enter
gt; gt;
gt; gt; =RANK(A2,$A$2:$A$3667)
gt; gt;
gt; gt; then copy A2:B2 down to row 3667.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;Matt D Francisquot; gt; wrote in
message
gt; gt; ...
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; I have looked at the Random Number Generator Add-in and the =RAND
function
gt; gt; gt; but can't get either to do what I want (although I'm sure they can)
gt; gt; gt;
gt; gt; gt; I have a range of cells in a single column (A2:A3667), and I want a
list
gt; gt; of
gt; gt; gt; random numbers to fill that range using the values 1:3666, using each
gt; gt; value
gt; gt; gt; once and once only. Effectively randomising the row numbers.
gt; gt; gt;
gt; gt; gt; How can I do this?
gt; gt; gt;
gt; gt; gt; Cheers,
gt; gt; gt;
gt; gt; gt; Matt
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;


quot;JE McGimpseyquot; wrote:

gt; Because RAND() returns values from [0...1).
gt;
gt; Another way, without the helper column:
gt;
gt; www.mcgimpsey.com/excel/udfs/randint.html
gt;
gt;
gt; In article gt;,
gt; quot;Matt D Francisquot; gt; wrote:
gt;
gt; gt; Also, don't know if this is normal but the random numbers I generate in Col
gt; gt; A are all decimals, why's that?
gt;