Triangular Distribution in VBA

Question

What I currently have:

Option Explicit

Function Triangular(a As Double, b As Double, c As Double) As Double

Randomize
Application.Volatile

Dim d As Double
Dim uniform As Double
Dim retval as Double 



d = (b - a) / (c - a)
    uniform = Rnd()

    If uniform <= d Then
    Triangular = a + (c - a) * Sqr(d * uniform)

Else

    Triangular = a + (c - a) * (1 - Sqr(1 - d) * (1 - uniform))

End If

End Function

I'm having trouble in regards to creating a triangular distribution function in VBA, which calculates a random number from arguments made from:

Calculate d = ( b - a )/( c - a )
Generate a uniformly distributed random number U between 0 and 1 with VBA's Rnd function.
If U <= d, return a + ( c - a ) × sqr(d×U) as the random number. (Sqr(x) is a VBA function which returns the square root of x.
If U > d , return a + ( c - a ) × (1 - sqr((1- d )×(1-U))) as the random number.

The parameters a and c are the minimum and maximum possible values respectively, and the parameter b is the most likely value (where you see the high point in the triangle).

I'm unsure on how to create this function and was wondering if someone could lend a hand? In working on the function I realize I need to use randomize function in order to not generate similar results each time the function is called, as well as the application.volatile operation.

score 0 · Answer 1 · answered Jun 25 '17 at 00:04

0

You have a bug in the code. Should be in second branch

Triangular = a + (c - a) * (1 - Sqr((1 - d) * (1 - uniform)))

answered Jun 25 '17 at 00:04

Severin Pappadeux

18,636
3
38
64

would you happen to know how to write a sub after this that takes the (a)min, (b)max, (c) likely, and total number of values, and display the specified number of random values on a new worksheet? – JaySmith Jun 25 '17 at 00:42
1

@JaySmith no, I don't - never used VBA in my life. To be more precise, I know how to write triangular sampling, and did it before, but not in VBA and have no idea how to `display the specified number of random values on a new worksheet` – Severin Pappadeux Jun 25 '17 at 01:37

A.S.H · Answer 2 · 2017-06-25T07:17:54.160

Not sure of the correctness of your generating equations. Take a look Here for the correct equations; with the difference that b and c are switched with respect to your definition. Here's an implementation adapting the formulas of that page to your own definitions of a, b and c:

Function Triangular(a As Double, b As Double, c As Double) As Double
    Application.Volatile
    Dim U As Double: U = Rnd()
    If U < (b - a) / (c - a) Then
      Triangular = a + sqrt(U * (b - a) * (c - a))
    Else
      Triangular = c - sqrt(U * (c - b) * (c - a))
    End If
End Function

To generate a sequence from the above distribution in a new Worksheet, you can

1- Create the new worksheet

2- Write your parameters in cells A1, B1 and C1

3- write this formula in A2: =Triangular($A$1, $B$1, $C$1)

4- Copy/Paste cell A2 down the column

score 0 · Answer 3 · answered Aug 27 '19 at 06:52

Please notice (1-Prob) in the second case. The Wikipedia link shows the correct formula but it was not implemented correctly by A.S.H.

Function Triangular(ByVal Min As Single, ByVal ML As Single, ByVal Max As Single) As Single
    Application.Volatile
    Dim Prob As Single
    Prob = Rnd
    If Prob < (ML - Min) / (Max - Min) Then
      Triangular = Min + Sqr(Prob * (ML - Min) * (Max - Min))
    Else
      Triangular = Max - Sqr((1 - Prob) * (Max - ML) * (Max - Min))
    End If
End Function

Triangular Distribution in VBA

3 Answers3