How to generate a series of randomly distributed numbers in sequence using Excel

Question

I have a number that I would like to normally distribute into 15 bins or cells. And I want the 15 numbers to be in sequence

Example:

Number to be distributed - 340 Output: 6 9 12 16 20 24 27 30 32 32 32 30 27 24 20

... yes, my series is not perfectly distributed but currently I'm doing this by,

first create a linear series of number 1 2 3 4 ... 14 15
Then use Norm.Dist(x,mean,standard_dev) to generate a series of z-score values where x=1, 2, 3 .. 14, 15
Then I scale those values using similar triangles ie. x1/y1=x2/y2 where x1=z-score; y1=sum(z-scores); x2=number I want; y2=340

Is there a better way to do this? because I have to generate multiple matrix for this and something is not quite right...

This really isn't very clear. On the one hand, you seem to want to generate some random numbers, but what the distribution of those numbers are is not very obvious. On the other hand, your actual sample numbers are completely deterministic (contradicting the phrase "randomly distributed" in the question title). Please explain. — John Coleman, Apr 10 '17 at 23:17
OK sorry, let me clarify - I would like to generate a series of numbers that are normally distributed in sequence. And the sum of these numbers should be 340. — RYY, Apr 10 '17 at 23:26
1) Normally distributed require a mean and standard deviation -- what are these in this case? 2) You seem to want integer values, the probability that a normal variable is an integer is zero. 3) The constraint that the sum is 340 seems inconsistent with being normally distributed. They definitely won't be independent. — John Coleman, Apr 10 '17 at 23:33
1) I would like to have the flexibility to modify the mean and std so I can modify my results to fit a specific curve. 2) I don't need integer values, as long as the sequence of the numbers resembles a normal distribution, I can always scale these numbers later. 3) Yes, the numbers generated will be dependent on each other since the sum of these numbers are fixed. — RYY, Apr 10 '17 at 23:41
Possible duplicate of [Produce random numbers with specific distribution with Excel](http://stackoverflow.com/questions/6241784/produce-random-numbers-with-specific-distribution-with-excel) — Craig, Apr 11 '17 at 03:14
@Craig related but the additional constraint is enough to make it a non-duplicate. — John Coleman, Apr 11 '17 at 14:56

score 1 · Accepted Answer · answered Apr 11 '17 at 00:55

Here is a hit-and-miss approach that searches for a random vector of independent normal variables whose sum falls within a given tolerance of the target sum and, if so, rescales all of the numbers so as to equal the sum exactly:

Function RandNorm(mu As Double, sigma As Double) As Double
    'assumes that Ranomize has been called
    Dim r As Double
    r = Rnd()
    Do While r = 0
        r = Rnd()
    Loop
    RandNorm = Application.WorksheetFunction.Norm_Inv(r, mu, sigma)
End Function

Function RandSemiNormVect(target As Double, n As Long, mu As Double, sigma As Double, Optional tol As Double = 1) As Variant
    Dim sum As Double
    Dim rescale As Double
    Dim v As Variant
    Dim i As Long, j As Long

    Randomize
    ReDim v(1 To n)
    For j = 1 To 10000 'for safety -- can increase if wanted
        sum = 0
        For i = 1 To n
            v(i) = RandNorm(mu, sigma)
            sum = sum + v(i)
        Next i
        If Abs(sum - target) < tol Then
            rescale = target / sum
            For i = 1 To n
                v(i) = rescale * v(i)
            Next i
            RandSemiNormVect = v
            Exit Function
        End If
    Next j
    RandSemiNormVect = CVErr(xlErrValue)
End Function

Tested like this:

Sub test()
    On Error Resume Next
    Range("A1:A15").Value = Application.WorksheetFunction.Transpose(RandSemiNormVect(340, 15, 20, 3))
    If Err.Number > 0 Then MsgBox "No Solution Found"
End Sub

Typical output with those parameters:

On the other hand, if I change the standard deviation to 1, I just get the message that no solution is found because then the probability of getting a solution within the specified tolerance of the target sum is vanishingly small.

How to generate a series of randomly distributed numbers in sequence using Excel

1 Answers1