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

XL's Analysis Toolpak Add-in has a function that returns random integers between two values: RANDBETWEEN(start, end).

Unfortunately, the function returns only a single value, so entering a range of cells with =RANDBETWEEN(1, 100) won't guarantee that there won't be duplicates.

The easiest way to return non-repeating integers is to place the numbers you want to return 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 you wanted 10 non-repeating numbers between 1 and 100 (inclusive). Put 1..100 in A1:A100. 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.

The following UDF will return an array of values that doesn't repeat.

Usage:

=RandInt(nStart, nEnd)

Returns a long integer between nStart and nEnd. If the function is array-entered, will return an array of N values where N is the number of cells the function is entered into.

Arguments:

nStart: (Optional) the minimum value to be returned, between -2147483647 and 2147483647. The default value is 1.

nEnd: (Optional), the maximum value to be returned, between -2147483647 and 2147483647. If no value is provided, or if nEnd < nStart, nEnd defaults to nStart + the number of cells in the array - 1.

Example:

To get the same set of 10 numbers between 1 and 100 as in the last example, select A1:A10 and array enter

=RandInt(1, 100)

Note that array-entering =RandInt() in the range A1:B3 will return a three-row, two-column array of {1,2,3,4,5,6} in random order. If the range of integers between nStart and nEnd is smaller than the number of array-entered cells, the function returns the #NUM! error.

Note also that this function is volatile, so the numbers will change every time a cell on the worksheet is calculated.

'****************************************************** 'Purpose: produce array of random ints in a range 'Inputs: nStart - the smallest value in the range ' nEnd - the largest value in the range 'Returns: array of integers ' 'Revised: 14 June 2005 to avoid randomizing vArr() '****************************************************** Public Function RandInt( _ Optional ByVal nStart As Long = 1&, _ Optional ByVal nEnd As Long = -2147483647) As Variant 'returns array of random longs 'J.E. McGimpsey http://www.mcgimpsey.com/excel/randint.html Dim vArr As Variant Dim vResult As Variant Dim nCount As Long Dim nIndex As Long Dim nRand As Long Dim nRows As Long Dim nCols As Long Dim i As Long Dim j As Long Application.Volatile If Not TypeOf Application.Caller Is Range Then Exit Function With Application.Caller nCount = .Count If nEnd < nStart Then nEnd = nStart + nCount - 1 If nCount = 1 Then RandInt = CLng((nEnd - nStart) * Rnd() + nStart) ElseIf nCount > nEnd - nStart + 1 Then RandInt = CVErr(xlErrNum) Else nRows = .Rows.Count nCols = .Columns.Count nIndex = nEnd - nStart + 1 ReDim vResult(1 To nRows, 1 To nCols) ReDim vArr(0 To nIndex - 1) For i = 0 To UBound(vArr) vArr(i) = i + nStart Next i For i = 1 To nRows For j = 1 To nCols nRand = Int(Rnd() * nIndex) nIndex = nIndex - 1 vResult(i, j) = vArr(nRand) vArr(nRand) = vArr(nIndex) Next j Next i RandInt = vResult End If End With 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.