0

I would like to make a formula that can essentially calculate an average output of a dice roll, think Dungeons and Dragons.

I want to use a single formula to calculate any possible roll, using each number as in input. If I want to calculate a roll of a 1d3+2, I can use =RANDBETWEEN(1,3)+2, but how would I be able to calculate a 2d3 roll without having to edit the formula? How would I go about executing RANDBETWEEN() an arbitrary amount of times within one formula?

The plan is to be able to use this for possibly hundreds and thousands of rolls, so I would like to avoid filling rows and rows with formulas, I plan to use different rolls as rows, and multiple different possibilities as columns so I would also like to save space. Here is what I have so far but as you can see, the formula for each roll will not suffice for more than a single dice.

I am willing to take no for an answer, I could create a solution in VBA if I had to, but I'd like to know if Excel can handle this for simplicity's sake.

2
  • Are you okay with having 3 entries in individual cells? One for the number of dice, number of sides and the addition? – Scott Craner Dec 18 '17 at 20:24
  • I am, that's what I have so far. – Bazul Dec 18 '17 at 20:44
2

First you will want to create a table with number of dice indicated. So in A6 put the following formula and copy down as many times as you will have the total number of dice wanted:

=IF(ROW(1:1)<=$A$2,ROW(1:1),"")

This will refer to A2 where I put the number of dice desired.

Then in B6 put this and copy down:

=IF(A6<>"",RANDBETWEEN(1,$B$2),"")

This will create a random "roll" of those dice. 1 row for each dice. B2 contains the number of sides.

Then sum and add the constant with:

=SUM($B$6:$B$1000)+$C$2

enter image description here


To do this in one cell, use this array formula:

=SUM(RANDBETWEEN(1,(ROW(INDEX($A:$A,1):INDEX($A:$A,$A2))<=$A2)*$B2))+$C2

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

This will do the number of RandBetweens noted in A2 with the upper limit of the Random numbers set by the number of sides.

enter image description here

4
  • Thanks Scott, but I should have been more clear with my original question. I would like to know if there is a way for me to use RANDBETWEEN() multiple times in one cell, under the assumption that I don't know how many rolls may be required, and cannot reserve an infinite number of cells for dice rolls. I've added some more information to the question. "It can't be done" is an acceptable answer. – Bazul Dec 18 '17 at 21:32
  • I might be misunderstanding, but it sounds like you want to simulate hundreds or thousands of random dice rolls to find the average output. If the dice are "fair", the average is determined by the number of sides and the number of dice. In fact, there's a handy calculator online. Maybe this will do what you want? – Bandersnatch Dec 18 '17 at 22:11
  • @Bazul see edit. – Scott Craner Dec 18 '17 at 23:28
  • Thanks Scott, that's a perfect solution, it allows me to compare many different situations, and with many data points: i.imgur.com/LmHHqL4.jpg . @Bandersnatch I am aware that those are available, and I have made my own through other means, but this happened to be excel specific. Having all the data, tables and calculations in Excel makes things much easier. – Bazul Dec 19 '17 at 2:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.