There are many different functions available in Excel that make data transformation and organization easy. One of these functions is the RANDARRAY Function, which allows you to randomize your data efficiently in Excel.
The RANDARRAY function becomes very handy when working on tasks like randomly shuffling people into groups. This guide will take you through the RANDARRAY function, covering its definition, and how to use the function in Excel.
What Is the RANDARRAY Function?
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 minimum and maximum values you specify. The return values of dynamic array functions automatically spill into the surrounding cells.
The syntax of the RANDARRAY function is:
Since all the parameters in the RANDARRAY function are optional, it will return a value if only one argument is specified. Thus, you can return an array of numbers based on the number you set in either of these four arguments: rows, columns, min, and max. a value.
How to Use the RANDARRAY Function in Excel
To get things started, let’s use the RANDARRAY function in some partial examples on its own to grasp how it navigates the spreadsheet.
1. Exploring the Row and Column Argument of the RANDARRY Function
Suppose you want random data occupying five rows and four columns.
Your final syntax will be:
Once you pressEnter, the formula will return random decimal numbers spread across five rows and four columns because you specified five for rows and four for columns in the formula. This means your formula worked!
2. Exploring the Min and Max Argument of the RANDARRY Function
Using the two other optional parameters, min and max, you can return a random array of numbers and specify the minimum and maximum number in the array.
Let’s pick up where we left off from the last example. This time, we want the smallest number to be20and the largest to be100.
3. Exploring the Integer Argument of the RANDARRY Function
The two examples before this returned decimal numbers because by default, if not specified, the RANDARRAY returns decimal numbers. To get whole numbers or integers, you have to specifyTRUEfor the integer argument in the formula.
Let’s demonstrate this.
Notice that all the numbers are integers. That is what theTRUEin the formula does. If you change theTRUEtoFALSE, the RANDARRAY function will return decimal numbers in your Excel spreadsheet.
How to Use the RANDARRAY and SORTBY Functions to Randomly Sort Data in Excel
The RANDARRAY function works well when it comes to producing random data. However, to sort the data, you will need to pair the RANDARRAY function with theSORTBY function in Microsoft Excel.
The syntax of the SORTBY function is:
Let’s use a fresh example below to demonstrate how you may pair the RANDARRAY function with the SORTBY function in Excel. In this example, we have a list of ten names. We’ll use the RANDARRAY to select the name randomly and the SORTBY to sort it.
You can use theevaluate feature to understand this Excel formula better.
Randomize Much More Than Names
The RANDARRAY function in Microsoft Excel is a versatile function that comes in handy in various situations. With it, you can randomize your data and effortlessly organize your data. You can pair it up with other Excel functions to maximize its potential and be more productive.
