A random sample is a portion of a larger population that is chosen objectively. Random sampling serves many business purposes, including market or product research. In this article, we’ll share how to easily take a random sample with Microsoft Excel using a realistic business example.
I’ve created a spreadsheet listing 478 NBA players in the first column. Suppose you need to interview a random sample of 50 NBA players about the new collective bargaining agreement. I want to take a random sample size of 50 – this means that any combination of 50 players has the same chance of being chosen. Effectively, we want to shuffle the listed players so that any combination of 50 players has the same chance of being at the top of the shuffled list, and then we choose the first 50 players listed after the shuffling.
Here are two approaches to choosing a random sample.
How to create a random sample without Office 365
The rand() function is the key tool needed to take a random sample. If you enter the function =rand() in a cell, Excel is equally likely to return any number between 0 and 1. For example, there is a 10% chance you will see a number between 0 and 0.1 and a 20% chance you will see a number between 0.8 and 1. The value returned by rand() functions are independent, which means that the value returned by rand() in any cell has no effect on any other rand() values in the workbook.
Figure 1: Random Sample without Office 365
To generate our random sample, proceed as follows (see Figure 1):
- Generate a random number for each player by copying from B3 to B4:B480 the formula = rand().
- “Freeze” the random numbers by copying the range B3:B480 and then select Paste Special Values.
- Now, sort the range A3:B480 on Column B (we chose sort smallest to largest). This step effectively shuffles the players so each player has the same chance to be in any position on the list.
- The first 50 players listed are your random sample of 50 players; each subset of 50 players has the same chance of being chosen.
How to create a random sample with Office 365
Office 365 greatly simplifies creating a random sample. Cell J3 contains a “magic” formula that will shuffle our list of NBA players (see Figure 2).
Figure 2: Picking a Random Sample with Office 365
The formula =SORTBY(G3:G480,RANDARRAY(COUNTA(G3:G480))) in cell J3 is a dynamic array formula, which can create results in multiple cells and automatically updates if the list of players in G3:G480 updates. This formula generates 477 random numbers via the =RAND() function. Then, these random numbers will be used to sort the player list.
The SORTBY portion of the formula sorts the list based on the random numbers. The default is ascending order, but descending order would work fine. The first 50 players listed would be your random sample. If you press the F9 key, your spreadsheet will recalculate the random numbers and you will have a new random sample.
This is a simple Microsoft Excel trick that could save you time and make your accounting job that much easier. To further sharpen your Excel skills, complete our Microsoft Excel Fundamentals + Data Analytics Certificate program. You’ll receive CPE credits for each course you complete and earn a certificate that reflects your knowledge in essential Excel skills for accountants.
Microsoft Excel Fundamentals + Data Analytics Certificate program >
