Random generator help

[benwork]

Hi all,

I'm looking for some help with the random generator function.

I have a value in B1 which will change depending on the date. For this example, say the value is 3

I then have a possible 3 streams that this number could go into

Stream 1 (B2)
Stream 2 (B3)
Stream 3 (B4)

I would like to be able to randomly distribute the value in B1 across the three streams. For example if could be,

Stream 1 (B2) - 1
Stream 2 (B3) - 1
Stream 3 (B4) - 1

or

Stream 1 (B2) - 0
Stream 2 (B3) - 2
Stream 3 (B4) - 1

or

Stream 1 (B2) - 0
Stream 2 (B3) - 0
Stream 3 (B4) - 3

etc

They would need to be randomly generated and the sum of the three streams would need to equal B1

Can anyone assist with this ?

Regards

Ben

 

[mikerickson]

I'm wondering what the rules are for this distribution.

One way would be to put

=RANDBETWEEN(0, A1) in B2
=RANDBETWEEN(0, A1-B2) in B3
=A1-B1-B2 in B4

 

[benwork]

I'm wondering what the rules are for this distribution.

One way would be to put

=RANDBETWEEN(0, A1) in B2
=RANDBETWEEN(0, A1-B2) in B3
=A1-B1-B2 in B4

Thanks for the reply,

I tried that before, but it puts a bias on stream 1

It does need to be random across the three streams

Cheers

Ben

 

[mikerickson]

Then I wouldn't use formulas. I'd use VBA to 1) get three numbers as above that sum to what you want 2) randomly re-order those numbers.
Here's a UDF that you could use.
Select B2:B4 and enter the array formula =TRANSPOSE(RandomSumTo(A1))

Function RandomSumTo(SoughtTotal As Long) As Variant
    Dim Numbers(1 To 3) As Long
    Dim randIndex As Long, temp As Long, i As Long
    
    Numbers(1) = WorksheetFunction.RandBetween(0, SoughtTotal)
    Numbers(2) = WorksheetFunction.RandBetween(0, SoughtTotal - Numbers(1))
    Numbers(3) = SoughtTotal - Numbers(1) - Numbers(2)
    For i = 1 To 3
        randIndex = WorksheetFunction.RandBetween(1, 3)
        temp = Numbers(randIndex)
        Numbers(randIndex) = Numbers(i)
        Numbers(i) = temp
    Next i
    RandomSumTo = Numbers
End Function

 

[benwork]

Worked perfectly

Much appreciated !

Cheers

Ben