1

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
Improve this question
2
  • @8odoros - Would a solution utilising google sheets instead be allowed? There are some additional functions available there, that are not in Excell Commented Jun 10, 2016 at 14:27
  • i think the best design to do this would be a while loop that generates a list of integers {0,1,2} then looks at the last 9 numbers to see if their sum is equal to 10. Yes this loop is theoretically not bound and could run forever, but statistically I would bet it resolves within the first 100 iterations every time. This keeps you from having to manually create set lists like most of the examples below. Commented Jun 10, 2016 at 16:05

4 Answers 4

Reset to default
2

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     │
╙────╨───────────────────────┴──────┴────────┴───┴───────────────────────┴────────┘
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

enter image description here

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

enter image description here

Improve this answer

4 Comments

I was thinking along the same lines, but it DOES mean that the bottom rows are more likely to have 0's in (not truly random). Also, it is still POSSIBLE to have a total of LESS THAN 10, if all of the cells are 0 (possible, but statistically unlikely)
@user3616725 You are correct that it is not a true random, but it will always = 10. Look at my RANDBETWEEN(), it is 1 or 2, no 0. It was the only way to ensure that 10 was always reached. Even if it ends up with all 1's in the first 8 the IF will put a 2 in the last place thus making it 10. The only way to get a true random would be to incorporate vba, load the results into an array then jumble the array. Then post the values back. It would then be static.
It's far from perfect (for example last row is almost never 2) but I accept. Other answers are good too but are less random and rely too much on the fact that in this particular case there are only 5 options (disregarding order). So in my case that's the best answer.
Also, using an extra column with RAND() and sorting by it rows weight is more random.
0

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:

ListedSets

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):

MainTable

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())

Randomizers

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.

Improve this answer

Comments

0

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:

enter image description here

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:

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.