Math Help Forum - View Single Post - sample selection based on unequal probability

awkward · #6 November 10th, 2009, 05:58 PM

I continued to think about this problem, and I finally came up with a solution.

As before, let's suppose the item identifiers are in cells A1:Am, and their associated weights are in cells B1:Bm.

1. Put the following formula into cell C1: = -LN(RAND())/B1

2. Copy cell C1 and paste into cells C2:Cm.

3. Copy cells C1:Cm and Paste Special-Values over the original cells C1:Cm. This fixes the values of those cells so they will not change during the next step.

4. Sort the data in cells A1:Cm in ascending order based on column C.
Your sample is in the first n rows (which have the lowest values in column C).

This process is based on two properties of the Exponential distribution.
First, if X has an Exponential( $\lambda$ ) distribution and Y has an Exponential( $\mu$ ) distribution, then
$\Pr(X < Y) = \frac{\lambda}{\lambda + \mu}$ .
Second, you can simulate a draw from an Exponential( $\lambda$ ) distribution on a computer by computing
$X = -\frac{1}{\lambda} \ln U$
where U is a (pseudo-) random number drawn from a Uniform(0,1) distribution.