lognormal distributions

hi all,

help please.

i'm having a difficult time trying to generate a pseudo random lognormal distribution in excel.

to make things worse, i need to generate a distribution that meets the folowing criteria: mean is 50 and 68th percentile is 250.

i'm told it's possible to calculate sigma out of percentile, but wouln't know where to start.

thank you all in advance for support and guidance.

zurich

i'm told it's possible to calculate sigma out of percentile, but wouln't know where to start.

This suggests to me that your question is more about statistics than Excel. I also find that before I can effectively build a spreadsheet (or any other computer program) to complete a task, I need to understand the underlying mathematics.

Your question seems like something related to a school assignment. My first piece of guidance would be to go to your textbook and study it until you get a good idea of what standard deviation and percentile mean. It is true that they should be related, but I'm not enough of a statistician to be able to explain it to you.

I did find this statement on Wikipedia that might help you understand how percentiles and standard deviations are related:

Percentiles represent the area under the normal curve, increasing from left to right. Each standard deviation represents a fixed percentile. Thus, rounding to two decimal places, −3 is the 0.13th percentile, −2 the 2.28th percentile, −1 the 15.87th percentile, 0 the 50th percentile (both the mean and median of the distribution), +1 the 84.13th percentile, +2 the 97.72nd percentile, and +3 the 99.87th percentile. Note that the 0th percentile falls at negative infinity and the 100th percentile at positive infinity.

Originally Posted by MrShorty

This suggests to me that your question is more about statistics than Excel. I also find that before I can effectively build a spreadsheet (or any other computer program) to complete a task, I need to understand the underlying mathematics.

Your question seems like something related to a school assignment. My first piece of guidance would be to go to your textbook and study it until you get a good idea of what standard deviation and percentile mean. It is true that they should be related, but I'm not enough of a statistician to be able to explain it to you.

I did find this statement on Wikipedia that might help you understand how percentiles and standard deviations are related:

Thank you for your help, Mr Shorty.

The statistical bit is now sorted – can send across the formula I was suggested if you’re interested – but now I’m left with a computing problem as I have to run the formula several times and XLS crashes after having run a few iterations.

My initial thought is to run the desired formula in row 1, copy it over in row 2 and then overwrite via copy/special/values what I have in row 1 in order to keep computing effort low.

I can do it manually for a few iterations, but this is a no go for a proper simulation as I’d need 500k / 1 mio iteration to have some significance.

Any VBA code I can use to have this concept replicated i-times, possibly with results copied over to 10 separate worksheets?

Thx



p.s.

apologies for late reply and additional request.

Been away for a few days but still haunted by this as you can see

Originally Posted by shg

I have UDFs to generate log-normal variates given either location and scale or mean and SD. If you can translate your parameters into either of those pairs, I'll post them.

brilliant - will revert asap.
thank you so much for your help and apologies for late reply