Some Useful Statistical Functions (Mac)

This page provides examples a few useful statistical functions you can use in Excel. Let's say you want to calculate two means, compare their standard deviations, and run a t-test to see if they're statistically distinguishable. 

Calculating the Mean

The AVERAGE function can calculate the mean of a distribution. Simply type "=AVERAGE(" and then fill in the column of numbers (which you can highlight as seen below). 

Then close the parentheses and hit enter. This will calculate the mean: 

You can click the cell and drag it over to calculate the mean for the next column as well:

Now we see that Column1 has a mean of 2.45 and Column2 has a mean of 3.3. 

Standard Deviation

You can get the standard deviation using the STDEV function. It uses the same logic as the AVERAGE function:

Close the brackets and hit enter to get the standard deviation for Column1. Then drag the cell to get the standard deviation for Column2 as well:

Column1 has a slightly larger standard deviation than Column2, but not by much. 

T-Test

Now we have some basic descriptive information about these two columns, but what about statistical significance? You can conduct a t-test in Excel using the TTEST function. This function requires four arguments. The first two are two arrays (columns or rows) you want to compare. The third argument is whether you want to use a 1- or 2-tailed test. Finally, the fourth argument is what type of t-test (Excel offers 3 options) you want. 

As seen below, we'll use B2:B21 as our first array and C2:C21 as our second array. Let's go with 2-tails and call for a type 2 t-test (more information about types can be found here):

Close the parentheses and hit enter to get a p-value:

A p-value of .04 tells us this difference is statistically significant at the standard .05 level.

 

<< Back to Common Function & Formula Error Messages

Continue to Macros >>

 

Return to Table of Contents

 

Questions, comments, concerns?
Send an email to the Empirical Reasoning Lab
Or drop by during the Lab's open hours.