Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Data Series in Excel
  • Move chart to a separate worksheet in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Working With Tables in Excel

References

  • Count unique text values with criteria
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Convert text string to valid reference in Excel using Indirect function
  • Two-way lookup with VLOOKUP in Excel
  • How to use Excel MMULT Function

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation no punctuation
  • Prevent invalid data entering in specific cells
  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only

How to randomly assign people to groups in Excel

by

To randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions.

Formula

=ROUNDUP(RANK(A1,randoms)/size,0)

How to randomly assign people to groups in Excel

Explanation

In the example shown, the formula in D5 is:

=ROUNDUP(RANK(C5,randoms)/size,0)

which returns a group number for each name listed in column B, where “randoms” is the named range C5:C16, and “size” is the named range G5.

How this formula works

At the core of this solution is the RAND function, which is used to generate a random number in a helper column (column C in the example).

Worked Example:   CEILING function: Description, Usage, Syntax, Examples and Explanation

To assign a full set of random values in one step, select the range C5:C16, and type =RAND() in the formula bar. Then use the shortcut control + enter to enter the formula in all cells at once.

Note: the RAND function will keep generating random values every time a change is made the worksheet, so typically you will want to replace the results in column C with actual values using paste special to prevent changes after random values are assigned.

Worked Example:   How to Use RANK function in Excel

In column D, a group number is assigned with the following formula:

=ROUNDUP(RANK(C5,randoms)/size,0)

The RANK function is used to rank the value in C5 against all random values in the list. The result will be a number between 1 and the total number of people (12 in this example).

This result is then divided by “size”, which represents the desired group size (3 in the example), which then goes into the ROUNDUP function as number, with num_digits of zero. The ROUNDUP function returns a number rounded up to the next integer. This number represents assigned group number.

Worked Example:   Round numbers in Excel

CEILING version

The CEILING function can be used instead of ROUNDUP. Like the ROUNDUP function, CEILING also rounds up but instead of rounding to a given number of decimal places, CEILING rounds to a given multiple.

=CEILING(RANK(C5,randoms)/size,1)

Post navigation

Previous Post:

How to fill cell ranges with random number from fixed set of options in Excel

Next Post:

How to create dynamic named range with INDEX in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • How to use Excel TRUE Function
  • How to use Excel NOT Function
  • OR function Examples in Excel

Date Time

  • Display Date is workday in Excel
  • Get month from date in Excel
  • Add decimal minutes to time in Excel
  • How to calculate workdays per month in Excel
  • How to Calculate Age in Excel

Grouping

  • Group times into unequal buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • Calculate conditional mode with criteria in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel

General

  • Convert column number to letter in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • How to calculate percent variance in Excel
  • Freeze and Unfreeze Panes in Excel
  • Currency vs Accounting Format in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning