Function Problems

How to Create a Balanced Random Array from 1 to 5 in Excel

Huda Qurayshi Oct 8, 2026 870 views

Question details

The user needs to generate a 17-row by 14-column array containing values from 1 to 5 in nearly equal quantities.

How to Create a Balanced Random Array from 1 to 5 in Excel

Product: Excel
Device & OS: not provided
Scenario: Planning a physical layout (such as a tile-style assignment) where five different styles need to be distributed randomly but ordered in equal quantities.
Observed behavior: Generating a grid of 238 cells where the numbers 1 through 5 appear approximately 47 or 48 times each in a completely randomized layout.

Before you start

Ensure your spreadsheet software supports modern dynamic array functions like RANDARRAY, SEQUENCE, VSTACK, and WRAPCOLS, as these are required to build and shape the data automatically.

Solution 1Recommended

Use Dynamic Arrays to Generate, Randomize, and Shape the Data

By combining SEQUENCE, VSTACK, SORTBY, RANDARRAY, and WRAPCOLS, you can enforce an equal distribution of values before shuffling them into a grid.

A standard RANDARRAY formula cannot guarantee an equal distribution of numbers. To achieve a perfectly balanced grid, you must first create the exact number of required values, combine them, shuffle them, and then shape them into the final dimensions.

For a 17x14 grid, you need 238 total cells. Dividing 238 by 5 (your maximum value) gives 47 full sets of the numbers 1 through 5, with a remainder of 3 cells.

Calculate the Base Arrays

Use the SEQUENCE function to create 47 instances of each number. For example, SEQUENCE(47, 1, 1, 0) generates 47 ones, while SEQUENCE(47, 1, 2, 0) generates 47 twos.

Generate the Remainder

To fill the remaining 3 cells out of 238, use RANDARRAY(3, 1, 1, 5, TRUE) to generate three random integers between 1 and 5.

Stack the Arrays

Wrap the five SEQUENCE formulas and the single RANDARRAY formula inside a VSTACK function. This combines all 238 values into a single vertical column.

Randomize the Stacked Column

Use SORTBY to shuffle the stacked array. The syntax will be SORTBY(stacked_array, RANDARRAY(238)), which sorts your exact distribution into a completely random order.

Shape into a 17x14 Grid

Finally, nest the randomized column inside WRAPCOLS(randomized_array, 17) to distribute the data into 17 rows and 14 columns.

Use Dynamic Arrays to Generate, Randomize, and Shape the Data

Combined Formula Example: You can paste this entire formula into the top-left cell of your target range: =WRAPCOLS(SORTBY(VSTACK(SEQUENCE(47,1,1,0), SEQUENCE(47,1,2,0), SEQUENCE(47,1,3,0), SEQUENCE(47,1,4,0), SEQUENCE(47,1,5,0), RANDARRAY(3,1,1,5,TRUE)), RANDARRAY(238)), 17)

Advanced Spreadsheet Functions

Easily Generate Complex Arrays with WPS Spreadsheet

WPS Office provides comprehensive support for modern dynamic array functions. You can seamlessly manage complex data distributions, randomize variables, and shape arrays for your projects without any hassle.

1. Open WPS Spreadsheet: Launch WPS Office and create a new blank workbook.
2. Select the Target Cell: Click on the top-left cell where you want your 17x14 randomized grid to begin.
3. Enter the Formula: Paste the combined dynamic array formula utilizing VSTACK, SEQUENCE, and WRAPCOLS into the formula bar.
4. Generate the Grid: Press Enter to instantly populate the randomized, balanced array across the necessary rows and columns.

Full compatibility with Microsoft Excel dynamic array formulasSeamless handling of advanced functions like VSTACK, SORTBY, and WRAPCOLSLightweight, fast, and completely free to downloadFamiliar user interface requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I just use the RANDARRAY function by itself?

Using a simple RANDARRAY(17, 14, 1, 5, TRUE) will generate random numbers from 1 to 5, but it relies purely on chance. It will not guarantee an equal distribution of each number, which is necessary if you are ordering specific quantities of physical items based on the grid.

Can I freeze the numbers so they stop randomizing?

Yes. The RANDARRAY function is volatile and recalculates whenever you make an edit to the spreadsheet. To make your balanced array permanent, select the entire 17x14 grid, copy it (Ctrl+C), and then use Paste as Values (Ctrl+Shift+V) over the same area.

How do I adjust this formula for a different grid size?

To change the dimensions, you must multiply your new rows by columns to get the total cell count. Divide that count by your maximum number to find the new SEQUENCE length. Then, update the WRAPCOLS row count parameter and the RANDARRAY sizes to reflect the new dimensions and remainder.

Excel & Data Problems