Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones

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.

In the following examples, we created an array that's 5 rows tall by 3 columns wide. The first returns a random set of values between 0 and 1, which is RANDARRAY's default behavior. The next returns a series of random decimal values between 1 and 100. Finally, the third example returns a series of random whole numbers between 1 and 100.

RANDARRAY function in Excel. RANDARRAY(5,3) returns random values between 0 and 1 in an array that's 5 rows tall by 3 columns wide.

RANDARRAY function with Min, Max & Decimal arguments

RANDARRAY function with Min, Max & Whole number arguments

Syntax

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

Argument

Description

[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

[integer]

Optional

Return a whole number or a decimal value

  • TRUE for a whole number

  • FALSE for a decimal number.

Notes: 

  • 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 RAND function 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.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

FILTER function

SEQUENCE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Implicit intersection operator: @

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.