1

I want to list the possible combinations of 2,3,4,5,6,etc dice.

For example:

For 2 dice, cell A1 is 11 (1&1). I would like to drag the cell down to automatically add the following 2-dice values:

12
13
14
15
16
22
23
24
...
55
56
66

(A total of 21 combinations)

To list all the values of N dice, we start with the minimal values (for N=3: 1,1,1). I just wanted to drag that first cell and for the next numbers to appear as the next combination of the dice; i.e., for Excel to increment using BASE 6, not 10, and to exclude combinations that have already appeared in a different sequence. I want to have the resulting values as a string. So, if one of the numbers (4 dice) would be 1126, the next one must be 1133, and not 1127.

Is this achievable for 2,3,4,5,6 dice?

2
  • It isn't clear what you're trying to do or what the numbers represent (where does 11 come from? What's the source of the ranges you want to add?)
    – fixer1234
    Oct 24, 2015 at 20:07
  • I am sorry, 11 is a min sum of combination of 2 dice.
    – SKYTTEN
    Oct 24, 2015 at 20:37

3 Answers 3

1

This would probably be easier with VBA, but it can be done with formulas. I'm working in LibreOffice Calc, which has a lower maximum characters per formula, so I needed to use helper columns. But you can consolidate this into a single formula if you want. I built this around a maximum of six dice, but if you follow the pattern in the helper columns, you can expand it to as many as you want.

screenshot

Cell A1 is where your starting number goes. Ordinarily, it would be a 1 for each die. I started with 123456 to illustrate the logic. Columns C through H are the helper columns, one for each of up to six dice. These cells figure out the next value for each one. Column A concatenates the values into a single string. Enter the formulas for row 2 and then copy the row down to pre-populate as many as you need (unneeded cells will be blank and you can hide columns C:H if you want).

The formula in A2:

=IF(A1="","",C2&D2&E2&F2&G2&H2)

The test for blank is what hides unneeded cells. If you want to turn everything into a single formula, substitute the formulas in C2:H2 for the references.

The formulas in C2:H2 are as follows:

C2:  =                IF(VALUE(LEFT(A1,1))=6,"", VALUE(LEFT(A1,1)) +          OR(VALUE(MID(A1,2,1))=6))
D2:  =                IF(VALUE(MID(A1,2,1))=6,C2,VALUE(MID(A1,2,1))+IF(LEN(A1)>2,VALUE(MID(A1,3,1))=6,1))
E2:  =IF(LEN(A1)<3,"",IF(VALUE(MID(A1,3,1))=6,D2,VALUE(MID(A1,3,1))+IF(LEN(A1)>3,VALUE(MID(A1,4,1))=6,1)))
F2:  =IF(LEN(A1)<4,"",IF(VALUE(MID(A1,4,1))=6,E2,VALUE(MID(A1,4,1))+IF(LEN(A1)>4,VALUE(MID(A1,5,1))=6,1)))
G2:  =IF(LEN(A1)<5,"",IF(VALUE(MID(A1,5,1))=6,F2,VALUE(MID(A1,5,1))+IF(LEN(A1)>5,VALUE(MID(A1,6,1))=6,1)))
H2:  =IF(LEN(A1)<6,"",IF(VALUE(MID(A1,6,1))=6,G2,VALUE(MID(A1,6,1))+1))

I've added spaces to align the formula patterns to make it easier to see the logic; you can remove these. You have a minimum of two dice, so the first two formulas don't need to test for whether they are present. When the first die reaches 6, all of the others can only be 6, so it is the last row. The OR function in C2 is because LO Calc balked at evaluating the Boolean expression; the OR forces it (and doesn't hurt anything). The last potential die doesn't need to carryover a value from a next one, so its formula is a little shorter.

Notice that columns D through H include a reference to the previous column. If you want to consolidate this into a single formula, replace the C2 reference in D2 with the C2 formula. Then do the same for each successive column (the formula will grow as you do this).

Here is the output for two dice:

screenshot 2

5
  • This really was beyond my belief. Turned out to be a perfect solution for my task.
    – SKYTTEN
    Oct 28, 2015 at 11:34
  • I have expanded the same task to 7 dice ( to understand, in part, the logic behind the 6 dice formulae.) I used for the dice 6: H2: =IF(LEN(A1)<6,"",IF(VALUE(MID(A1,6,1))=6,G2,VALUE(MID(A1,6,1))+IF(LEN(A1)>6,VALUE(MID(A1,6,1))=6,1))). and for dice 7: I2: =IF(LEN(A1)<7,"",IF(VALUE(MID(A1,7,1))=6,H2,VALUE(MID(A1,7,1))+1)) But it doesnt work. The sheet keeps repeating the same sycle: 111,1111 to 111,1116 and 111,1111 again. Could you please advise?
    – SKYTTEN
    Nov 13, 2015 at 4:53
  • @SKYTTEN: Just eyeballing your formulas, I didn't see any obvious problem so I'll need to dig into it. I just noticed that you have a comma in what I assume is your starting number. Can you confirm that you're starting with 1111111?
    – fixer1234
    Nov 13, 2015 at 5:59
  • @SKYTTEN: Found two problems with your formula. It worked after I corrected them. Both are with H2. 1) When I copied your formula from the comment, there was some strange hidden character in the last VALUE between the U and E. I replaced the characters and that error disappeared. 2) In the last MID function, that should be 7 rather than 6, so it should read MID(A1,7,1).
    – fixer1234
    Nov 13, 2015 at 6:17
  • Thanks again. It worked fine. Now I understood more where "6" means no of dice and not the no of sides on each die. Not sure what was the "strange" character though, I was using IPad's Numbers but when I copied the formula to the Excel sheet, it worked perfectly. Appreciate your time and this valuable lesson.
    – SKYTTEN
    Nov 13, 2015 at 7:39
1

In A1 enter:

=ROW()

and copy down. In B1 enter:

=ROW()*6

and copy down. In C1 enter:

=B1-A1+1

and copy down.

Column C represents the possible values for N dice.

It is worth noting that the number of possible outcomes is:

(5 * the number of dice) + 1

5
  • I guess, I really badly explained what I wanted. To enlist all the values of N dice, we start with the minimal values (for N=3: 1,1,1). I just wanted to drag that first cell and next numbers to appear as the next combination on dice, i.e. the Excel to "guess" that the BASE is 6, not 10 and enlist the dice values as a string. So, if one of the numbers (4 dice) would be 1126, the next one must be 1133, and not 1127
    – SKYTTEN
    Oct 24, 2015 at 20:42
  • @SKYTTEN Your explanation is O.K. For three dice the lowest outcome is 3 (three snake-eyes) and the highest outcome is 18 (3 x 6) So the total number of outcomes is 16 Oct 24, 2015 at 20:46
  • @SKYTTEN: Are you saying you want to list all of the actual combinations, not just the count of possible combinations?
    – fixer1234
    Oct 24, 2015 at 20:49
  • @fixer1234: That's exactly right.
    – SKYTTEN
    Oct 24, 2015 at 20:53
  • @SKYTTEN Sorry....................I miss-understood Oct 24, 2015 at 21:03
0

You can do this with formulas, but for more than 2 dices it will become quite hard to follow.

This is a generic example, it will work for dices or for any other example. Let's say we have 3 tables (Table1=A1:A7), (Table2=B1:B7) and (Table3=C1:C7). First row is header (say dice1, dice2 and dice3).

You will have to copy the formulas down until until you get empty results (max number of combinations reached).

(You may need to replace ";" with "," in formulas as parameter separator)

For 2 dices (36 combinations):

=IF(ROW()-ROW($F$2)+1>COUNTA(Table1)*COUNTA(Table2);"";INDEX(Table1;QUOTIENT(ROW()-ROW($F$2);COUNTA(Table2))+1)&INDEX(Table2;MOD(ROW()-ROW($F$2);COUNTA(Table2))+1))

For 3 dices (216 combinations):

=IF(ROW()-ROW($F$2)+1>COUNTA(Table1)*COUNTA(Table2)*COUNTA(Table3);"";INDEX(Table1;QUOTIENT(ROW()-ROW($G$2);COUNT(Table2)*COUNT(Table3))+1))&INDEX(Table2;MOD(QUOTIENT(ROW()-ROW($G$2);COUNT(Table3))+1;COUNT(Table3))+1)&INDEX(Table3;MOD(ROW()-ROW($G$2);COUNT(Table3))+1)

How does it work:

  1. The current combination number will be given by ROW()-ROW($F$2) + 1 where F2 is the cell where you place the first formula.

  2. We will rotate first dice values every 6 rows (COUNTA(Table2)) with to dices and every 36 rows (COUNTA(Table1)*COUNTA(Table2)) with 3 dices.

  3. We will rotate the second dice values every row with 2 dices and every 6 rows with 3 dices.

  4. With 3 dices we will rotate the dice value every row.

An alternate method would be Microsoft Query (Data / From Other Sources). You would need to create as many sheets or tables (although with tables I get "This data source contains no visible tables" and I can't figure out why, so sheets it is) containing in column A values from 1 to 6, with headers like "Dice1", "Dice2" and "Dice3". To obtain the Cartesian product of the 3 sets, the query would be:

SELECT `Sheet1$`.Dice1 & `Sheet2$`.Dice2 & `Sheet3$`.Dice3 as  Combinations
FROM `Book1.xlsx`.`Sheet1$` `Sheet1$`, `Book1.xlsx`.`Sheet2$` `Sheet2$`, `Book1.xlsx`.`Sheet3$` `Sheet3$`

It will be harder to prepare but easier to understand (If you know basic SQL) and to expand.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .