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

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.