Need excel formula to randomaly distribute.

[pedie]

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

 

[shg]

If a UDF will work,

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

 

[meldoc]

Hi Pedie

Not quite what you ask for, but maybe.

Excel Workbook
	A	B
1		29
2
3		4
4		4
5		4
6		4
7		4
8		5
9		4
10	Totel	29
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B3	=ROUND(($B$1-SUM(B$2:B2))/ROWS(B3:B$9),0)
B10	=SUM(B3:B9)

See the links.
http://www.youtube.com/user/ExcelIsFun#p/search/0/n2Vzd9SEDsc
http://www.mrexcel.com/forum/showthread.php?t=485265

 

[pedie]

Brian, Shg,....thanks guys...works perfect. Thanks for the idea...that was a good one...

And thanks for the Vid. link too.


Regards
Pedie