Triangular Distribution in Excel

In excel, there are cases where there are only a few samples of data available, the triangle distribution offers a simplification of the probability distribution. The minimum, maximum, and peak data points make up its parameters. Common uses include modeling of natural processes, project management planning, business, and economic simulations, and audio dithering.

Triangular distribution can be indicated by:

• a represents the lowest value, where a ≤ c,
• c represents the highest value (the height of the triangle), where a ≤ c ≤ b,
• b represents the highest value, where b ≥ c.

This makes estimating the parameters of the distribution using sample data very simple:

• Use any logical statistic (such as the sample mean, mode, or median) as an estimator for c. 
• Calculate a using the sample minimum as an estimator.
• As an estimator for b, use the sample maximum.

If you don’t have sample data, you can estimate a likely minimum, maximum, and most likely value using a knowledge base (i.e. the mode). The triangular distribution has the PDF and CDF listed below:

PDF (Probability Density Function)

The probability density function, or PDF, is a term used to describe how a random variable will behave. The value of the PDF at a specific location is the value of the PDF random variable. In other words, it is the probability that a specific value will be assigned to the random variable. Both continuous and discrete random variables are described using PDFs. A PDF could be used, for instance, to show how persons in a population vary in height. Height would be plotted on the x-axis and probability would be plotted on the y-axis for the PDF. When calculating the probability that a random variable will fall inside a particular range, the probability density function is defined as follows:

CDF (Cumulative Distribution Function)

The cumulative distribution function (CDF) of a certain random variable is taken into consideration while calculating the values of CDF random variables. A sort of function known as a CDF produces a number between 0 and 1, inclusive, from a real value x. They provide the possibility that a specific random variable will have a value less than or equal to x, therefore one may think of them as a sort of “running total.” When working with continuous random variables, CDFs are very helpful since they make it possible to calculate probabilities over intervals rather than simply single points. One only needs to enter the correct numbers into the CDF equation and perform an evaluation to determine the CDF value of a given random variable.

When calculating the probability that a random variable will fall inside a particular range, the cumulative distribution function is defined as follows:

Triangular Distribution in Excel

The Triangular distribution can be used in Excel to determine probabilities, as shown by the examples below.

Example 1: Imagine a store predicts that in any given week, it will welcome at least 600, at most 4,000, and most likely 2,400 customers. How likely is it that the store will see more than 2500 visitors in a single week?

Answer: 

From the question, we can note,

Maximum, b = 4000

Minimum, a = 600

Peak, c = 2400

Random variable, x = 2200

Using the CDF, we can apply the following calculation to estimate the probability that there will be more than 2,500 customers overall:

P(X < x) = (x-a)² / ((b-a)(c-a))

Observe here x < c

We are applying this formula because of this. 

Here is an Excel formula to determine this probability:

Step 1: Enter all the data in an excel sheet.

Step 2: Calculate the probability by using the formula that is mentioned above:

Step 3: Press enter and you will get your answer.

There is a 0.4183 percent chance that more than 2,200 people will enter the store.

Example 2: Let’s say a mobile store predicts that its total sales for the missing week will be at least $5,000, most likely $25,000, and at most $50,000. What is the chance that the restaurant’s overall sales would be less than $26,000?

Answer: 

From the question, we can note,

Maximum, b = 50000

Minimum, a = 5000

Peak, c = 25000

Random variable, x = 26000

Using the CDF, we can apply the calculation below to determine the likelihood that total sales will be under $26,000:

P(X > x) = 1 – [1 – (b-x)2 / (b-a)(b-c))]

Observe here x > c

We are applying this formula because of this. 

Here is an Excel formula to determine this probability:

Step 1: Enter all the data in an excel sheet.

Step 2: Calculate the probability by using the formula that is mentioned above:

Step 3: Press enter and you will get your answer.

There is a 0.512 chance that the restaurant’s overall sales will be less than $26,000.

Example 3: Let’s say a teacher predicts that the combined scores of all of their students will be at least 100, a maximum of 600, and most likely 400 for the upcoming week. What is the probability that a student’s score will be less than 300 overall?

Answer: 

From the question, we can note,

Maximum, b = 600

Minimum, a = 100

Peak, c = 400

Random variable, x = 300

Using the CDF, we can apply the calculation below to determine the likelihood that at total student score will be under 300:

P(X < x) = (x-a)² / ((b-a)(c-a))

Observe here x < c

We are applying this formula because of this. 

Here is an Excel formula to determine this probability:

Step 1: Enter all the data in an excel sheet

Step 2: Calculate the probability by using the formula that is mentioned above:

Step 3: Press enter and you will get your answer.

There is a 0.26667 chance that the student’s overall score will be less than 300.