microsoft excel - How to use RANDBETWEEN() x times in a single formula?

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.

Answer

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

---

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.