Excel functions / Dynamic arrays

RANDARRAY function

Fills an array with random numbers.

Syntax

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

Arguments

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

  1. Five random whole numbers from 1 to 6, all in range

    ABC
    1CheckTRUE

    In C1: =AND(RANDARRAY(5, 1, 1, 6, TRUE)>=1, RANDARRAY(5, 1, 1, 6, TRUE)<=6) → TRUE

    Try this example

  2. A shuffled list keeps every name

    ABCD
    1NameScore
    2Ana725
    3Ben48
    4Chloe90
    5Dev65
    6Eve81

    In D2: =ROWS(SORTBY(A2:A6, RANDARRAY(5))) → 5

    Try this example

Common mistakes

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.

Translate a whole formula

Try RANDARRAY in the playground

Related functions