Random sampling in excel 2007

QMTH 205 Spring 2016

Project 2 Sampling Distribution and Interval Estimation

Due: Thursday, April 21, before class

Download file project2.xls from Blackboard. Type in your name on every worksheet.. Turn in the printed copies of completed spreadsheets “Sampling”, “Sigma known case”, “Sigma unknown case”, and “Proportion” before class on April 21st. Penalty is possible for late work.

Part I. Random Sampling with Excel The population includes 200 students and their test scores shown in worksheet “Sampling”. Test scores are approximately normally distributed. 1. Enter formulas to compute the population mean score, population stardard deviation (stdevp() for

Excel 2007 or earlier, or stdev.p()) and the population size. 2. Select a simple random sample of 20 students.

(1) Enter =Rand() in cell C4 and copy it down for all students. (2) Sort Students and Scores by Random Number. The first 20 students make a random sample.

3. Enter the sample size and enter formulas to compute the sample mean score and sample standard deviation (stdev() for Excel 2007 or earlier, or stdev.s()).

Part II. Interval Estimation of Population Mean 1. The V known case (worksheet “Sigma Known Case”) A sample of 30 customers is collected and their satisfaction scores are shown in worksheet “Sigma Known Case”. The population standard deviation is 10. For the V known case, the Excel function CONFIDENCE(D, V, n) for Excel 2007 or earlier, or CONFIDENCE.norm(D, V, n)) computes the margin of error. Construct a 90% confidence interval for the population mean (confidence level 1-D = 90%): enter Excel formulas to compute sample mean score, sample size, margin of error, lower and upper limits (Upper Limit = the sample mean + the margin error and Lower Limit = the sample mean – the margin error) of confidence interval. 2. The V unknown case (worksheet “Sigma Unknown Case”) For the V unknown case, use Excel built-in data analysis procedure “Descriptive Statistics” to estimate population mean. Work with the random sample of 20 students you took in worksheet “Sampling” to estimate population mean test score. Run Excel built-in procedure “Descriptive Statistics” to develop a 90% confidence interval estimate for the population mean score. Show label “Score” in output. Report sample size, sample mean, margin of error, lower limit and upper limit of confidence interval. You will see two numbers from the output of “Descriptive Statistics” procedure: the sample mean in the row of “Mean” and the margin of error in the row of “Confidence Level (90%)”. We have Upper Limit = the sample mean + the margin error and Lower Limit = the sample mean – the margin error. Enter formulas to compute the upper confidence limit and lower confidence limit of your estimation.

2

Part III. Small program for interval estimation of population proportion A sample of 450 responses is shown in worksheet “Proportion”. Develop a 90% confidence interval estimation for proportion of “Yes”. Then,

(1) Enter “Response of Interest” (Yes), and Excel formulas for “Count for Response” (COUNTIF()) and “Sample Size” (COUNTA()).

(2) Enter an Excel formula to compute the sample proportion. Enter the confidence level. The Excel formulas in the protected cells give out values of Z, margin of error, upper limit and lower limit of confidence interval.