Skewness in Excel

Skewness is a measurement which helps you to understand the effect outliers are having in your data.

 

A positive skewness indicates a ‘right-skewed’ distribution where the majority of the values are on the left of the distribution and the outlier values are on the right. A negative skewness indicates the opposite. If a skewness equals zero, that describes a perfect normal distribution. As a general rule, the mean (average) is the best measurement for a typical value unless you have a positive skew above 1 or a negative skew below -1, in those cases the median provides a more accurate measurement.

 

The SKEW function can be used to measure skewness in Excel as displayed in this example.

 

 

You can see this dataset has a positive skew of 2.2123. In this example, the median value gives a more realistic example of typical salary than the mean. The data is skewed by some outliers on large salaries.

 

To detect individual outliers, you can use a measurement of anything outside of 1.5 multiplied by the IQR (interquartile range). Here’s an example of how to do this using Excel formulas.

 

 

Note that we have added formulas for each of the first three quartiles. Quartile 2 is the same as the median. The interquartile range (IQR) is the difference between Quartiles 1 and 3. To identify outliers, the result needs to be either less than 1.5 of the IQR under Q1, or more than 1.5 of the IQR above Q3. The result here finds two observations above the maximum threshold, so we would define those as outliers.

Leave a Reply

Your email address will not be published. Required fields are marked *