Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    173
    Thanks
    27
    Thanked 0 Times in 0 Posts

    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. Subscribe to our Windows Secrets Newsletter - It's Free!

    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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 765 Times in 699 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=((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
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


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

    mitchbvi (2013-01-13)

  5. #3
    2 Star Lounger
    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

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 765 Times in 699 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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


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

    mitchbvi (2013-01-13)

  8. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    I am surprised that you don't get a runtime error. VBA uses Rnd not Rand and Sqr not SQRT...

    Steve

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

    mitchbvi (2013-01-13)

  10. #6
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 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$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

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

    mitchbvi (2013-01-13)

  12. #7
    2 Star Lounger
    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

  13. #8
    2 Star Lounger
    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

Posting Permissions

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