How to Use the RANDARRAY Function in Excel

RANDARRAY Function in Excel and Google Sheets

Overview

The RANDARRAY function generates an array of random numbers, each between 0 and 1. This functionality is available in both Excel and Google Sheets. It allows users to define the desired size of the output array by specifying the number of rows and columns. This function is particularly useful in various applications such as simulations, modeling, and statistical analysis where random numbers are required.

Syntax

The syntax for the RANDARRAY function is as follows:

=RANDARRAY(rows, columns, min, max, unique)
  • rows: Specifies the number of rows in the array.
  • columns: [Optional] Defines the number of columns in the array. If not specified, the function returns a single-column array.
  • min: [Optional] Sets the minimum value for the random numbers. The default is 0.
  • max: [Optional] Sets the maximum value for the random numbers. The default is 1.
  • unique: [Optional] Determines whether the random numbers should be unique. The default setting is FALSE.

Examples

Here are some examples of how the RANDARRAY function can be used:

Example 1: Generating a 1-D Random Array

To create a one-dimensional array of 5 random numbers, use the formula:

=RANDARRAY(5)

Example 2: Generating a 2-D Random Array

To create a 3×3 array with random numbers ranging from 10 to 20, apply the following formula:

=RANDARRAY(3, 3, 10, 20)

Example 3: Generating Unique Random Numbers

To generate an array of 10 unique random numbers between 1 and 100, use this formula:

=RANDARRAY(10, 1, 1, 100, TRUE)

Usage Tips

  • Refreshing Random Numbers: The function recalculates and updates the random numbers each time a modification is made to the worksheet.
  • Creating Random Dates and Times: Random numbers generated by RANDARRAY can be formatted as dates or times to simulate random date or time values.
  • Dynamic Data Generation: Combining RANDARRAY with other functions can help in creating dynamic and random data sets for testing or demonstration purposes.

The RANDARRAY function offers a versatile and powerful way to generate random numbers efficiently in Excel and Google Sheets. Its ability to customize the size and characteristics of the returned array makes it an essential tool for a wide range of tasks in spreadsheet analysis and modeling.

More information: https://support.microsoft.com/en-us/office/randarray-function-21261e55-3bec-4885-86a6-8b0a47fd4d33

Other functions
Returns the absolute value of a number
Returns the arccosine of a number
Returns the inverse hyperbolic cosine of a number
Returns the arccotangent of a number
Returns the hyperbolic arccotangent of a number
Returns an aggregate in a list or database
Converts a Roman number to Arabic, as a number
Returns the arcsine of a number
Returns the inverse hyperbolic sine of a number
Returns the arctangent of a number
Returns the arctangent from x- and y-coordinates
Returns the inverse hyperbolic tangent of a number
Converts a number into a text representation with the given radix (base)
Rounds a number to the nearest integer or to the nearest multiple of significance
Rounds a number up, to the nearest integer or to the nearest multiple of significance
Rounds a number the nearest integer or to the nearest multiple of significance Regardless of the sign of the number, the number is rounded up
Returns the number of combinations for a given number of objects
Returns the number of combinations (with repetitions) for a given number of items
Returns the cosine of a number
Returns the hyperbolic cosine of a number
Returns the hyperbolic cosine of a number
Returns the cotangent of an angle
Returns the cosecant of an angle
Returns the hyperbolic cosecant of an angle
Converts a text representation of a number in a given base into a decimal number
Converts radians to degrees
Rounds a number up to the nearest even integer
Returns
Returns the factorial of a number
Returns the double factorial of a number
Rounds a number down, to the nearest integer or to the nearest multiple of significance
Rounds a number the nearest integer or to the nearest multiple of significance Regardless of the sign of the number, the number is rounded up
Returns the greatest common divisor
Rounds a number down to the nearest integer
Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance
Returns the least common multiple
Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
Returns the natural logarithm of a number
Returns the logarithm of a number to a specified base
Returns the base-10 logarithm of a number
Returns the matrix determinant of an array
Returns the matrix inverse of an array
Returns the matrix product of two arrays
Returns the remainder from division
Returns a number rounded to the desired multiple
Returns the multinomial of a set of numbers
Returns the unit matrix or the specified dimension
Rounds a number up to the nearest odd integer
Returns the value of pi
Returns the result of a number raised to a power
Multiplies its arguments
Returns the integer portion of a division
Converts degrees to radians
Returns a random number between 0 and 1
Returns a random number between the numbers you specify
Converts an arabic numeral to roman, as text
Rounds a number to a specified number of digits
Rounds a number down, toward zero
Rounds a number up, away from zero
Returns the secant of an angle
Returns the hyperbolic secant of an angle
Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
Returns the sum of a power series based on the formula
Returns the sign of a number
Returns the sine of the given angle
Returns the hyperbolic sine of a number
Returns a positive square root
Returns the square root of (number * pi)
Returns a subtotal in a list or database
Adds its arguments
Adds the cells specified by a given criteria
Adds the cells in a range that meet multiple criteria
Returns the sum of the products of corresponding array components
Returns the sum of the squares of the arguments
Returns the sum of the difference of squares of corresponding values in two arrays
Returns the sum of the sum of squares of corresponding values in two arrays
Returns the sum of squares of differences of corresponding values in two arrays
Returns the tangent of a number
Returns the hyperbolic tangent of a number
Truncates a number to an integer