RANDARRAY function
Summary
Returns an array of random numbers between 0 and 1. However, you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.
Syntax
RANDARRAY([rows],[columns],[min],[max],[whole_number])
[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
[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
Example
=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?
DiscoverCommunity
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
Ask the Microsoft Community
Microsoft Tech Community
Windows Insiders
Microsoft 365 Insiders
Was this information helpful?
Yes No
Thank you! Any more feedback for Microsoft? Can you help us improve? (Send feedback to Microsoft so we can help.)
What affected your experience?
Resolved my issue
Clear instructions
Easy to follow
No jargon
Pictures helped
Other
Didn't match my screen
Incorrect instructions
Too technical
Not enough information
Not enough pictures
Other
Any additional feedback? (Optional) To protect your privacy, do not provide any personal information such as email address, phone number, product key, password, or credit card number.
Submit feedback
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.
Thank you for your feedback!
×
What's new
Surface Pro
Surface Laptop
Surface Laptop Studio 2
Copilot for organizations
Copilot for personal use
AI in Windows
Explore Microsoft products
Windows 11 apps
Microsoft Store
Account profile
Download Center
Microsoft Store support
Returns
Order tracking
Certified Refurbished
Microsoft Store Promise
Flexible Payments
Education
Microsoft in education
Devices for education
Microsoft Teams for Education
Microsoft 365 Education
How to buy for your school
Educator training and development
Deals for students and parents
AI for education
Business
Microsoft Cloud
Microsoft Security
Dynamics 365
Microsoft 365
Microsoft Power Platform
Microsoft Teams
Microsoft 365 Copilot
Small Business
Developer & IT
Azure
Microsoft Developer
Microsoft Learn
Support for AI marketplace apps
Microsoft Tech Community
Microsoft Marketplace
Marketplace Rewards
Visual Studio
Company
Careers
About Microsoft
Company news
Privacy at Microsoft
Investors
Diversity and inclusion
Accessibility
Sustainability
English (United States)
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?
DiscoverCommunity
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
Ask the Microsoft Community
Microsoft Tech Community
Windows Insiders
Microsoft 365 Insiders
Was this information helpful?
Yes No
Thank you! Any more feedback for Microsoft? Can you help us improve? (Send feedback to Microsoft so we can help.)
What affected your experience?
Resolved my issue
Clear instructions
Easy to follow
No jargon
Pictures helped
Other
Didn't match my screen
Incorrect instructions
Too technical
Not enough information
Not enough pictures
Other
Any additional feedback? (Optional) To protect your privacy, do not provide any personal information such as email address, phone number, product key, password, or credit card number.
Submit feedback
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.
Thank you for your feedback!
×
What's new
Surface Pro
Surface Laptop
Surface Laptop Studio 2
Copilot for organizations
Copilot for personal use
AI in Windows
Explore Microsoft products
Windows 11 apps
Microsoft Store
Account profile
Download Center
Microsoft Store support
Returns
Order tracking
Certified Refurbished
Microsoft Store Promise
Flexible Payments
Education
Microsoft in education
Devices for education
Microsoft Teams for Education
Microsoft 365 Education
How to buy for your school
Educator training and development
Deals for students and parents
AI for education
Business
Microsoft Cloud
Microsoft Security
Dynamics 365
Microsoft 365
Microsoft Power Platform
Microsoft Teams
Microsoft 365 Copilot
Small Business
Developer & IT
Azure
Microsoft Developer
Microsoft Learn
Support for AI marketplace apps
Microsoft Tech Community
Microsoft Marketplace
Marketplace Rewards
Visual Studio
Company
Careers
About Microsoft
Company news
Privacy at Microsoft
Investors
Diversity and inclusion
Accessibility
Sustainability
English (United States)