RANDBETWEEN with left/right bias?

[pharrcide]

Hello everyone,

Is there a way to use the RANDBETWEEN function and add bias towards the upper or lower bounds? I have used formulas such as ROUND(100*(RAND()*RAND()),0) to give me a left biased distribution and ROUND(100*(1-RAND()*RAND()),0) to give me a left biased distribution. However, I haven't seen a marriage of these two concepts.

For my example, I am trying to fill up a spreadsheet with random customer data. One of the columns is CREDIT SCORE. For this application I would use RANDBETWEEN(500-800). But this will give me a very flat, unbiased distribution of numbers between 500-800. Ideally, I would like very few scores near 500 and many more scores towards 800.

Any help is greatly appreciated!

 

[Scott Huish]

What about this?

=RANDBETWEEN(CHOOSE(RANDBETWEEN(1,3),500,600,700),800)

 

[joeu2004]

For my example, I am trying to fill up a spreadsheet with random customer data. One of the columns is CREDIT SCORE. For this application I would use RANDBETWEEN(500-800). But this will give me a very flat, unbiased distribution of numbers between 500-800. Ideally, I would like very few scores near 500 and many more scores towards 800.

Suppose you want about 20% to be 500-599, 30% to be 600-699 and 50% to be 700-800.

One way to implement the algorithm is:

=IF(RAND()<20%,RANDBETWEEN(500,599),
IF(RAND()<30%/80%,RANDBETWEEN(600,699),RANDBETWEEN(700,800)))

Since RAND and RANDBETWEEN are recalculated every time you edit any cell in the workbook, you might prefer to implement the algorithm in a macro.

Moreover, the result might not have exactly the desired distribution, due to the stochastic behavior of RAND.

If you want "exactly" that distribution (to the extent possible, considering quantization error), that is yet-another reason to implement the selection in a macro. I would use a very different algorithm in that case.

 

[pharrcide]

What about this?

=RANDBETWEEN(CHOOSE(RANDBETWEEN(1,3),500,600,700),800)

This solution won't work for my particular application because I need many different values to show in a histogram, but thank you for this answer. I have not seen this before and can use it other data creation sheets. Thank you for your help and the quick response.

 

[joeu2004]

What about this?
=RANDBETWEEN(CHOOSE(RANDBETWEEN(1,3),500,600,700),800)

I like the simplicity of an approach like this. But I believe it is difficult to determine and control the probability distribution. Right?

I believe the probability distribution is about 11% for 500-599, 28% for 600-699 and 61% for 700-800. Only "pharrcide" can decide if that is good enough.

 

[pharrcide]

Suppose you want about 20% to be 500-599, 30% to be 600-699 and 50% to be 700-800.

One way to implement the algorithm is:

=IF(RAND()<20%,RANDBETWEEN(500,599),
IF(RAND()<30%/80%,RANDBETWEEN(600,699),RANDBETWEEN(700,800)))

Since RAND and RANDBETWEEN are recalculated every time you edit any cell in the workbook, you might prefer to implement the algorithm in a macro.

Moreover, the result might not have exactly the desired distribution, due to the stochastic behavior of RAND.

If you want "exactly" that distribution (to the extent possible, considering quantization error), that is yet-another reason to implement the selection in a macro. I would use a very different algorithm in that case.

This works and will allow me to control the bias variance. Kicking myself for not thinking of this Thank you for your help and quick response.

 

[joeu2004]

Since RAND and RANDBETWEEN are recalculated every time you edit any cell in the workbook, you might prefer to implement the algorithm in a macro.

Moreover, the result might not have exactly the desired distribution, due to the stochastic behavior of RAND.

If you want "exactly" that distribution (to the extent possible, considering quantization error), that is yet-another reason to implement the selection in a macro. I would use a very different algorithm in that case.

If you are interested in VBA solutions, try one or both of the following VBA macros.

For both, set up a lookup table with the probability distribution. For example, in Sheet2:

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, align: right"]
[/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 64, align: center"]B
[/TD]
[TD="width: 64, align: center"]C
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64, align: right"]20%
[/TD]
[TD="width: 64, align: right"]500
[/TD]
[TD="width: 64"]min
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]600
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]700
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]
[/TD]
[TD="align: right"]801[/TD]
[TD]max+1
[/TD]
[/TR]
</tbody>[/TABLE]

Modify the "lookupTable" definition in the macro. In this case:

Const lookupTable As String = "Sheet2!A1:B4"

Select a range of cells for the random results, for example Sheet1!A1:A100.

Use alt+F8 to select and run one of the macros.

The "exactDistrib" macro generates 100 random numbers with exactly the distribution specified in "lookupTable", namely 20% between 500 and 599, 30% between 600 and 699, and 50% between 700 and 800.

The "approxDistrib" macro generates 100 random numbers between 500 and 800 according to the distribution specified in "lookupTable". It behaves very similar to the RAND/RANDBETWEEN formula that I offered. Consequently, the percentage of 500-599, 600-699 and 700-800 will be close to the distribution, but it varies. For example, after one execution, the resulting distribution was 25%, 31% and 44% respectively.

Sub approxDistrib()
    Const lookupTable As String = "Sheet2!A1:B4"  '***CUSTOMIZE***
    Static didRandomize As Boolean
    Dim t As Variant, r As Range
    Dim nt As Long, nr As Long, nd As Long
    Dim i As Long, x As Long
    
    If Not didRandomize Then Randomize: didRandomize = True
    
    ' set up lookup tables
    t = Range(lookupTable)
    nt = UBound(t, 1)
    ' d(i) = cumulative distribution
    nd = nt - 1
    ReDim d(1 To nd) As Double
    d(1) = 0
    For i = 1 To nd - 1: d(i + 1) = d(i) + t(i, 1): Next
    
    ' do random selection
    Set r = Selection
    nr = r.Count
    ReDim res(1 To nr, 1 To 1) As Long
    For i = 1 To nr
        x = WorksheetFunction.Match(Rnd, d, 1)
        res(i, 1) = Int(t(x, 2) + Rnd * (t(x + 1, 2) - t(x, 2)))
    Next
    r = res
End Sub

Sub exactDistrib()
    Const lookupTable As String = "Sheet2!A1:B4"  '***CUSTOMIZE***
    Static didRandomize As Boolean
    Dim t As Variant, r As Range, tmp As Variant
    Dim nt As Long, nr As Long, n As Long
    Dim i As Long, j As Long, k As Long, x As Long
    Dim s As Long, p As Double, d As Double
    
    If Not didRandomize Then Randomize: didRandomize = True
    
    ' get lookup tables
    t = Range(lookupTable)
    nt = UBound(t, 1)
    
    ' do random selection
    Set r = Selection
    nr = r.Count
    ReDim res(1 To nr, 1 To 2) As Variant
    p = 0: s = 0: k = 0
    For i = 1 To nt - 1
        p = p + t(i, 1)
        n = WorksheetFunction.Round(nr * p, 0) - s
        s = s + n
        d = t(i + 1, 2) - t(i, 2)
        For j = 1 To n
            k = k + 1
            res(k, 1) = Int(t(i, 2) + Rnd * d)
            res(k, 2) = Rnd
        Next j
    Next i
    
    ' randomly sort results
    For i = 1 To nr - 1
        k = i
        For j = i + 1 To nr
            If res(j, 2) < res(k, 2) Then k = j
        Next j
        If k <> i Then
            tmp = res(i, 1): res(i, 1) = res(k, 1): res(k, 1) = tmp
            tmp = res(i, 2): res(i, 2) = res(k, 2): res(k, 2) = tmp
        End If
    Next i
    r.Resize(nr, 1) = res
End Sub