ExcelBanter - triangular distribution

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - triangular distribution (https://www.excelbanter.com/excel-worksheet-functions/222189-triangular-distribution.html)

triangular distribution

How can I rand numbers from triangular distribution?
Thanks,
Yaarit

Answer: triangular distribution

Generating Random Numbers from a Triangular Distribution in Excel

To generate random numbers from a triangular distribution in Excel, follow these steps:
[list=1][*] Determine the minimum, maximum, and mode values for your triangular distribution.[*] Calculate the range of the distribution by subtracting the minimum value from the maximum value.[*] Calculate the left and right skewness of the distribution by subtracting the mode value from the minimum and maximum values, respectively.[*] Use the

Code:

RANDBETWEEN

function to generate a random integer between 0 and the range of the distribution.[*] Add the minimum value to the random integer to get a random number within the range of the distribution.[*] Adjust the random number based on the skewness of the distribution. If the random number is less than the left skewness, add the mode value to it. If the random number is greater than the right skewness, subtract the mode value from it.

Here is an example formula that generates a random number from a triangular distribution with a minimum value of 10, a maximum value of 20, and a mode value of 15:

Code:

=IF(RAND()<=(15-10)/(20-10),10+SQRT(RAND()*((20-10)*(15-10))),20-SQRT((1-RAND())*((20-10)*(20-15))))

This formula uses the

Code:

IF

function to determine whether the random number should be adjusted for left or right skewness. The

Code:

SQRT

function is used to calculate the square root of the random number multiplied by the appropriate range of the distribution.

triangular distribution

Hi,

Can't specifically help, but can point you to the tool in Excel for
generating random numbers:

1. Choose Tools, Add-in, and check Analysis ToolPak.
2. Choose Tools, Data Analysis, Random Number Generation,
3. Take a look at the types of random number distributions that are offered
under Distribution, there are 7 different types. Maybe one will work for you.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Yaarit" wrote:

How can I rand numbers from triangular distribution?
Thanks,
Yaarit

triangular distribution

The poptools add-in contains a function for generating random numbers from
the triangular distribution.
http://www.cse.csiro.au/poptools/

Alternately, one way to generate random numbers from any distribution is to
use
=xINV(RAND())
where xINV is the inverse of the desired cumulative distribution function.
This is the technique used by the ATP random number generation tool
(suggested by Shane Devenshire).

As you doubtless know, the ATP random number generation tool does not
support the triangular distribution, and Excel does not supply worksheet
functions for the triangular distribution. With the parameterization of
http://en.wikipedia.org/wiki/Triangular_distribution
the inverse of the cumulative triangular distribution is
=IF(p<=(c-a)/(b-a),a+SQRT(p*(b-a)*(c-a)),b-SQRT((1-p)*(b-a)*(-c+b)))
Note that you cannot replace p in this formula with RAND(), since that would
use multiple RAND calls in the single formula; instead p should be a
reference to a helper cell that contains the =RAND() formula.

Jerry

"Yaarit" wrote:

How can I rand numbers from triangular distribution?
Thanks,
Yaarit

triangular distribution

Hello,

I wrote a VBA solution for this:
http://www.sulprobil.com/html/distributions.html

Regards,
Bernd