Selecting Random Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

myssieh - 13 Feb 2008 15:16 GMT

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!!

Reply to this Message

Max - 13 Feb 2008 16:36 GMT

> 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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

myssieh - 13 Feb 2008 16:51 GMT

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!!

> > 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
[quoted text clipped - 11 lines]
> random samples (non repeating), or till C40 for the maximum scramble of the
> entire lot of source values in A1:A40

Reply to this Message

Max - 13 Feb 2008 17:00 GMT

> 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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

myssieh - 13 Feb 2008 17:04 GMT

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

> > Thanks Max... I made these changes but Cell B:1 is still displaying a
> > number... I guess I can just hide the cell contents??
[quoted text clipped - 8 lines]
> Click Tools > Options > Calculation tab
> Check "Manual" > OK

Reply to this Message

Max - 13 Feb 2008 17:09 GMT

Welcome, good to hear that.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message