Dynamic number of random numbers

Question

I've been using a Calc spreadsheet to keep track of my D&D character, and I'm looking to increase the automation in it all the time - in this case, I want it to roll my dice for me.

My character might do something like 2d6 points of damage (roll 2 6-sided dice, add together) or 12d8 points of damage (roll 12 8-sided dice, add together). If I know both of these numbers separately - the number of dice, and the sides of the dice, can I 'roll' this number?

I'm aware of the RandBetween function, which when given (1, N) as arguments will simulate rolling an N-sided die. But M x RandBetween(1, N) just multiplies the roll by M, rather than 'rolling' M times.

For portability reasons, I don't want to write a macro for this. Is there any kind of function or trick that will let me add an arbitrary number of random numbers?

Answer 1

Make a list of random numbers in one column (for example column C below). Then use as many of them as needed in the formula to determine the outcome.

      A      B            C                       D               E
# of dice  Sides    Random Numbers              Range          Outcome      
      2      6    =RANDBETWEEN(1,$B$2)  ="C2:C" & A2 + 1   =SUM(INDIRECT(D2))
                  =RANDBETWEEN(1,$B$2)
                  =RANDBETWEEN(1,$B$2)
                  =RANDBETWEEN(1,$B$2)
                  =RANDBETWEEN(1,$B$2)
                  =RANDBETWEEN(1,$B$2)
                  =RANDBETWEEN(1,$B$2)
                  =RANDBETWEEN(1,$B$2)
                  =RANDBETWEEN(1,$B$2)
                  =RANDBETWEEN(1,$B$2)