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
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
function to determine whether the random number should be adjusted for left or right skewness. The
function is used to calculate the square root of the random number multiplied by the appropriate range of the distribution.