Best way to 'bound' a lognormal random draw

dr_gzus

New Member
Joined
Sep 15, 2010
Messages
28
Hi everyone, thanks for being there!

I'm trying to create a lognormal distribution random draw but I can't have the draw be less than 1. Basically 1 and above, instead of the typical 0 and above. If I use an 'if' statement to default to 1 if the draw is less than 1, I get waaaay more 1's than I should, so the resulting distribution is bubkus. Any thoughts?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,813
Office Version
  1. 2010
Platform
  1. Windows
What are you using now to generate lognormal random deviates?
 

dr_gzus

New Member
Joined
Sep 15, 2010
Messages
28
I have a mean of 1 (no change) and a sensitivity range of 1 to 2.67. I'm trying to fit a lognormal prior to this range. I don't want a draw below 1 because this would indicate a smaller likelihood in my model when my model assumes this is not possible. I'm using the following formula (sorry for the complexity).

EXP(NORMINV(RAND(),LN(mean)-(0.5*(LN(1+(SD/mean)^2))),(LN(1+(SD/mean)^2)))

Thanks.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,813
Office Version
  1. 2010
Platform
  1. Windows
What's a pair of typical values for your SD and mean?
 

dr_gzus

New Member
Joined
Sep 15, 2010
Messages
28

ADVERTISEMENT

So mean (or base case, really) is 1, and sd = (2.67-1)/(2*1.96) = 0.43. Probably not very precise.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,813
Office Version
  1. 2010
Platform
  1. Windows
Code:
Option Explicit
 
Const BigPos        As Single = 3.402823E+38
Const BigNeg        As Single = -BigPos
Const SmlPos        As Single = 1.401298E-45
 
Function NLT1(Mean As Single, SD As Single, Optional bVolatile As Boolean = False) As Double
    If bVolatile Then Application.Volatile
 
    Do
        NLT1 = RandLogNormMV(Mean, SD * SD)(0)
    Loop While NLT1 < 1#
End Function
 
Function RandLogNormMV(m As Single, _
                       v As Single, _
                       Optional bVolatile As Boolean = False) As Variant
    ' shg 2009-1010
    ' Returns a pair of Log-Normal random variates with mean m and variance v
 
    ' [URL]http://en.wikipedia.org/wiki/Log-normal_distribution[/URL]
    ' u (Real) and s (> 0) are the mean and standard deviation
    ' of the variable's natural log
 
    ' m = exp(u + s^2/2)
    ' v = (exp(s^2)- 1)* exp(2*u + s^2)
    ' Given m & v,
    ' u = Log(m)- Log(1 + v / m^2)/ 2
    ' s = Sqr(log(1 + v / m^2))
 
    Dim u           As Single
    Dim s           As Single
 
    If bVolatile Then Application.Volatile
 
    u = Log(m) - Log(1 + v / m ^ 2) / 2
    s = Sqr(Log(1 + v / m ^ 2))
 
    RandLogNormMV = RandLogNorm(u, s)
End Function
 
Function RandLogNorm(u As Single, _
                     s As Single, _
                     Optional bVolatile As Boolean = False) As Variant
    ' shg 2008-1230
    ' Returns a pair of Log-Normal random variates
    ' [URL]http://en.wikipedia.org/wiki/Log-normal_distribution[/URL]
    ' u (Real) and s (> 0) are the mean and standard deviation
    ' of the variable's natural log
 
    ' m = exp(u + s^2/2)
    ' v = (exp(s^2)- 1)* exp(2*u + s^2)
 
    ' Given the mean and deviation of the distribution,
    ' u = Log(m)- Log(1 + v / m^2)
    ' s = Sqr(log(1 + v / m^2))
 
    Dim afRN()      As Single    ' pair of random normal variates
 
    If bVolatile Then Application.Volatile
    afRN = RandNorm()
    RandLogNorm = Array(Exp(u + s * afRN(0)), _
                        Exp(u + s * 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, as an alternative to 
    ' =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(0) + Sqr(1! - fCorrel ^ 2) * z(1)
    RandNorm = z
End Function

E.g.,

=NLT1(mean, SD [, bVolatile = False])
 
Last edited:

dr_gzus

New Member
Joined
Sep 15, 2010
Messages
28

ADVERTISEMENT

So, uh... hmmm. Looks awesome, but a lot to process.

Paste the code into VBA, and enter the formula in blue into the cell in question?

Or is what I need a smaller component of the bigger code?
 

dr_gzus

New Member
Joined
Sep 15, 2010
Messages
28
Not as daunting once pasted into VBA; Excel splits up the parts for me, and I can see the code for the function you're directing me to. I'll give it a go. Thanks dude!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,813
Office Version
  1. 2010
Platform
  1. Windows
It's an optional argument.

=NFT1(1, 0.43) (non-volatile)

or

=NFT1(1, 0.43, False) (same as above)

or

=NFT1(1, 0.43, True) (volatile)
 

Forum statistics

Threads
1,141,019
Messages
5,703,760
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top