Here we have a list of 36 people. Let’s say we want to randomly assign each person to a team of 4 people so that we have a total of 9 teams with 4 people in each. I’m going to solve this problem in small steps, with helper columns, then bring things together in the end. This is a great way to solve more complicated problems in Excel. I’ll start with an Excel Table, to make the formulas very fast to enter. Then, I’ll add columns for Rand, Rank, Grouping, and Team number. The purpose of each column will become clear as we go along. Next, I’ll use the RAND function to get a random number for each person. RAND generates small numbers between 0 and 1. RAND is a volatile function, so it will recalculate with every worksheet change. We don’t want that behavior, so I’ll use Paste Special to convert the formulas to values. Next, I’ll use the RANK function to rank each person according to their random number. RANK needs a number, and a list of numbers to rank against. The result is a list of numbers between 1 and 36, where 1 represents the largest value, and 36 represents the smallest. We’re getting close. We just need a way to group by rank. I’ll do this by dividing rank by the team size, which is 4. This produces some messy numbers, but, we now have what we need. If we round these numbers up, we’ll have team numbers between 1 and 9. This is a perfect job for the CEILING function, which rounds up to a given multiple. I need to give CEILING the number and specify a multiple of 1, and we have our teams. Now, to make sure this is working right, I’ll use the COUNTIF function to count team members. Next, I’ll replace the hard-coded team size with a reference. Now when I change the team size, everything still works. Finally, I’ll consolidate formulas. First I’ll copy in the grouping formula. Next, I’ll copy in the RANK formula. Now I can delete the two helper columns. To generate new teams at any time, I can again use the RAND function.
Dave Bruns
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.
