# Dividing up a number randomly

#### abberyfarm

##### Well-known Member
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
ABCDEF
1No. tripstotal distanceIndividual distances
2220515
33305205
43251555
5440205510
Sheet1

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
The RandLen function I gave you a while ago gives one way.

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))

Hello 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?

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?

John

Hi,
try the random solution presented by the macro:
Code:
``````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.

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.

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

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
ABCDEFG
1No. tripstotal distanceIndividual distances
221091
33202414
4450219614
5550314393
6443716146
7166
811010
9215213
10860
Sheet1

Would you know why this is?

Thank you

John

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.

Last edited:
Hi,
I found quicker and better Random Solution for You, try:
Code:
``````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.

Replies
3
Views
632
Replies
11
Views
374
Replies
4
Views
587
Replies
1
Views
487
Replies
0
Views
80

### Forum statistics

1,203,397
Messages
6,055,165
Members
444,767
Latest member
bryandaniel5 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back