How to Perform a Kolmogorov-Smirnov Test in Excel

The Kolmogorov-Smirnov test is used to determine whether or not or not a sample is normally distributed.

This test is widely used because many statistical tests and procedures make the assumption that the data is normally distributed.

The following step-by-step example shows how to perform a Kolmogorov-Smirnov test on a sample dataset in Excel.

Step 1: Enter the Data

First, let’s enter the values for a dataset with a sample size of n = 20:

Step 2: Calculate Actual vs. Expected Values from Normal Distribution

Next, we’ll calculate the actual values vs. the expected values from the normal distribution:

Kolmogorov-Smirnov test in Excel

Here is the formula we used in various cells:

  • B2: =ROW() – 1
  • C2: =B2/COUNT($A$2:$A$21)
  • D2: =(B2-1)/COUNT($A$2:$A$21)
  • E2: =IF(C2<1, NORM.S.INV(C2),””)
  • F2: =NORM.DIST(A2, $J$1, $J$2, TRUE)
  • G2: =ABS(F2D2)
  • J1: =AVERAGE(A2:A21)
  • J2: =STDEV.S(A2:A21)
  • J4: =MAX(G2:G21)

Step 3: Interpret the Results

A Kolmogorov-Smirnov test uses the following null and alternative hypotheses:

  • H0: The data is normally distributed.
  • HA: The data is not normally distributed.

To determine if we should reject or fail to reject the null hypothesis we must refer to the Maximum value in the output, which turns out to be 0.10983.

This represents the maximum absolute difference between the actual values of our sample and the expected values from a normal distribution.

To determine if this maximum value is statistically significant, we must refer to a Kolmogorov-Smirnov Table of critical values and find the number equal to n = 20 and α = .05.

The critical value turns out to be 0.190.

Since our maximum value is not greater than this critical value, we fail to reject the null hypothesis.

This means we can assume that our sample data is normally distributed.

Additional Resources

The following tutorials explain how to perform other common statistical tests in Excel:

How to Perform a Correlation Test in Excel
How to Perform a Durbin-Watson Test in Excel
How to Perform a Jarque-Bera Test in Excel
How to Perform Levene’s Test in Excel

Leave a Reply

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