Table of Contents
The Kolmogorov-Smirnov Test is a non-parametric test used to compare two distributions. It can be used to compare the similarity of two data sets in Excel by calculating the maximum difference between the cumulative distributions of the two sets. This calculation is then compared to a critical value from a look-up table to determine whether the two data sets are significantly different. The process involves entering the data into Excel, creating the cumulative distributions for each data set, and then using the K-S Test function to calculate the maximum difference between the two distributions.
The Kolmogorov-Smirnov test is used to determine whether or not or not a sample is .
This test is widely used because many statistical tests and procedures make the 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:
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(F2–D2)
- 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.
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.
The following tutorials explain how to perform other common statistical tests in Excel: