random group assignment excel

Randomly assign people to groups

Related functions .

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. In the example shown, the formula in D5 is:

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.

Generic formula

Explanation .

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).

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.

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

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.

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.

Related formulas

• Randomly assign data to groups

• Random text values

• Random number from fixed set of options

Related functions

• RAND Function

The Excel RAND function returns a random number between 0 and 1. For example, =RAND() will generate a number like 0.422245717. RAND recalculates when a worksheet is opened or changed.

• ROUNDUP Function

The Excel ROUNDUP function returns a number rounded up to a given number of decimal places. Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDUP rounds all numbers up .

• CEILING Function

The Excel CEILING function rounds a given number up to the nearest specified multiple. CEILING works like the MROUND function , but CEILING always rounds up .

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

Get training, quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

• Kutools for Excel
• Kutools for Outlook
• Kutools for Word
• Setup Made Simple
• End User License Agreement
• Get 4 Software Bundle
• 60-Day Refund
• Tips & Tricks for Excel (3000+)
• Tips & Tricks for Outlook (1200+)
• Tips & Tricks for Word (300+)
• Excel Functions (498)
• Excel Formulas (350)
• Excel Charts
• Outlook Tutorials
• About Us Our Team

Feature Tutorials

• Search Search more
• Retrieve License Lost License?
• Report a Bug Bug Report
• Forum Post in Forum

Quickly generate random groups for list of data in Excel

Sometimes, you may want to randomly assign data to groups as screenshot 1 shown, or generate groups for a list of names as below screenshot 2 shown, but how can handle these jobs quickly? Actually, in Excel, you can use formulas to solve them easily.

Randomly assign data to groups

Generate random groups in a specified data size, download sample file.

If you want to randomly assign data to a specified number of groups, each group is allowed with different numbers of data, you can use the CHOOSE and RANDBETWEEN functions.

Select a blank cell next to the list you want to assign to random groups, copy or type this formula

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

In the formula, (1, 3) indicates to group data into 3 groups, Group A, Group B and Group C are the texts will be displayed in formula cells which used to distinguish different groups.

Then the list of data has been randomly assigned to groups, and each group may have different numbers of data.

The calculated results will not be fixed, they will be recalculated if there is any change to the workbook.

If you want to generate random groups for a list of data, and each group has a specified data size, you can use the ROUNDUP and RANK functions.

1. Firstly, you need a helper column to list some random data next to your data. Supposing in cell E2, type this formula

Then drag fill handle down to fill this formula to cells you use.

2. In the next column, supposing in cell F2, copy or type this formula

=ROUNDUP(RANK(E2,$E$2:$E$13)/4,0)

E2:E13 is the range that contains formula =RAND(), 4 is the number of data that you want each group contains.

Click to download sample file

Other Popular Articles

Remove first or last n characters from a cell or string in Excel

Extract part of text string from cell in Excel?

Two Easy Ways to convert or import Word document contents to Excel worksheet

Calculate the absolute difference between two values/times in Excel

More articles

Excel Productivity Tools

The best office productivity tools, kutools for excel solves most of your problems, and increases your productivity by 80%.

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range ...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average ... Prevent Duplicate Cells; Compare Ranges ...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets , Check Boxes and more...
• Favorite and Quickly Insert Formulas , Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text , Add Text, Remove by Position, Remove Space ; Create and Print Paging Subtotals; Convert Between Cells Content and Comments ...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets ; Merge Tables based on key columns; Split Data into Multiple Sheets ; Batch Convert xls, xlsx and PDF ...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name ...

Office Tab - brings tabbed interface to Office, and make your work much easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Statistics Made Easy

How to Create Random Groups in Excel (With Example)

Often you may want to create random groups in Excel.

For example, you might want to assign 12 basketball players to one of three random teams:

Fortunately this is easy to do and the following step-by-step example shows how to do so.

Step 1: Enter Original Data

First, let’s enter the names of 12 basketball players that we’d like to assign to random teams:

Step 2: Generate Random Values

Next, we will generate a random number between 0 and 1 for each player by typing the following formula into cell B2 :

We can then click and drag this formula down to each remaining cell in column B:

Each player now has a random value associated with them between 0 and 1.

Step 3: Generate Random Groups

Next, we will assign each player to a random group.

To do so, we will type the following formula into cell C2 :

We can then click and drag this formula down to each remaining cell in column C:

Column C now assigns each player to one of three random teams.

For example:

• Andy has been assigned to Team 2.
• Bob has been assigned to Team 2.
• Chad has been assigned to Team 1.

Note that the value after the division symbol in the formula specifies the number of players to include in each group.

For example, we could change this number from 4 to 6 to instead include 6 players on each team:

We can type this formula into cell C2 and then click and drag it down to each remaining cell in column C:

Notice that six players are now assigned to Team 1 and six players are assigned to Team 2.

Feel free to change the number after the division symbol in the formula to assign a different number of players to each team.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Generate Random Number Between Range in Excel How to Randomly Select Cells Based on Criteria in Excel How to Select a Random Sample in Excel

Published by Zach

Leave a reply cancel reply.

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

How to Generate Random Groups in Excel (Formula)

- Written by Puneet

• Random Groups with Random Size (CHOOSE + RANDBETWEEN)
• Random Groups with Same Size (RAND + ROUND + RANK)

In both methods, we need to write a formula. And in this tutorial, we will learn both ways and understand them in detail.

In this example, you have a list of students with their names, and now you need to assign them a random group from north, south, east, and west.

Generating Random Groups in Excel

To write this formula, you can use the below steps:

• First, in a cell, enter the CHOOSE function.
• And in the first argument of the CHOOSE, which is index_num enter the RANDBETWEEN function.
• Now, in the RANDBETWEEN, enter “1” as the bottom and “4” as the top. So you have four groups to get the result; that’s why you need to use 1 and 4 to create a range of random numbers.
• Next, in the second argument of CHOOSE, enter the name of all four groups by using double quotation marks (“North”,”South”,”East”,”West”).
• In the end, hit enter to get the result. And drag the formula up to the last name.

Note: RANBBETWEEN is a volatile function that updates itself when you change your worksheet.

How this Formula Works

To understand this formula, you need to split it into two parts: In the first part, we have RANDBETWEEN, which returns a random number between 1 to 4 (as we have four groups).

In the second part, we have CHOOSE function, which returns a value from the list you define using the index_number. When RANDBETWEEN returns a random number, CHOOSE returns the value from the list using that number.

When you have 3 in the index number, CHOOSE returns “East” in the result.

But there’s a Problem.

When you use this formula, there’s no same-size grouping. So you can see in the result that the groups assigned to the students are not of the same size.

This method is only proper when you don’t want to consider the group size; otherwise, you need to use the formula we will discuss next.

Generating Random Groups (Same Size)

To use this formula, you need to create a helper column with the RAND function to get the random number between 0 and 1, just like the following.

Note: RAND is also a volatile function that changes its value. And here, I’m going to convert the formula into values .

After that , enter a new column and the RANK function. Then, in the number argument, specify the random number from the B2; in the ref argument, use the entire range of random numbers.

It creates a unique ranking for all the 12 students you have on the list. Now , you need to divide this ranking by three, as you need to have three students in a single group.

Next , you need to use the ROUNDUP to round these rankings upwards.

After using ROUNDUP, you get an even size group where each group has the same number of students (12 students in the four groups with three students in each group). Then, again, use the CHOOSE to convert these number groups into group names.

Get the Excel File

Related formulas.

• Create a Horizontal Filter in Excel
• Create a Star Rating Template in Excel
• Get File Name in Excel
• Get Sheet Name in Excel
• Quickly Generate Random Letters in Excel
• Randomize a List (Random Sort) in Excel
• Count Characters in Excel (Cell and Range)
• Get File Path (Excel Formula)
• Get the Value from a Cell
• Compare Two Strings (Text)
• Get the Domain from the Email ID
• Extract Only Numbers from a Text (String)
• Extract Text After and Before a Character in Excel
• Back to the List of Excel Formulas

How to Randomize a List in Excel Into Groups (5 Suitable Ways)

In this article, I will show you how to randomize a list in Excel into groups. While working on an Excel worksheet, you need to do a massive type of work. Sometimes you may need to rank any dataset randomly. In this article, I will show you how to randomize a list   in Excel properly. I hope this article will increase your Excel skills. However, follow the procedures step-by-step. I have added pictures for your better understanding.

How to Randomize a List in Excel Into Groups: 5 Suitable Ways

You can see the dataset I have used in this article in the following picture. The dataset has two columns called B and C . The dataset has the values called Student Id, and Name of students of a certain school. I will use this dataset to show how to randomize a list in Excel into groups. Follow the steps of every method one by one.

1. Randomize a List in Excel Into Groups Using RAND Function

In this method, you will know the randomization method of a list using the RAND function . The process is simple. Follow the procedures one by one. Hopefully, you will find interest in this method. I have made a slight change in the dataset. I am considering only the name column and added a new column called Random Rank here. However, follow the steps carefully.

• First, select the C5 cell and copy the following formula into it.

• After pressing Enter, you will find the following number.

• Then, Fill Handle the formula to copy down from C5 to C10 .

• Then, go to the Formula tab in your toolbar.
• After that, select the Calculation  option.
• Hence, select the manual option.

• Then, select the Home tab and go to the Editing  option.

• Then, select the Sort and Filter  option.

• Meanwhile, a pop-up window will appear. Select the Sort Largest to Smallest  option.

• Then, like the following window, select the Expand the Selection option and select the Sort  button.

• As a result, Excel will sort the Names like the following picture.

This is how the randomization of a list can happen.

Read More: List of Names for Practice in Excel

2. Implementing Excel RANDBETWEEN Function to Randomize a List

In this portion of this article, I will implement the RANDBETWEEN function to randomize a list in Excel. This is another short method. I hope this will increase your Excel skills. Follow the procedure step by step.

• First, select the C5  cell.

• Then, copy down the following formula in the selected cell.

• After pressing Enter , Excel will show the following result.

• Then, copy down the formula to the C10  cell.
• Hence, you will get random numbers like the picture given below.

• Moreover, to stop the automatic changing of the numbers, go to the Formulas tab and select the Calculation Options .
• Then, select the Manual  option.

• Then, go to the Home tab and select the Editing  option.

• After that, select the Sort & Filter  option.

• Hence, select the Sort Largest to Smallest  option.

• Meanwhile, the following window will appear. Select the Expand the selection option and then press the Sort  button.

• As a result, Excel will show the following sorted result.

This is how you can use the RANDBETWEEN function to randomize a list in Excel.

3. Combine RANDARRAY, SORTBY, and ROWS Functions

In this part of this article, I will Combine RANDARRAY, SORTBY, and ROWS Functions. This method is not as easy as the former two methods. Follow the method step by step given below.

• Then, copy the following formula in the selected cell.
• ROWS(B5:B10): Returns the row numbers of the array.
• RANDARRAY(ROWS(B5:B10): Returns an Array according to the ROWS
• SORTBY(B5:B10, RANDARRAY(ROWS(B5:B10))) : Returns the newly sorted names and spills the names throughout the whole column.

4. Using INDEX and RANDBETWEEN Functions

In this part of this article, I will use INDEX and RANDBETWEEN functions to randomize a list in Excel into groups. Follow the following steps to randomize the list.

• Select the C5 cell first.

• Then, write down the following formula in the selected cell.
• RANDBETWEEN(1,6): Returns a random number in a cell between 1 to 6.
• INDEX($B$5:$B$10, RANDBETWEEN(1,6),1): Returns randomly sorted names in a certain cell.

• After pressing Enter , the following result will be shown.

• After copying down the formula, Excel will show the following result.

We can randomize a list in Excel using the INDEX and RANDBETWEEN Functions.

5. Applying CHOOSE and RANDBETWEEN Functions

In this method, I will apply CHOOSE and RANDBETWEEN functions to randomize a list in Excel into groups. Follow the following steps.

• Write down the following formula in the selected cell.
• CHOOSE(RANDBETWEEN(1,6),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10): Returns the shuffled names in the C  column.

• Then, Copy down the formula from C5 to C10

• Hence, the following result will be found.

This is the procedure how to randomize a list in Excel into groups.

Read More: Excel Practice Exercises PDF with Answers

Things to Remember

• You should bear in mind that the SORTBY function can only be found in Excel 365.

Download Practice Workbook

In this article, I have tried to explain how to randomize a list in Excel into groups. I hope you have learned something new from this article. Now, extend your skill by following the steps of these methods. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.

<< Go Back to Randomize in Excel   |  Learn Excel

What is ExcelDemy?

Tags: Randomize in Excel

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

• Ablebits blog
• Random data

RANDARRAY function - quick way to generate random numbers in Excel

The tutorial shows how to generate random numbers, randomly sort a list, get random selection and randomly assign data to groups. All with a new dynamic array function - RANDARRAY.

As you probably know, Microsoft Excel already has a couple of randomizing functions - RAND and RANDBETWEEN . What is the sense in introducing another one? In a nutshell, because it's far more powerful and can replace both older functions. Apart from setting up your own maximum and minimum values, it lets you specify how many rows and columns to fill and whether to produce random decimals or integers. Used together with other functions, RANDARRAY can even shuffle data and pick a random sample.

Excel RANDARRAY function

• Basic RANDARRAY formula

Generate random numbers between two numbers

Generate random date between two dates.

• Create random workdays in Excel
• Generate random numbers without duplicates
• Random sort in Excel
• Get a random sample
• Select random rows
• Random assignment in Excel
• Randomly assign data to groups

Excel RANDARRAY function not working

The RANDARRAY function in Excel returns an array of random numbers between any two numbers that you specify.

It is one of six new dynamic array functions introduced in Microsoft Excel 365. The result is a dynamic array that spills into the specified number of rows and columns automatically.

The function has the following syntax. Please notice that all the arguments are optional:

Rows (optional) - defines how many rows to fill. If omitted, defaults to 1 row.

Columns (optional) - defines how many columns to fill. If omitted, defaults to 1 column.

Min (optional) - the smallest random number to produce. If not specified, the default 0 value is used.

Max (optional) - the largest random number to create. If not specified, the default 1 value is used.

Whole_number (optional) - determines what kind of values to return:

• TRUE - whole numbers
• FALSE or omitted (default) - decimal numbers

RANDARRAY function - things to remember

To efficiently generate random numbers in your Excel worksheets, there are 6 important points to take notice of:

• The RANDARRAY function is only available in Excel for Microsoft 365 and Excel 2021. In Excel 2019, Excel 2016 and earlier versions the RANDARRAY function is not available.
• If the array returned by RANDARRAY is the final result (output in a cell and not passed to another function), Excel automatically creates a dynamic spill range and populates it with the random numbers. So, be sure you have enough empty cells down and/or to the right of the cell where you enter the formula, otherwise a #SPILL error will occur.
• If none of the arguments is specified, a RANDARRAY() formula returns a single decimal number between 0 and 1.
• If the rows or/and columns arguments are represented by decimal numbers, they will be truncated to the whole integer before the decimal point (e.g. 5.9 will be treated as 5).
• If the min or max argument is not defined, RANDARRAY defaults to 0 and 1, respectively.
• Like other random functions, Excel RANDARRAY is volatile , meaning it generates a new list of random values every time the worksheet is calculated. To prevent this from happening, you can replace formulas with values by using Excel's Paste Special > Values feature.

Basic Excel RANDARRAY formula

And now, let me show you a random Excel formula in its simplest form.

Supposing you want to fill a range consisting of 5 rows and 3 columns with any random numbers. To have it done, set up the first two arguments this way:

• Rows is 5 since we want the results in 5 rows.
• Columns is 3 as we want the results in 3 columns.

All of the other arguments we leave to their default values and get the following formula:

=RANDARRAY(5, 3)

How to randomize in Excel - RANDARRAY formula examples

Below you will find a few advanced formulas that cover typical randomizing scenarios in Excel.

To create a list of random numbers within a specific range, supply the minimum value in the 3 rd argument and the maximum number in the 4 th argument. Depending on whether you need integers or decimals, set the 5 th argument to TRUE or FALSE, respectively.

As an example, let's populate a range of 6 rows and 4 columns with random integers from 1 to 100. For this, we set up the following arguments of the RANDARRAY function:

• Rows is 6 since we want the results in 6 rows.
• Columns is 4 as we want the results in 4 columns.
• Min is 1, which is the minimum value we wish to have.
• Max is 100, which is the maximum value to be generated.
• Whole_number is TRUE because we need integers.

Putting the arguments together, we get this formula:

=RANDARRAY(6, 4, 1, 100, TRUE)

Looking for a random date generator in Excel? The RANDARRAY function is an easy solution! All you have to do is input the earlier date (date 1) and later date (date 2) in predefined cells, and then reference those cells in your formula:

For this example, we have created a list of random dates between the dates in D1 and D2 with this formula:

Of course, nothing prevents you from supplying the min and max dates directly in the formula if you wish to. Just be sure you enter them in the format that Excel can understand:

=RANDARRAY(10, 1, "1/1/2020", "12/31/2020", TRUE)

To prevent mistakes, you can use the DATE function for entering dates:

=RANDARRAY(10, 1, DATE(2020,1,1), DATE(2020,12,31), TRUE)

Generate random workdays in Excel

To produce random working days, embed the RANDARRAY function in the first argument of WORKDAY like this:

RANDARRAY will create an array of random start dates, to which the WORKDAY function will add 1 workday and ensure that all the returned dates are working days.

With date 1 in D1 and date 2 in D2, here's the formula to produce a list of 10 weekdays:

How to generate random numbers without duplicates

Though modern Excel offers 6 new dynamic array functions, unfortunately, there is still no inbuilt function to return random numbers without duplicates.

To build your own unique random number generator in Excel, you will need to chain several functions together like shown below.

Random integers :

Random decimals :

• N is how many values you wish to generate.
• Min is the lowest value.
• Max is the highest value.

For example, to produce 10 random whole numbers with no duplicates, use this formula:

To create a list of 10 unique random decimal numbers , change TRUE to FALSE in the last argument of the RANDARRAY function or simply omit this argument:

Tips and notes:

• The detailed explanation of the formula can be found in How to generate random numbers in Excel without duplicates .
• In Excel 2019 and earlier, the RANDARRAY function is not available. Instead, please check out this solution .

How to randomly sort in Excel

To shuffle data in Excel, use RANDARRAY for the "sort by" array ( by_array argument) of the SORTBY function . The ROWS function will count the number of rows in your data set, indicating how many random numbers to generate:

With this approach, you can randomly sort a list in Excel, whether it contains numbers, dates or text entries:

Also, you can also shuffle rows without mixing your data:

How to get a random selection in Excel

To extract a random sample from a list, here's a generic formula to use:

Where n is the number of random entries you wish to extract.

For example, to randomly select 3 names from the list in A2:A10, use this formula:

=INDEX(A2:A10, RANDARRAY(3, 1, 1, ROWS(A2:A10), TRUE))

Or input the desired sample size in some cell, say C2, and reference that cell:

How this formula works:

At the core of this formula is the RANDARRAY function that creates a random array of integers, with the value in C2 defining how many values to generate. The minimal number is hardcoded (1) and the maximum number corresponds to the number of rows in your data set, which is returned by the ROWS function.

The array of random integers goes directly to the row_num argument of the INDEX function, specifying the positions of the items to return. For the sample in the screenshot above, it is:

=INDEX(A2:A10, {8;7;4})

How to select random rows in Excel

If your data set contains more than one column, then specify which columns to include in the sample. For this, supply an array constant for the last argument ( column_num ) of the INDEX function, like this:

=INDEX(A2:B10, RANDARRAY(D2, 1, 1, ROWS(A2:A10), TRUE), {1,2})

Where A2:B10 is the source data and D2 is the sample size.

How to randomly assign numbers and text in Excel

To do random assignment in Excel, use RANDBETWEEN together with the CHOOSE function in this way:

• Data is a range of your source data to which you want to assign random values.
• N is the total number of values to assign.
• Value1 , value2 , value3 , etc. are the values to be assigned randomly.

For example, to assign numbers from 1 to 3 to participants in A2:A13, use this formula:

For convenience, you can enter the values to assign in separate cells, say from D2 to D4, and reference those cells in your formula (individually, not as a range):

=CHOOSE(RANDARRAY(ROWS(A2:A13), 1, 1, 3, TRUE), D2, D3, D4)

How this formula works

At the heart of this solution is again the RANDARRAY function that produces an array of random integers based on the min and max numbers that you specify (from 1 to 3 in our case). The ROWS function tells RANDARRAY how many random numbers to generate. This array goes to the index_num argument of the CHOOSE function . For example:

=CHOOSE({1;2;1;2;3;2;3;3;1;3;1;2}, D2, D3, D4)

How to randomly assign data to groups

When your task is to randomly assign participants to groups, the above formula may not be suitable because it does not control how many times a given group is chosen. For example, 5 persons could be assigned to group A while only 2 persons to group C. To do random assignment evenly , so that each group has the same number of participants, you need a different solution.

First, you generate a list of random numbers by using this formula:

=RANDARRAY(ROWS(A2:A13))

And then, you assign groups (or anything else) by using this generic formula:

Where n is the group size, i.e. the number of times each value should be assigned.

For example, to randomly assign people to the groups listed in E2:E5, so that each group has 3 participants, use this formula:

=INDEX($E$2:$E$5, ROUNDUP(RANK(B2,$B$2:$B$13)/3,0))

Please notice that it's a regular formula (not a dynamic array formula!), so you need to lock the ranges with absolute references like in the above formula.

Please remember that the RANDARRAY function is volatile. To prevent generating new random values every time you change something in the worksheet, replace formulas with their values by using the Paste Special feature.

The RANDARRAY formula in the helper column is very simple and hardly requires explanation, so let us focus on the formula in column C.

The RANK function ranks the value in B2 against the array of random numbers in B2:B13. The result is a number between 1 and the total number of participants (12 in our case).

The rank is divided by the group size, (3 in our example), and the ROUNDUP function rounds it up to the nearest integer. The result of this operation is a number between 1 and the total number of groups (4 in this example).

When your RANDARRAY formula returns an error, these are the most obvious reasons to check:

#SPILL error

#value error.

A #VALUE! error may occur in these circumstances:

• If a max value is less than a min value.
• If any of the arguments is non-numeric.

#NAME error

In most cases, a #NAME! error indicates one of the following:

• The function's name is misspelled.
• The function is not available in your Excel version.

#CALC! error

That's how to build a random number generator in Excel with the new RANDARRAY function. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

You may also be interested in.

• How to select random sample in Excel
• How to sort randomly in Excel
• How to generate random numbers in Excel with no repeats
• Flash Fill in Excel with examples

Table of contents

S. No Date Time Shift Day Subject Session Group

S.No Date Time Shift Day Subject Session Group

Formula in excel

Hi! We have a special tutorial that can help to solve your problem: How to convert rows to columns in Excel (transpose data) .

how to random array with the total of each row is 100?

I think you can try to use the custom functions and macros described in this article to create a random array with a specific sum in a row: How to find all combinations of numbers that equal given sum in Excel .

I always get a #name error

Hi, This error most often appears if you specified the function name incorrectly.

Post a comment

• CELLS REFERENCE
• CELL LITERATURE

Post your problem and you'll get adept help in seconds

• Get articles
• Randomly Assign People to Groups in Excel

Randomly Assign Populace to Groups in Outshine

Among times we have to randomly assign populace to user. This maybe seem an mind-numbing task, but information can be made remarkably easy when executed includes Excel.

How on randomly map people to groups in Excel

Here we can use a formula created on an Excel RANK and ROUNDUP functionalities collectively. We also use a helper column where RAND function is employed to randomly compose values.

To randomly assign populace to groups we usage the formulation

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

The up formula returns a group don. by each entry.

‘ randoms ’ plus ‘ size ’ are benannter range.

Random is a helper file generated by Excel RAND Function.

Explanation of formula

Outstanding RANK function is to inbuilt function in Excel which returns the rank of a numerical within a large pick of numbers otherwise array. This function is categorized than an inbuilt This tutorial will teaching you till write Choose formulas in creating indiscriminate groups. Let's check this out… statistical function include Excel.

Excel ROUNDUP function is another inbuilt function included Excel which returns a number which is rounded up to a definite number the digits. This key rounds away from ‘0’. It is ranked as a Try or Math function furthermore can be appealing used as a worksheet function in Excel.

Let us take one example; we own to assign random groups to collegiate for the aerobics class. We provide the font of group 4 in G10 as shown. To create groups, we use the formula shown within D5.

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

Here, “ random ” is the name of the range [C5:C20] and “ size ” can the benannter range [G10] .

We can drag the formula and use the above formula fork a hefty amount of dating.

We can also make uses away the RAND item, which would make random numbers. This function will keep on generating random values every zeit we change the values in the sheets.

Alternate Solution

Another approach of assigning random value is by by the CEILING operate which is another build function in Excel.

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

This function can be used as an alternative to the ROUNDUP function. The roof item rounds above, but instead of rounding to some given item a decimal place, thereto rounds up to ampere existing multiple. Either help/advice gratefully received.  I at planning a school wide event. By it every student will choose four activities they would be interested to..

Like per the example is shown in Figure 1, she can you use the above alternate formula to randomly assign people the groups as shown below.

Still need more assistance with Excel formatting or have other questions regarding Excel? Connect equipped a live  Excels expert  here for some 1 on 1 help. Your first session is always free. 

Did this post not answer is question? Get a solution from connecting with the expert.

Leave a Comment

Subscribe to Excelchat.co

Get updates off helpful Excels topics

Did this post not answer your question? Get a solution since connecting with the technical

Post your problem and you'll get technical helping in seconds.

Privacy & Cookies: This web uses chocolate. By continuing for use diese website, you agree to their use.

Save time per asking instead! Most questions solution and answered in 10 minute.

Random Group Generator

Our random group generator template was created based on a request from somebody wanting to set up networking breakout sessions for a conference. As we researched the solution we found it had many applications in classrooms (e.g. seating charts and project groups), networking (e.g. lunch groups), and sports (randomizing teams). Although it is not specifically a solution to the social golfer problem (see below), it is a useful tool for generating random groups from a list of names.

License : Private Use (not for distribution or resale)

"No installation, no macros - just a simple spreadsheet"

How to Randomly Assign Groups or Teams

1. edit the list of names.

The Names worksheet is where you list the names of the people you want to assign to groups. You can include additional information as needed, similar to a roster or a sign-in sheet .

2. Sort the List of Names

Use the built-in Sort feature in Excel to sort by the RAND column, by the CUST ORDER column, alphabetically, or by any other sorting criteria that you want to use (e.g. skill level).

Every time you sort by the RAND column , the groups will be randomized.

If you want to preserve the order that you first entered the names, use the CUST ORDER column to enter 1,2,3,4..., then at any time you can sort by CUST ORDER to return them to the original order

3. Choose a Grouping Order

The list of names is either assigned to groups as 1,1,1,2,2,2,3,3,3,4,4,4... or 1,2,3,4,1,2,3,4,1,2,3,4... This means that after you have sorted the list of names using whatever sorting criteria you want to use, you have Two Unique Groupings to choose from (people are grouped differently in each).

4. Edit the Group Names

The number of groups is determined by the number of group names you include in the list. The number per group is determined automatically by the number of people and number of groups.

The Social Golfer Problem

Creating a randomized set of groups once is very simple, and this spreadsheet does it quite well. You can continue to sort the list by the RAND function every time you want to re-randomize the groups. But, what if you want to mix the groups again so that everybody is with a new group of people? How many times can you mix up the groups without anyone ever being with the same person more than once? That is the social golfer problem.

It's called "social" because the idea is to meet new people each time. This has application to education and business as well. In the classroom, you may want to assign class groups so that students are with a different set of students each time. At a training workshop you may want to create lunch groups over multiple days so that people can network with a different group of people each time.

Though the social golfer problem has been "solved" for specific numbers of people and group sizes, it is still considered an "unsolved" problem in general. If you are thinking "That's silly, how hard could it really be?" then I would encourage you to try it. It has stumped combinatorial mathematicians for a couple of decades so far.

We do not yet have a template for this type of social golfer problem.

• Social Golfer Problem at wikipedia.org - Although not exactly the same as wanting to create random groups, it is a similar problem.
• Breakout Group Allocation Schedules ... by Miller et al., https://doi.org/10.3390/sym13010013

Follow Us On ...

Schedules & Planners

• CELL REFERENCE

Post your problem and you'll get expert help in seconds

• All articles

How to Set Up a Random Team Generator in Excel

Excel allows us to create a random team generator using the RAND, RANK and CEILING functions. This step by step tutorial will assist all levels of Excel users to learn how to set up a random team generator in Excel.

Syntax of the RAND Formula

The generic formula for the RAND function is:

The function returns a random decimal number between 0 and 1 and has no parameters.

Syntax of the RANK Formula

The generic formula for the RANK function is:

=RANK(number, ref, [order])

The parameters of the RANK function are:

• number – a number which we want to rank
• ref – a list of numbers for ranking
• [order] – 1 for ascending or 0 for descending order. This parameter is optional, if it’s omitted, the default order is descending.

Syntax of the CEILING Formula

The generic formula for the CEILING function is:

=CEILING(number, significance)

• number – a number which we want to round up to the nearest significance
• significance – a significance for rounding a number up.

Setting up Our Data for the Formula

Let’s look at the data that we will use in the example. To group names into teams, we will need to have several helper columns. In column B (“Names”), we have names of team members. In column C (“Random”), we will have a random number 0-1 for every name. In column D (“Rank”), we will get a rank for every random number. In column E (“Grouping”), we’ll divide the rank with 3, as we have 3 teams. Finally, in column F (“Team”), we’ll get the team (1, 2 or 3) for each name

Set up a Random Team Generator

First, we need to get a random number in column C for each name.

The formula for RAND in C3 looks like:

To apply the formula, we need to follow these steps:

• Select cell C3 and click on it
• Insert the formula: =RAND()
• Press enter
• Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

When we get a random number for each name we can rank them in column D.

The formula for RANK in C3 looks like:

=RANK(C3, $C$3:$C$14)

The number parameter is the cell C3. The ref parameter is the range $C$3:$C$14. We must fix the range, as it’s not changing when the formula is copied down the cells.

• Select cell D3 and click on it
• Insert the formula: =RANK(C3,$C$3:$C$14)

Now we have the rank for every random number in column D and can divide them by 3.

The formula in E3 looks like:

• Select cell E3 and click on it
• Insert the formula: =RANK/D3

Finally, we can use the CEILING formula, to assign team 1, 2 or 3 to each name.

The formula for CEILING in F3 looks like:

=CEILING(E3, 1)

The parameter number is the cell E3, while the significance is 1.

• Select cell F3 and click on it
• Insert the formula: =CEILING(E3,1)

As you can see in Figure 6, in “Team” column we have assigned a team for each name from column B.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.

Save time by asking instead! Most questions solved and answered in 10 minutes.

• [email protected]
• +14372198199

How to Make Random Groups in Excel?

Random Groups in Excel can be a powerful tool for various applications, from statistical sampling to team assignments in a workplace setting. Creating these groups manually can be time-consuming and prone to bias, making Excel’s randomization functions a valuable asset. Whether you’re a researcher conducting a randomized control trial, a teacher assigning project groups, or a manager distributing tasks among teams, this guide will navigate you through the steps to efficiently generate random groups in Excel. By harnessing the power of Excel’s randomization features, you can ensure fairness and objectivity in your group assignments, while saving time and enhancing the overall efficiency of your organizational processes.

This Content Covers:

• Use of RANDBETWEEN Function
• Use of CHOOSE Function
• Use of combining RANDBETWEEN and CHOOSE Functions
• Explanation of formula
• CEILING Function
• Explanation of CEILING function
• Explanation with example

1. Make Random Groups in Excel

In certain situations, it may be desirable to generate groups for a list of names or randomly allocate data to groups. You can quickly create random groups for a set of data in Microsoft Excel by using a formula.

The RANDBETWEEN and CHOOSE Functions can be used to randomly allocate items (data, people, etc.) to groups.

Procedure of using RANDBETWEEN Function

Step 1: Next to the list you want to divide into random groups, choose a blank cell, then enter the RANDBETWEEN function, outlined in Red below.

After applying the function or formula, the result looks like below.

Step 2: After that, to fill all the cells, pull the “Fill Handle” further. The result is outlined in Red below.

• Use of CHOOSE Function:

Procedure of using CHOOSE Function:

Step 1: Next to the list you want to divide into random groups, choose a blank cell, then enter the CHOOSE function, outlined in Red below.

• Use of combination of RANDBETWEEN and CHOOSE Functions

Combining the two allows us to assign teams to groups by randomly “choosing” one item from a list.

Procedure of combining RANDBETWEEN and CHOOSE Functions:

Step 1: Next to the list you want to divide into random groups, choose a blank cell, then enter the function or formula, outlined in Red below.

2.Randomly assign people in the groups in Excel

Sometimes we have to pick groups at random. Although it could appear like a difficult undertaking, Excel makes it incredibly simple to complete by using formula.

Here, we may make use of a formula produced by combining the Excel RANK and ROUNDUP tools. Additionally, we employ a helper column where the RAND function is utilized to produce values at random.

We use the formula to assign individuals into groups at random.

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

For each element, the aforementioned formula returns a group number.

“size” and “randoms” are called range.

The Excel RAND function creates the helper column “Random”.

• Explanation of formula:

The Excel RANK function is a built-in feature that provides the position of a given number in an array or huge collection of numbers. In Excel, this function falls under the category of built-in statistical function.

Another built-in function in Excel is the ROUNDUP function, which returns a value that has been rounded to a predetermined number of digits. This function deviates from zero. It can be used effectively as an Excel worksheet function and is categorized as a Trig or Math function.

Procedure of assigning people in the groups randomly in Excel

Step 1: Let’s use a case where we have to assign teams to random groups. We apply the formula shown in D2 to build groups.

3. Alternative solution

The CEILING function, another built-in Excel function, can be used as an alternate method of assigning random values.

• CEILING Function:

We can use CEILING function or formula to assign individuals into groups at random.

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

• Explanation of CEILING function:

In place of the ROUNDUP function, you can use this function. The ceiling function rounds up, but it does so to a specific multiple rather than to a specific number of decimal places.

• Explanation with example:

Procedure of assigning people in the groups randomly in Excel using CEILING function:

Here, “random” is the name of the range [C2:C13] and “size” is the named range[G5].

You may be interested:

• Financial Dashboards
• Sales Dashboards
• HR Dashboards
• Data Visualization Charts

Leave a Comment Cancel Reply

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

Save my name, email, and website in this browser for the next time I comment.

• Count Characters
• Print in Excel
• Bar Chart with Bubble in Top
• Dual Axis Chart
• Dumbbell DNA Chart
• Give Axis in Chart
• Half circle KPI chart
• Line Chart in Excel
• Mirror Bar Chart
• Progress Chart In Excel
• Side by Side Bar Chart
• Speedometer Chart
• Stacked Column Chart
• Timeline Chart
• ABS Function
• ACCRINT function in Excel
• ACOS Function in Excel
• Add Column in Excel
• Add Plus Sign Before Numbers
• Alphabetize by last name
• Amortization Schedule in Excel
• AREAS Function in Excel
• ASIN Function in Excel
• ATAN and ATAN2 Function
• AutoFit Column Width
• Automatic Formatting
• AVEDEV Function in Excel
• Bell Curve in Excel
• BETAINV Function in Excel
• BIN2DEC Function in Excel
• Box and Whisker Plot in Excel
• Bullet Points in Excel
• Calculate Age in Excel
• Calculate Confidence Interval
• Calculate Correlation Coefficient
• Calculate Discounted Price
• Calculate Percentage Change
• Calculate Quarter from date
• Calculate Square Root
• Calculate the Area
• Calculate the week ending date
• Calculate Workdays in Excel
• Calculate Years of Service
• Calculated Field in Pivot Table
• Calculating BMI in Excel
• Calculating Simple Interest
• Capitalize First Letter in Excel
• Cell Reference in Excel
• Change Row Height
• Change the Negative Numbers to Zero
• Check Mark Symbol
• Check Not Equal To in Excel
• Check Text or Number
• Circular Reference
• Clean Data in Excel
• CLEAN Function in Excel
• Clear Formatting in Cell
• Clear Table Formatting
• CODE Function in Excel
• Color Scales in Excel
• COLUMN Function
• Combine Date and Time in Excel
• Combine Names in Excel
• Compare Columns in Excel
• Compare Text in Excel
• Compare Texts of Two Cells
• Compare Two Dates
• Compound Annual Growth Rate
• Compound Interest Formula
• CONCAT function in Excel
• CONCATENATE Excel
• Conditional Formatting in Excel
• Conditional Formatting to Pivot Table
• Confidence Interval in Excel
• Convert Date to Text
• Convert Date to Text in Excel
• Convert Excel to PDF
• Convert Formulas to Values
• Convert KG to LBS in Excel
• Convert Serial Number to Date
• Convert Text to Number
• Convert XML files to Excel
• Copy and Paste Multiple Cells
• Copy Conditional Formatting to Another Cell
• Copy Excel Formula
• Copy formula in Excel
• CORREL function in Excel
• COS Function in Excel
• COSH function in Excel
• Count Cells with Text
• Count Characters in Excel
• Count Colored Cells
• COUNT Function
• Count Unique Values
• COUNTBLANK Function
• COUNTIF – COUNTIFS Function
• COUPDAYBS Function
• Create a Custom List
• Create and Install Excel Add In
• Create and Use Scroll Bar
• Create and Use Workbooks
• Create budget in Excel
• CRITBINOM function in Excel
• Criteria Validity in Excel
• CUMPRINC Function in Excel
• Dashboard in Excel
• Data Entry Form
• Data Formatting in Excel
• Data Validation in Excel
• DATEDIF Function in Excel
• DATEVALUE Function
• DAVERAGE Function in Excel
• DAYS360 Function in Excel
• DEGREES function in excel
• Delete Blank Rows
• Delete Hidden Rows
• Delete Pivot Table
• Delete Rows Based on Cell Value
• Delta symbol in Excel
• Developer Tab in Excel
• DMIN function in Excel
• Drop-Down List in Excel
• DSUM Function in Excel
• DURATION function in Excel
• DVAR function in excel
• Dynamic Named Range
• Email from Excel Sheet
• Enable Macro
• EOMONTH Function in Excel
• Excel Camera Tool
• Excel File Not Open
• Excel IFERROR Function
• Excel Interview Question
• Excel MAX Function
• Excel RAND and RANDBETWEEN Function
• Excel RANK Function
• Excel REPLACE and SUBSTITUTE
• Excel SUMPRODUCT Function
• Excel Templates
• Excel VBA ASC Function
• Excel VBA TRIM Function
• Exponents in Excel
• Extract Dates in Excel
• Extract Numbers from Cell
• F-Test in Excel
• File Names list
• Fill Handle in Excel
• Fill Sequential Data
• Filter in Excel
• Find and Remove Outliers
• Find and Replace in Excel
• FIND and SEARCH Function
• Find Merged Cells
• Find Range in Excel
• Fix Excel Formula
• Fix Slow Excel Spreadsheets
• Flip Data in Excel
• Format Painter in Excel
• Formatting Shortcuts in Excel
• Freeze Rows and Columns
• Function Keys in Excel
• Future value of the annuity
• Generate Random Number
• Get Month name from date in Excel
• Greater Than or Equal to
• Gridlines in Excel
• Group in pivot table
• Heat Map in Excel
• Hide Formulas in Excel
• Hide Sheets in Excel
• Highlight Active Row Column
• Highlight Blank Cells and Fill Down Blank Cells
• HLOOKUP Function in Excel
• Hyperlink in Excel
• IF – AND Function
• IF – OR Function
• IF -AND-OR Function
• IFERROR with VLOOKUP to Replace #N/A Error
• Income tax bracket in excel
• Indent in Excel
• INDEX and MATCH Functions
• Index Match Formula in Excel
• INDIRECT – OFFSET Function
• Insert (Embed) PDF in Excel
• Insert and Delete Comments
• Insert Data and Timestamps
• Insert Degree Symbol in Excel
• Insert Line Break
• Integral or In-Built Themes
• Internal Rate of Return in Excel
• Intersect Operator
• Intersect Operator in Excel
• IRR in Excel
• ISNA Function
• Keyboard shortcuts
• Leading Zeros in Excel
• Linear Regression Analysis
• LINEST function in Excel
• lock and hide cells and protect worksheet
• Logical Functions in Excel
• Lookup and Reference Functions
• Make Scatter Plot
• Match Function in Excel
• Merge Cells in Excel
• Merge Two Columns
• Merge Two or More Tables
• MID Function in Excel
• MINIFS Function in Excel
• Mixed Reference
• MOD Function in Excel
• Month Name to Number
• Months to a date
• Mortgage Payment in Excel
• Move Row Column
• Multiple Columns in Excel
• Multiple option in a Drop Down
• Multiple Selections in Drop Down
• Multiplication in excel
• Multiply Column in Excel
• Named Range in Excel
• Negative Number to Positive
• New Excel Formulas
• NPER for Excel
• Number of Days Between Two Dates
• Offset Function in Excel
• Open and Use Visual Basic Editor
• Outline (group) Data
• Paste special in Excel
• Paste Special Shortcuts in Excel
• Picture Lookup
• Pivot Chart in Excel
• Chart from Pivot Table
• Filter in Pivot Table
• Number Formatting in Pivot Table
• Slicer in Pivot Table
• PMT function
• Print Title in Excel
• Print top rows
• PROPER Function
• Protect Excel File
• PV function in Excel
• Quadratic Equation
• Radio Button (Option Button)
• Radio Button Option
• Random Groups in Excel
• Record Macro in Excel
• Reduce Excel file size
• Reference Another Workbook
• Refresh Pivot Table
• Remove Dotted Lines
• Remove Duplicates
• Remove line breaks
• Remove Macro in Excel
• Remove Space in Excel
• Remove Time from Date
• Return Cell Address
• Rotate Text in Cells
• ROUND Function in Excel
• ROUNDUP and ROUNDDOWN
• Run Macro in Excel
• Scenario Manager in Excel
• Searchable Drop-Down List
• Select Multiple Cells
• Set Print Area in Excel
• Shade or Color Alternate Rows
• Show Formulas
• Shuffle Items in Excel
• Slicer in Excel
• Slicers in Pivot Table
• Slope in Excel
• Sort Data by Color in Excel
• Sort in Excel
• Sparklines in Excel
• Spell Check in Excel
• Standard Deviation in Excel
• Status Bar in Excel
• Stop numbers to dates
• Strikethrough Text in Excel
• Subtraction in Excel
• Sum Positive Numbers in Excel
• Superscript and Subscript
• T.TEST in Excel
• Text in Pivot Table
• Text to Column
• TODAY’s Date Function
• Top 10 Values in Excel
• Track Change
• Transpose in Excel
• Turn on AutoSave in Excel
• Two variable data table
• Unhide Columns
• Unhide Sheet in Excel
• Unmerge Cells in Excel
• Usage of 103 functions
• UserForm Excel
• VBA Message Box in Excel
• Weekdays in Excel
• Weighted Average Formula
• What-If Analysis in Excel
• Wrap Text in Excel
• XLOOKUP Function
• Zoom in and Zoom out

Contact Info

Find us on Social Media

Our Address

273 Pharmacy Avenue Toronto, ON M1L3E9

© 2023 All rights reserved | Biz Infograph

• Forgotten Your Password?

• Mark Forums Read
• Quick Links :
• What's New?
• Members List

Forum Rules

• Commercial Services
• Advanced Search

• Microsoft Office Application Help - Excel Help forum
• Excel General

Randomly assign to groups, without duplicates

Thread tools.

• Show Printable Version
• Subscribe to this Thread…

Rate This Thread

• Current Rating
• ‎ Excellent
• ‎ Average
• ‎ Terrible

I have a list of say 500 samples, which includes some duplicates. I want to randomly assign all 500 samples into 20 groups (25 in each group). I can do this using rand() and a helper column. However, I don't want any of the 20 groups to contain duplicates within them i.e. I want all 25 members of each group to be unique. How can I do this please? Thanks

Re: Randomly assign to groups, without duplicates

I have assumed that you are using Excel 2007 or later - Vista is an operating system version, but is not a version of Excel. Let's say that your samples (with duplicates) are in column A, and your helper column of group assignment formulas is column D, and both start in row 2. In E2, use the formula =COUNTIFS(A:A,A2,D:D,D2) and copy down to match. Then in F2, use the formula =MAX(E:E) and press F9 until F2 is 1, which will indicate that each group has no repeated values. Depending on your number of repeats, it may take a few or many recalcs. Once that value is 1, copy column D and paste special values so the values are frozen. You could also use a macro to do the same thing.

Last edited by Bernie Deitrick; 05-07-2015 at 10:25 AM .

Bernie Deitrick Excel MVP 2000-2010

Thanks! That does a great job at checking if the randomisation has produced the desired pattern - much appreciated. Does anyone know a formula to ensure that duplicate samples are never put in the same group in the first place? It's basically, sampling without replacement, but also without duplicates.

The solution would depend on how you are doing your binning - but assuming that you are just ranking the random values, something like this that distributes the repeats evenly but randomly through the population may work in place of your simple RAND() usage: =IF(COUNTIF($A$2:A2,A2)=1,RAND()*((COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2)+1)/COUNTIF(A:A,A2)-(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2))+(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2),VLOOKUP(A2,A:B,2,FALSE)*((COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2)+1)/COUNTIF(A:A,A2)-(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2))+(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2)) Give it a try with the previous checking formulas in place - if it works, no recalc will result in a MAX of more than 1. Of course, if you don't mind other columns, then using this in C2: =(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2) and this in D2: =(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2)+1)/COUNTIF(A:A,A2) Allows that formula to become =IF(COUNTIF($A$2:A2,A2)=1,RAND()*(D2-C2)+C2,VLOOKUP(A2,A:B,2,FALSE)*(D2-C2)+C2)

Last edited by Bernie Deitrick; 05-07-2015 at 01:09 PM .

Thanks, that's an interesting idea, and helps reduce the number of duplicates, if not eliminating them entirely. My problem now is that my actual dataset is much larger - about 100000 values, and the calculations take forever. I don't suppose you know a way to speed things up?! Thanks again

That would - especially since you said 500 samples. You also never said how many duplicates you are actually talking about? 0.1% 1% 10% 50%? It may be quicker to sort the samples into random order, and then use a macro to find the duplicates within blocks and switch them with one another. But how many duplicates you actually have and the block size would have an impact on its efficacy.

Similar Threads

Randomly select 4 groups using 8 teams, [solved] randomly group n elements (where n is a factor of 7) into groups of 7, randomly dispatch list of employee in three groups according to skills, randomly assign a value to rows, [solved] how do i randomly assign values in a grid, posting permissions.

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is Off
• HTML code is Off
• Trackbacks are Off
• Pingbacks are Off
• Refbacks are Off
• ExcelForum.com

404 Not found

Randomization assign people go groups

Relationship functions .

To indiscriminately assign people to groups or teams of a specific size, you canister use a assistants column with a value generates by to RAND function, collaboratively with a formula foundation on the RANK furthermore ROUNDUP responsibilities. In the exemplary shown, the formula in D5 is:

the sales a group number for respectively name listed in column B, where "randoms" is aforementioned named range C5:C16, real "size" is the named range G5.

Generic formula

Explanation .

At the core of this solution is the REEF function, which is used to generate a haphazard number in a helferin column (column C in to example).

Until assign a full set of random values in one step, select the range C5:C16, and sort =RAND() in the formula bar. Then use the keyboard control + enter to enter the formula in all cells with once. (2) Assign a random number to each school: The key RAND () is Excel's coincidental number generator. To use it, in Column C, type in the after = RAND() in ...

Notes: the SIDE features will stay generating random values every while a make is make the worksheet, so typically you will wish to replace the results in column C by actual values using paste special to prevent changes after random values are assigned.

In column DICK, a group number is assigned about the following formula:

The TRACK function can used toward range the valuated in C5 against see random worths in the list. The result will be a number between 1 the the total total of people (12 in this example).

This result is then divided by "size", which represents the desired group size (3 the the example), which later goes into the ROUNDUP function as number , at num_digits to zero. The GATHER function returns a number rounded up to this next integer. This amount representes assigned group number.

CHANGING version

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

Related formulas

Randomly assign data to groups

Random text philosophy

Random number from fixed set off options

Relates functions.

RAND Function

An Excel RAND function returns one random number between 0 and 1. With example, =RAND() will generate ampere number how 0.422245717. RAND recalculates when a worksheet is open or changed.

• ROUNDUP Function

The Beat MUSTER operate returns a number rounded up to a provided number of decimal places. Other standard rounding, where single amounts less than 5 are bowed down, ROUNDUP rounds all numbers up .

CEILING Function

Aforementioned Excel CEILING function rounds a considering number up to the locate specific multiple. CEILING works like the MROUND function , although CEILING always turns boost .

Hi - I'm David Bruns, and I run Exceljet with my wife, Lisa. Our goal will to help they work faster in Excellence. We create short videos, furthermore clear browse of formulas, duties, whirl tables, limited system, and charts.

Related Information

• Randomizing give data to bands
• Random text values
• Random number from fixed set of opportunities
• RANDOM Function
• PAINT Function

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are swift, clear, the to the point, so you can learn Excel in less time, and easily review key topics when needed. Each picture comes from its personal practice worksheet.

Help states improve Exceljet

My email address is privately and not shared.