View Full Version : Drawing sample in Excel

02-24-2015, 08:08 AM
Hi again, I am wondering if there is any formula to draw a random sample of fixed size in excel.

Let say, In the 1st column I have a list of City-names of length 100. Now I want to draw a Random-sample of Cities of size 10 out of 100. All 100 cities have equal probability to get selected in my sample of size 10. What is the way for doing this.

Really appreciate for your pointer.


02-27-2015, 09:18 AM
Pinter is that unless doing this regularly, no need for VBA.
Excel has a random number function. I would just use that as follows:

1 In cell A1 type "City Names"
2 In cells A2 to A101 put in your 100 City Names
3 In cell B2 type "Random"
4 In cell B2 type the formula =RAND()
5 Copy the formula from B2 to cells B3 to B101
6 Select column B and overwrite column B by pasting values
7 Decide now whether to take the first 10 city names, or the last 10, or 10 city names starting at a given row - choose anything - it does not matter!
8 Sort the data in columns A and B - sort by RANDOM (=columnB) telling Excel that you have headers
9 Based on what you chose to do in 7 above, you have your random sample

02-27-2015, 09:20 AM
Oops in previous post please read Pinter as Pointer!!