How to use the RANDARRAY function to randomly classify data in Excel

The RANDARRAY function is useful for performing tasks such as randomly sorting people into groups. This guide covers the definition and usage of this Excel function.

What is the RANDARRAY function in Microsoft Excel?

The RANDARRAY function in Excel is a dynamic array function that returns an array of random numbers based on the number of rows, columns, and min and max values ​​you specify. The return value of the dynamic array function will automatically overflow into surrounding cells.

Syntax of the RANDARRAY function:

=RANDARRAY([rows],[columns],[min],[max],[integer])

In there:

1. Rows (optional) represents the number of rows to fill. By default, this is 1 row.
2. Columns (optional) is the number of columns to fill. By default, this is 1 column.
3. Min (optional) is the smallest random number to return. Default is 0.
4. Max (optional) is the largest random number returned. Default is 1.
5. Integer (optional) defines the type of value to return. TRUE represents an integer, while FALSE, the default, represents a decimal number. You should note that the integer argument is required if you want to return an integer.

Because all parameters in the RANDARRAY function are optional, it returns a value if only one argument is specified. Therefore, you can return an array of numbers based on the number you set in one of the following four arguments: row, column, min, and max. a value.

How to use the RANDARRAY function in Excel

Explore the row and column arguments of the RANDARRY function

Let's say you want random data to occupy 5 rows and 4 columns.

1. Select a cell to enter the formula. We will use cell B3 .

2. In the formula bar, enter function = RANDARRAY( .

3. This formula calls the RANDARRY function to determine the number of rows. Write 5 .

4. Next, this function calls the set number of columns. Write 4 .

5. Write 5 , then comma and 4 .

How to use the RANDARRAY function to randomly classify data in Excel Picture 1

6. Write a ) to close the parentheses because we will not use other arguments in this first example.

7. Press Enter .

The final syntax would be:

=RANDARRAY(5,4)

How to use the RANDARRAY function to randomly classify data in Excel Picture 2

After pressing Enter , the formula returns random decimals spread across 5 rows and 4 columns because you specified 5 for the row and 4 for the columns in the formula. This means your formula worked properly.

Explore the min and max arguments of the RANDARRY function

Using two other optional parameters, min and max, you can return an array of random numbers and specify the minimum and maximum numbers in the array.

Start where you left off in the previous example. This example wants the smallest number to be 20 and the largest number to be 100 .

1. Select the cell where you want to enter the formula. Here is cell B3 .

2. In the formula bar, enter:

=RANDARRAY(5,4,20,100)

This formula works just like the previous one. The only difference is specifying 20 and 100 as the smallest and largest numbers.

3. Press the Enter key . This new formula will return random decimal numbers from 20 to 100 spread across five rows and four columns.

How to use the RANDARRAY function to randomly classify data in Excel Picture 3

Explore the integer argument of the RANDARRY function

The previous two examples return decimals because, by default, if not specified, RANDARRAY will return decimals. To get an integer or integer, you must specify TRUE for the integer argument in the formula.

1. Select the cell where you want to enter the formula (B3).

2. In the formula bar, enter the formula below and press Enter.

=RANDARRAY(5,4,20,100,TRUE)

How to use the RANDARRAY function to randomly classify data in Excel Picture 4

Note that all numbers are integers. That's what TRUE in the formula does. If you change TRUE to FALSE, the RANDARRAY function will return a decimal number in the Excel spreadsheet.

Above is how to use the RANDARRY function in Excel . Hope the article is useful to you.

David Pac

Update 04 September 2023