# Can Excel produce a random distribution of a fixed size and a fixed sum?

We need an Excel macro or formula or something that can produce a random distribution of integers such that the result set has a fixed size and a fixed sum. More specifically, we want to simulate a month's worth of integer data-points (hence a fixed size) whose total represents demand for that month (hence the fixed sum). Does such a feature already exist in Excel or from a third-party? If not, any tips on writing one?

I am looking for something that looks like this:

``````GetSetWithSum( count: 5, sum: 36)
``````

The result of such a formula would be something like this:

``````5
13
9
6
3
``````
-
This really should be at stackoverflow, not superuser. That site is geared towards programming, and you'd be likely to get more answers over there. – eidylon Nov 9 '10 at 21:21
I came from StackOverflow. If your question involves using an application that isn't an IDE, it is shunted toward SuperUser or ServerFault. – flipdoubt Nov 9 '10 at 21:25

The following code works, but it does have the possibility of returning 0 for one or more of the values, depending on how quickly it reaches the required total (using your example, if the first random number is 36 then the other four will be 0). the fine folks on Stack OverFlow may be better at writing code which prevents this than I.

Core Random Generator Code

``````Sub RandomNumbers(ByVal ReqNum As Integer, ByVal ReqSum As Integer, Output As Collection)
Dim NewNum As Integer, SumNum As Integer
Dim I As Integer

SumNum = 0
Do Until (SumNum = ReqSum) Or (Output.Count = (ReqNum - 1))
Randomize Timer
NewNum = Int((ReqSum - SumNum) * Rnd + 1)
SumNum = SumNum + NewNum
Loop
If Output.Count < ReqNum Then
If SumNum < ReqSum Then
Else
NewNum = Output.Count
For I = (NewNum + 1) To ReqNum
Next I
End If
End If
End Sub
``````

you can call this Sub using code with something along the lines of this. This code simply puts the random values into column A.

``````Dim Numbers As New Collection, NumNum As Integer, SumofNum As Integer

Range("A:A").ClearContents
NumNum = 5
SumofNum = 36
Call RandomNumbers(NumNum, SumofNum, Numbers)
For SumofNum = 1 To Numbers.Count
NumNum = Numbers(SumofNum)
Cells(SumofNum, 1).Value = NumNum
Next SumofNum
``````
-
Uh, where does this code go? – flipdoubt Nov 9 '10 at 16:56
the code would go into a module. open excel and press ALT+F11 to open the Visual Basic Editor. on the left side of the window it will display a VB Project for each open workbook. right-click on the workbook you want to store the macro in and select Insert > Module. Copy the first code block into there. the second block of code is purely an example. something similar might be placed into the code for a button, or used within another macro. – Xantec Nov 9 '10 at 17:06
Your solution works but, as you pointed out, most of the values it returns are zeros because it reaches the required sum too soon. I am a .NET developer and think I could come up with a solution in C#, but I wanted to know if there is an easier way in Excel. Do you know how to call .NET code from Excel or can you point me to such a tutorial? – flipdoubt Nov 9 '10 at 19:20
Sorry. VBA in Excel is the extent of my programming prowess unfortunately. I would imagine it might be possible to create your own Type Library and register on your computer. then you'd just need to add it as a reference in the VBE (Tools > References). however, that would of course then require that your custom library be registered on the computer of anyone who wanted to run the macro. – Xantec Nov 9 '10 at 21:09