This tutorial demonstrates how to generate random numbers, shuffle lists, perform random selections, and randomly assign data to groups using Excel's RANDARRAY function. RANDARRAY surpasses older functions like RAND and RANDBETWEEN in its versatility. It allows you to specify minimum and maximum values, define the output array's dimensions (rows and columns), and choose between integer and decimal outputs. Combined with other functions, RANDARRAY enables sophisticated randomization tasks.

Excel's RANDARRAY Function

RANDARRAY returns an array of random numbers within a specified range. It's a dynamic array function in Microsoft 365 and Excel 2021, automatically spilling results into the defined area.

Syntax:

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

• rows (optional): Number of rows in the output array (defaults to 1).
• columns (optional): Number of columns in the output array (defaults to 1).
• min (optional): Minimum random number (defaults to 0).
• max (optional): Maximum random number (defaults to 1).
• whole_number (optional): TRUE for integers, FALSE or omitted for decimals (defaults to FALSE).

Key Considerations:

• Excel Version: Only available in Microsoft 365 and Excel 2021.
• Spill Range: Ensure sufficient empty cells for the output array to avoid the #SPILL! error.
• Default Values: Omitting arguments uses default values (1 row, 1 column, min=0, max=1, decimals).
• Decimal Arguments: Decimal row/column arguments are truncated to integers.
• Volatile Function: Recalculates with every worksheet change. Use "Paste Special > Values" to fix results.

Basic RANDARRAY Formula:

To generate a 5x3 array of random numbers: =RANDARRAY(5, 3)

Advanced RANDARRAY Examples:

• Random Numbers Between Two Numbers: Generate a 6x4 array of random integers from 1 to 100: =RANDARRAY(6, 4, 1, 100, TRUE)

• Random Dates: Generate 10 random dates between dates in cells D1 and D2: =RANDARRAY(10, 1, D1, D2, TRUE) (Format cells as Date).

• Random Workdays: Generate 10 random workdays between dates in D1 and D2: =WORKDAY(RANDARRAY(10, 1, D1, D2, TRUE), 1) (Format cells as Date).

• Random Numbers Without Duplicates: This requires combining functions (see the original for the formula – it's too complex to reproduce here concisely).

• Random Sort: Shuffle a list in A2:A13: =SORTBY(A2:A13, RANDARRAY(ROWS(A2:A13)))

• Random Sample: Select 3 random names from A2:A10: =INDEX(A2:A10, RANDARRAY(3, 1, 1, ROWS(A2:A10), TRUE))

• Random Row Selection: Select random rows (with duplicates possible) from A2:B10 (sample size in D2): =INDEX(A2:B10, RANDARRAY(D2, 1, 1, ROWS(A2:A10), TRUE), {1,2})

• Random Assignment: Assign numbers 1-3 to A2:A13 (values in D2:D4): =CHOOSE(RANDARRAY(ROWS(A2:A13), 1, 1, 3, TRUE), D2, D3, D4)

• Random Assignment to Groups (Evenly): This involves a more complex formula using RANK and ROUNDUP (see original for details).

Troubleshooting:

• #SPILL!: Insufficient space for the output array.
• #VALUE!: Invalid arguments (e.g., max
• #NAME!: Misspelled function name or unavailable in your Excel version.
• #CALC!: Rows or columns argument is less than 1 or refers to a blank cell.

This enhanced summary provides a more concise yet comprehensive overview of the RANDARRAY function and its applications. Remember to consult the original for the more complex formulas and detailed explanations.

The above is the detailed content of Excel RANDARRAY function - quick way to generate random numbers. For more information, please follow other related articles on the PHP Chinese website!