I am attempting to simulate a large quantity of dice rolls using Excel or LibreOffice Calc. I managed to find here a formula to generate multiple random numbers based on values specifying how many numbers to generate and number generation range, and then adds the total:

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

Rather than calculating the sum of the 'dice' as this does, what I'm trying to do is get an output showing how many times the a specific outcome occurs, e.g. how often a 1 is rolled.

While I know how to use the various count functions to do this if I were trying to determine the frequency of a value occurring in a column or row of cells, I can't figure out how to do this for the set of numbers generated within that single cell.

Is this even possible? Any help would be greatly appreciated.

Edit for (hopefully) further clarity:

So, for example, the function above produces an outcome like this:

It allows me to specify a number of dice to roll (at A2) and the number of sides on the dice (at B2) (the picture also includes a constant modifier, but I'm not concerned with that here), and then in a single cell (E1 in the image) it generates multiple random numbers within those parameters and list the sum of them all. So, in the image, it generated 2 numbers between 1 and 3, and added constant modifier of two, resulting in 7.

What I am trying to figure out is how to have the E1 cell output indicate the how many times a specific value was generated. So, in the example, in order to get that output of 7, the two randomly generated numbers had to have been 2 and 3. What I am hoping to do is figure out a way to specify I want E1's output to equal how often a 3 was generated, so that in the example the E1 cell would read 1.

Edit2:

So when I enter a #dice and #sides value, the function as currently written generates several random numbers between 1 and the #sides value, and outputs their sum. If the #dice value is 3, it generates 3 random numbers and gives the sum of those three numbers. If I change the #dice value to 10, it generates 10 random numbers and gives the sum of those. At no point do I see the randomly generated numbers, only the sum.

I am not interested in the sum of the random numbers. Instead, what I am hoping to do is change the function so it tracks how often a specific number is generated when it generates the random numbers.

So, for example, let's say the #dice is 4 and the #sides is 6, and function then generates this set of numbers: 1, 3, 5, 1. The solution I'm hoping for would count that a 1 was generated 2 times, and the output of the function would be 2. If it generated a set of 4, 5, 1, 3, the output would be 1. A set of 1, 1, 1, 2 would have an output of 3.

Like the current function, the solution I'm hoping to find does not actually show me the randomly generated numbers, only the final output - the number of times a 1 was generated. Just as single cell array formula to track how often ones are generated.

2more comments