Need excel formula to randomaly distribute.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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>​
 

Some videos you may like

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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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
    Dim adOut()     As Double
 
    If nNum < 1 Or dTot < nNum * dMin Then Exit Function
    ReDim adOut(1 To nNum)
    dSig = 10 ^ -iSig
 
    With New Collection
        dTot = WorksheetFunction.Round(dTot, iSig)
        dMin = WorksheetFunction.Round(dMin, iSig)
        .Add Item:=0
        .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
                    .Add Item:=dRnd, After:=j
                    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
    aiRandLen = adOut
End Function

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

=RandLen(29, 3)
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Brian, Shg,....thanks guys...works perfect.:) Thanks for the idea...that was a good one...

And thanks for the Vid. link too.


Regards
Pedie
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,591
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top