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).
Code:
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.
