# Need excel formula to randomaly distribute.

#### pedie

##### Well-known Member
Hi, I need help excel formula to randomaly distribute/rather divide the total of 29 to 7 users.
something like the below table...

Thanks.

Randbetween 3 to 7 but the answer sum total should be 29.

<TABLE style="WIDTH: 64pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=86><COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17 width=35>
1
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=51>
5
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>
2
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
3
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>
3
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
4
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>
4
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
3
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>
5
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
4
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>
6
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
4
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>
7
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
6
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>
Total
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
29
</TD></TR></TBODY></TABLE>​

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### shg

##### MrExcel MVP
If a UDF will work,

Code:
``````'========================= S t r i n g  C u t t i n g ========================
' These routines generate random numbers totalling a desired sum with the
' (correct) Beta distribution

Function RandLen(dTot As Double, _
Optional dMin As Double = 0, _
Optional ByVal iSig As Long = 0, _
Optional bVolatile As Boolean = False) As Variant
' shg 2011
' UDF wrapper for aiRandLen

If bVolatile Then Application.Volatile

With Application.Caller
If .Rows.Count > 1 And .Columns.Count > 1 Then
RandLen = CVErr(xlErrRef)

ElseIf .Columns.Count > 1 Then
RandLen = aiRandLen(dTot, .Columns.Count, dMin, iSig)

Else
RandLen = WorksheetFunction.Transpose(aiRandLen(dTot, .Rows.Count, dMin, iSig))
End If
End With
End Function

Function aiRandLen(ByVal dTot As Double, _
nNum As Long, _
Optional ByVal dMin As Double = 0, _
Optional ByVal iSig As Long = 307) As Double()
' shg 2011
' Applies string-cutting to return an array of nNum
' numbers totalling dTot, with each in the range
'           dMin <= number <= dTot - nNum * dMin

' Each number is rounded to iSig places

Dim i           As Long
Dim j           As Long
Dim dRnd        As Double
Dim dSig        As Double
Dim col         As Collection

If nNum < 1 Or dTot < nNum * dMin Then Exit Function
dSig = 10 ^ -iSig

With New Collection
dTot = WorksheetFunction.Round(dTot, iSig)
dMin = WorksheetFunction.Round(dMin, iSig)
.Add Item:=dTot - nNum * dMin
' create the cuts
For i = 1 To nNum - 1
dRnd = Int(Rnd * ((dTot - nNum * dMin) / dSig)) * dSig

' insertion-sort the cut
For j = .Count To 1 Step -1
If .Item(j) <= dRnd Then
Exit For
End If
Next j
Next i

' measure the lengths
For i = 1 To nNum
adOut(i) = .Item(i + 1) - .Item(i) + dMin
Next i
End With
End Function``````

Then for you example, select B1:B7 and array-enter

=RandLen(29, 3)

#### pedie

##### Well-known Member
Brian, Shg,....thanks guys...works perfect. Thanks for the idea...that was a good one...

And thanks for the Vid. link too.

Regards
Pedie

Replies
1
Views
72
Replies
0
Views
226
Replies
7
Views
94
Replies
5
Views
287
Replies
13
Views
691