# Thread: User-defined function: Triangular Distribution

1. ## User-defined function: Triangular Distribution

I am trying to create a used defined function that will calculate a Triangular distribution. I actually thought I had it working but the best I can do is return the formula to the cell the function is called from. That is entered as text.

this is the function as written.
Public Function Triang4(mn, md, mx)

rr = "=rand()"
Triang4.Value = "= IF(rr=((md-mn)/(mx-mn)),md,IF(rr<((md-mn)/(mx-mn)),mn+SQRT(rr*(mx-mn)*(md-mn)),mx-SQRT((1-rr)*(mx-mn)*(mx-md))))"

'ActiveCell.Value = x
'Calculate
End Function

I have tried using active cell as the result cell. I have dropped "value" off the result so Triang4. The formula does work if entered in a cell and I have named the cells wi the mn,md,mx and rr values. I cannot call the rand function from within the formula as the number will change each time it is called. The error returned is value if I just use the name of the function, using value active cell etc.

Thanks for any suggestions

Peter

2. Peter,

I think this is what you want

Code:
```Public Function Triang4(mn, md, mx) as Double

Dim rr as Double

rr = Rand()
Triang4 =  IF(rr=((md-mn)/(mx-mn)),md,IF(rr<((md-mn)/(mx-mn)),mn+SQRT(rr*(mx-mn)* _
(md-mn)),mx-SQRT((1-rr)*(mx-mn)*(mx-md))))
End Function```
Notes:
1. to use place this in cell: =Triang4( mm, md, mx)
Where mm, md, mx are either numbers or cell references.
2. I would also declare types for mn, md, mx, e.g.
Public Function Triang4(mn as Double, md as Double, mx as Double) as Double
of course I'm just guessing not being a math guy but you could of course use Integer, Long, etc.

HTH

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

mitchbvi (2013-01-13)

4. Hi HTH

First my thanks for taking the trouble to reply. Unfortunately it did not work my function now looks exactly as you suggested but it still returns the "Value" error. I had named the cells for the purpose of testing as mn, md, and mx so i dumped the names and tried just with cell references. I know the formula works as I have entered that in a cell by itself . I also ran a sub routine with the variables defined and that works (not much use of course). I am thinking maybe what I need is a function that calls a sub routine not sure if that works but will try. Again many thanks.

Peter

5. Peter,

HTH = Hope that Helps
RG = Me

Could you give me values {actual numbers} that you want to pass the function and what result the function should return or you could post an example workbook. Then maybe I can figure what is going on.

6. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

mitchbvi (2013-01-13)

7. I am surprised that you don't get a runtime error. VBA uses Rnd not Rand and Sqr not SQRT...

Steve

8. ## The Following User Says Thank You to sdckapr For This Useful Post:

mitchbvi (2013-01-13)

9. This was posted as a solution on an internet post. Perhaps it can help you.

If A1, B1, and C1 contain respectively, the minimum, mode, and maximum, of a
triangular distribution, and if A3 contains a value x, use the following
formulas:

For the Probability Density Function, P(x),

=MAX(0,IF(A3<\$B\$1,2*(A3-\$A\$1)/((\$C\$1-\$A\$1)*(\$B\$1-\$A\$1)),2*(\$C\$1-A3)/((\$C\$1-\$B\$1)*(\$C\$1-\$A\$1))))

and for the Cumulative Distribution Function, D(x)

=IF(A3<\$A\$1,0,IF(A3<\$B\$1,(A3-\$A\$1)^2/((\$C\$1-\$A\$1)*(\$B\$1-\$A\$1)),
IF(A3<=\$C\$1,1-(\$C\$1-A3)^2/((\$C\$1-\$B\$1)*(\$C\$1-\$A\$1)),1)))

I converted it to a function and got the same results. You can do the same for the Cumulative Distribution Function by replacing the formula and a little tweak.
HTH,
Maud
Code:
```Public Function Tridist(min As Double, mode As Double, max As Double, x As Double) As Double
'Tridist=MAX(0,IF(x<mode,2*(x-min)/((max-min)*(mode-min)),2*(max-x)/((max-mode)*(max-min))))
Dim integral As Double
If x < mode Then
integral = 2 * (x - min) / ((max - min) * (mode - min))
Else:
integral = 2 * (max - x) / ((max - mode) * (max - min))
End If
Tridist = WorksheetFunction.max(0, integral)
End Function```

10. ## The Following User Says Thank You to Maudibe For This Useful Post:

mitchbvi (2013-01-13)

11. Thanks Steve

I think it was because I was trying to run it as if it were an Excel Function. Have your reply helped me sort it out I realised I just needed to write it as VBA.
So this works for anyone that is interested.
Public Function TriangD(mn, md, mx)
rr = Rnd()
Calculate
' Triangular
If rr = ((md - mn) / (mx - mn)) Then
TriangD = md
Else
If rr < ((md - mn) / (mx - mn)) Then
TriangD = mn + Sqr(rr * (mx - mn) * (md - mn))
Else
TriangD = mx - Sqr((1 - rr) * (mx - mn) * (mx - md))
End If
End If
End Function

Thanks Peter

12. Hi RG

HTH must be my age.

This now works.
Public Function TriangD(mn, md, mx)
rr = Rnd()
Calculate
' Triangular
If rr = ((md - mn) / (mx - mn)) Then
TriangD = md
Else
If rr < ((md - mn) / (mx - mn)) Then
TriangD = mn + Sqr(rr * (mx - mn) * (md - mn))
Else
TriangD = mx - Sqr((1 - rr) * (mx - mn) * (mx - md))
End If
End If
End Function

Thanks again

Peter

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•