# Best way to 'bound' a lognormal random draw

#### dr_gzus

##### New Member
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
What are you using now to generate lognormal random deviates?

#### dr_gzus

##### New Member
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
What's a pair of typical values for your SD and mean?

#### dr_gzus

##### New Member

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

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
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!

#### dr_gzus

##### New Member
I get a #REF! error using

=NFT1(1,0.43,(bVolatile=FALSE))

#### shg

##### MrExcel MVP
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)

Replies
6
Views
1K
Replies
0
Views
135
Replies
3
Views
575
Replies
12
Views
236
Replies
3
Views
327

### Forum statistics

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.

### Which adblocker are you using?    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

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