Excel functions / Dynamic arrays
RANDARRAY function
Fills an array with random numbers.
Syntax
=RANDARRAY([rows], [columns], [min], [max], [whole_number])
Arguments
rows(optional): How many rows (default 1).columns(optional): How many columns (default 1).min(optional): The smallest number (default 0).max(optional): The largest number (default 1).whole_number(optional): TRUE for whole numbers; FALSE (default) for decimals.
When to use it
RANDARRAY fills a block of cells with random numbers in one formula: choose how many rows and columns, the lowest and highest, and whether they are whole numbers. With SORTBY it shuffles a list.
Examples
Five random whole numbers from 1 to 6, all in range
A B C 1 Check TRUE In C1:
=AND(RANDARRAY(5, 1, 1, 6, TRUE)>=1, RANDARRAY(5, 1, 1, 6, TRUE)<=6)→ TRUEA shuffled list keeps every name
A B C D 1 Name Score 2 Ana 72 5 3 Ben 48 4 Chloe 90 5 Dev 65 6 Eve 81 In D2:
=ROWS(SORTBY(A2:A6, RANDARRAY(5)))→ 5
Common mistakes
- Repeats. Random whole numbers can repeat. For a draw with no repeats, shuffle a list with SORTBY and take the top.
- Older versions of Excel. RANDARRAY needs Excel for Microsoft 365 or Excel 2021 and later.
RANDARRAY in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Danish: SLUMPMATRIX
- Finnish: SATUNN.MATRIISI
- German: ZUFALLSMATRIX
- Hungarian: VÉLETLENTÖMB
- Italian: MATR.CASUALE
- Norwegian: TILFELDIGMATRISE
- Polish: LOSOWA.TABLICA
- Portuguese (Brazil): MATRIZALEATÓRIA
- Portuguese (Portugal): MATRIZALEATÓRIA
- Spanish: MATRIZALEAT
- Swedish: SLUMPMATRIS
- Turkish: RASGDİZİ
Try RANDARRAY in the playground
Related functions
- RAND: A random number between 0 and 1. Changes on every edit.
- RANDBETWEEN: A random whole number between two limits.
- SORTBY: Sorts a range by the values in another range.
- SEQUENCE: Generates a list of sequential numbers that spills into neighbouring cells.