How to produce a random number from a triangular distribution?

[Sayerssss]

Hello guys!

I would like to be able to create a random number using a triangular distribution...

However this is not a function pre-programmed into Excel - I can not find an easy way to put in the formula either, even using add-ins.

Could someone help me to to do this? I have added a picture of the triangular distribution with minimum value=2, mode=4, max-10.


Thanks in advance!

 

[MARK858]

Function below by Mike Middleton on Microsoft.com...
Btw, please note that because I use IE then I can't see png images on boards (not without cheating anyway).

Function RandomTriangular(Minimum As Double, Mode As Double, Maximum As Double) As Double
    Dim LowerRange As Double, HigherRange As Double, TotalRange As Double, CumulativeProb As Double
    Application.Volatile
    LowerRange = Mode - Minimum
    HigherRange = Maximum - Mode
    TotalRange = Maximum - Minimum
    CumulativeProb = Rnd()
    If CumulativeProb < (LowerRange / TotalRange) Then
        RandomTriangular = Minimum + Sqr(CumulativeProb * LowerRange * TotalRange)
    Else
        RandomTriangular = Maximum - Sqr((1 - CumulativeProb) * HigherRange * TotalRange)
    End If
End Function

The notes Mike posted are below (the code was A)...

(B) The function above uses the (standard) inverse cumulative method for generating a random number. The triangular density function has a piecewise-quadratic cumulative distribution, and the If statement uses the inverse of the appropriate quadratic.

(C) You can find similar code if you use Google or Bing to search for "excel vba random number triangular" (without the quotes) or some similar phrase.

(D) I use Minimum, Mode, and Maximum instead of Worst Case, Most Likely, and Best Case. For a revenue random variable, Minimum is the worst case. For a cost uncertain quantity, Minimum is the best case.

(E) The Excel VBA Rnd function is not robust, so you may want to investigate some of its criticisms. And you may decide to use an alternative uniform random number generator.


I also note that your png is hosted on ExcelForum and so can you please post a link to your cross-post as per the forum rules so we don't waste time duplicating answers.

 

[MARK858]

The cross-posted link with a response is below...

How to produce a random number from a triangular distribution?