Excel Data Analysis Tool – Descriptive Statistics – Simple Explanation

After writing some articles for linear regression here and here, I have decided to get a little deeper into the Excel Data Analysis Tool. Pretty much, what it does is providing a lot of statistical information for a set of data.

In my example, I have generated some random data for 2013 and some data for 2014. After running the Excel Data Analysis Tool, I have obtained 13 statistical indicators for my sets. In order to obtain these indicators, we should simply select “Descriptive Statistics” from the “Data Analysis” menu:

Descriptive Statistics

Then you have to select the data range, you would like to examine and the type of result you need. After this action, you get the following data:

Descriptive Statistics1

Here comes the real reason why I have wrote this article! I simply need a place, where these statistics are explained. Wikipedia is a good choice, but I have to search quite a lot. Thus, using the definitions of the Guerrero’s book, here is what they mean:


Mean – the sum of observations, divided by the number of observations (a.k.a average)

Standard Deviation – a statistical measure of the degree of variation of observations relative to the mean of all the observations. The calculation of the standard deviation is the square root of the sum of the squared deviations for each value in the data from the mean of the distribution, which is then divided by the number of observations.

Range – a simple measure of variation which is calculated as the high observation value minus the low

Median is the data point in the middle of the distribution of all data points. There are as many values below as above the median.

Mode is the most often occuring value in the data observations.

Standard error is the sample standard deviation divided by the square root of the number of data observations.

Sample variance is the square of the sample standard deviation of the data observations.

Kurtosis (peakedness) and skewness (asymmetry) are measures, related to the shape of a data, organized into a frequency distribution.


And in stead of calculating these statistics separately with a formula, you may obtain the information with the pre-installed Add-In for Data Analysis.

About

VBA Developer

Tagged with: , , , ,