modeling Monte Carlo with non-standard distribution

nbirnbaum

New Member
Joined
Jul 31, 2017
Messages
3
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
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
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
Joined
Jul 31, 2017
Messages
3
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
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
I don't know enough about how to describe distributions. Can you help?
Truthfully, probably not, if only for lack of time.

I would start with: what is the purpose of your interest?

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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top