CPE

How to take a random sample in Excel

person pointing to a spreadsheet

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. 

 

Learn valuable skills and earn CPE with Becker's Microsoft Excel + Data Analytics Certificate

 

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.

Table

Description automatically generated

Figure 1: Random Sample without Office 365

 

To generate our random sample, proceed as follows (see Figure 1):

  1. Generate a random number for each player by copying from B3 to B4:B480 the formula = rand().
  2. “Freeze” the random numbers by copying the range B3:B480 and then select Paste Special Values.
  3. 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.
  4. 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).

Graphical user interface, application, table, Excel

Description automatically generated

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. 

 

becker ribbon

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!

Share

FacebookLinkedinXEmail
CPE FREE COURSE
Sidebar CTA
Browse our CPE Offerings

Featured

Tax tips for accountants: Independent contractor taxes
Tax tips for accountants: Independent contractor taxes
Read More
Accounting Tips: Schedule K-1 Form 1065
Accounting Tips: Schedule K-1 Form 1065
Read More
How a CPA should respond to a known or suspected NOCLAR
How a CPA should respond to a known or suspected NOCLAR
Read More

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.

Continue