How to Generate Multiple Random Dice Rolls in a Single Excel Cell
Question details
The user needs to create a formula that simulates rolling a 100-sided die multiple times and outputs all the results separated by commas within a single cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Simulating multiple dice rolls or generating an array of random numbers in a single cell based on a specified count.
- Observed behavior
- The goal is to dynamically generate and combine a series of random integers into one text string that recalculates along with the worksheet.
Ensure you are using a modern version of Excel (Office 365, Excel 2021) or WPS Spreadsheet that supports dynamic array functions like RANDARRAY and TEXTJOIN.
Use the TEXTJOIN and RANDARRAY Formula
This method uses RANDARRAY to generate the desired number of random integers and TEXTJOIN to combine them into a single string.
The RANDARRAY function is a dynamic array function that can return an array of random numbers. By nesting it inside the TEXTJOIN function, we can take that entire array and merge it into one continuous text string separated by a delimiter.
Select a cell to specify the number of dice rolls (for example, cell C6) and enter a number, such as 5.
Click on the target cell where you want the combined dice rolls to appear. Type the formula =TEXTJOIN(",",FALSE,RANDARRAY(,C6,1,100,TRUE)) and press Enter.
To generate a new set of random dice rolls, press the F9 key on your keyboard to recalculate the worksheet.
Use RANDBETWEEN with the Ampersand (&) Operator
For older versions of Excel that do not support dynamic arrays, you can manually concatenate multiple RANDBETWEEN functions.
Easily Generate Random Numbers and Dice Rolls with WPS Spreadsheet
WPS Office provides a powerful and free Spreadsheet application that fully supports modern dynamic array functions like RANDARRAY and TEXTJOIN. You can effortlessly simulate dice rolls, generate mock data, and perform complex statistical calculations.
- 1. Create a new workbook: Open WPS Spreadsheet and create a new blank workbook.
- 2. Specify the roll count: Enter your desired number of rolls in cell C6.
- 3. Input the formula: In your target cell, input the formula =TEXTJOIN(",",FALSE,RANDARRAY(,C6,1,100,TRUE)).
- 4. Generate the result: Press Enter to instantly see the combined random numbers, and press F9 whenever you want to reroll.
Frequently Asked Questions
How can I change the number of sides on the dice?
In the RANDARRAY function RANDARRAY([rows], [cols], [min], [max], [whole_number]), change the [max] argument. For a standard 6-sided die, change 100 to 6, making the formula =TEXTJOIN(",",FALSE,RANDARRAY(,C6,1,6,TRUE)).
Why do the random numbers change every time I edit the spreadsheet?
RANDARRAY and RANDBETWEEN are volatile functions, meaning they recalculate every time any change is made to the worksheet. To keep the values static, copy the cell and paste it as values using right-click > Paste Special > Values.
What should I do if I get a #NAME? error?
A #NAME? error usually means your version of Excel does not support the RANDARRAY or TEXTJOIN functions. You will need to upgrade to Microsoft 365, a newer version of Office, or use WPS Office which natively supports these modern functions.
Can I use a different separator instead of a comma?
Yes, you can easily change the delimiter in the TEXTJOIN function. Just replace the "," argument with "-", " | ", or any other character string you prefer inside the quotation marks.
