Dividing up a number randomly

[abberyfarm]

Hi there,

Would anybody know how I could divide a number up randomly into a certain number of smaller numbers?

For example I have (1) the number of journeys and (2) the total distance travelled. I want to divide up the total distance travelled into individual journey distances.

Here is an example

Excel Workbook
	A	B	C	D	E	F
1	No. trips	total distance	Individual distances
2	2	20	5	15
3	3	30	5	20	5
4	3	25	15	5	5
5	4	40	20	5	5	10
Sheet1

Thank you for your help

 

[shg]

The RandLen function I gave you a while ago gives one way.

 

[West Man]

Perhaps: in C2 =RANDBETWEEN(1,B2-A2+1)
in D2 =IF(A2=2,B2-C2,RANDBETWEEN(1,B2-C2-A2+1))
in E2 =IF(A2=2,"",IF(A2=3,B2-C2-D2,RANDBETWEEN(1,B2-C2-D2-A2+1)))
in F2 =IF(A2<4,"",B2-SUM(C2:E2))

 

[abberyfarm]

Hello Shg,

Thank you for your reply.

I'm looking at the RandLen - I hope I don't sound stupid but does this function not generate a certain number of random numbers that sum to a total?

I've decided to try a different approach to the problem because I could not figure out if or how you could generate numbers from a distribution that sum to a certain number.

So I decided to just generate a total distance from a distribution and divide it up by randomly by the number of journeys. I don't understand how the RandLn can do this?

Thanks for your help

John

 

[hurgadion]

Hi,
try the random solution presented by the macro:

Sub RanDomSum()
Dim i&, j&, l&, a&, b&

Randomize
For i = 2 To 5

  a = Cells(i, 1).Value
  b = Cells(i, 2).Value
  ReDim tbl(1 To a)
  
  For l = 1 To 100000
    For j = 1 To a
      tbl(j) = Int(1 + Rnd * b)
    Next j
    If Application.Sum(tbl) = b Then
       Cells(i, 3).Resize(, a) = tbl
       Exit For
    End If
  Next l

Next i
End Sub

Best regards.

 

[shg]

I'm looking at the RandLen - I hope I don't sound stupid but does this function not generate a certain number of random numbers that sum to a total?

... So I decided to just generate a total distance from a distribution and divide it up by randomly by the number of journeys. I don't understand how the RandLn can do this?

I don't understand how what you want in the second paragraph is different than what the code does in the first.

 

[abberyfarm]

Hi Shg.

I didn't explain it very well. The first method that I was trying was to generate a certain number of random numbers (individual journey distances) from the same distribution (gamma) that sum to a total (the total distance travelled for the day).The RandLn function does this but only for a beta distribution. I don't think that I can use this method because of infinitely divisible property that I talked about in the other thread.

So my new approach is to just generate a total distance travelled for the day. Then generate the number of journey and then divide the total distance up randomly by the number of journeys. That way I avoid the problem above of summing up random numbers to a given total which could result is a different distribution, beta + beta is not equal to beta.

Thanks

John

 

[abberyfarm]

Hi hurgadian,

Thanks for your reply. This seems to be working well. Do you mind me asking if the process completely random? Also if the number in Column A is greater than 7 it does not seem to work all the time? But sometimes it does?

Here is an example

Excel Workbook
	A	B	C	D	E	F	G
1	No. trips	total distance	Individual distances
2	2	10	9	1
3	3	20	2	4	14
4	4	50	21	9	6	14
5	5	50	31	4	3	9	3
6	4	43	7	16	14	6
7	1	6	6
8	1	10	10
9	2	15	2	13
10	8	60
Sheet1

Would you know why this is?

Thank you

John

 

[hurgadion]

Change, please, the number 100 000 on 10 000 000. My solution is not optimal, iIwill think about a quicker and better solution,

Best regards.

 

[hurgadion]

Hi,
I found quicker and better Random Solution for You, try:

Sub RandomQuicker()
Dim a&, b&, c&, i&, j&, tbl()
Randomize
For i = 2 To 10
   If Cells(i, 1).Value = 1 Then
     Cells(i, 3).Value = Cells(i, 2).Value
   Else
     If Cells(i, 1).Value = 2 Then
       Cells(i, 3).Value = Int(1 + Rnd * Cells(i, 2).Value)
       Cells(i, 4).Value = Cells(i, 2).Value - Cells(i, 3).Value
     Else
       a = Cells(i, 1).Value
       b = Cells(i, 2).Value
       ReDim Preserve tbl(1 To a - 1)
       
       For j = 1 To a - 1
         tbl(j) = Int((1 + b * Rnd))
       Next j
       
       c = Application.Sum(tbl)
       
       For j = 1 To a - 1
         tbl(j) = Int(tbl(j) / (1.1 * c) * b)
       Next j
       
       c = Application.Sum(tbl)
       
       ReDim Preserve tbl(1 To a)
       tbl(a) = b - c
       Cells(i, 3).Resize(, UBound(tbl)) = tbl
     End If
   End If
Next i
End Sub

Best regards.