Welcome to this demo on Microsoft Excel Analysis ToolPak. Before reviewing this demo, please be sure to complete all of the module readings. You should have a basic understanding of descriptive and inferential statistics, before moving on to Microsoft Excel Analysis ToolPak, for performing complex statistical data analysis. The instruction is to turn on this Excel add on. Or slightly different between Windows and Mac. On Windows, inside of Excel you would click "File", click "Options", click "Add-ins." In the manage box, select "Excel Add-ins", and then click "Go." You would then see a checkbox next to Analysis ToolPak, which you would enable and click "Okay". On macOS, which I'm demoing today, you click on "Tools", "Excel Add-ins", and make sure you have Analysis ToolPak checked, and click "Okay." This will activate the tools for you. You may remember that descriptive statistics will summarize a variable for a given dataset. Here we're looking at central tendency, which is mean, median, mode. You might ask, where's the center of the variable? We're also looking at dispersion, which is range and standard deviation. Here we might ask, how much does the variable vary? Then we're looking at skewness, which is the symmetric nature of the data. What is its shape, and what does the variable's distribution look like? In Excel we can model some of these various statistical tests using the Analysis ToolPak. To get started, we'll click on the "Data" tab in the Excel ribbon, and all the way on the right-hand side you should see a button that says Data Analysis. We'll select that. You'll then see a long list of data analysis capabilities that Excel has through the Data Analysis ToolPak. To get started on the demo, we'll look at descriptive statistics. I'll highlight descriptive statistics, and I will click "Okay". Next, it wants me to select the input range for my dataset. I may want to investigate a single variable here. In this example, I have a sample dataset called high school data, so I'm looking at various demographic information about students and their SAT verbal, SAT math scores. I may just want to get some descriptive statistics on SAT verbal scores. If I select that entire column H, which is the SAT verbal, notice the input range here updates to column H. I'll group the data by columns, which is the default. You want to make sure if the first row is labels for your data, that you have that checkbox checked, so I have labels in the first row. Then I would like for Excel to produce a new worksheet with my descriptive statistics. I'll click "Okay", and it'll take just a minute for Excel to run the analysis. Then you'll see we have a new sheet here, with many descriptive statistics available to us. Obviously, we have mean, median, mode, which all describe various levels of averages. You can see the mean SAT verbal scores just over 490. We also have other terms that are familiar to statistics such as standard error or standard deviation, kurtosis, and skewness. We also get some summary information here with the range minimum and maximum total sum, which really doesn't have any bearing on SAT scores. But we get all of this information with just a couple of clicks of a button, using Excel's Analysis ToolPak. Now we can also use Excel Analysis ToolPak for correlation, which would look at relationship between variables. Again, using the same sample dataset, make sure you're on the Data tab in the Excel ribbon. Go to that same Data Analysis button. This time from the list, let's find correlation, and click "Okay." We have a similar correlation input window to what we saw with descriptive statistics. We're looking for the input range. In correlation I can select many variables here. I will look at everything from male 2, SAT math. I've just highlighted all of those columns, and notice the input range has updated to include columns D through I. Again, we'll group by columns, I do have labels in the first row, and I'd like the output to be generated in a new worksheet. I'll click "Okay." Excel will run the computations here. Now, we can see the correlation r values for each of the relationships in this dataset. Based on typical statistics ranges of the correlation coefficient, it looks like one of the strongest relationships we have here, although none of them are very strong. It looks like here. There's a negative relationship between SAT verbal scores and whether a student is on the free or reduced lunch program or not. Again, none of them are very strong, but you can refer to the table in the module readings that will identify relationship strength based on correlation coefficient. Now, obviously, the Data Analysis ToolPak can perform many more statistical tests and you can explore those with additional datasets.
