NormRand – Produce random numbers with normal distribution

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:
data observability

Data Observability Explained

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular