Calculate skewness (asymmetry) in Excel using VBA.

Steps to Create the VBA Function for Skewness Calculation:

1. Open the VBA Editor:
   In Excel, press Alt + F11 to open the VBA editor.
2. Create a New Module:
   Go to Insert > Module to insert a new module.
3. Write the VBA Code to Calculate Skewness:

Here is the VBA code to calculate the skewness of a data set:

Function Skewness(DataRange As Range) As Double
    ' Variable declarations
    Dim n As Long
    Dim Mean As Double
    Dim StdDev As Double
    Dim SumCubedDiff As Double
    Dim i As Long
    Dim diff As Double   
    ' Number of data points in the range
    n = DataRange.Count   
    ' Calculate the mean of the data
    Mean = Application.WorksheetFunction.Average(DataRange)   
    ' Calculate the standard deviation of the data
    StdDev = Application.WorksheetFunction.StDev(DataRange)   
    ' Check if the standard deviation is zero (to avoid division by zero)
    If StdDev = 0 Then
        Skewness = 0
        Exit Function
    End If   
    ' Calculate the sum of cubed differences
    SumCubedDiff = 0
    For i = 1 To n
        diff = DataRange.Cells(i).Value - Mean
        SumCubedDiff = SumCubedDiff + diff ^ 3
    Next i   
    ' Calculate skewness using the formula
    Skewness = (n / ((n - 1) * (n - 2))) * (SumCubedDiff / (StdDev ^ 3))
End Function

Explanation of the Code:

1. Function Arguments:
   • DataRange: The range of data over which the skewness is calculated. This range is passed to the function when called in Excel.
2. Variable Declarations:
   • n: Number of data points in the given range.
   • Mean: The mean (average) of the data.
   • StdDev: The standard deviation of the data.
   • SumCubedDiff: The sum of the cubed differences between each value and the mean.
   • i: A counter for looping through the data.
3. Calculating the Mean (Mean) and Standard Deviation (StdDev):
   • Application.WorksheetFunction.Average(DataRange) is used to calculate the mean, and Application.WorksheetFunction.StDev(DataRange) is used to calculate the standard deviation of the data.
4. Checking if Standard Deviation is Zero:
   • If the standard deviation is zero (i.e., all the data points are the same), the function will return a skewness of zero to avoid division by zero.
5. Calculating the Sum of Cubed Differences:
   • For each value in the range, the difference between the value and the mean is cubed and added to SumCubedDiff.
6. Calculating the Skewness:
   • The skewness is calculated using the formula: Skewness=n(n−1)(n−2)×(∑i=1n(Xi−mean)3std dev3)\text{Skewness} = \frac{n}{(n – 1)(n – 2)} \times \left( \frac{\sum_{i=1}^{n}(X_i – \text{mean})^3}{\text{std dev}^3} \right)Skewness=(n−1)(n−2)n​×(std dev3∑i=1n​(Xi​−mean)3​) Where nnn is the number of data points, XiX_iXi​ represents each data value, and std dev is the standard deviation of the data.

How to Use the Function in Excel:

1. After writing the code in the VBA module, you can use this function in any Excel cell like any other built-in Excel function.
2. For example, if your data is in the range A1:A10, you can enter the following formula in any cell:

=Skewness(A1:A10)

This formula will return the skewness of the data in the range A1:A10.

Example:

• If you have the following data in cells A1:A10:

1
2
3
4
5
6
7
8
9
10

• Using the function =Skewness(A1:A10), you will get a result close to 0, indicating that the data is relatively symmetric.

Remarks:

• If the data set has significant skewness, you will get a higher positive or negative value depending on whether the skew is to the right (positive skew) or to the left (negative skew).