Random Number Generation

**vioravis** · 02-25-2009, 08:38 PM

I am trying to generate random numbers with the the constraint that the maximum value is 40 and the most likely value is around 40% of the maximum value. Are there any distributions (lognormal??) that would suit this requirement? Is there any way I can do this in Excel? Thanks a lot.

**solnajeff** · 02-25-2009, 09:13 PM

Hi

If you are generating integers then there is a relatively straightforward way to generate 'random' or 'semi random' numbers by creating a list of the number range you are interested in and using VBA to choose your random number. A simple example is attached, the example usaes the RAND function to generate a number alongside each member of the set and then uses the MAX function to determine the highest random number and then uses the set member alongside. This can be used with whatever sample is appropriate and substituted accordingly. There are many other ways to achieve the same result if something else would be more appropriate.

If each number must be generated only once then some additinal code is required, please advise if this is the case.

Regards

Jeff

**shg** · 02-25-2009, 09:17 PM

This generates Log-Normal random variates. The second function generates normal variates and is called by the first.

Const BigPos    As Single = 3.402823E+38
Const BigNeg    As Single = -BigPos
Const SmlPos    As Single = 1.401298E-45

Function RandLogNorm(fMu As Single, _
                     fSigma As Single, _
                     Optional bVolatile As Boolean = False) As Variant

    ' shg 2008-1230
    
    ' Returns a pair of Log-Normal random variates
    ' http://en.wikipedia.org/wiki/Log-normal_distribution
    '   fMu Real
    '   fSigma > 0
    ' mean = exp(fMu + fSigma^2/2)
    ' var = (exp(fSigma^2) - 1) * exp(2*fMu + fSigma^2)

    Dim afRN()  As Single   ' pair of random normal variates
    
    If bVolatile Then Application.Volatile

    afRN = RandNorm()
    RandLogNorm = Array(Exp(fMu + fSigma * afRN(0)), _
                        Exp(fMu + fSigma * afRN(1)))
End Function

Function RandNorm(Optional Mean As Single = 0!, _
                  Optional Dev As Single = 1!, _
                  Optional fCorrel As Single = 0!, _
                  Optional fMin As Single = BigNeg, _
                  Optional fMax As Single = BigPos, _
                  Optional bVolatile As Boolean = False) As Single()

    ' shg 1999-1103
    ' rev 2008-1205 to add min and max

    ' Returns a pair of random deviates (Singles) with the specified
    ' mean, deviation, and correlation. Orders of magnitude faster than
    '     =NORMINV(RAND(), Mean, Dev)

    ' Box-Muller Polar Method
    ' Donald Knuth, The Art of Computer Programming,
    ' Vol 2, Seminumerical Algorithms, p. 117

    Dim z(0 To 1) As Single
    Dim u       As Single
    Dim v       As Single
    Dim s       As Single

    If bVolatile Then Application.Volatile
    If fMax < fMin Then fMax = fMin

    Do
        Do
            u = 2! * Rnd - 1!
            v = 2! * Rnd - 1!
            s = u ^ 2 + v ^ 2
        Loop Until s < 1!

        s = Sqr(-2 * Log(s) / s)
        z(0) = Dev * u * s + Mean
        z(1) = Dev * v * s + Mean
    Loop Until WorksheetFunction.Max(z) >= fMin And _
         WorksheetFunction.Min(z) <= fMax

    If fCorrel <> 0! Then z(1) = fCorrel * z(1) + Sqr(1! - fCorrel ^ 2) * z(2)
    RandNorm = z
End Function

EDIT: I also have one for skew-normal variates if that's what you want ...