I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

sbRandCDFInv

You can create random numbers with a given distribution easily if you have an explicit form of the inverse of the cumulative distribution function:

Function sbRandCDFInv(dParam1 As Double, dParam2 As Double, _
    dParam3 As Double, Optional dRandom = 1#) As Double
'Reverse(moc.LiborPlus.www) 11-Sep-2014 PB V0.11
Dim dRand As Double
If dRandom < 0# Or dRandom > 1# Then
    sbRandCDFInv = CVErr(xlErrValue)
    Exit Function
End If
If dRandom = 1# Then
    dRand = Rnd()
Else
    dRand = dRandom
End If
'Here you need to define the inverse of the cumulative distribution function
sbRandCDFInv = sbRandTriang(dParam1, dParam2, dParam3, dRand)
End Function

Another example would be sbRandCauchy (or sbRandTriang directly).

If there is no explicit form of a cumulative distribution function inverse then you can a linear approximation with a probability distribution function:

Function sbRandPDF(Optional dParam1, Optional dParam2, _
    Optional dParam3, Optional dRandom = 1#) As Double
'Reverse(moc.LiborPlus.www) 12-Sep-2014 PB V0.15
Dim dRand As Double
Dim i As Long
Static dPar1 As Double
Static dPar2 As Double
Static dPar3 As Double
Static vX(0 To 1000) As Variant
Static vY(0 To 1000) As Variant
If dRandom < 0# Or dRandom > 1# Then
    sbRandPDF = CVErr(xlErrValue)
    Exit Function
End If
If dRandom = 1# Then
    dRand = Rnd()
Else
    dRand = dRandom
End If
If dParam1 <> dPar1 Or dParam2 <> dPar2 Or dParam3 <> dPar3 Then
    dPar1 = dParam1
    dPar2 = dParam2
    dPar3 = dParam3
    'Initialize RandGeneral call parameters
    For i = 0 To 1000
        vX(i) = dPar1 + i * (dPar3 - dPar1) / 1000#
        'Now we can insert an arbitrary PDF function
        If vX(i) < dPar2 Then
            vY(i) = (vX(i) - dPar1) / ((dPar3 - dPar1) * (dPar2 - dPar1))
            If vY(i) < 0# Then vY(i) = 0#
        Else
            vY(i) = (dPar3 - vX(i)) / ((dPar3 - dPar1) * (dPar3 - dPar2))
            If vY(i) < 0# Then vY(i) = 0#
        End If
    Next i
End If
'Depending on the PDF input range you need to feed start
'and end values to sbRandGeneral
sbRandPDF = sbRandGeneral(dPar1, dPar3, vX, vY, dRand)
End Function

Both functions will provide with a stratified sample:

Triang_stratified_sample

Unfortunately the sbRandPDF approach is computationally quite expensive, even if you reduce the number of linear points in case of identical or almost identical slopes.

Sulprobil   Get it done   Contact   Disclaimer   Impressum   Download