# NormRand – Produce random numbers with normal distribution ``' VBA's intrinsic Rnd function returns numbers evenly'  distributed between 0 and 1. Each number in that'  interval has equal probability of being returned'  for any given function call.' This function NormRand returns a random number between'  -infinity and +infinity distibuted normally around'  zero and with standard deviation equal to 1 (i.e.'  about 95% of the values are within the range -2 to +2,'  this is a standard statistical result, often referred'  to as a confidence interval).'' Common uses for such randomly distributed numbers occur'  in finance, statistics and many real-world applications.'  For example, if I wanted to simulate the heights of'  children in a class I might postulate that their heights'  would have mean=x and standard deviation=s. I might decide'  at a value for s by suggesting that 95% of them should'  have heights between x-2s and x+2s. It follows that we can'  simulate the height of any one member of the (imaginary)'  class of children = (s*NormRand + x).'' IMPORTANT: call Randomize before using NormRandFunction NormRand() As Double    ' NormRand returns a randomly distributed drawing from a    ' standard normal distribution i.e. one with:    ' Average = 0 and Standard Deviation = 1.0    Dim fac As Double, rsq As Double, v1 As Double, v2 As Double    Static flag As Boolean, gset As Double        ' Each pass through the calculation of the routine produces    '  two normally-distributed deviates, so we only need to do    '  the calculations every other call. So we set the flag    '  variable (to true) if gset contains a spare NormRand value.    If flag Then        NormRand = gset        ' Force calculation next time.        flag = False    Else        ' Don't have anything saved so need to find a pair of values        ' First generate a co-ordinate pair within the unit circle:        Do            v1 = 2 * Rnd - 1#            v2 = 2 * Rnd - 1#            rsq = v1 * v1 + v2 * v2        Loop Until rsq <= 1#                ' Do the Math:        fac = Sqr(-2# * Log(rsq) / rsq)                ' Return one of the values and save the other (gset) for next time:        NormRand = v2 * fac        gset = v1 * fac        flag = True    End If    End Function``

Share the Post:

Recent Articles: