Kurtosis Calculation

VBA Code: Kurtosis Calculation

Function Kurtosis(rng As Range) As Double
    Dim cell As Range
    Dim n As Long
    Dim sumX As Double, sumX2 As Double, sumX4 As Double
    Dim meanX As Double, stdDev As Double
    Dim result As Double
    ' Initialize variables
    n = rng.Cells.Count
    If n < 4 Then
        Kurtosis = CVErr(xlErrDiv0) ' Error if fewer than 4 values (kurtosis requires a sample size of at least 4)
        Exit Function
    End If   
    ' Calculate the mean
    For Each cell In rng
        sumX = sumX + cell.Value
    Next cell
    meanX = sumX / n
    ' Calculate variance and fourth-order moments
    For Each cell In rng
        sumX2 = sumX2 + (cell.Value - meanX) ^ 2
        sumX4 = sumX4 + (cell.Value - meanX) ^ 4
    Next cell
    ' Standard deviation
    stdDev = Sqr(sumX2 / n)
    ' Compute kurtosis using Fisher’s formula (adjusted for a sample)
    If stdDev <> 0 Then
        result = (sumX4 / n) / (stdDev ^ 4)
        Kurtosis = ((n * (n + 1)) / ((n - 1) * (n - 2) * (n - 3))) * result - (3 * (n - 1) ^ 2) / ((n - 2) * (n - 3))
    Else
        Kurtosis = CVErr(xlErrDiv0) ' Error if standard deviation is zero
    End If
End Function

Code Explanation

Sample size verification

• If the range contains fewer than 4 values, kurtosis cannot be properly calculated (risk of division by zero).
• Returns a #DIV/0! error.

Mean calculation

• Sums all values in the range and divides by n.

Variance and fourth-order moment calculation

• sumX2: Sum of squared deviations from the mean (raw variance).
• sumX4: Sum of deviations raised to the fourth power.

Standard deviation calculation

• The standard deviation is the square root of variance.

Application of adjusted kurtosis formula (excess kurtosis)

• Fisher’s formula for a finite sample is used:

• This formula adjusts sample kurtosis to avoid bias.

Error handling

• If the standard deviation is zero (all values are identical), kurtosis is undefined → returns #DIV/0! error.

How to use this function in Excel?
Enter values in a column (e.g., A1:A10).
In a cell, enter the formula:

=Kurtosis(A1:A10)

The cell will display the sample kurtosis.

This function is more accurate than Excel’s built-in KURT() as it uses an unbiased formula for a sample.