HumaniTV #1 - Choose a Random Sample for your Next Survey using Excel's RAND() Function
Go to this link to get a copy of the template I use in this video: https://humanitariandatasolutions.ac-...
Do you ever have to report on an M&E outcome indicator and give a percentage, for example, 70% of the population was impacted?
How do you figure out what that percentage is?
I’m going to share with you a really technical (nerdy?) tip with you this week – all about how to choose a random sample when you’re doing a survey (like a baseline or endline).
I know this sounds boring, but you know what? A lot of people don’t do random sampling when they carry out an assessment. And it bugs me. Because when you choose a random sample, your assessment results are just that much more reliable, more professional. Without a random sample, your data is biased.
So...do you use random sampling?
The ONE mistake many people make when carrying out a “survey” is they don’t do a random sample, and yet they still report percentages as if their results apply to the whole population.
Step 0 – What is your “population”
Before you report on it, you have to define who exactly is in your population.
Step 1 – Define your Sample Size
Based on your population, what should your “sample size” be? I explain what kind of confidence interval and margin of error you might want to have to calculate this.
Step 2 – Plan for People who aren't Available to Interview
What if you choose a sample to interview, but then someone isn’t available when you go to interview? How do you prepare for this before you begin your survey?
Step 3 – What's the Probability
Calculate the probability that any one person in your whole population could be chosen for an interview.
Step 4 – Bernoulli and RAND()
Using the Bernoulli distribution, you can now use Excel’s RAND() function to randomly select people from your population list to interview.
Step 5 – Paste Special Values
Every time you refresh an Excel formula, all of the formulas refresh. So it’s important to copy and “paste special” your random interview selection list so it doesn’t change.
Step 6 – Double-Check your Random Interview List
Use a Pivot Table to double-check that your random selection produced the right number of people to interview.
Steps 7 & 8 – Filter and Finalize
Filter your household list to produce the FINAL list of people to interview – these people were selected randomly, so after you interview them, the percentage results you get from them should apply to your ENTIRE population.
Go to this link to get a copy of the template I use in this video: https://humanitariandatasolutions.ac-...