Transcription
Creating Confidence Intervals using Excel 2013XL8A-V0RCreate Confidence Intervals using Excel 20131XL8A-V0RXL8A-V0RCreate Confidence IntervalsUsing Excel 2013Member: International Statistical InstituteUS Rep: International Statistical Literacy ProjectDirector, W. M. Keck Statistical Literacy ProjectAssignmentCompete all five sheets (one sheet per tab).Slides, Output and Data at ce-Intervals-Data.xlsxCreate Confidence Intervals using Excel 201331. Using Excel to BuildOne-group Margin of Error1. Single sample: Create slides 5 and 7.2. Two samples: Create slides 9, 10 and 11.Note: slide 10 involves an array formula.3. Upload completed spreadsheet.XL8A-V0R1A: Use Excel Toolpak Descriptive-Statistics.Excel describes the Margin of Error as theConfidence Level (95%).[Behaves like a command: no auto-update]4.From Data ribbon, select Data AnalysisFrom Data Analysis menus, select Descriptive Statistics.1B: Use functions: CONFIDENCE (or STDEVand COUNT) and AVERAGE.Recommended since it auto-updates.Create Confidence Intervals using Excel 2013Create Confidence Intervals using Excel 20131A: Margin of Errorusing Descriptive StatisticsFor single-sample (1-group) confidence intervalsXL8A-V0R2Input data (Pulse.xls with 240 e-Intervals-Data.xlsxMilo SchieldXL8A-V0RCreate Confidence Intervals using Excel 201351A: Margin of Errorusing Descriptive StatisticsEnter input range. Check box for “Labels in 1st row.”Set “Output Range” to J19.Check “Summary statistics” and “Confidence level.”Press OK.XL8A-V0RCreate Confidence Intervals using Excel 201361B: Margin of error (ME)using CONFIDENCE FunctionCONFIDENCE.T: Population std deviation unknown.This is the most conservative: biggest ME.Approaches confidence.norm as sample size increases.CONFIDENCE.NORM: Population standard deviationis known or the sample statistic is a proportion.Excel CONFIDENCE functions return Margin of Error. Confidence.T (alpha, Sample std. deviation, sample size) Confidence.Norm (alpha, Pop. std. deviation, sample size)If CL 95%, .05. Confidence.T (T/Z)*Confidence.NormConfidence Level: Margin of Error T*StdDev()/Sqrt(n)Use Confidence.Norm for proportions or size Intervals-Slides.pdf1
Creating Confidence Intervals using Excel 2013XL8A-V0RCreate Confidence Intervals using Excel 2013XL8A-V0RXL8A-V0R71B: Confidence Intervalsusing Stdev.S and Confidence.TCreate Confidence Intervals using Excel 201382. Using Excel to BuildTwo-group Margin of ErrorFor two-group confidence intervals of stacked data(one subject per row) without pre-sorting:2A: Use Average, Stdev and Count in pivot table;[Behaves like a command: no auto update]2B: Use AverageIF and CountIF functions.Use Stdev(IF) within an array function.2C: Use Data functions: dAverage, dStdev and dCount.Average gives proportion if binary data is coded 0 or 1XL8A-V0RCreate Confidence Intervals using Excel 2013** If stacked data is sorted contiguously, one can build twoconfidence intervals using two one-group approaches.XL8A-V0R92A: Build 2-Grp Conf. Intervalsusing Pivot Table StatisticsCreate Confidence Intervals using Excel 2013102B: Build 2-Grp Conf. Intervals.Use AverageIf, CountIf & {Stdev(If)}.XL8A-V0RCreate Confidence Intervals using Excel 2013112C: Build 2-Grp Conf. IntervalsUse dAverage, dStdev & dCount.XL8A-V0RCreate Confidence Intervals using Excel 201312Statistical Significanceand Confidence IntervalsIf two 95% confidence intervals do not overlap, thenthe difference in means is statistically-significant. Butthe converse may be false: those cases that overlap maystill have differences that are statistically significant.If the 95% confidence interval for one group does notoverlap the mean for the second, then the differencein means is statistically-significant – at the 0.05 level.In this case, the converse is also true: those cases wherethe confidence interval overlaps the mean are notstatistically significant. See Conrad Carlberg’s x?p 1717265&seqNum ntervals-Slides.pdf2
XL8A-V0RCreate Confidence Intervals using Excel 20131Create Confidence IntervalsUsing Excel 2013Milo SchieldMember: International Statistical InstituteUS Rep: International Statistical Literacy ProjectDirector, W. M. Keck Statistical Literacy ProjectSlides, Output and Data at ce-Intervals-Data.xlsx
XL8A-V0RCreate Confidence Intervals using Excel 2013AssignmentInput data (Pulse.xls with 240 e-Intervals-Data.xlsxCompete all five sheets (one sheet per tab).1. Single sample: Create slides 5 and 7.2. Two samples: Create slides 9, 10 and 11.Note: slide 10 involves an array formula.3. Upload completed spreadsheet.2
XL8A-V0RCreate Confidence Intervals using Excel 201331. Using Excel to BuildOne-group Margin of ErrorFor single-sample (1-group) confidence intervals1A: Use Excel Toolpak Descriptive-Statistics.Excel describes the Margin of Error as theConfidence Level (95%).[Behaves like a command: no auto-update]1B: Use functions: CONFIDENCE (or STDEVand COUNT) and AVERAGE.Recommended since it auto-updates.
XL8A-V0RCreate Confidence Intervals using Excel 201341A: Margin of Errorusing Descriptive Statistics.From Data ribbon, select Data AnalysisFrom Data Analysis menus, select Descriptive Statistics.Enter input range. Check box for “Labels in 1st row.”Set “Output Range” to J19.Check “Summary statistics” and “Confidence level.”Press OK.
XL8A-V0RCreate Confidence Intervals using Excel 201351A: Margin of Errorusing Descriptive StatisticsConfidence Level: Margin of Error T*StdDev()/Sqrt(n)
XL8A-V0RCreate Confidence Intervals using Excel 201361B: Margin of error (ME)using CONFIDENCE FunctionCONFIDENCE.T: Population std deviation unknown.This is the most conservative: biggest ME.Approaches confidence.norm as sample size increases.CONFIDENCE.NORM: Population standard deviationis known or the sample statistic is a proportion.Excel CONFIDENCE functions return Margin of Error. Confidence.T (alpha, Sample std. deviation, sample size) Confidence.Norm (alpha, Pop. std. deviation, sample size)If CL 95%, α .05. Confidence.T (T/Z)*Confidence.NormUse Confidence.Norm for proportions or size 30
XL8A-V0RCreate Confidence Intervals using Excel 201371B: Confidence Intervalsusing Stdev.S and Confidence.TAverage gives proportion if binary data is coded 0 or 1
XL8A-V0RCreate Confidence Intervals using Excel 201382. Using Excel to BuildTwo-group Margin of ErrorFor two-group confidence intervals of stacked data(one subject per row) without pre-sorting:2A: Use Average, Stdev and Count in pivot table;[Behaves like a command: no auto update]2B: Use AverageIF and CountIF functions.Use Stdev(IF) within an array function.2C: Use Data functions: dAverage, dStdev and dCount.** If stacked data is sorted contiguously, one can build twoconfidence intervals using two one-group approaches.
XL8A-V0RCreate Confidence Intervals using Excel 201392A: Build 2-Grp Conf. Intervalsusing Pivot Table Statistics
XL8A-V0RCreate Confidence Intervals using Excel 2013102B: Build 2-Grp Conf. Intervals.Use AverageIf, CountIf & {Stdev(If)}.
XL8A-V0RCreate Confidence Intervals using Excel 2013112C: Build 2-Grp Conf. IntervalsUse dAverage, dStdev & dCount.
XL8A-V0RCreate Confidence Intervals using Excel 201312Statistical Significanceand Confidence IntervalsIf two 95% confidence intervals do not overlap, thenthe difference in means is statistically-significant. Butthe converse may be false: those cases that overlap maystill have differences that are statistically significant.If the 95% confidence interval for one group does notoverlap the mean for the second, then the differencein means is statistically-significant – at the 0.05 level.In this case, the converse is also true: those cases wherethe confidence interval overlaps the mean are notstatistically significant. See Conrad Carlberg’s x?p 1717265&seqNum 3
XL8A-V0R Create Confidence Intervals using Excel 2013 1 Milo Schield Member: International Statistical Institute US Rep: International Statistical Literacy Project Director, W. M. Keck Statistical Literacy Project
