How to randomize a list?

[Updraft]

Greetings all and Happy New Year,

I have searched the forums to no avail so I turn to you for help. I want to randomize a list, but don't know how to do it in Excel. In particular, I want to take ten items (we can refer to them as A through J) and put them in a random order. The RandBetween function works well for one item, say, the first one, but how do I get the other items to be randomly distributed between the remaining positions?

Thanks in advance,

- - -Updraft

 

[MintJulep]

A possible method - I haven't played with it enough to have figured out all the details.

With the Analysis Toolpack add-in loaded:

Populate a helper column with random numbers generated from the Data Analysis | Random Number Generation tool - using one of the distributions.

Sort your list on the helper column.

 

[IRstuff]

You can use the standard RAND() function to populate MJs helper column, which doesn't require the Analysis toolpack, then sort on that column. If you need fixed values, then you can copy/PasteSpecial values into another column and sort on that.

For only 10 rows, the degree of "randomness" is academic for the most part

TTFN

FAQ731-376

 

[MintJulep]

How would you use RAND()to randomly pick numbers out of a hat?

That was my first thought as well, but I couldn't figure how to avoid the possibility of picking the same number more than once. That is to say, once an item is randomly placed in a position on the list, no other item can occupy the same place.

 

[Updraft]

Thanks for the ideas. Here is what I have found:

The RAND() function returns a number between 0 and 1. RandBetween returns a number between the start and stop values. For instance RANDBETWEEN(1,10) returns a number between 1 and 10. Combining this with the Choose function yields: =CHOOSE(RANDBETWEEN(1,10),B26,B27,B28,B29,B30,B31,B32,B33,B34,B35) returns one of the ten values in the cells B26 through B35. This is a very usefull function and what I used for the initial placement. My problem then is placing the remaining nine items in a random order.

I did solve this but had to use several other columns. For the next placement I had to create a list of the nine remaining values, in this case in cells C26-C34, then applied
=CHOOSE(RANDBETWEEN(1,9),C26,C27,C28,C29,C30,C31,C32,C33,C34)
and
=CHOOSE(RANDBETWEEN(1,8),D26,D27,D28,D29,D30,D31,D32,D33) choosing from the eight remaining values in D26-D33
and so on until I had all ten items placed.

Does anyone see an easier way or a way to make a function do this?

- - -Updraft

 

[melone]

Would this work?

A B
Item A =RAND()
Item B =RAND()
Item C =RAND()
Item D =RAND()
Item E =RAND()

Then just select columns A & B, and sort by column B.

 

[MintJulep]

I'll go back to my initial suggestion, and further suggest using the "Uniform" distribution. I think (if I am reading the help correctly), that this will avoid duplication of the same number.

Far simpler than what you are doing now.

 

[Updraft]

melone,

Your technique works for sorting the values into a random order since the chance of having duplicate random numbers is so slim. However, I cannot sort the columns in this application. BUT I can use another column that ranks this Rand() column which becomes a list of positions. This should be more elegant than the method I used. Thanks for the stimulus!

- - -Updraft

 

[IRstuff]

I think that there's some confusion about "random" and "mutually exclusive." Oddly enough, there was an episode of "Numbers" that discussed the fact that true randomness does not impose maximum distance between samples, and the most people's intuition about duplicate numbers being somehow "not random" is incorrect. Just consider the standard fair coin toss. A naive notion of randomness would suggest that HTHTHTHTHTHTHT is "more random" than HHHHHHHTTTTTTT, but, it's not.

Anyway, so what if there are duplicates? The sorting will put them in order, but so what? That's part of the overall randomness of numbers and random number generators. As such, uniform distribution, hence RAND() is the correct choice, and it's unnecessary to widen the span to 1 to 10, since equal probability is still equal probability with a span of 0 to 1.

TTFN

FAQ731-376

 

[Updraft]

MintJulep, IRstuff and melone,

As I reread your posts you are all basically saying the same thing and it got me where I wanted. The RandBetween(1,10) was an attempt to use a consolidated function to give me an index or position value rather than having to work with the long decimal values. It works well for some applications and I am glad I found it, but the three-column approach has proven to be an elegant solution: Items, Rand(), Rank of Rand(). Since I can't sort the Rand() column each time this third column gives me just what I needed, a position/index value. It fits with your suggestion, MintJulep, providing the distribution I wanted. It also fits with your comments, IRstuff, regarding randomness and spacing, since all I want here is the 10 items to be randomly distributed. The Rand() values will not be evenly spaced, but the Ranking function is what sorts everything out.

Great help and thanks to all of you!

- - -Updraft

 

[MintJulep]

IRStuff,

I agree that should two items get assigned the same number, SORT will put them in an order.

However it will do so according to some logic rule embedded within Excel.

The difference is between truly random and "random enough".

 

[GregLocock]

You could guarantee that they never match by doing a drunkards walk (well, there is till a tiny probability)

B1=rand()
B2=B1+rand()-.5
B3= B2+rand()-.5

etc

I think that has no bias in it.

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.