Random Assignment Is Important To Ensure That Excel

Gary has two lists in a worksheet. One of them, in column A, contains a list of surplus items in our company and the other, in column G, contains a list of names. There is nothing in columns B:F. Gary would like to assign names, randomly, to the list of items. Each name from column G should be assigned only once. If there are more names than items, then some names won't get used. If there are fewer names than items, then some items won't have associated names.

There are a couple of ways that this can be done. Perhaps the easiest, though, is to simply assign a random number to each item in column A. Assuming that the first item is in cell A1, put the following in cell B1:


Double-click the fill handle in cell B1, and you should end up with a random number (between 0 and 1) to the right of each item in column A.

Now, select all the cells in column B and press Ctrl+C to copy them to the Clipboard. Use Paste Special to paste values right back into those cells in column B. (This converts the cells from formulas to actual static values.)

Sort columns A and B in ascending order based on the values in column B. If you look across the rows, you'll now have items (column A) associated randomly with a name (column G).

Even though it is not necessary, you could also follow these same steps to add a random number to the right of each name and then sort the names. (I say it isn't necessary because randomizing the items should be enough to assure that there are random items associated with each name.)

The technique discussed so far works great if you have to do the random pairing only once in a while. If you need to do it quite often, then a macro may be a better approach. There are, of course, many different macro-based approaches you could use. The following approach assumes the item list is in column A and the name list in column G. It also assumes that there are header cells in row 1 for each column.

Sub AssignNames() Set srItems = Range("A2").CurrentRegion Set srNames = Range("G2").CurrentRegion NameCount = srItems.Rows.Count - 1 ItemCount = srNames.Rows.Count - 1 'Randomize Names ReDim tempArray(NameCount, 2) For x = 0 To NameCount - 1 tempArray(x, 0) = Range("G2").Offset(x, 0) tempArray(x, 1) = Rnd() Next x 'Bubble Sort For i = 0 To NameCount - 2 For j = i To NameCount - 1 If tempArray(i, 1) > tempArray(j, 1) Then tempItem = tempArray(j, 0) tempName = tempArray(j, 1) tempArray(j, 0) = tempArray(i, 0) tempArray(j, 1) = tempArray(i, 1) tempArray(i, 0) = tempItem tempArray(i, 1) = tempName End If Next j Next i 'AssignNames Range("B2") = "Assigned" AssignCount = NameCount If NameCount > ItemCount Then AssignCount = ItemCount For x = 0 To AssignCount Range("B2").Offset(x, 0) = tempArray(x, 0) Next x End Sub

If there are more names than items the macro randomly assigns names to items. If there are more items than names it randomly assigns some items to names and randomly leaves "holes" (items without names). It stores them in column B, overwriting whatever was there.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5682) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...

Beginning a Mail Merge

Performing a mail merge can be intimidating to some people. It needn't be; Word provides a handy step-by-step wizard that ...

Discover More

Changing Label Printing Order

If you want to change the order in which labels are printed when doing a mail merge, Word doesn't provide many options. ...

Discover More

Jumping Around Folders

If you need to move between two different folders quite regularly in the Open dialog box, you'll find the technique ...

Discover More

Figuring Out the Low-Score Winner

Need to figure out the lowest score in a range of scores? Here's the formulas to get the information you need.

Discover More

Searching for a Value Using a Function

Searching for a value using Excel's Find tool is easy; searching for that same value using a formula or a macro is more ...

Discover More

Returning the Smallest Non-Zero Value

In a series of values you may need to know the smallest value that isn't a zero. There is no built-in function to do ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

This article explains how random sampling works. If you want to skip the article and quickly calculate how many people you need for your random sample, click here for an online calculator.

If you are collecting data on a large group of employees or customers (called a "population"), you might want to minimize the impact that the survey will have on the group that you are surveying. It is often not necessary to survey the entire population. Instead, you can select a random sample of employees or customers and survey just them. You can then draw conclusions about how the entire population would respond based on the responses from this randomly selected group of people. This is exactly what political pollsters do - they ask a group of people a list of questions and based on their results, they draw conclusions about the population as a whole with those often heard disclaimers of "plus or minus 5%."

If you are simply looking at one large group of people as a whole, the process of determining a random sample is pretty straightforward. You will need to know how many people are in the entire group (e.g. the total number of employees) and how "accurate" you want your results to be (see "Statistical Confidence" below). When you survey a portion of a population, there will be some margin of error in the results, but when the margin of error is reduced to just a few percentage points, it often becomes of little concern.

If your population consists of just a few hundred people, you might find that you need to survey almost all of them in order to achieve the level of accuracy that you desire. As the population size increases, the percentage of people needed to achieve a high level of accuracy decreases rapidly.

In other words, to achieve the same level of accuracy:

Larger population = Smaller percentage of people surveyed

Smaller population = Larger percentage of people surveyed

Stratified Random Sampling

More often than not, you will not only want to examine the results from the overall population, but also understand the differences between key demographic subgroups within the population. For example, you might want to understand the differences between different groups of employees, like senior managers vs. regular employees. If you plan to look at distinct subgroups such as these, you should perform a stratified random sample. In a nutshell, this means you will need to select a separate random sample from each of the subgroups rather than just taking a single random sample from the entire group. The process is slightly more time consuming and will require you to survey a greater number of people overall, but this technique can be very valuable.

If you want to conduct a stratified random sample, think carefully about the single most relevant demographic division that can be made between people within your population. It is probably not practical to conduct a stratified random sample on more than one demographic category as the process becomes much more complex and you will ultimately end up needing to survey almost the entire population if any of the subgroups are very small. For example, if you wanted to look at employee survey results and by level and job function, you would need to look at each level/function combination and you might find very small numbers of employees within some of these areas.

Statistical Accuracy - Confidence and Error

In order to understand random sampling, you need to become familiar with a couple of basic statistical concepts.

1. Error - This is that "plus or minus X%" that you hear about. What it means is that you feel confident that your results have an error of no more than X%.

2. Confidence - This is how confident you feel about your error level. Expressed as a percentage, it is the same as saying if you were to conduct the survey multiple times, how often would you expect to get similar results.

These two concepts work together to determine how accurate your survey results are. For example, if you have 90% confidence with an error of 4%, you are saying that if you were to conduct the same survey 100 times, the results would be within +/- 4% of the first time you ran the survey 90 times out of 100.

If you are not sure what sort of error you can tolerate and what level of confidence you need, a good rule of thumb is to aim for 95% confidence with a 5% error level.

Error is also referred to as the "confidence interval" and Confidence is also known as "Confidence Level." In order to avoid confusion, these concepts will simply be referred to as "Error" and "Confidence" in this article.

Determining the "Correct" Sample Size

Determining the "correct" sample size requires 3 pieces of information

1. The size of your population
2. Your desired error level (e.g. 5%)
3. Your desired level of confidence (e.g. 95%)

To calculate the number of people you will need to survey, click here

Performing a Stratified Random Sample

If you are performing a stratified random sample, there are a couple of additional steps that you need to take.

1. Determine the size of the smallest subgroup in your population. For example, if you want to look at males vs. females and there are fewer females, then this is the group you want to look at.
2. Calculate the number of people required to achieve your desired error level and level of confidence for this subgroup.
3. Calculate what percentage of people that you will need to survey within this subgroup (number of people to survey divided by total subgroup size).
4. Finally, calculate the number of people in each of the other subgroups that are needed to achieve this same ratio (multiply the percentage from step 3 by the size of each of the other subgroups). This is how many people you will need to survey within each group.

Remember, a larger group means a smaller percentage required to get the same level of accuracy. That is why we start with the smallest group and work our way up. The results you get from the larger groups should actually be even more accurate than the results from the smallest group, but you can at least be sure that each group meets your minimum accuracy requirements.

Do not calculate the number of people required to achieve the desired error level and level of confidence for each subgroup. While this might seem tempting since it would mean surveying fewer people from the larger groups, it will distort your overall results. It is important that each subgroup is proportionately represented. If you survey 75% of the people from a smaller group and only 25% of the people from a larger group, then the overall results for the entire population will be skewed in favor of the smaller group since they will be disproportionately represented. You might find this rather restrictive, especially if your subgroups vary greatly in size. While it might be OK to fudge a little around the edges, it is critical that you not disregard the importance of this fact. Alternatively, if the groups are not proportionally represented, adjust the final results to get proportionately weighted results from each group.

Final Steps - Putting it All Together

Once you have determined how many people you need from either your population as a whole or from each subgroup within your population, you simply need to determine a way to randomly select the specified number of people from each group. There are many wrong ways to go about this. Whatever technique you use, be sure that you really are selecting people at random and not accidentally giving preference to anybody for any reason. An easy and fast way to randomly select people is to use MS Excel. The steps to make the random selection are as follows:

1. Copy and paste a list of every person in the group into a single column. You can use names, email addresses, employee numbers, or whatever.
2. In a second column, fill the entire column with Excel's "Randomize" function. The exact value of each cell should be "=rand()" (do not include the quotation marks). Only fill the cells next to where you pasted the group info in step #1.
3. Sort both columns by the "Randomize" column. It does not matter whether you sort them in ascending or descending order.
4. Scroll down to the row number of the group size. Everybody from this row up is a part of your sample (see important note below regarding response rates).

For a sample Excel spreadsheet that illustrates how this would look, click here.

Adjusting for Estimated Response Rate

This last and very important step might require a bit of guesswork. At this point, you have figured out how many responses you need from your population or from each subgroup within your population. If every one of those people were to respond to your survey, then you would be all set; however, in reality, many of the people you have randomly selected will not complete your survey. You will need to estimate what percentage of people you expect to respond. Response rates can vary widely depending on the population and the nature of the survey. You can use past experience, your knowledge of the population, and the nature of the survey itself (longer surveys will have lower response rates) to come up with your best estimate. You will then need to figure out how many people you need to ask to complete the survey in order to get your desired number of responses. For employee surveys, a typical response rate is usually around 70% - 80%. For customer surveys, response rates are usually much lower - often 5% - 10% or even less.

Once you have come up with your best estimate of the response rate, just divide the number of people needed by the response rate percentage to figure out how many people you need to ask to complete the survey. For example, if you determined that you need 500 people to respond to your survey and you estimate that 75% of people will complete the survey, you will need to ask 667 people to complete the survey in order to get 500 responses (500 / 0.75 = 667).

It is worth noting that there might be some skewing of your results based on the fact that you are conducting an internet-based survey. Only people with access to the internet and who are comfortable filling out an online survey will respond. If you were conducting a survey of internet usage, this might be of particular importance. For most (non-academic) surveys, this is not a major concern. You will need to determine for yourself whether the survey medium might have an effect on your survey results.

For employee surveys, most organizations are too small for random sampling to be useful. For large companies (e.g. tens of thousands of employees), random sampling can be an option to consider when conducting an employee survey. Keep in mind, however, that many of the most critical employee engagement or employee satisfaction problems are often found in small subgroups within the organization. Random sampling can make it difficult or impossible to identify these hidden pockets of discontent since there won't be enough employees selected within those small groups to measure local employee attitudes.
Categories: 1

0 Replies to “Random Assignment Is Important To Ensure That Excel”

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *