Microsoft Excel help. Random sampling?

Question

i have about 30 000 emails for work on excel and we're supposed to send out a survey to a random sample of 1500 people. is there a way i can get excel to randomly pick 1500 of the emails out of the total of 30 000?

Answer 1

Your data currently in col A. Insert new col A and type in A1 =ROW(). Copy down to end of data now in col B. Leave col C blank. In cell E1 type =COUNTA(B:B) In col E2 type number of random samples you require ie 1500. In cell E3 type =INT(E1/E2) In cell F1 type =RANDBETWEEN(1,$E$3)

In F2 type =RANDBETWEEN(F1+1,ROW()*$E$3) In G1 type =VLOOKUP(F1,A:B,2,False) Copy down one cell, then select cells F2 & G2 and copy down as far as is required ie 1500 rows. this will give you a record that is approximately 1 in every (cell E3 result) grouping. You can still sort col B any way you want to further randomise the result.

It would probably be best to turn off the AutoCalculate feature as every time you type something it will change the result. If you wish to recalculate a set of results just press F9.

Answer 2

if each email have an unique ID number (running number 1 ~ 30000)

then they can pick based on a list of random unique numbers



you can easily generate unique random numbers in excel sheet by using an add-inn.



http://www.office-addins.com/-excel-addins/excel-random-generator.html

(Fill in the Excel range with unique random numbers, integers, real numbers, dates or strings with just one click. Generate passwords, do random sorting in rows, columns or a range, and fill selected cells with random values from Excel custom lists..

Random Generator for Excel is an add-in for Microsoft Excel 2007, Excel 2003, Excel XP (2002), and Excel 2000.)

Answer 3

RAND Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated. Syntax RAND( ) Remarks To generate a random real number between a and b, use: RAND()*(b-a)+a If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press "F9" to change the formula to a random number.