0

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:

enter image description here

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.

Improve this question
7
  • It isn't clear what you mean by a set of numbers generated within a single cell, and how you want to use it. Can you provide some sample date (mock-up or screenshot), and what results you want from that sample data?
    – fixer1234
    May 21, 2019 at 9:51
  • Sorry for the lack of clarity. Edited to hopefully better explain what I'm looking for.
    – gta
    May 21, 2019 at 10:21
  • There won't always be a single answer to this, there may be a number of correct answers. For example if you had a 4 sided dice in your example and the answer was 7 with a modifier of 2, any of the following would be correct 1,4 | 4,1 | 2,3 | 3,2. How do you want to account for this?
    – Clinton
    May 21, 2019 at 10:51
  • The direction you want to go with this is clearer, but it's very vague and ambiguous. It will be hard for people to actually nail down a specific requirement to address (or what gets addressed won't really cover what you want). Can you make the requirements more explicit (comprehensively define exactly what would be a successful solution)?
    – fixer1234
    May 21, 2019 at 10:55
  • Apologies for ambiguity - I am out of my depth here, but hopefully this edit clarifies things.
    – gta
    May 21, 2019 at 12:14

1 Answer 1

Reset to default
0

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

So this does what I was looking for. The latter COUNTIF condition can be changed as well, so for example instead tracking how often 1 occurs, by entering "<=2" as the last condition, the cell will output the total number of ones or twos that are rolled.

edit: On further testing, this seems to produce an error when only generating 1 random number. So clearly it's not a perfect solution, but good enough for my purposes.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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