Excel Functions | nandeshwar.info

Recently, I had the need of generating different types of random numbers. Although, Excel’s Data Analysis Toolpak can generate good random numbers, I had to create these functions to meet my needs.

First: Unique Random Numbers
This function was modified from ozgrid’s RandLotto function, which returns string. This function will return an array of unqiue random integers in the given range. If you want to return more than one number then this function should be entered as an array formula with Ctrl + Shift + Enter.

Function UniqueRand(lngSmallest As Long, lngHighest As Long, HowManyNos As Long) As Long()
‘modified from http://www.ozgrid.com/VBA/RandomNumbers.htm
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Application.Volatile
ReDim iArr(lngSmallest To lngHighest)
For i = lngSmallest To lngHighest
iArr(i) = i
Next i
For i = lngHighest To lngSmallest + 1 Step -1
r = Int(Rnd() * (i – lngSmallest + 1)) + lngSmallest
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
Dim resultArr() As Long: ReDim resultArr(HowManyNos – 1, 0)
For i = lngSmallest To lngSmallest + HowManyNos – 1
resultArr(j, 0) = iArr(i)
j = j + 1
Next i
UniqueRand = resultArr
End Function

Second: Gaussian or Normal Random Numbers with specified mean and Standard deviation(SD). Now, this function is different than the Analysis toolpak utility because it generates random numbers on a fixed mean and Standard deviation. This function will allow the user to generate random numbers each with different mean and SD.

‘This function will generate Gaussian or Normal Random Numbers based on given mean and SD
Public Function GenGaussNoise(mean As Double, SD As Double, HowMany As Long) As Double()
Dim x As Double, i As Long, j As Long
itr = 50
Dim dblResArr() As Double: ReDim dblResArr(HowMany – 1, 0)
For j = 1 To HowMany
x = 0
For i = 1 To itr
Randomize
U = Rnd
x = x + U
Next i
‘ /* for uniform randoms in [0,1], mu = 0.5 and var = 1/12 */
‘ /* adjust X so mu = 0 and var = 1 */
x = x – itr / 2 ‘ /* set mean to 0 */
x = x * Sqr(12 / itr) ‘ /* adjust variance to 1 */
dblResArr(j – 1, 0) = mean + SD * x
Next j
GenGaussNoise = dblResArr
End Function

Third: Generate white noise or Guassian or Normal random numbers with mean = 0 and SD =1. Although, this could be achieved by the previous function, this function uses Box-Muller method. In this function too, you can use different mean and SD, although if not provided it would take mean =0 and SD=1

Public Function GenWhiteNoise(HowMany As Long, Optional mean As Double = 0, Optional variance As Double = 1) As Double()
‘Returns a normally distributed random variate with mean 0 and variance 1, a.k.a. “White Noise”.
‘ Uses the Box-Muller method
Dim Value1 As Single, Value2 As Single, Fac As Single, Rsq As Single, SD As Double
Dim dblResArr() As Double: ReDim dblResArr(HowMany – 1, 0)
SD = Sqr(variance)
For
pan> i = 1 To HowMany
Do
Randomize
Value1 = 2 * Rnd – 1
Value2 = 2 * Rnd – 1
Rsq = Value1 ^ 2 + Value2 ^ 2
Loop Until Rsq > 0 And Rsq < 1
Fac = (-2 * Log(Rsq) / Rsq) ^ 0.5
If Rnd < 0.5 Then
dblResArr(i – 1, 0) = mean + SD * Value1 * Fac
Else
dblResArr(i – 1, 0) = mean + SD * Value2 * Fac
End If
Next i
GenWhiteNoise = dblResArr
End Function

This is a screenshot of the Excel file. Also, the original Excel file can be downloaded here.

Posts tagged excel functions

Count Text in a Range

Add Specific number of zeros to a value

Random Numbers

Count number of asterisk (*)

Network

Recent Comments