Selecting Random Data

[myssieh]

Good Morning, I am using Excel 2003 with Windows XP.

In cell A I have a list of 40 values and I am trying to pull a random
sample. In column B I put Rand() and in Column C I put
=INDEX(A$1:A$200,RANK(B1,B$1:B$200))

I appers to be working in Colulmn C but Column B is displaying a number.
for example A:1 has the letter A in it. Column B has 0314237.

Why is it doing that. Is it something I have done and how do I fix it?

thanks in advance for your help!!

 

[Max]

In cell A I have a list of 40 values and I am trying to pull a random

sample. In column B I put Rand() and in Column C I put
=INDEX(A$1:A$200,RANK(B1,B$1:B$200))

Assuming values to be sampled are in A1:A40
In B1: =RAND()
First, B1 has to be copied down to B40

And then in C1, place: =INDEX(A$1:A$40,RANK(B1,B$1:B$40))
C1 will then return a random sample from A1:A40,
which is re-generable via pressing the F9 key

You could of course copy C1 down by as many rows as desired to return more
random samples (non repeating), or till C40 for the maximum scramble of the
entire lot of source values in A1:A40

 

[myssieh]

Thanks Max... I made these changes but Cell B:1 is still displaying a
number... I guess I can just hide the cell contents?? Also, F9 works to
change the random sample but it also changes evertime they hit enter on
another cell, even a blank one. Is there a way to prevent this?

Thanks again!!

 

[Max]

Thanks Max... I made these changes but Cell B:1 is still displaying a
number... I guess I can just hide the cell contents??

Col B is a helper col, which could be hidden away for neatness

Also, F9 works to change the random sample
but it also changes evertime they hit enter on
another cell, even a blank one. Is there a way to prevent this?

Try setting the calc mode to Manual
Click Tools > Options > Calculation tab
Check "Manual" > OK

 

[myssieh]

that worked!!! Thanks so much!! Have a great day!!

 

[Max]

Welcome, good to hear that.