A to Z of Excel Functions: The SKEW Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the SKEW function.

The SKEW function

Imagine you plotted a sample of the likely outcomes of an event occurring (i.e. the likelier the outcome, the greater the frequency / probability).  This could be plotted as a distribution of outcomes.  The SKEW function returns the skewness of this distribution.  Skewness characterises the degree of asymmetry of a distribution around its mean.  Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values.  Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.

There are various measures employed to determine the level of skewness.  Pearson Mode skewness uses the formula

where σ represents the standard deviation of the distribution.  An alternative version often used is 

which is also referred to as Pearson’s second coefficient of skewness.  Which one would you lean to?  (Sigh – Ed.)

These calculations assume you know the mean, mode or median, and the standard deviation of your data.  Sometimes you might not have that information.  Instead, you might have information about your quartiles.  In this instance, you can use Bowley Skewness as an alternative to find out more about the asymmetry of your distribution. It’s very useful if you have extreme data values (outliers) or if you have an open-ended distribution.  It is calculated as

Kelly’s Measure of Skewness is one of several ways to measure skewness in a data distribution.  The last approach is based upon the central 50% of the observations in a data set.  It leaves 25% of the observations in each tail of the distribution.  Kelly suggested that leaving out 50% of data to calculate skewness was too extreme.  He created a measure to find skewness with more data.  Kelly’s measure is based on P90 (the 90th percentile) and P10 (the 10th percentile).  Only 20% of observations (10% in each tail) are excluded from the measure.

The technique you use depends upon what you know about your data.  There are more sophisticated approaches, such as the one Microsoft employs.

Excel has the function SKEW to calculate it.  Its syntax is: 

SKEW has the following arguments:

• number1, number2, …: the first argument (number1) is required, whereas subsequent numbers are optional.  You may have between one [1] and 255 arguments for which you want to calculate skewness.  You can also use a single array or a reference to an array instead of arguments separated by commas.

It should be further noted that:

• arguments may either be numbers or names, arrays or references that contain numbers
• logical values and text representations of numbers that you type directly into the list of arguments are counted
• if an array or reference argument contains text, logical values or empty cells, those values are ignored; however, cells with the value zero [0] are included
• arguments that are error values or text that cannot be translated into numbers cause errors
• if there are fewer than three data points or the sample standard deviation is zero, SKEW returns the #DIV/0! error value
• the equation for skewness is defined as the adjusted Fisher-Pearson standardised moment coefficient:

Here, n represents the number of data points in the sample, x_i represents the ith data point, x with a line over it represents the mean and σ denotes the standard deviation of the sample.  It is difficult to find this formula; It generally doesn’t even appear in elementary statistics textbooks.  However, the results of the calculation are important and tell you:
• the direction of the skew (positive or negative)
• how the sample compares with a normal (symmetric) distribution.  The further the skew result is from zero, the greater the skew.

Please see my example below:

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.