Skip to content
In this article, I will be talking about one of the new dynamic array functions: the RANDARRAY function. Sometimes we need a data table filled out with random data. With this function, you can quickly fill as many rows and columns with numeral data. And you can find our articles about other functions on our blog.
WHAT DOES IT DO
The RANDARRAY function returns an array of random numbers. You can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values. The function returns a random set of values between 0 and 1 if you don’t enter an argument. For example, you can create a 20-row and 5-column table that contains whole numbers between 50 and 500 quickly in a few seconds.You will see an example below.
SYNTAX
=RANDARRAY([rows],[columns],[min],[max],[whole_number])This function has 5 arguments.
All of them are optional.
Now, let’s take a look at these arguments and what they mean, and what we will give them.
| [rows] Optional | The number of rows to be returned |
| [columns] Optional | The number of columns to be returned |
| [min] Optional | The minimum number you would like returned |
| [max] Optional | The maximum number you would like returned |
| [whole_number] Optional | Return a whole number or a decimal value
|
WORKING CONDITIONS
-
If you don’t input a row or column argument, RANDARRAY will return a single value between 0 and 1.
- If you don’t input a minimum or maximum value argument, RANDARRAY will default to 0 and 1 respectively.
-
The minimum number argument must be less than the maximum number, otherwise RANDARRAY will return a #VALUE! error.
- If you don’t input a whole_number argument, RANDARRY will default to FALSE, or decimal value.
-
The RANDARRAY function will return an array, which will spill if it’s the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you’re using structured references. For more details, see this article on spilled array behavior.
- RANDARRAY is different from the RANDfunction in that RAND does not return an array, so RAND would need to be copied to the entire range.
- An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the array for our RANDARRAY formula is range D2:F6,or 5 rows by 3 columns.
-
Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.
USE OF THE FUNCTION
Now, let’s see how this function works with 3 different examples. First, we will determine rows and columns. And then we will give row, column, min and max number values and return a retrospective array. And then, we will request the numbers to be returned to be whole number. The image we will obtain is going be like this:
See you in other articles, bye. 🙋🏻♂️
You can share this post with your friends and get them informed as well. 👍🏻
Outlook
PowerPoint
Word
Excel
Project
Access
. Select the converted object again to be able to access other conversion alternates, to make sure the conversion fits your needs. Give it a try! Give it a try!
on the Quick Access Toolbar. I recommend you to have the camera feature there all the time.
