sbRandTriang (VBA) ·

Abstract

The triangular distribution is a continuous probability distribution with a probability density function shaped like a triangle. It is a simple distribution because you only need its mininimum, its median, and its maximum value:

sbRandTriang

This distribution is sometimes also called the distribution of missing data because you can use it with a minimal given amount of information. It is often used to simulate expert opinions or when it is too difficult or too expensive to gather more data.

sbRandTrigen

sbRandTrigen provides a triangular distribution with different given inputs. You do not specify the left (min) and the right (max) point of the triangle (in addition to the median point) but a bottom point and a top point with percentiles of what is to the left of them.

sbRandGeneral

The triangular distribution is a special case of the more general sbRandGeneral distribution. This distribution is made of n linear pieces where the triangular one shows only 2.

Literature

A usage example:

Sarah Hewitt (2021). An Evaluation of Key Performance Indicators for Beef Herds.
(External link!) https://eprints.nottingham.ac.uk/69382/1/S%20Hewitt%20Thesis%20corrections%2015_06_22.pdf

Appendix – sbRandTriang Code

Please read my Disclaimer.

Option Explicit

Function sbRandTriang(dMin As Double, dMode As Double, _
    dMax As Double, Optional dRandom = 1#) As Double
'Generates a random number, Triang distributed
'[see Vose: Risk Analysis, 2nd ed., p. 128]
'Source (EN): http://www.sulprobil.de/sbrandtriang_en/
'Source (DE): http://www.berndplumhoff.de/sbrandtriang_de/
'(C) (P) by Bernd Plumhoff 30-Aug-2024 PB V0.32
'Similar to @RISK's (C) RiskTriang function.
'sbRandTriang(minimum,mode,maximum) specifies a triangular
'distribution with three points — a minimum, a mode and
'a maximum. The skew of the triangular distribution is
'driven by the distance of the mode from the minimum and
'from the maximum. Reducing the distance from mode to
'minimum will increase the skew.
'Please ensure that you execute Randomize before you call
'this function for the first time.
Dim dRand As Double, dc_a As Double, db_a As Double
Dim dc_b As Double

If dMode < dMin Or dMax < dMode Then
  sbRandTriang = CVErr(xlErrValue)
  Exit Function
End If
If dMin = dMax Then
  sbRandTriang = dMin 'Triangle is just one point
  Exit Function
End If
dc_a = dMax - dMin
db_a = dMode - dMin
dc_b = dMax - dMode
If dRandom = 1# Then
  dRand = Rnd()
Else
  dRand = dRandom
End If
If dRand < db_a / dc_a Then
  sbRandTriang = dMin + Sqr(dRand * db_a * dc_a)
Else
  sbRandTriang = dMax - Sqr((1# - dRand) * dc_a * dc_b)
End If
End Function

Download