I need to generate a random log-normal distribution with a given average 500,000 and standard deviation 100,000. For a normal distribution I would simply type
which produces reasonable results with approximately an average output of 500,000 and standard deviation around 100,000. However, I can't get my head around the =LOGNORM.INV() function. Typing
produces a #NUM! error, seemingly because the output is too large. Typing
solves this issue, but produces an average output of approximately 245 and standard deviation over 300, which just isn't reasonable. Microsoft's support page seems to suggest
but this also produces erratic and unreasonably large output, of more than 10^24.
The problem doesn't necessarily have to be solved using the =LOGNORM.INV() function, but it does seem to be the most straight-forward method, isn't it? I can't find any clear explanation, and I'm all out of ideas. So can somebody please advise me on how to generate random numbers from a log-normal distribution?
=NORM.INV(RAND();500000;100000)
which produces reasonable results with approximately an average output of 500,000 and standard deviation around 100,000. However, I can't get my head around the =LOGNORM.INV() function. Typing
=LOGNORM.INV(RAND();500000;100000)
produces a #NUM! error, seemingly because the output is too large. Typing
=LOGNORM.INV(RAND();5;1)
solves this issue, but produces an average output of approximately 245 and standard deviation over 300, which just isn't reasonable. Microsoft's support page seems to suggest
=LOGNORM.INV(RAND();LN(500000);LN(100000))
but this also produces erratic and unreasonably large output, of more than 10^24.
The problem doesn't necessarily have to be solved using the =LOGNORM.INV() function, but it does seem to be the most straight-forward method, isn't it? I can't find any clear explanation, and I'm all out of ideas. So can somebody please advise me on how to generate random numbers from a log-normal distribution?
