The following version of the Shapiro-Wilk Test handles samples between 12 and 5,000 elements, although samples of at least 20 elements are recommended. We also show how to handle samples with more than 5,000 elements.
Assuming that the sample has n elements, perform the following steps:
1. Sort the data in ascending order x1 ≤ … ≤ xn
2. Define the values m1, …, mn by
mi = NORMSINV((i − .375)/(n + .25))
3. Let M = [mi] be the n × 1 column vector whose elements are these mi and let
If M is represented by the n × 1 range R1 in Excel, then =SUMSQ(R1) calculates the value m.
4. Set u = 1/ and define the coefficients a1, …, an where
ai = mi / for 2 < i < n − 1
a2 = −an-1 a1 = −an
It turns out that ai = −an-i+1 for all i and that
where A = [ai] is the n × 1 column vector whose elements are the ai.
5. The W statistic is now defined by
Because of the above properties of the coefficients a1, …, an it turns out that W = the square of the correlation coefficient between a1, …, an and x1, …, xn. Thus the values of W are always between 0 and 1.
It also turns out that for values of n between 12 and 5,000 the statistic ln (1−W) is approximately normally distributed with the following mean and standard deviation:
6. Thus we can test the statistic
using the standard normal distribution. If the p-value ≤ α then we reject the null hypothesis that the original data is normally distributed.
Example 1: Repeat Example 1 of Shapiro Wilk Original Test using the expanded test.
We carry out the calculations described above to get the results shown in Figure 1 (see Figure 2 for key formulas used). The W statistic is 0.971066. The p-value = .921649 > .05 = α shows that there are no grounds for rejecting the null hypothesis that the data is normally distributed.
Real Statistics Excel Functions: The Real Statistics Resource Pack contains the following supplemental functions where R1 consists of only numerical data without headings.
SHAPIRO(R1) = the Shapiro-Wilk test statistic W for the data in the range R1 using the expanded method
SWTEST(R1) = p-value of the Shapiro-Wilk test on the data in R1 using the expanded method
SWCoeff(n, j) = the jth coefficient for samples of size n
SWCoeff(R1, C1) = the coefficient corresponding to cell C1 within sorted range R1
SWPROB(n , W) = p-value for the Shapiro-Wilk test for a sample of size n and statistic W
Note that these functions can optionally take an additional argument b, i.e. SHAPIRO(R1, b), SWTEST(R1, b), SWCoeff(n, j, b) and SWCoeff(R1, C1, b) and SWPROB(n, W, b). When omitted this argument defaults to TRUE (i.e. the values for the expanded Shapiro-Wilk test as described above are used). If b is set to FALSE then the values for the original Shapiro-Wilk test are used instead.
Observation: The functions SHAPIRO and SWTEST ignore all empty and non-numeric cells. The range R1 in SWCoeff(R1, C1) should not contain any empty or non-numeric cells.
Example 2: Determine whether the data in range A3:E14 of Figure 3 is normally distributed using the Shapiro-Wilk’s test.
This time we use the supplemental functions described above to obtain the results shown in Figure 3. The value of W and the p-value are as indicated using the formulas indicated. Since p-value = 0.019314 < .05 = α, we reject the hypothesis that the data is normally distributed. Note that we don’t need to sort the data and the data does not have to be arranged in a column to use the formulas.
If for some reason we want to obtain the coefficients, we need to sort the data. This is done by highlighting the range G3:K14 and entering =QSORT(A3:E14) and simultaneously pressing Ctrl-Shft-Enter. The first coefficient is obtained by entering the formula =SWCoeff($G$3:$K$14,G3) in cell M3. If you highlight the range M3:Q14 and press Ctrl-R and Ctrl-D, all the coefficients will be displayed as shown in Figure 3.
Observation: If a sample larger than 5,000, you can randomly divide the larger sample into a number of approximately equal-sized smaller samples and then run the SW algorithm as described above on each sample to obtain the z score for each smaller sample. Suppose that there are k such samples with z scores of z1, …, zk. Recall that if range R1 contains sample i then zi = NORMSINV(SWTEST(R1)).
The average of the z-scores will be an approximation of the z value for the whole sample. The expected mean of z is the average of the means of the zi, namely 0 and the standard deviation of z should be the standard deviation of the zi divided by √k, namely 1/. Thus you should test z using the standard normal distribution.
Real Analysis Data Analysis Tool: The Descriptive Statistics and Normality supplemental data analysis tool in the Real Statistics Resource pack has a Shapiro-Wilk option. You can use this option to perform the Royston version of the SW Test, as described in Example 3.
Example 3: Determine which of the three samples displayed on the left side (range A3:C16) of Figure 4 is normally distributed.
Enter Ctrl-m and select the Descriptive Statistics and Normality tool from the dialog box that appears. The dialog box shown in Figure 5 now appears. Choose the Descriptive Statistics, Box Plot and Shapiro-Wilk options as shown.
Figure 5 – Dialog box for Descriptive Statistics and Normality
The resulting output is shown on the right side of Figure 4. As we can see, based on the Shapiro-Wilk test only Sample 2 shows a significant departure from normality (p-value = 0.044 < .05 = α).
This conclusion is supported by the fact that the kurtosis for Sample 2 is high (3.326) and the fact that the Box Plot for Sample 2 is not very symmetric.
Note that at present, the SW Test only produces the correct answer if there are no blank or non-numeric cells in the data.