CODEWITHSUNDEEP
excelsimulationmontecarlo
user2612534
user2612534

Reputation: 21

Monte Carlo Simulation in Excel for Non-normal Distributions

I would like to simulate the performance a baseball player. I know his expected performance for every future year and the standard deviations of those performances (based on regression analysis). At first, I was thinking of using the NORMINV(RAND(),REF,REF) function in excel, but the underlying distribution of baseball players' performances is dramatically right skewed. Is there a way that I can perform this sort of analysis in Excel or some other free or low-cost software? The end-goal here is for the simulation to use the right skewed distribution. Thanks very much.

Upvotes: 2

Views: 4102

Answers (2)

Peladao
Peladao

Reputation: 4090

If you have a cumulative distribution table (that is evenly spaced and sufficiently detailed) then you can easily generate random values from this distribution in Excel by looking up a uniform random number generated by RAND() in your distribution table and take the corresponding "x-axis" value.

=OFFSET($A$1,MATCH(RAND(),$B$2:$B$102),0)

A1 is the cell just above the table of "x-axis" values.
B2:B102 is the cumulative distribution table.

This is a simplified example. Some small modifications may be needed to handle edge-cases and adjust for biases.

If you have enough empirical data you should be able to create the cumulative distribution table.

Upvotes: 0

Joel
Joel

Reputation: 5674

R has lots of tools to do this sort of analysis, though you'd have to look through the docs to figure out how to use it. R is free, at least for non-commercial use.

Upvotes: 1

Related Questions