PROBLEM: How to build a table with random values, characters or numbers?
For example: =TEXTJOIN("";1;INDEX(CHAR(SEQUENCE(26;1;65;1));RANDARRAY(4;1;1;26;TRUE)));
This formula is a nested TEXTJOIN, INDEX, SEQUENCE, and RANDARRAY function that returns a string of two random uppercase letters from the English alphabet.
It is used to count the number of times a specific value appears in a table, given certain conditions.
Here is a breakdown of the formula:
TEXTJOIN(delimiter, ignore_empty, text1, [text2],…)
Joins 2 random numbers from 1 to 26 delimited by "", ignoring empty values.
At our table:
TEXTJOIN("";1;INDEX(SEQUENCE(26;1;1;1);RANDARRAY(2;1;1;26;TRUE))
4 24
Joins 4 random characters from A to Z delimited by "", ignoring empty values.
At our table:
TEXTJOIN("";1;INDEX(CHAR(SEQUENCE(26;1;65;1));RANDARRAY(4;1;1;26;TRUE)))
B J L H
SEQUENCE(rows, [columns], [start], [step])
When the number of rows are determined we can have a sequence.
At our table:
| SEQUENCE(26;1;1) | SEQUENCE(26;1;65) |
|---|---|
| 1 | 65 |
| 2 | 66 |
| 3 | 67 |
| 4 | 68 |
| 5 | 69 |
| 6 | 70 |
| 7 | 71 |
| 8 | 72 |
| 9 | 73 |
| 10 | 74 |
| 11 | 75 |
| 12 | 76 |
| 13 | 77 |
| 14 | 78 |
| 15 | 79 |
| 16 | 80 |
| 17 | 81 |
| 18 | 82 |
| 19 | 83 |
| 20 | 84 |
| 21 | 85 |
| 22 | 86 |
| 23 | 87 |
| 24 | 88 |
| 25 | 89 |
| 26 | 90 |
CHAR(number)
Determines the letter behind each number. The number "65" correponds to the character "A",
At our table:
CHAR(SEQUENCE(26;1;65;1)) (SEQUENCE(26;1;65;1)
A 65 B 66 C 67 D 68 E 69 F 70 G 71 H 72 I 73 J 74 K 75 L 76 M 77 N 78 O 79 P 80 Q 81 R 82 S 83 T 84 U 85 V 86 W 87 X 88 Y 89 Z 90
RANDARRAY([rows],[columns],[min], [max],[whole_number])
It determines the line, from 1 to 26 (our decision) and from 65 to 90 to determine the character;
At our table:
| Characters | |
|---|---|
| RANDARRAY(4;1;65;90;TRUE) | Matches |
| 87 | V |
| 71 | G |
| 70 | F |
| 68 | D |
For numbers in our table: RANDARRAY(4;1;1;26;TRUE)
INDEX(array, row_num, [col_num], [area_num])
Lists the 2 random numbers determined above, ignoring empty values.
At our table:
From our example, above::
INDEX(SEQUENCE(26;1;1;1);RANDARRAY(2;1;1;26;TRUE))
4 24
Lists the 4 random characters determined above, ignoring empty values.
From our example, above::
INDEX(CHAR(SEQUENCE(26;1;65;1));RANDARRAY(4;1;1;26;TRUE))
B J L H