This site will look better if you upgrade to a browser that supports web standards.
If you need to draw a random sample from a range, you might start with using the RAND() and INDEX() functions. Say you wanted a random sample from A1:A100:
=INDEX($A$1:$A$100,RAND()*100+1)
This works fine for one sample, or if you want more than one value and don't care if you get duplicates (just copy the formula to other cells). If instead you want to avoid duplication, you need to sample "without replacement" (imagine a hat with 100 slips of paper with the numbers 1 to 100 - if you take slips out without replacing them, you're guaranteed not to get duplication).
There are two "standard" worksheet methods for accomplishing this. In addition, I provide a User Defined Function (UDF) that is somewhat more flexible in that the source and destination ranges need not be columns or rows.
Note: If you just want a series of random integers, see Random integers without repetition.
The easiest way to return non-repeating values is to place them in a column. Then place the formula =RAND() in an adjacent column and copy down for as many rows as you have numbers. Choose , and sort on the column with the random integers. You can then take as many numbers as you wish. The act of sorting will cause the random numbers to recalculate, so you can repeat the sort to get a new set.
Example:
Suppose your list were in A1:A100 and you wanted 10 random but non-duplicating samples. Enter =RAND() in B1 and copy down to B100. Choose and sort on Column B. You can then use A1:A10 for your data set. Sort again on Column B for a new set of 10.
If you don't want to sort your list, enter the =RAND() function in the helper column as in method 1, then in a third column select the cells to contain the sample values. Say you want 4 values, so select C1:C4. Then array-enter (holding down CTRL-SHIFT-ENTER for Windows or CMD-RETURN for Macs):
=INDEX(A1:A100,MATCH(SMALL(B1:B100,{1;2;3;4}),B1:B100,0))
The following UDF will return an array of values that doesn't repeat (unless there are repetitions in the source range)
Usage:
=SampleNR(rng)
Returns a value from the range rng. If the function is array-entered, it will return an array of N values where N is the number of cells the function is entered into.
Arguments:
rng: The source range to be sampled
Example:
To get the same sample of 4 values from the range A1:A100 as in the last example, select B1:B4 and array enter
=SampleNR(A1:A100)
Note that the sample range needn't be the same shape as the source range. Array-entering =SampleNR(A1:A100) in B1:D3 will return a 3 row by 3 column array of values.
Note also that this function is volatile, so the sample will change every time a cell on the worksheet is calculated.
'******************************************************* 'Purpose: produce an random sample without replacement 'Entry: Array-enter into range no larger than rSource 'Inputs: rSource: the range to be sampled 'Returns: array of values from the source range. Returns ' #N/A if sample range > than source range '******************************************************* Public Function SampleNR(rSource As Range) As Variant Dim vTemp As Variant Dim nArr() As Long Dim nSource As Long Dim nDest As Long Dim nRnd As Long Dim nTemp As Long Dim i As Long Dim j As Long Application.Volatile nSource = rSource.Count With Application.Caller ReDim vTemp(1 To .Rows.Count, 1 To .Columns.Count) nDest = .Count End With If nDest > nSource Then SampleNR = CVErr(xlErrNA) Else ReDim nArr(1 To nSource) For i = 1 To nSource nArr(i) = i Next i For i = 1 To nDest nRnd = Int(Rnd() * (nSource - i + 1)) + i nTemp = nArr(nRnd) nArr(nRnd) = nArr(i) nArr(i) = nTemp Next i nTemp = 1 For i = 1 To UBound(vTemp, 1) For j = 1 To UBound(vTemp, 2) vTemp(i, j) = rSource(nArr(nTemp)) nTemp = nTemp + 1 Next j Next i SampleNR = vTemp End If End Function
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Not familiar with UDFs? See David McRitchie's Getting Started with Macros and User Defined Functions