Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to get address of named range in Excel
  • Left Lookup in Excel
  • How to reference named range different sheet in Excel
  • How to use Excel MATCH Function

Data Analysis Examples

  • Create Scatter Chart in Excel
  • How to calculate average last N values in a table in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Excel Pie Chart
  • How to create Checklist in Excel

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation must not contain
  • Excel Data validation whole percentage only
  • Excel Data validation number multiple 100
  • Excel Data validation don’t exceed total

How to randomly assign data to groups in Excel

by

To randomly people (or anything) to groups you can use the RANDBETWEEN function with the CHOOSE function.

Formula

=CHOOSE(RANDBETWEEN(1,3),"Group A","Group B","Group B")

Explanation

In the example shown, the formula in F3 is:

=CHOOSE(RANDBETWEEN(1,3),"A","B","C")

When copied down the column, this formula will generate a random group (A, B, or C) for each person in the list.

Worked Example:   How to generate random times at specific intervals in Excel

How this formula works

The RANDBETWEEN function generates random numbers between two integers. The CHOOSE function takes a number as the first argument, and uses that number to select the “nth” item from the following arguments.

So, in this formula, RANDBETWEEN generates a number between 1 and 3, this number is used to choose a group from the 3 following values: “A”,”B”,”C”.

Worked Example:   How to fill cell ranges with random text values in Excel

You can use this same approach any time you need make random assignments. It’s especially useful when you you need to assign data to a limited number of text values.

Worked Example:   Get fiscal quarter from date in Excel

Automatic recalculation

Be aware that RANDBETWEEN will re-calculate whenever there is any change to a workbook, or even when a workbook is opened. Once you have a set of random assignments, you may want to copy and paste the formulas as values to prevent further changes.

Post navigation

Previous Post:

How to get random value from list or table in Excel

Next Post:

How to fill cell ranges with random text values 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
  • How to strip protocol and trailing slash from URL in Excel
  • Split numbers from units of measure in Excel
  • LEFT, LEFTB functions: Description, Usage, Syntax, Examples and Explanation
  • How to Separate Text Strings in Excel
  • How to use double quotes inside a formula in Excel
  • Get fiscal year from date in Excel
  • Extract time from a date and time in Excel
  • Add years to date in Excel
  • Display Date is same month in Excel
  • Get first Monday before any date in Excel
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • How to calculate principal for given period in Excel
  • How to Calculate Tax Rates in Excel
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • PRICEDISC function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles