Then use theshortcutcontrol + enter to enter the formula in all cells at once.
The smallest number gets rank 1, the next smallest rank 2, and so on.
Because there are 18 numbers in the list, RANK will generate a rank of 1-18.
This is the mechanism by which the formula generates groups of equal size.
TheRANDARRAY functioncreates an array of random numbers of the same size.
Next, theSORTBY functionsorts the sequence in the order of the random numbers, effectively shuffling the sequence.
The result is the numbers 1 to 18 in a random order.
Then they handed off to the ROUNDUP function which rounds the numbers up to the nearest whole number.
The result from ROUNDUP is an array that contains 18 numbers between 1 and 3.
The final result is that each group contains 6 random names from the list.
There is no need for a helper column.
you might use INDEX to retrieve individual values, or entire rows and columns.
For example, =RAND() will generate a number like 0.422245717.
RAND recalculates when a worksheet is opened or changed.
RANK can rank values from largest to smallest (i.e.
top sales) as well as smallest to largest (i.e.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
RANDARRAY Function
The Excel RANDARRAY function generates an array of random numbers between two values.
The size or the array is specified byrowsandcolumnsarguments.
The generated values can be either decimals or whole numbers.
The range or array used to sort does not need to appear in results.
LET Function
The Excel LET function lets you define named variables in a formula.
