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.

5 comments
clifforde
clifforde

The other 2 posts are very valid. When I Inserted the =RAND() function, I received .xxxxx. I multiplied by 1,000 to get better numbers. For those folks with, let's say, 3,000 clients, multiply by 10,000 and make sure you have at least 8 digits showing to make the Sort unique. Too few digits and you will get Duplicates.

bob.ellis
bob.ellis

Random sampling is important and Mr Lawrence's reinforcement of the "paste special" function has merit. However, also keep in mind that random sampling may bias your analysis if the sample does not represent the characteristics of the analysis you are attempting on your customer base. For example, if you have a customer base that is structured in a Pareto effect (i.e., customer types have a 70%, 20%, and 10% breakout) then you need to review your sample to ensure that the percentages of the sample are similar to the customer base percentages. Sampling at best is only as good as the customer representation.

cisauditor
cisauditor

Every time you perform an action in Excel, the automatic recalculation feature is going to re-perform all the RAND functions. So, when you perform the Sort function, all the RAND functions are going to change. To remedy this, copy the RAND function column and then use Paste Special, Values to save the static values calculated rather a dynamic formula.

VickyToo
VickyToo

Because the RAND function will randomly assign numbers to each cell, there is potential there for duplicates. I found a website that gives an example of a customized function that will generate unique random numbers: http://www.ozgrid.com/VBA/RandomNumbers.htm Vicky

ssharkins
ssharkins

I generally take on only simple development projects. I'm just not interested in long-term, serious hard core development solutions. They make my head hurt. :) Anyway, a few years back, I took on an Access project for a distribution company -- they wanted a "simple" routine for retrieving records they could then analyze to measure future growth prospects. OH MY! You wouldn't believe the "conditions" that went into the retrieving a random set. It nearly killed me. :)

Editor's Picks