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:
Please read my Disclaimer.
1
Function sbRandCDFInv(dParam1 As Double, dParam2 As Double, _
2
    dParam3 As Double, Optional dRandom = 1#) As Double
3
'Source: https://berndplumhoff.gitbook.io/sulprobil/excel/excel-vba-solutions/sbrandcdfinv
4
'03-Nov-2020 PB V0.2
5
Static bRandomized As Boolean
6
Dim dRand As Double
7
If dRandom < 0# Or dRandom > 1# Then
8
    sbRandCDFInv = CVErr(xlErrValue)
9
    Exit Function
10
End If
11
If Not bRandomized Then
12
    Randomize
13
    bRandomized = True
14
End If
15
If dRandom = 1# Then
16
    dRand = Rnd()
17
Else
18
    dRand = dRandom
19
End If
20
'Here you need to define the inverse of the cumulative distribution function
21
sbRandCDFInv = sbRandTriang(dParam1, dParam2, dParam3, dRand)
22
End Function
Copied!
This function will provide with a stratified sample:
Other examples would be sbRandCauchy or sbRandTriang directly.
If there is no explicit form of a cumulative distribution function inverse then you can apply a linear approximation with the probability distribution function sbRandPDF.

sbRandCauchy

sbRandCumulative

Last modified 1yr ago

Copy link