Siobhan Cooney on Using Excel’s RAND Function in Evaluation

Hello! I’m Siobhan Cooney, an internal evaluator at the College Board and member of the Eastern Evaluation Research Society’s Board of Directors. In this post I share information on one of my favorite features of Excel – the RAND function – and how it can be used in a random selection process.

Evaluators at times need to randomly select individual study participants or groups of participants. In random assignment studies, we select teachers, schools, neighborhoods and the like to form our treatment and control groups. Or, we might randomly select individuals from a large population in an attempt to achieve a smaller but representative sample. And, when offering incentives to study participants, we might give them each an equal chance of winning one of five iPads, for example; in this case, we want to randomly select five names from a full list of participants.

Hot Tip: Sophisticated software is not required to complete a random selection process. When you need to choose one of two options, I recommend flipping a coin. For many other cases, Excel works well.

Cool Trick: Let’s suppose we want to randomly select two AEA past-presidents for a research study. Eleven past-presidents have agreed to be part of our research, if selected.

(1) In an Excel file containing the list of 11 AEA presidents, we first create a column in which we will tell Excel to generate random numbers.

(2) We then type “=RAND()” into the first empty cell in this new column. This function will return a random number between 0 and 1. We then click the cursor on the bottom right corner of that cell and pull down to apply the same function to other cells in the column.

(3) We have decided a priori that we will select the two AEA presidents with the lowest random numbers. Therefore, we want sort our cases from the smallest random number to the largest one. However, Excel’s typical set-up tells it to automatically recalculate our random values when we sort cases. For this reason, we first need to copy our list of names and numbers and paste it in a new sheet. Importantly, we need to “Paste Values” so that only our values are pasted, not our formulas as well.

(4) We can then select our list of names and numbers and sort them (Data > Sort > Sort by Random # on Values with Order Smallest to Largest). Our end result indicates that Nick Smith and Mel Mark have been randomly selected for our research.

Rad Resources: YouTube and Ann Emery’s Excel blog contain a wealth of resources on using Excel’s features to your advantage.

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.