This final content-related post is dedicated the monster called statistics.  Although I took the required statistical analysis class here at Vanderbilt a couple of years ago, this topic continues to intimidate me as well as many others.  Today, I am aiming to learn and transfer the basic statistical functions as well as teach myself how to use Excel for advanced statistical analysis.

Mean

When I said that I was starting from the bottom, I was not kidding.  First up?  The AVERAGE function – synonymous with the mean from your fourth-grade algebra class.  To calculate the average of a range of cells, that’s right, you just enter in =AVERAGE(range).  See Figure 1 below.

As seen earlier in this blog, you can average cells based on one argument using the AVERAGEIF function.  I will not show an example of this because it is relatively straightforward.

Median and Mode

Remember the big three mathematical M’s from fourth grade?  Well, they are back and better than ever.  To find the median or a middle number of a set of data, simply use the MEDIAN function with the formula as follows: =MEDIAN(range).  Similarly, you can use the MODE function to find the most frequently occurring number.  See Figure 2 for an example of the median, and Figure 3 for an example of the mode (N/A because there were no repeating numbers.

Average

The standard deviation concept is one of the most basic ideas of statistics.  Essentially, it is the number that tells you exactly how far numbers are from their mean.  This number is useful because it tells you how consistent that data is from each other, and it also will give you the most relevant information on each individual entry.  To do so, enter the formula as follows: =STDEV(range).  If your standard deviation is 0, then all the numbers are the same which is good for your statistics.  There is an example below (Figure 4) where I calculated the standard deviation of each representative’s number of units sold.

 

Statistical Analysis

Alright, we are now moving on from the basics.  Before you begin, make sure that you have your Data Analysis Tool pack installed on your Excel application.  It should be on the far right of your tool bar.  If not, go to File>Option>Add-Ins>Add Analysis Tool.  (Keep in mind, this changes per Excel version and year.)

This Data Analysis tool can do a variety of things.  If you want an overview of the data, which ties into my learning goal from the beginning, then you can get a list of descriptive statistics that explain the data.

Simply highlight the column containing the data that you want to analyze without the header included and check SUMMARY STATISTICS.  It will return with a list of data that might be overwhelming, but each of these variables is actually extremely helpful in looking at your data.

Summary

Although there are a variety of things that you can do with the formulas above, the most powerful statistical analysis is generated by the Data Analysis tool that must be added in.  It will give you the list of information below:

• Mean
• Standard Error
• Median
• Mode
• Standard Deviation
• Sample Variance
• Kurtosis
• Skewness
• Range
• Minimum
• Maximum
• Sum
• Count
• Confidence Level (95.0%)

From here, you can start to determine regression and more to evaluate your data.  Again, all of this is helpful, but I have learned that the basics above will give you most of what you need to know to move forward.

Let me know if you have any questions at all!

Signing off,

Anna Lee