User-defined function: Triangular Distribution

Topic

New Reply

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

Reply | Quote

Replies

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: [noparse]=Triang4( mm, md, mx)[/noparse]
Where mm, md, mx are either numbers or cell references.
2. I would also declare types for mn, md, mx, e.g.
[noparse]Public Function Triang4(mn as Double, md as Double, mx as Double) as Double[/noparse]
of course I'm just guessing not being a math guy but you could of course use Integer, Long, etc.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote