nov 15, 2017

A mathematician friend of mine worked for the Navy on calculations involving the movement of sound waves through water. It’s a difficult problem, since the speed of sound varies with temperature and salinity. Being a mathematician to the core, he always wanted the exact answer. At times the Navy had to shake him by the shoulders and say “Imagine a torpedo heading towards the ship! A guess now is better than an exact answer later on!”

Sometimes we just have to guess.

The triangle distribution is not as important in probability theory as many other distributions, both well known and obscure. But for guessing in the world of business it can be a very valuable alternative to the uniform distribution.

Let’s imagine that we are attempting to model product inventories. We don’t have very good facts about the inventory variation. We do, however, know that it is never below, say, 80, never above 130, and it is usually around 120. Modelling such data with a uniform distribution leads us to some pretty silly conclusions. In a uniformly distributed model, an inventory of 80 is just as likely as an inventory of 120, but an inventory of 79 never happens ever. The triangle distribution gives us a reasonable guess based on what we know. The peak of the triangle is at 120. On either side of 120 it slopes off, pretty quickly heading in the 130 direction and a bit more slowly moving towards 80.

We saw previously that we can approximate any distribution with a lookup table as long as we can build a table of cumulative distribution values. But Excel does not provide a triangle distribution function. It turns out that it is fairly easy to build a formula for a triangle distribution as long as one can overcome those memories of high school geometry.

If we take “a” represent the lowest possible value, “b” to represent the highest possible value, and “c” to represent the value where the function reaches its maximum, we come up with the following formula.

Looks daunting, doesn’t it? But it’s really just a few “IFs” and a few divisions. Unfortunately, Excel didn’t let me use “c” as a named range for my test, so I could use the standard “a, b, and c” nomenclature in my formula. I went with “m” for “mode”.

=IF($D5<a,0,

IF($D5<m,2*($D5-a)/((b-a)*(m-a)),

IF($D5<b, 2*(b-$D5)/((b-a)*(b-m)),0)))

In this formula, “a” is a named cell that contains the minimum value, “b” is a named cell containing the maximum value, and “m” is a named cell marking the peak (or mode). $D5 contains the number for which we would like to calculate the value of the triangle probability density function.

Entering the formula next to a column representing the range of values in Excel makes it easy to plot our triangle function and confirm the correct shape and values.

In the world of business, a triangle distribution often provides good working estimates when parameters of the actual probability density are either unknown or too complex. Fortunately, this useful function is also very easy to implement as an Excel formula.