# How to produce a random number from a triangular distribution?

##### New Member
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.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### MARK858

##### MrExcel MVP
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.

Last edited:

Replies
6
Views
101
Replies
14
Views
761
Replies
11
Views
3K
Replies
8
Views
2K
Replies
0
Views
74

1,191,686
Messages
5,988,071
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back