• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multivariable Monte Carlo in Excel: use many =RAND() cells, or one global one?

lingyai

New Member
I am making a simple monte carlo model in Excel, using a Data Table to calculate Profit as Revenue minus Costs, where Revenue is assumed to follow a defined normal distribution and Costs, a triangular distribution.

Right now, each of the two random variable cells refers to its own cell containing simply = RAND(). I.e. there are two such cells.

Is this appropriate? Or is it for some reason better to have one global RAND() cell which the different distribution formulas refer to? Does it make any difference? What if I decided to add another 10 stochastic variables?

Grateful for any advice.
 

Hui

Excel Ninja
Staff member
Firstly, Welcome to the Chandoo.org Forums

You need to do each variable separately

Also why use a flat distribution?
You can easily use a normal distribution

Have a read of The appropriate section here

Try that and see how you go
 
Last edited:

lingyai

New Member
Thanks Hui.

Re:
Also why use a flat distribution?
You can easily use a normal distribution
In my first sentence I mentioned "Revenue is assumed to follow a defined normal distribution and Costs, a triangular distribution. " Neither are flat, and one is in fact normal.
 

lingyai

New Member
Pardon my late response. I am using the formula which gets created by this free simulation add-in

https://www.probabilitymanagement.org/tools-1

The formula is

'=IF( Minimum > Most_Likely, NA(), IF( Most_Likely > Maximum, NA(), IF( Minimum = Maximum, Most_Likely, IF( Random_number < (( Most_Likely - Minimum ) / ( Maximum - Minimum )), Minimum + SQRT( Random_number * ( Most_Likely - Minimum ) * ( Maximum - Minimum )), Maximum - SQRT(( 1 - Random_number ) * ( Maximum - Minimum ) * ( Maximum - Most_Likely ))))))
 

Hui

Excel Ninja
Staff member
I figured you may have used that

I wrote my own in triangular function in VBA several year ago before I saw that
 

Hui

Excel Ninja
Staff member
I decided to check mine out and realised I had also written a Triangular Formula solution

59594

So there is an alternative

I even wrote a formulaic solution for a situation where you have a totally user defined distribution.
 
Top