Software

Use Excel to obtain a random sample of customers for market analysis

No matter whether you are testing a new marketing campaign or evaluating your current level of customer service, you want to be sure that you base your analysis on a reliable sample of your customers. Here's how to use the Excel information you already have to your advantage.

No matter whether you are testing a new marketing campaign or evaluating your current level of customer service, you want to be sure that you base your analysis on a reliable sample of your customers.

For example, you would like to use a random sample of 200 of your 1,000 customers in your analysis. You have an alphanumeric list of those customers in Column B of your Excel Customer Data worksheet, so to obtain a random list, follow these steps:

  1. Insert a new column to the right of Column B.
  2. Enter Customer No. in C1.
  3. Click in C2 and press [Ctrl][Shift][End].
  4. Type =RAND() and then press [Ctrl][Enter].
  5. Select C1.
  6. Click the AZ (sort) button in the Standard toolbar.
  7. Delete Column C.

Column C now associates a random Customer No. between 0 and 1 for each customer in the list. Thus, sorting the worksheet in Column C randomly sorts the customer data in Column B, from which you can choose the first 200 for your sample.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks