WPS Spreadsheet: Randomly Distribute Fixed Counts of Numbers
Question details
The user needs to generate a randomized list containing exact quantities of specified numbers, such as 15 ones, 14 twos, and 4 threes.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants to repeat the process for different value-and-count distributions without manually entering or shuffling the list.
- Observed behavior
- A dynamic-array formula is needed to create the required repeated values and randomly reorder the resulting list.
Prepare a two-column input table with the headers Time and Amount, where Time contains each required count and Amount contains the corresponding number. Name the table DistribInput so the formula can use its structured references.
Use a dynamic-array formula to generate and shuffle the fixed-count list
Use this method when you want WPS Spreadsheet to create exact quantities of each value and randomize their order automatically.
The formula first creates the requested number of repetitions for every Amount, combines the results into one spilled list, and then sorts that list by random values generated with RANDARRAY.
For example, enter 15 and 1, 14 and 2, and 4 and 3 in the Time and Amount columns. The resulting spill range contains 33 items in a random order.
In WPS Spreadsheet, enter the headers Time and Amount in two columns, add one row for each value-count pair, select the range, and choose Insert > Table. Set the table name to DistribInput in the Table Design tab.
Select an empty cell outside the table and enter =LET(amts,DROP(REDUCE(0,DistribInput[Time],LAMBDA(seed,t,VSTACK(seed,SEQUENCE(t,,INDEX(FILTER(DistribInput[Amount],DistribInput[Time]=t),1),0)))),1),SORTBY(amts,RANDARRAY(ROWS(amts))))
Press Enter and allow WPS Spreadsheet to spill the results into the cells below. The list will contain the exact requested count for every Amount and will be randomly ordered.
Replace or add values in the DistribInput table, then recalculate the sheet with Formulas > Calculate Now or press F9. The spilled list updates to match the new counts.
Check the input table when the formula does not calculate
Use these checks if the formula returns an error, does not spill, or produces unexpected counts.
Create Randomized Fixed-Count Lists in WPS Spreadsheet
WPS Spreadsheet can build an exact-count value list with a dynamic-array formula, then randomize its order with RANDARRAY. Edit the input table whenever you need a different distribution and recalculate the sheet to refresh the result.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a blank spreadsheet or an existing XLSX workbook.
- 2. Build the distribution table: Create the Time and Amount columns, enter the required counts and values, and name the table DistribInput from Table Design.
- 3. Add the formula: Select an empty cell and enter the LET, REDUCE, VSTACK, SEQUENCE, and SORTBY formula that references DistribInput.
- 4. Refresh the randomized list: Press Enter to generate the spilled list, then use Formulas > Calculate Now or press F9 whenever you want a new random order.
Frequently Asked Questions
How do I create 15 ones, 14 twos, and 4 threes?
Create a table named DistribInput with Time and Amount columns, then enter 15/1, 14/2, and 4/3 as the three data rows. Place the dynamic-array formula in an empty cell to generate and shuffle all 33 values.
Why does the formula use the Time and Amount columns?
Time specifies how many times a value should appear, while Amount specifies the value to repeat. The formula uses each Time entry to create a sequence of the corresponding Amount.
How can I randomize the list again?
Press F9 or choose Formulas > Calculate Now in WPS Spreadsheet. RANDARRAY generates new random sort values, so the list order changes while the counts remain fixed.
What should I do if the formula produces a spill error?
Clear any nonempty cells in the intended output area, confirm that the formula is outside the DistribInput table, and verify that the table headers are exactly Time and Amount.
