# modeling Monte Carlo with non-standard distribution

#### nbirnbaum

##### New Member
Hello everybody,

I'm using the NORMINV() function to calculate likely random outcomes of specific variables in a Monte Carlo simulation. (Modeling this after the example from the excellent book, How To Measure Anything.)

However, NORMINV() uses standard distribution. What if my distribution is not standard?

For example, I'm doing a Monte Carlo for how much a house might sell for. The max expected value is \$950,000 and the min value is \$850,000. However, the likeliest outcome is not in the middle, but slightly lower -- \$875,000 rather than the mean of \$900,000.

If it was a standard distribution, I would use the formula =NORMINV (RAND(), mean sale price, (max price - min price)/3.29)

So I can't use NORMINV to calculate this. Any idea how I would model this calculation instead?

#### joeu2004

##### Well-known Member
Then you don't use NORMINV. What you do use depends on the description of the complete distribution. You did not post sufficient details for us to be more specific.

#### nbirnbaum

##### New Member
Then you don't use NORMINV. What you do use depends on the description of the complete distribution. You did not post sufficient details for us to be more specific.
I don't know enough about how to describe distributions. Can you help?

#### joeu2004

##### Well-known Member
I don't know enough about how to describe distributions. Can you help?
Truthfully, probably not, if only for lack of time.

If it is a class assignment, you are limited by the problem statement and parameters of the assignment, and reality has nothing to do with it. In that case, I would suggest that you post all information provided by the assignment.

On the other hand, if it is for professional reasons with a client depending on the results, it is important that you get correct information. The internet is not a reliable resource. If you are not savvy enough to separate good and bad guidance, it would behoove you to defer to someone who is, ideally a professional. "You get what you pay for".

But if it is just for personal interest, this is might be a good place to start. However, your question sounds more like a statistics forum question than an Excel forum question. Oh well....

As for the distribution, that depends on the data that you have.

A distribution might be discrete. For example, x% between 925,000 and 950,000; y% between 900,000 and 925,000; etc.

Or a distribution might need to be derived from historical data. If it is short (say, 20 lines or less), post it here. Otherwise, I would suggest that you upload an Excel file to a file-sharing website (e.g. box.net/files), and post the public/share URL here. Test the URL first, being careful to log out of the filie-sharing website.

Caveat: Some people in this forum object to that suggestion. That's fine: they don't have to participate. But the fact is: that might be the best way to communicate large amounts of data and/or complex implementations like a Monte Carlo simulation, IMHO.

If it was a standard distribution, I would use the formula =NORMINV (RAND(), mean sale price, (max price - min price)/3.29)
Why would you think that?! In particular, why would the std dev be (max-min)/3.29?!

For a normal distribution, we usually consider the max and min to be the mean +/-3 or +/-4 sd. In that case, sd would be (max-min)/6 or (max-min)/8.

Perhaps you meant to write (max-mean)/3.29. Even then, 3.29 is an unusual z-score to use, IMHO. Arguably, it covers 99.90% of the data v. 99.73% for +/-3 sd and 99.99% for +/-4 sd.

1,081,983
Messages
5,362,550
Members
400,679
Latest member
alecalec202

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...