Using Excel for Statistics in Gateway Cases Office 2007 & 2010Concepts CoveredTo Set Up Statistical Package 2007To Set Up Statistical Package 2010Using Excel: Descriptive StatisticsUsing Excel: Constructing HistogramsUsing Excel: Hypothesis TestingUsing Excel: Scatter PlotsUsing Excel: Regression Analysis1Using Excel for Statistics in Gateway Cases Office 2007 & 20102Concepts CoveredStatistics- Descriptive Statistics- Histograms- Hypothesis Testing- Scatter Plots- Regression Analysis3To Set Up Statistical Package2007Click Microsoft Office Button , and Then Click Excel OptionsClick Add-ins. In Manage Box, Select Excel Add-insClick GoIn the Add-Ins Available Box, Select Analysis ToolPak Check Box and Click OK. (If ToolPak Is Not Listed, Click Browse to Locate It.)4To Set Up Statistical Package2010Click File Tab > Options > Add-InsIn Inactive Applications Add-Ins , click Analysis ToolPak > GoData Analysis will appear at the far right of Data Tab of Excel spreadsheet5Using Excel:Descriptive StatisticsClick Data/Data Analysis (Far Right) /Descriptive Statistics & OK.Put Checkmarks on Summary Statistics, 95% or 99% Confidence Interval, & Labels in First Row Boxes. Move Cursor to Input Range Window, Highlight Data to Analyze including Labels, & Click OK.Your Data will Appear on New Worksheet.Widen Columns by Clicking Home/Format/AutoFit Column Width.6Using Excel:Constructing HistogramsClick Data/Data Analysis/Histogram & OK.Put Checkmarks on Chart Output & New Worksheet Boxes.Move Cursor to Input Range Window, Highlight Data Going into Histogram.Move Cursor to Input Bin Range, Highlight Data Showing Upper Value of Each Bin & Click OK.Histogram will be on New Worksheet. You May Lengthen it by Clicking Blank Space in Window, Moving Cursor to Window Bottom Line & Holding Down Mouse Button as You Pull Down Window.7Using Excel:Hypothesis TestingGo to Sheet One.Click Data/Data Analysis/ and the Appropriate Statistical Test. Then Click OK.On New Window Check Labels Box and Put Cursor on Variable 1 Range.Highlight Variable 1 Data Including Label.Put Cursor on Variable 2 Range & Highlight Variable 2 Data (Including Label). Then Click OK.Click Home/Format/AutoFit/Column Width8Using Excel:Scatter PlotsGo to Sheet One.Highlight Data (Be Sure X Values are in Left Column and Y Values are in Right Column).Click Insert/Scatter. Pull down menu and click Upper Left Icon.Click a Datum Point on Chart with Right Mouse Key, Add Trendline, & Click Linear.9Using Excel:Regression AnalysisGo to Sheet One.Click Data/Data Analysis (On Far Right) /Regression & Click OK.On New Window Check Labels Box and Put Cursor on X Range.Highlight X Data Including Label.Put Cursor on Y Range & Highlight Y Data (Including Label), Then Click OK.Click Home/Format/AutoFit Column