Random numbers from specific range with fixed sum in Excel

Question

I want to create a number of random columns in Excel with those characteristics:

• Each column has 9 cells
• Each cell is either 0, 1, or 2
• Each column has SUM = 10

I tried creating 9 random numbers in column A and then use ROUND(B1/SUM(B$1:B$9);1)*10 for the columns but due to ROUND (I think) it is not completely correct as not all columns have sum=10 (some have 8 others 10 e.t.c.)

For example:

• Column B: 0,1,1,1,1,1,1,2,2
• Column C: 0,0,1,1,1,1,2,2,2
• Column D: 0,0,0,1,1,2,2,2,2
• Column E: 0,0,0,0,2,2,2,2,2

and so on, numbers in any order like

• Column Z: 1,1,2,0,1,1,1,1,2

Answer 1

Because of the constraint, there are only 5 unique combinations of values to get to 10:

• 5 two's; 0 one's ; 4 zero's
• 4 two's; 2 one's ; 3 zero's
• 3 two's; 4 one's ; 2 zero's
• 2 two's; 6 one's ; 1 zero
• 1 two's; 8 one's ; 0 zero's

We pick one of the five possibilities at random, scramble the elements and stuff the results into a column.

Store the templates in Sheet1 and the output in columns A through Z in sheet Sheet2.

In Sheet1:

The code:

Sub croupier()
    Dim Itms(1 To 9) As Variant
    Dim i As Long, J As Long, s1 As Worksheet, s2 As Worksheet

    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    For i = 1 To 26
        J = Application.WorksheetFunction.RandBetween(1, 5)
        For k = 1 To 9
            Itms(k) = s1.Cells(k, J).Value
        Next k

        Call Shuffle(Itms)

        For k = 1 To 9
            s2.Cells(k, i).Value = Itms(k)
        Next k
    Next i
End Sub


Sub Shuffle(InOut() As Variant)
    Dim HowMany As Long, i As Long, J As Long
    Dim tempF As Double, temp As Variant

    Hi = UBound(InOut)
    Low = LBound(InOut)
    ReDim Helper(Low To Hi) As Double
    Randomize

    For i = Low To Hi
        Helper(i) = Rnd
    Next i


    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For i = Low To Hi - J
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = temp
          End If
        Next i
        For i = Hi - J To Low Step -1
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = temp
          End If
        Next i
        J = J \ 2
    Loop
End Sub

Sample Sheet2:

Answer 2

Here's a randomized solution for you. First, create a table of possible sets. Given your constraints, there are only 5 possible sets of solutions. I put this table in cells B2:F10, with the headers in row 1. Note that this table can go anywhere, even on a different sheet if preferred. In a final product, I would probably hide these rows. Anyway, it looks like this:

Next, because you want a random number of columns, in cell A12 I put in a header called # of Columns and in cell B12 is this formula (feel free to adjust the upper and lower bounds to what you're looking for, this is just a random number between 3 and 10): =RANDBETWEEN(3,10)

Now we can setup our randomized columns and what sets they use:

• In cell B14 and copied right (to the maximum number of columns defined in the previous formula, so in this example it goes to K because B:K is 10 columns), use this formula:

  =IF(COLUMN(A14)>$B$12,"","Column "&COLUMN(A14))

• In cell B15 and copied right is this formula:

  =IF(B14="","",INDEX($B$1:$F$1,,RANDBETWEEN(1,5)))

• In cell B16 and copied right and down for 9 rows (so in this example it is copied to K24) is this formula:

  =IF(B$14="","",INDEX($B$2:$F$10,MATCH(LARGE(B$26:B$34,ROW(B1)),B$26:B$34,0),MATCH(B$15,$B$1:$F$1,0)))

• Finished, it will look like this (note that before completing the next step of this answer, it will show #NUM! errors, explained below):

You'll notice that third formula references a range we haven't built yet, in rows 26:34. In that range, there is another table full of randomized numbers so that the Sets can get scrambled to give us randomized results. Building that table is very easy. In cell B26 and copied over and down to K34 (again, over to the maximum number of columns and down for 9 rows), is this formula:

=IF(B$14="","",RAND())

Now with the randomizers, you'll get results as shown in the second image, with randomized sets of 9 numbers that sum to 10, consisting of 0s, 1s, and 2s. At this point you can cut/paste the Sets and Randomizers tables to a different sheet if preferred, or simply hide those rows.

Answer 3

there are only 5 possible combinations of 9 numbers 0,1 & 2 (disregarding order) where the total = 10.

• 2,2,2,2,2,0,0,0,0
• 2,2,2,2,1,1,0,0,0
• 2,2,2,1,1,1,1,0,0
• 2,2,1,1,1,1,1,1,0
• 2,1,1,1,1,1,1,1,1

put those combinations in a spreadsheet:

╔════╦══════════════════════╤═════════╤═════════╤═════════╤═════════╕
║    ║          A           │    B    │    C    │    D    │    E    │
╠════╬══════════════════════╪═════════╪═════════╪═════════╪═════════╡
║ 1  ║                      CORRECT COMBINATIONS                    │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 2  ║ Group 1              │ Group 2 │ Group 3 │ Group 4 │ Group 5 │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 3  ║ 2                    │ 2       │ 2       │ 2       │ 2       │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 4  ║ 2                    │ 2       │ 2       │ 2       │ 1       │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 5  ║ 2                    │ 2       │ 2       │ 1       │ 1       │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 6  ║ 2                    │ 2       │ 1       │ 1       │ 1       │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 7  ║ 2                    │ 1       │ 1       │ 1       │ 1       │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 8  ║ 0                    │ 1       │ 1       │ 1       │ 1       │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 9  ║ 0                    │ 0       │ 1       │ 1       │ 1       │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 10 ║ 0                    │ 0       │ 0       │ 1       │ 1       │
╟────╫──────────────────────┼─────────┼─────────┼─────────┼─────────┤
║ 11 ║ 0                    │ 0       │ 0       │ 0       │ 1       │
╙────╨──────────────────────┴─────────┴─────────┴─────────┴─────────┘

• use RAND() to produce 9 random numbers in a column (say cells G3:G11)
• use RANK(G3,$G$3:$G$11) to get randomly-ordered list of numbers 1-9 in the neighbouring column.
• use RANDBETWEEN(1,5) to randomly choose one of the 5 allowed number combinations (say in cell I2)

• use INDEX to reference the cell in the randomly selected column (1-5) and the randomly ordered row (1-9), from within the 9x5 region of allowed values. eg: in cell I3:=INDEX($A$3:$E$11,H3,$I$2)

• you can combine the RANK() into the index function also.

╔════╦═══════════════════════╤══════╤════════╤═══╤═══════════════════════╤════════╕
║    ║           G           │  H   │   I    │ J │           K           │   L    │
╠════╬═══════════════════════╪══════╪════════╪═══╪═══════════════════════╪════════╡
║ 1  ║                       │      │ group: │   │                       │ group: │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 2  ║ RANDOM number (order) │ rank │    3   │   │ RANDOM number (order) │    4   │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 3  ║ 0.04                  │ 8    │ 0      │   │ 0.92                  │ 2      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 4  ║ 0.13                  │ 7    │ 1      │   │ 0.79                  │ 1      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 5  ║ 0.9                   │ 1    │ 2      │   │ 0.2                   │ 0      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 6  ║ 0.36                  │ 6    │ 1      │   │ 0.31                  │ 1      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 7  ║ 0.49                  │ 5    │ 1      │   │ 0.98                  │ 2      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 8  ║ 0.89                  │ 2    │ 2      │   │ 0.65                  │ 1      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 9  ║ 0                     │ 9    │ 0      │   │ 0.68                  │ 1      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 10 ║ 0.84                  │ 3    │ 2      │   │ 0.57                  │ 1      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 11 ║ 0.65                  │ 4    │ 1      │   │ 0.28                  │ 1      │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 12 ║                       │      │        │   │                       │        │
╟────╫───────────────────────┼──────┼────────┼───┼───────────────────────┼────────┤
║ 13 ║                       │      │ 10     │   │                       │ 10     │
╙────╨───────────────────────┴──────┴────────┴───┴───────────────────────┴────────┘

Answer 4

The closest I can get is with this:

=IF(SUM(A$1:A1)>=10,0,IF(SUM(A$1:A1)=9,1,IF(SUM(A$1:A1)=8,2,RANDBETWEEN(1,2))))

Put it in A2 and copy down and over. It must go in row 2 or it will cause a circular reference.

It fills the column with 1 or 2 till it sums to 10, then the rest are zeros.

---

Edit

This is about as random as I can get, this will allow 0s randomly:

=IF(SUM(A$1:A1)>=10,0,IF(SUM(A$1:A1)=9,1,IF(SUM(A$1:A1)=8,2,IF(AND(SUM(A$1:A1)<=ROW()-2,ROW()>5),2,RANDBETWEEN(0,2)))))