Best way to 'bound' a lognormal random draw

[dr_gzus]

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?

 

[dr_gzus]

Okay got it on the formula, and thanks big time for your help, but I'm still getting a #REF! error.

 

[shg]

Where did you paste the code?

Did you copy it all?

EDIT: And the function name is NLT1 (as in Not Less Than 1)

 

[dr_gzus]

Pasted the whole thing into a module in VBA.

 

[dr_gzus]

Excel is suggesting as it should that I write NLT1, so I know the formula is there.

 

[shg]

http://www.box.net/shared/r7ai2zy3je

 

[dr_gzus]

Your version works. My spreadsheet does not. Same code. I'm noticing that my spreadsheet capitalizes and blue-highlights =NLTI( while yours remains =nlt1(

Is there a setting I might need to tweak somewhere?

 

[shg]

Dunno. Put your workbook on a friendly file sharing site and post a link.

 

[dr_gzus]

Confidential info. No can do. But thanks for all of your help, I'll try to figure out what's going on from here. If yours works I'm sure I can figure out what to do with mine. As soon as I get it worked out it will be just what I need.

Just out of curiosity, I assume the same VBA code could be tweaked to not return a number less than x if I had a different base case, right?

Thanks in advance, I'm off to lunch!