Results 1 to 8 of 8

20130112, 19:06 #1
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 173
 Thanks
 27
 Thanked 0 Times in 0 Posts
Userdefined 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=((mdmn)/(mxmn)),md,IF(rr<((mdmn)/(mxmn)),mn+SQRT(rr*(mxmn)*(mdmn)),mxSQRT((1rr)*(mxmn)*(mxmd))))"
'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

Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20130112, 20:14 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,243
 Thanks
 202
 Thanked 796 Times in 729 Posts
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=((mdmn)/(mxmn)),md,IF(rr<((mdmn)/(mxmn)),mn+SQRT(rr*(mxmn)* _ (mdmn)),mxSQRT((1rr)*(mxmn)*(mxmd)))) End Function
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

The Following User Says Thank You to RetiredGeek For This Useful Post:
mitchbvi (20130113)

20130113, 11:37 #3
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 173
 Thanks
 27
 Thanked 0 Times in 0 Posts
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

20130113, 15:30 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,243
 Thanks
 202
 Thanked 796 Times in 729 Posts
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.

The Following User Says Thank You to RetiredGeek For This Useful Post:
mitchbvi (20130113)

20130113, 15:34 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
I am surprised that you don't get a runtime error. VBA uses Rnd not Rand and Sqr not SQRT...
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
mitchbvi (20130113)

20130113, 16:57 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,218
 Thanks
 46
 Thanked 234 Times in 215 Posts
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$1A3)/(($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$1A3)^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*(xmin)/((maxmin)*(modemin)),2*(maxx)/((maxmode)*(maxmin)))) 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

The Following User Says Thank You to Maudibe For This Useful Post:
mitchbvi (20130113)

20130113, 19:37 #7
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 173
 Thanks
 27
 Thanked 0 Times in 0 Posts
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

20130113, 19:39 #8
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 173
 Thanks
 27
 Thanked 0 Times in 0 Posts
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