In this Excel tutorial lesson, you will learn what skewness is and how to calculate skewness in an Excel application.
To find the skewness in Excel, please prepare some data tables.
Calculate Skewness using SKEW function
Excel provides a SKEW function. Syntax is:
=SKEW(number1, [number2], …)
In this example, the formula is: =SKEW(B2:B13)
Skewness here is strongly above 0 (skewness > 0), which means that it is a right-sided asymmetry. The conclusion is that for many months, sales were significantly lower than the average sales.
Calculate Skewness using Data Analysis ToolPak Add-In
First, you need to make sure your Data Analysis Toolpak Add-In is installed. Here is the lesson on how to install Data Analysis Toolpak Add-In.
Go to Ribbon to the Data tab. Click the Data Analysis button.
A new window appears. Choose Descriptive Statistics and click OK.
Enter the proper parameters:
- Input Range – range of your data ($B$1:$B$13 in my example).
- Labels in first row – there are labels in my data table.
- Output Range – choose where you want to get your skewness calculated (I chose the same Sheet $E$1:$F$15).
- Summary statistics – make sure it is checked.
And this is how you see my statistics summary. Skewness is calculated in row 10. The result is the same as in Example 1 where we checked how to calculate Skewness using the SKEW Excel function.
Using the STDEV.P function and the AVG function
Another way to calculate skewness in Excel is to use the STDEV.P function and the AVG function. To do this, you need to calculate the standard deviation and the mean of the data first, and then use the following formula to calculate skewness:
Skewness = 3 * (AVG – Mean) / STDEV.P
Where AVG represents the average of the data, Mean represents the mean of the data, and STDEV.P represents the standard deviation of the data.
The third moment of the data
Another way to calculate skewness in Excel is to use the formula for skewness that is based on the third moment of the data. The third moment measures the amount of skewness in the distribution. The formula for skewness is as follows:
Skewness = (N / (N – 1) * (N – 2)) * SUM((X – Mean)^3) / (STDEV.P^3)
Where:
- N is the number of data points
- X is the value of each data point
- Mean is the average of the data
- STDEV.P is the standard deviation of the data calculated by the STDEV.P function
- SUM is the SUM function in Excel, which calculates the sum of a range of values
To use this formula in Excel, you first need to calculate the mean, standard deviation, and the sum of the cubed deviations from the mean. Once you have these values, you can use the formula to calculate the skewness.
This method provides a more detailed and accurate representation of skewness in your data, as it takes into account the third moment of the distribution. However, it requires more calculation steps than the other methods and may be more complex for those who are new to statistical analysis in Excel.