Confidence Intervals in Excel

Page history last edited by Mr. Kretsch 3 months ago

Getting started:

Download, save, then open the following file: 2011StartOfYearPollBoth(3).xls. The file is the combined results of the poll we did at the beginning of the year. The sheet you will be working on contains the data for all female respondents who gave their total SAT score.

Preparing the spreadsheet

What do you want to do?	What are the steps?	What will you see?
Insert a column for labels	Select column A Select Columns from the Insert menu	A new column A will be added. the other columns will be moved to the right.
Insert rows for the calculations	Select rows 2 though 9 Select Rows from the Insert menu.	Seven new rows, number 2 through 8, will be added. The remaining rows will be moved down.
Add labels	Type in the following text in cells A2 to A9 Mean Standard deviation Sample size Degree of confidence Alpha Margin of error Interval Minimum Interval Maximum Make column A wider if you have to to fit the text
Enter formulas into the spread sheet. These formulas will calculate statistics on information you will eventually paste into column C	Enter the function for the average of the Total SAT in cell E2 Enter =average followed by a left parenthesis, (. Select the cells containing the total SAT data, Then close the function with a right parenthesis, ), and Enter Similarly, enter the function for standard deviation in cell E3. The function name is =stdev Similarly, enter the function for counting the sample in cell E4. The function name is =count Enter 0.95 into cell E5 Enter into cell E6 the calculation: =1-E5	Once you type the formula and type Enter, you will not see the text of the formula but its results.

Calculate the Confidence interval

What do you want to do?

What are the steps?

What will you see?

Enter the formula for the Margin of Error

Select cell E7
Enter the following function: =confidence, followed by a left parenthesis.
Enter the cell address of the alpha value, followed by a comma,
Enter the cell address of the standard deviation value, followed by a comma,
Finally, enter the cell address of the sample size value, followed by a right parenthesis and Enter

The margin of error will be displayed in cell E7

Enter the calculations for the Confidence interval from

Select cell E8
Enter the following formula: =E2-E7
Similarly, enter the calculation to find the interval maximum in cell E9

The contents will change from decimal to the percent equivalent.

Find the values for other data

What do you want to do?	What are the steps ?	What will you see?
Copy the calculation to height	Select cells E2 to E9 Select Copy Seelct cell F2 Select Paste	The seven values foe height will be displayed in column F