#### Mac XL

This site will look better if you upgrade to a browser that supports web standards.

## Sampling a range without replacement

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.

### Worksheet Method 1: Sorting on a helper column

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 Data/Sort, 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 Data/Sort 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.

### Worksheet Method 2: Using SMALL() with a helper column

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))`

### A User Defined Function to sample without replacement

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
```