Thread: Probability to turn i into range

I am trying to create a procedure to create a set of numbers based on percentages, and I just don't have the probability knowledge to get this:

I will be iterating from i to some number (lets say 100).
I have the following table:

Code:

Quantity_UPB	          Percent
$0 < UPB < $50,000	            5
$50,000 <= UPB < $100,000	   15
$100,000 <= UPB < $200,000     45
$200,000 <= UPB < $300,000	   20
$300,000 <= UPB < $417,001	   15

How can I go through i = 1 to 100 and make sure each i has a 5% chance of being 1-49,999, a 15% chance of being 50,000 - $99,999, ect.

Thanks

You want a random number that 5% of the time will fall 0 to 50K, 15% will fall 50 to 100K, ...?

You got it, thanks!

Using a formula,

Code:

       ---A---- B ---C---- --D--
   1   Deviate      Bin     Pct 
   2    73,800          0   0.0%
   3   315,715     50,000   4.4%
   4    83,938    100,000  15.1%
   5   146,099    200,000  47.4%
   6   134,597    300,000  19.0%
   7   269,597    450,000  14.1%
   8   448,470              0.0%
   9   107,785                  
  10   392,260                  
  11   315,764                  
  12   104,481

The formula in A2:A1001 is

=INT(LOOKUP(RAND(), {0,5,20,65,85}/100, RAND() * {50,50,100,100,150} + {0,50,100,200,300}) * 1000)

The frequency formula at right shows the distribution for one particular set of numbers.

Ok, I see how that lookup function works, very nice. I am looking to run quite a few of these in vba. Is there a vba method or function that works the same way?

This is considered a probability problem, right?

Try this:

Code:

Sub Demo()
    Dim i As Long
    
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        
        For i = 1 To 1000
            Cells(Rows.Count, "B").End(xlUp)(2).Value = RandGugg(Array(0.05, 0.2, 0.65, 0.85), _
                                                                 Array(50000, 100000, 200000, 300000, 450000))
        Next i
    
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Function RandGugg(avdCum As Variant, avdVal As Variant) As Double
    ' VBA function ONLY
    
    ' avdCum contains the CUMULATIVE density function, e.g.,
     '      {0.05,0.2,0.65,0.85}
     ' => The first value is omitted and IMPLICITLY 0 (would appear in the formula version)
     ' => The last value is IMPLICITLY 1 (wouldn't appear in the formula version either)
     ' => Values MUST BE strictly monotone ascending
     
     ' avdVal contains the associated values to be interpolated,
     ' and must have ONE MORE value than avdCum
     
    Dim dRnd        As Double
    Dim i           As Long
    Dim iLB         As Long
    
    iLB = LBound(avdCum)
    If LBound(avdVal) <> iLB Then Exit Function
    If UBound(avdVal) <= UBound(avdCum) Then Exit Function
    
    dRnd = CDbl(Rnd)
    
    If dRnd <= avdCum(iLB) Then
        dRnd = Rnd
        RandGugg = dRnd * avdVal(iLB)
    Else
        i = WorksheetFunction.Match(dRnd, avdCum)
        dRnd = Rnd
        RandGugg = dRnd * avdVal(i + iLB) + (1 - dRnd) * avdVal(i + iLB - 1)
    End If
End Function

Wow, outstanding!

I'm actually a math major but never took a probability class (took two optimization research classes instead, which is how I know the Solver Add-In so well). I had to do a little research on wikipedia to understand how your function works, but I have the hang of it. You must have some formal education in math, where'd you go to school?

Thanks a lot, you deserve 10 reputation adds for that!

You're welcome.

Grab it again, I made a change to accommodate varying base for the arrays.

Actually -- stand by -- both versions have an insidious error.

EDIT: OK, fixed.

Got it, thanks