The RANDARRAY function is a Math & Trigonometry formula that creates an array of randomly generated numbers. In this guide, we’re going to show you how to use the RANDARRAY function and also go over some tips and error handling methods.
Supported versions
- At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it’s ready, the feature is planned for release for Office 365 users.
Syntax
Arguments
[rows] |
Optional. The number of rows of randomly generated numbers to be returned. The default value is 1. |
[columns] |
Optional. The number of columns of randomly numbers to be returned. The default value is 1. |
[min] |
Optional. The minimum number to be returned. The default value is 0. |
[max] |
Optional. The maximum number to be returned. The default value is 1. |
[whole_number] |
Optional. A Boolean value that specifies the type of numbers to return. · FALSE: Decimal number to 15 decimal places. (default) · TRUE: Whole number |
Examples
Row/column only list
=RANDARRAY(1,10)
Row & column array
Random Whole Numbers
Tips
- The RANDARRAY function is a volatile function. This means that it re-calculates the results with every calculation and this can affect the performance of the workbook.
- If you enter nothing for the parameters, the RANDARRAY function acts like the RAND function.=RANDARRAY()
- You can use RANDARRAY as a substitute for the RANDBETWEEN. This requires omitting the [rows] and [columns] arguments and setting [whole_number] to TRUE. For example, to return values between 5 and 10, use =RANDARRAY(,,5,10,TRUE)
- You can combine the RANDARRAY with the SORTBY function to shuffle an array of values.
Issues
#SPILL!
If there isn’t enough space for adding the results below the formula, you will see a #SPILL! error. Excel marks the target range with dashed lines. Clear the contents of the cells in this range, and Excel will automatically update the results.
#VALUE!
- If the [min] is greater than or equal to the [max] you will get a #VALUE! error.
- If any of the argument is not a numeric value, you will get a #VALUE! error.