Selecting Random Data

  • Thread starter Thread starter myssieh
  • Start date Start date
M

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!!
 
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))
Click to expand...

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

---
 
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!!
 
myssieh said:
Thanks Max... I made these changes but Cell B:1 is still displaying a
number... I guess I can just hide the cell contents??
Click to expand...

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?
Click to expand...

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

---
 
Back
Top