When you need to analyze a large quantity of data, taking a random sample helps you to make accurate, unbiased conclusions. Taking a random sample with Microsoft Excel can assist you with market or product research and serve a wide variety of business purposes. To help you get started, we'll share how easy it is to use Excel to take a random sample using realistic examples.
Using a random sample to pull a fraction of data
Suppose I need to interview 50 random NBA players about their new collective bargaining agreement. First, I created a spreadsheet and listed 478 NBA players in Column A. I want to shuffle the listed players so that any combination of 50 players has the same chance of being at the top of the list. To take a random sample, we would then choose the first 50 players listed after the shuffling.
There are two ways to create a random sample depending on whether or not you have Office 365.
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 makes it much easier to create a random sample in Excel. 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.
That's all there is to it!
Sharpen your Excel skills with CPE courses
This is a simple Microsoft Excel trick that can save you time and make it easier to analyze data. To take your Excel skills to the next level, Becker offers several CPE courses on the topic.
Get a FREE CPE course from Becker
With Becker, you can meet your CPE requirements, gain new skills, and stay aware of the latest updates and news. See why more accountants are choosing Becker with a free CPE course!
