A
Ariel.Prizont
I need to take a random sample of 400 rows out of 16,000.
What would be the best function in excel to use?
What would be the best function in excel to use?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
Frank Kabel
Hi
one way:
- in a helper column adjacent to your data put the formula
=RAND()
and copy down for all rows
After this sort with this column and use the first 400 entries as your
sample
one way:
- in a helper column adjacent to your data put the formula
=RAND()
and copy down for all rows
After this sort with this column and use the first 400 entries as your
sample
T
Tushar Mehta
For a few different options see
Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
R
Richard Dobis
Let me repeat your problem to see whether I understand.
You have 16000 row (and each of them contains numbers text etc )
You want to randomly choose 400 rows out of given 16000.
If I am right then you should use function OFFSET and RAND together.
Let in column A (A1:A16000) are 16000 numbers
Let in column B (B1:B400) are formulas =FLOOR(RAND()*16000,1)
And let the column C (C1:C400) is filled by formulas =OFFSET($A$1,B1,0) (B1
for row 1 -> B2 for row 2 -> B3 for row 3 etc)
After each F9 column C is different random sample (uniformly distributed)
from A.
I think that this number example could be easily transformed into your
problem with rows.
rd
PS: later on you can change B column to =FLOOR(RAND()*(16000-1)+1,1), which
is more appropriate formula for you.
You have 16000 row (and each of them contains numbers text etc )
You want to randomly choose 400 rows out of given 16000.
If I am right then you should use function OFFSET and RAND together.
Let in column A (A1:A16000) are 16000 numbers
Let in column B (B1:B400) are formulas =FLOOR(RAND()*16000,1)
And let the column C (C1:C400) is filled by formulas =OFFSET($A$1,B1,0) (B1
for row 1 -> B2 for row 2 -> B3 for row 3 etc)
After each F9 column C is different random sample (uniformly distributed)
from A.
I think that this number example could be easily transformed into your
problem with rows.
rd
PS: later on you can change B column to =FLOOR(RAND()*(16000-1)+1,1), which
is more appropriate formula for you.