RANDARRAY function

RANDARRAY(size?, minimumValue?, maximumValue?, onlyIntegers?) RANDARRAY(size?; minimumValue?; maximumValue?; onlyIntegers?)

size — Number (optional)

The size of the returned array. If omitted, 1 is assumed.

minimumValue — Number (optional)

The minimum number put in the returned array. If omitted, 0 is assumed.

maximumValue — Number (optional)

The maximum number put in the returned array. If omitted, 1 is assumed.

onlyIntegers — Logical (optional)

Whether only integers (whole numbers) should be used, as opposed to decimal numbers. If omitted, FALSE is assumed.

Returns — { Number }

An array with random numbers.

Returns an array with random numbers. RANDARRAY()RANDARRAY() returns an array with a single value, which is between 0 and 1 (inclusive). RANDARRAY(10)RANDARRAY(10) returns 10 such numbers. RANDARRAY(10, 20, 30)RANDARRAY(10; 20; 30) returns 10 numbers between 20 and 30 (inclusive). Finally, RANDARRAY(10, 20, 30, TRUE)RANDARRAY(10; 20; 30; TRUE) returns 10 integers (whole numbers), which are all between 20 and 30 (again, inclusive).

Returning a random text string

To return a text string containing five, six or seven random letters, use this formula:

TEXTJOIN("", FALSE, INDEX(TEXTSPLIT("abcdefghijklmnopqrstuvwxyz", ""), RANDARRAY(RANDBETWEEN(5, 7), 1, 26, TRUE)))TEXTJOIN(""; FALSE; INDEX(TEXTSPLIT("abcdefghijklmnopqrstuvwxyz"; ""); RANDARRAY(RANDBETWEEN(5; 7); 1; 26; TRUE)))

Let's break the formula apart to see how it works. Consider this part of the formula:

The part above returns an integer which is either 5, 6 or 7. This value is given as the first parameter to RANDARRAY, and as a result determines the size of the array returned by RANDARRAY.

Next, consider this part:

RANDARRAY(RANDBETWEEN(5, 7), 1, 26, TRUE)RANDARRAY(RANDBETWEEN(5; 7); 1; 26; TRUE)

The part above returns an array, whose size is 5, 6 or 7, consisting of integers between 1 and 26 (inclusive). Let's say that RANDARRAY returns { 4, 22, 2, 14, 5 }{ 4; 22; 2; 14; 5 }.

Next is a part that uses TEXTSPLIT:

TEXTSPLIT("abcdefghijklmnopqrstuvwxyz", "")TEXTSPLIT("abcdefghijklmnopqrstuvwxyz"; "")

This part returns the array { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" }{ "a"; "b"; "c"; "d"; "e"; "f"; "g"; "h"; "i"; "j"; "k"; "l"; "m"; "n"; "o"; "p"; "q"; "r"; "s"; "t"; "u"; "v"; "w"; "x"; "y"; "z" }.

TEXTSPLIT can be used to split text strings like "a ; b ; c" into the array { "a", "b", "c" }{ "a"; "b"; "c" } by specifying " ; " as a delimiter. Here, an empty text string is used instead, ensuring that every letter is put into its own array element. The array could have been typed manually, but using TEXTSPLIT saves some space and makes the formula easier to read.

(An even more compact way of producing an array with the 26 lower-case letters of the English alphabet is to use CHAR(SEQUENCE(26, 97))CHAR(SEQUENCE(26; 97))). SEQUENCE is instructed to produce character codes for the 26 letters of the alphabet, where a lower-case "a" starts at 97. CHAR then produces one letter for every character code.)

The second last formula part uses INDEX:

INDEX({ "a", "b", ..., "z" }, { 4, 22, 2, 14, 5 })INDEX({ "a"; "b"; ..., "z" }; { 4; 22; 2; 14; 5 })

(Assume that the two parameters to INDEX have been expanded with the values mentioned above.)

This formula part returns the desired word, but as an array of letters instead of as a text string: { "d", "v", "b", "n", "e" }{ "d"; "v"; "b"; "n"; "e" }. INDEX returns an array element at a specific position, meaning that INDEX({ 10, 20, 30 }, 2)INDEX({ 10; 20; 30 }; 2) returns 20, the array element at position 2.

If the second parameter to INDEX, which specifies the position, is instead given as an array, INDEX returns an array with the desired results. INDEX({ 10, 20, 30 }, { 2, 3 })INDEX({ 10; 20; 30 }; { 2; 3 }) returns { 20, 30 }{ 20; 30 }. The formula fragment INDEX({ "a", "b", ..., "z" }, { 4, 22, 2, 14, 5 })INDEX({ "a"; "b"; ..., "z" }; { 4; 22; 2; 14; 5 }) makes use of this feature to ultimately return the array { "d", "v", "b", "n", "e" }{ "d"; "v"; "b"; "n"; "e" }.

The final step is to assemble the array { "d", "v", "b", "n", "e" }{ "d"; "v"; "b"; "n"; "e" } into the text string "dvbne" using TEXTJOIN:

TEXTJOIN("", FALSE, { "d", "v", "b", "n", "e" })TEXTJOIN(""; FALSE; { "d"; "v"; "b"; "n"; "e" })

This final formula part returns the final text string. TEXTJOIN can join array elements together with a delimeter (say, a comma), but is here instructed to use the empty text string "". It can also be instructed to ignore empty array elements, but as there are no empty array elements here, the second parameter is set to FALSE.

Examples

RANDARRAY()RANDARRAY()

An array with a single random decimal number, which is between 0 and 1 (inclusive).

RANDARRAY(5)RANDARRAY(5)

An array with five random decimal numbers, which are between 0 and 1 (inclusive).

RANDARRAY(5, 10, 20)RANDARRAY(5; 10; 20)

An array with five random decimal numbers, which are between 10 and 20 (inclusive).

RANDARRAY(5, 10, 20, TRUE)RANDARRAY(5; 10; 20; TRUE)

An array with five random integers (whole numbers), which are between 10 and 20 (inclusive).

INDEX(TEXTSPLIT("aeiou", ""), RANDARRAY(2, 1, 5, TRUE))INDEX(TEXTSPLIT("aeiou"; ""); RANDARRAY(2; 1; 5; TRUE))

Returns an array containing two random vowels (such as { "o", "i" }{ "o"; "i" } or { "a", "u" }{ "a"; "u" }). RANDARRAY(2, 1, 5, TRUE)RANDARRAY(2; 1; 5; TRUE) returns a random array with two elements and integers between 1 and 5 (inclusive), such as { 1, 5 }{ 1; 5 }. TEXTSPLIT("aeiou", "")TEXTSPLIT("aeiou"; "") returns the array { "a", "e", "i", "o", "u" }{ "a"; "e"; "i"; "o"; "u" }. INDEX finally joins the random letter positions together with the vowels to produce the final array. Refer to the main text for details on a similar formula.