STAT-C 225 (SURVEY SAMPLING)

Lesson 3. Sampling Size and Sample Calculator using MS Excel

Course Pack compiled by: Prof. Frances Jay B. Pacaldo, LPT | Faculty, Department of Mathematics and Statistics | francesjay.pacaldo@ctu.edu.ph

Course Packs on Survey Sampling

Course : STAT-C 225 Survey Sampling

Program : BS Statistics

Year Level : 2

Semester : Second

Creating a sample Size using MS Excel

MS Excel is a commonly used Microsoft Office application. It is a spreadsheet program which is used to save and analyze numerical data.

Cochran’s Sample Size Formula:

The Cochran formula allows you to calculate an ideal sample size given a desired level of precision, desired confidence level, and the estimated proportion of the attribute present in the population.

Cochran’s formula is considered especially appropriate in situations with large populations. A sample of any given size provides more information about a smaller population than a larger one, so there’s a ‘correction’ through which the number given by Cochran’s formula can be reduced if the whole population is relatively small.

The Cochran formula is:

Where:

e is the desired level of precision (i.e. the margin of error),

p is the (estimated) proportion of the population which has the attribute in question

q is 1 – p.

z is the z-score (see z-table)

Cochran’s Formula Example

Suppose we are doing a study on the inhabitants of a large town, and want to find out how many households serve breakfast in the mornings. We don’t have much information on the subject to begin with, so we’re going to assume that half of the families serve breakfast: this gives us maximum variability. So p = 0.5. Now let’s say we want 99% confidence, and at least 5 percent—plus or minus—precision. A 95 % confidence level gives us Z values of 2.58, per the normal tables, so we get

((2.58)² (0.5) (0.5)) / (0.05)² = 666

How to apply this in MS Excel:

Step 1. Open a new a blank workbook

Step 2. Choose a certain cell (i.e., A1:A9) where you can input the following and a corresponding cell where input their values:

Confidence Level
Z score
Proportion of the Population [P]
Q = 1 - P
Margin of Error

In our case, we chose cell C6:D10

See image below.

Note: You can also add borders by just clicking "Home" and look for "All borders"

Step 3. Provide the values of the Confidence Level using "Data Validation"

Step 3.1. Input the following values first in your workbook. (Just choose any cell)

Step 3.2. Click cell D6 and go to "Data" tab, and look for Data Validation. Go to settings and change "any value" to "list" and then go to source and highlights the list of values you want to include in your confidence level. (i.e., 90%, 95%, etc.). In our example, highlights D17:D26

Step 4. Use =vlookup to provide a value in your z-score that corresponds to your confidence level. That when you choose a confidence level it will automatically provide you its equivalent z-score. Use the values you have in your cell D17 to E26.

=VLOOKUP(D6,$D$17:$E$26,2,FALSE)

Select D7 and enter =vlookup.

For lookup_value, highlight the value in your confidence level (D6)

For table_array, highlight the values in your percentage and z-score (D17:E26) and click f4 to lock the values: $D$17:$E$26

For index_col_num, input 2 since you want to display the second column of your highlighted values (z-score)

For range_lookup, choose FALSE since you want to display the exact value that corresponds your confidence level. Otherwise, choose TRUE if you want to have the approximate values.

Step 5. Since the Proportion of the Population [P] depends on your population, we just leave it blank. We will provide formula for Q instead since Q = P-1. Select D9 and input = 1-D8

Step 6. Since the Margin of Error (e) is to be provided by the researcher, we just leave it blank. We now select another cell where we will input our sample size (n). Since we are using Cochran’s Sample Size Formula we use n0 as its symbol. We select cell C12 and D12 where we input and show the result of our sample size using Cochran.

Step 7. Input the Cochran’s Sample Size Formula in D12,

= (Z-score^2 * Proportion of the Population (P) * Q ) / Margin of Error^2)

Step 7.1. Select D12 and input the Cochran's Formula, start with the equal sign "=" and select D7 using your mouse and raised it to the power of 2 = D7^2. Multiply it with the Proportion of the Population [P], D8, and the Value of Q= 1-P,D9 and divide it with the margin of error, D10 raised to 2, D10^2, then click enter. Result will show #DIV/0! since you don't have any values yet.

= (D7^2*D8*D9)/(D10^2)

So Lets try our calculator if its working. Try the example below.

Example:

So a random sample of 666 households in our target population should be enough to give us the confidence levels we need.

Page updated

Google Sites

Report abuse