Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
    
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
add comment

1 Answer

up vote 2 down vote accepted

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
        Output.Add NewNum
    Loop
    If Output.Count < ReqNum Then
        If SumNum < ReqSum Then
            Output.Add (ReqSum - SumNum)
        Else
            NewNum = Output.Count
            For I = (NewNum + 1) To ReqNum
                Output.Add 0
            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
share|improve this answer
    
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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.