Multiple dice roller (d6) with two cells and one formula?

What a fabulous question. I can make a macro/UDF that does this for you in my sleep, and we may have to resort to that. In the meantime I've called some other eyes to this question to see if anyone can pickup on a workable formula-only approach. I'm stumped so far. Everything I try, no matter how creative, ultimately just multiplies the original two dice roll by the number of rolls requested.

Still scratching my head.

It's long but it caters for up to 20 dice rolls

put the number of dice rolls in B3

Please Login or Register  to view this content.

The thing that makes this so difficult is your insistence that this be done in a single cell. With a million rows and 10's of thousands of columns, it seems overly stingy to insist on a single cell formula. What is behind your desire to have a single cell? Helper cells containing single RANDBETWEEN() functions could be put in a hidden column, and the X number of cells added up fairly easily.

Has anyone tried an iterative solution? Put =RANDBETWEEN(1,6) in A1. B1=A1+B1. Enable iteration and set max iteration to X. Put 0 in A2 and copy B1 into B2, to give you an easy way to reset the calculation. With max iteration set to the desired X, copy B2 into B1 to get your result.

The main difficulty is that it is less convenient to change X, and the hassle of resetting the summation, since the summation will continue to sum up with each calculation event until you reset it (manual calculation might be desirable, so it will only calculate when you command it).

How about using a helper column?

Row\Col	A	B	C
1		min
2		1	B2: Input
3		max
4		6	B4: Input
5		value
6		5	B6: =SUM(B5, RANDBETWEEN($B$2, $B$4))
7		7
8		11
9		15
10		16
11		19
12		23
13		29
14		33
15		34
16		35
17		40
18		44
19		45
20		46
21		48
22		51
23		52
24		54
25		58
26
27	dice	value
28	5	16	B28: =INDEX($B$6:$B$25, A28)

Take a look at this:

http://spreadsheetpage.com/index.php/file/dice_roller/

I'm pretty sure it can be done (and trying to figure out how)

Considering what I have seen from those who have responded and those who have chosen not to respond because they are "not interested", the evidence I see is "it might be possible, but it is not a desirable approach" to this problem. If you also want it "easily modfiable", I don't think a single cell formula is really the answer here.

I'm hoping it can be simple, compact (reduced the most efficient minimum) and thus, easily modifiable in case have future problems with it (and so my reluctance on macros).

If you really do not want to use a helper block to hold the individual random numbers, I think the easiest way to meet all of these requirements is going to be a UDF (user-defined function-- VBA function procedure called from the spreadsheet). A UDF for this will be simple (a single For...Next loop should do the trick), easy to understand and modify, forward and backward compatible within Excel over many versions (though it will not work in other spreadsheet applications), compact, and efficient.

I guess what I am suggesting at this point -- reconsider your requirements and restrictions in order to open up easier possibilities.

Array-enter:

=SUM(RANDBETWEEN(IF(1,ROW(INDIRECT("1:"&A2))/ROW(INDIRECT("1:"&A2))),IF(1,ROW(INDIRECT("1:"&A2))/ROW(INDIRECT("1:"&A2))*6)))

for example.

DUDE! That is IT! (cheer)

So, the only caveat here is the number you put in A2 is the "number of dice to roll". So if you want 20 rolls of 2 dice, put 40 in A2. Brilliance!

Oh yes - thanks, I should have clarified that!

surely if you have one die and one roll then it can only be 1 to 6, if you have 2 dice then then total can only be 2 to 12
so sum 20 rolls of 1 die randomly will be from 20 to 120
from 2 dice min 2 to max 12 would sum between 80 to 240 or am i missing something?

Very Nice.

Roll the dice without macro

Originally Posted by martindwilson

surely if you have one die and one roll then it can only be 1 to 6, if you have 2 dice then then total can only be 2 to 12
so sum 20 rolls of 1 die randomly will be from 20 to 120
from 2 dice min 2 to max 12 would sum between 80 to 240 or am i missing something?

You're missing the probability of rolls, Martin.

I tested both solutions against the actual statistical distribution for 3 dice rolls and am comfortable they give the right distribution

It occurs to me you only need one array, so:

=SUM(RANDBETWEEN(IF(1,ROW(INDIRECT("1:"&A2))/ROW(INDIRECT("1:"&A2))),6))

Stomper, that is a leap of insight. Very nice.

Just to generalize for N-sided dice, starting with any number,

Row\Col	A	B	C
1	iMin	1
2	sides, N	6
3	dice, M	10
4	roll	43	B4: {=SUM(RANDBETWEEN(ROW(INDIRECT("1:" & M)) ^ 0, N)) + M * (iMin-1)}

Er, couldn't this be done with just

=RANDBETWEEN(B3,B2*B3)

Where Sides on a dice is B2 and # of dice is B3?

or am i missing something

Originally Posted by Speshul

Er, couldn't this be done with just

=RANDBETWEEN(B3,B2*B3)

Where Sides on a dice is B2 and # of dice is B3?

or am i missing something

Yes you are missing something. Each dice (6 sided) will average 3.5 so as the number of dice/rolls increases the likely hood is that the total will be approximately 3.5 times the total number of rolls, and outliers at the top and bottom end of the possible totals will be less likely. =RANDBETWEEN(B3,B2*B3) means that all outcomes in the range are equally likely, so therefore not a real world facsimile.

Like GAk67 said, your formula will give equal probability for all numbers between lower and upper where as the multple dice roll will have a bell shaped distribution with very low probability at teh extremes. ie hte probability of getting a sum of 3 ina three dice roll is 1 in 216 yet with your formula the probability of a 3 is 1 in 18

To elaborate on romperstomper's formula I would go with this array formula:

Formula:

Please Login or Register  to view this content.

Where A2 = number if dice, B2 = number of times those dice are rolled, and C2 = the number of sides each dice has.

Post #21 ?