This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates 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.
