We present the original approach to the performing the Shapiro-Wilk Test. This approach is limited to samples between 3 and 50 elements. By clicking here you can also review a revised approach using the algorithm of J. P. Royston which can handle samples with up to 5,000 (or even more).

The basic approach used in the **Shapiro-Wilk** (SW) test for normality is as follows:

- Rearrange the data in ascending order so that
*x*≤ … ≤_{1}*x*._{n} - Calculate
*SS*as follows:

- If
*n*is even, let*m*=*n*/2, while if*n*is odd let*m*= (*n*–1)/2 - Calculate
*b*as follows, taking the*a*weights from the Table 1 (based on the value of_{i}*n*) in the Shapiro-Wilk Tables. Note that if*n*is odd, the median data value is not used in the calculation of*b*.

- Calculate the test statistic
*W*=*b*⁄^{2}*SS* - Find the value in the Table 2 of the Shapiro-Wilk Tables (for a given value of
*n*) that is closest to*W*, interpolating if necessary. This is the p-value for the test.

For example, suppose *W* = .975 and *n* = 10. Based on Table 2 of the Shapiro-Wilk Tables the p-value for the test is somewhere between .90 (*W* = .972) and .95 (*W* = .978).

**Example 1**: A random sample of 12 people is taken from a large population. The ages of the people in the sample are given in column A of the worksheet in Figure 1. Is this data normally distributed?

**Figure 1 – Shapiro-Wilk test for Example 1**

We begin by sorting the data in column A using** Data > Sort & Filter|Sort** or the **QSORT** supplemental function, putting the results in column B. We next look up the coefficient values for *n* = 12 (the sample size) in Table 1 of the Shapiro-Wilk Tables, putting these values in column E.

Corresponding to each of these 6 coefficients a_{1},…,a_{6}, we calculate the values *x*_{12} – *x*_{1}, …, *x*_{7} – *x*_{6}, where *x _{i}* is the

*i*th data element in sorted order. E.g. since

*x*

_{1}= 35 and

*x*

_{12}= 86, we place the difference 86 – 35 = 51 in cell H5 (the same row as the cell containing a

_{1}). Column I contains the product of the coefficients and difference values. E.g. cell I5 contains the formula =E5*H5. The sum of these values is

*b*= 44.1641, which is found in cell I11 (and again in cell E14).

We next calculate *SS* as DEVSQ(B4:B15) = 2008.667. Thus *W* = *b ^{2}* ⁄

*SS*= 44.1641^2/2008.667 = .971026. We now look for .971026 when

*n*= 12 in Table 2 of the Shapiro-Wilk Tables and find that the p-value lies between .50 and .90. The

*W*value for .5 is .943 and the

*W*value for .9 is .973.

Interpolating .971026 between these value (using linear interpolation), we arrive at p-value = .873681. Since p-value = .87 > .05 = *α*, we retain the null hypothesis that the data are normally distributed.

**Example 2**: Using the SW test, determine whether the data in Example 1 of Graphical Tests for Normality and Symmetry are normally distributed.

**Figure 2 – Shapiro-Wilk test for Example 2**

As we can see from the analysis in Figure 2, p-value = .0419 < .05 = *α*, and so we reject the null hypothesis and conclude with 95% confidence that that the data are not normally distributed, which is quite different from the results using the KS test that we found in Example 2 of Kolmogorov-Smironov Test.

**Real Statistics Function**: The Real Statistics Resource Pack contains the following supplemental functions where R1 consists only of numeric data without headings:

**SHAPIRO**(R1, FALSE) = the Shapiro-Wilk test statistic *W* for the data in the range R1

**SWTEST**(R1, FALSE, *h*) = p-value of the Shapiro-Wilk test on the data in R1

**SWCoeff**(*n, j*, FALSE) = the *j*th coefficient for samples of size *n*

**SWCoeff**(R1, C1, FALSE) = the coefficient corresponding to cell C1 within sorted range R1

**SWPROB**(*n, W, *FALSE, *h*) = p-value of the Shapiro-Wilk test for a sample of size *n* for test statistic *W*

The functions SHAPIRO and SWTEST ignore all empty and non-numeric cells. The range R1 in SWCoeff(R1, C1, FALSE) should not contain any empty or non-numeric cells.

When performing the table lookup, the default is to use harmonic interpolation (*h* = TRUE). To use linear interpolation, set *h* to FALSE. See Interpolation for details.

For example, for Example 1 of Chi-square Test for Normality, we have SHAPIRO(A4:A15, FALSE) = .874 and SWTEST(A4:A15, FALSE, FALSE) = SWPROB(15,.874,FALSE,FALSE) = .0419 (referring to the worksheet in Figure 2 of Chi-square Test for Normality).

It is important to note that SHAPIRO(R1, TRUE), SWTEST(R1, TRUE), SWCoeff(*n, j*, TRUE), SWCoeff(R1, C1, TRUE) and SWPROB(*n, W, *TRUE) refer to the results using the Royston algorithm, as described in Shapiro-Wilk Expanded Test.

For compatibility with the Royston version of SWCoeff, when *j* ≤ *n*/2 then SWCoeff(*n, j*, False) = the negative of the value of the *j*th coefficient for samples of size *n* found in the Shapiro-Wilk Tables. When *j* = (*n*+1)/2, SWCoeff(*n, j*, FALSE) = 0 and when *j* > (*n*+1)/2, SWCoeff(*n, j*, FALSE) = -SWCoeff(*n*, *n–j*+1, FALSE).

Can I get the idea how to do the below :

Interpolating .971026 between these value (using linear interpolation)

Salman,

Please look at the following webpage:

Interpolation

Charles

Thank you very much for your excellent explanation and excel workbooks!

Dear Dr. Zaionts,

Thank you very much for your great tool.

I recently downloaded the latest Release (3.5.3) for the Mac version of Excel. In this one, the SWTEST function apparently gives a #VALUE! output with range size greater than 3. Is there a way to fix this? If not, where may I find and download a previous Release?

I thank you in advance for your attention.

Stefano

Dear Stefano,

I don’t think I made any changes to this function since the previous release. In any case, if you send me an Excel file with your data and function results I will try to figure out what is causing this. You can send the file to my email address, which you can find at Contact Us.

Charles

These are the W values I have got from a raw data of response times for n=18.

1,012157199 0,996684879 0,824085184 0,960953212 1,006536182

Most of these values of W are out of range from the (n/p)table. Does that mean I have some calculation errors? If not, then how do I interpret the data?

Pri,

Since W = 0,824085184 is less than the smallest value in the table for n = 18 and p = .01, it just means that p < .01 Actually, I calculate that the p-value = 0,003394 using the Royston approximation that is described elsewhere on the website. This means that your data is likely not normally distributed. Similarly, W = 0,9609532124 is greater than the largest value in the table for n = 18 and p = .99. This just means that the p-value is larger than .99. This means that your data is probably normally distributed. The value W = 0,9609532124 is not in the table, but you know that it occurs between the values p = .5 and p = .9. You can interpolate (as described on the referenced webpage) to come up with an approximate p-value of .59, but in any case the value is much higher than .05, and so the random sample probably comes from a population that is normally distributed. Now the cases where W > 1 are causes for concern since I believe the value for W can’t exceed 1. There is a good chance that you have made a calculation error.

Charles

Hello Dr. Zaiontz,

I really appreciate your examples and web page on real statistics using excel. I tried Shapiro-Wilk test on my data (n=10),however, I have got many variables, so I am testing the normality for each of the variables. So for one of the data, I got W=0.5679 and I referred the Wilk Test sheet, I could not get the P-values. Could something be wrong with my data itself? Or is there an extended table? Please help.

Thanks

Soira,

Since the value for W is less than the critical value at p = .01, you can conclude from the table that p-value is less than .01

Alternatively, you can use the Royston version of Shapiro-Wilk test. See the webpage

http://www.real-statistics.com/tests-normality-and-symmetry/statistical-tests-normality-symmetry/shapiro-wilk-expanded-test/

In this case, you can calculate the p-value as SWPROB(10,.5679) = 2.3E-05.

Charles

thank you Charles

Hi Charles,

Thanks for the information on the website. It is really useful. However when I applied the Shapiro test to my data it gave me an error. This error does not happen for larger samples (mine is 4) like 5 or 6. Is there a limitation to the excel function that does not allow small samples to be tested with this function?

Thanks

It looks like it should work for samples of size at least 5.

Charles

Hi Charles,

I tried again the Shapiro test on my data and surprisingly it work for a sample size 3 but still not 4… Just thought I should let you know.

Thanks for the website

Joana

Joana,

Thanks for finding this bug.

The original test for sample size of 4 does work (setting the second argument in the SHAPIRO or SWTEST function to False). The Royston version of the test has the bug when the sample size is 4.

I will provide a fix in the next release.

Thanks again for helping me improve the accuracy of the software.

Charles

I have gone through your explanation and I found very rewarding and useful. However, will appreciate an example for sample that is odd and not even like your two examples.

Regards

Tony,

The sample in the second example has an odd number of elements. The middle element is not used.

Charles

I want to know what happens if data fails the SW test?

Is there any way out?

Jerry,

If data is not normally distributed, then for tests that assume normality you can

1. use a nonparametric test that doesn’t require normality

2. transform the data so that the resulting data is sufficiently normal

In addition, some tests that require normality (e.g. the t test) are sufficiently robust that as long as the data is symmetric the test will usually be ok (although even in these cases, the Mann-Whitney nonparametric test should give similar results).

Charles

Thank you Dr. I am learning a lot from your useful website. When I tried Real Stat for Shapir0-Wilk test for the two data given in the two examples, I get different W and p values from those given in the examples, as follows:

W=b^2/SS 0.971025924 W 0.971122526

0.5 0.943 p-value 0.922200674

0.9 0.973 alpha 0.05

p-value 0.873679 normal yes

W=b^2/SS 0.873965213 W 0.874012

0.02 0.855 p-value 0.03866

0.05 0.881 alpha 0.05

p value 0.041882692 normal no

Could you please explain why the difference? Have I committed any mistake in the calculations?

I don’t know why you get different results. If you send me a spreadsheet with your calculations I will try to understand why there is a difference.

Charles

how is analysis durbin watson test using excel or spss software. Please tell step by step sending my email id

Sundar,

The following webpage has the description of the Durbin-Watson test: http://www.real-statistics.com/multiple-regression/autocorrelation/

I am updating the Durbin-Watson webpage, and so you will find additional information in a day or two.

Charles

The example 1 is well explained. However, my linearly interpolated value of Wc (p-value) comes out to be 0.89999 instead of 0.876681. The interpolation coeffcient is 0.075 per probability of .1, between 0.5 and 0.9. Hence for approx. diff. of 0.002 in W (0,973-0,971), p value = 0.89999. Pl. correct me if wrong.

The calculation I used was to interpolate between the table values .973 – .943 = .03 and .9 – .5 = .4. So the answer is .9 – .002/.03 * .4 = .873.

In any case, the value is far more than .05. Note that you can get a more exact value (which doesn’t require interposlation) by using the Royston approximation, as described on the webpage http://www.real-statistics.com/tests-normality-and-symmetry/statistical-tests-normality-symmetry/shapiro-wilk-expanded-test/

Charles

Hi Charles,

I found this webpage is very useful and it guided me so well. Thank you very much. But I would like to know something..How will you rank this test with respect to A-D and K-S test?

Shreya

Hi Shreya,

I would use SW over KS. I have not used AD and so don’t have an opinion.

Charles

Hi Charles,

Thanks a lot for this web page!!

You said that the function SWTEST ignore all empty and non-numeric cells. Sure? Because if I add empty cells at the end of the range R1, the p-value is different.

Also, what is the difference between the original Shapiro-Wilk test and the Royston algorithm, and when do you one or the other? (Meaning that I don’t know if in the SWTEST I have to write “FALSE” or “TRUE”.

Thank you very much!

Julien

Hi Julien,

I just retested the SWTEST and SHAPIRO functions by adding empty and non-numeric cells at the beginning, end and in the middle of the range. The results are all the same. Which version of Excel are you using?

If the values you are looking for are found in the table then you might as well use the original algorithm (although the results using the Royston algorithm are quite similar). Otherwise you should use the Royston algorithm. I tend to use the Royston algorithm always since in that case I don’t need to make any decisions.

Charles

I use Microsoft Excel for Mac 2011 in English

Julien,

Which version of the Real Statistics Resource Pack do you have? You can find this out by entering =VER() in any cell. If it is not one of the latest releases (Release 2.15) then this could account for the problem.

Charles

Hi Charles,

It’s the release 2.10.1

Julien,

This is the latest version of the software for the Mac, but it doesn’t contain some of the features that I have added for Windows. In particular WTEST only returns the one-tailed version of the test. You just need to double the value to get the p-value for the two-tailed test. I hope to get a new version for the Mac out soon (as soon as I can get a Mac computer to test it on).

Charles

Julien,

Now I understand the problem. I have not yet updated the Mac version of the software with the latest features. This is why some of the arguments don’t work and why some of the functions don’t handle missing data the same way. My problem is that I don’t have a Mac myself and need to borrow one to test and update the software.

Charles

Sir

Sorry! Forgave me. I found the explanation at the end of this blog.

Colin

Sir

SWCoeff(n, j, False) gives us a negative number. LOL. You may change it in the next version of Real Statistics, or we can just multiply -1 to fix the problem. I am using the latest Real Statistics add-in.

Colin

Colin,

I set SWCoeff(n,j,False) to be negative for consistency with the Royston version, namely SWCoeff(n,j,True). For j < (n+1)/2 SWCoeff(n,j,False) is negative, while for j > (n+1)/2 it is positive.

Charles

Sir

SWCoeff(n, j, False) gives us a negative number. LOL. You may change it in the next version of Real Statistics, or we can just multiply -1 to fix the problem.

Colin

Hi guys,

I understand t tests can only be performed on two groups of samples with uniform distribution. What can i use if one group is uniform while the other isn’t?

Olu,

The t test requires normally distributed samples (not uniform), but it is pretty forgiving and will usually work well with reasonably symmetric data. If one group is not acceptable then you should use a non-parametric test. E.g. if the two samples are independent then you can use Mann-Whitney.

Charles

I believe there are two minor errors in the two paragraphs following Figure 1. First, about halfway through the paragraph following Figure 1, the text states “. . . we calculate the values x12 – x11, . . . ” I believe x11 is a typo and the correct text should read “. . the values x12 – x1, . . . ”

Second, I believe there is an error in the cell range given in first sentence, of second paragraph, following Figure 1. Specifically, the text currently reads “We next calculate SS as DEVSQ(B5:B16) = . . . ” The correct cell range should be ” . . . as DEVSQ(B4:B15) = . . . ”

Thanks for the great web page! Its has been very helpful to me.

Richard,

You are correct on both counts. Thanks for finding these errors. I have now updated the webpage with your corrections. I am pleased to read that you found the webpage helpful. I hope that you will continue to use the site.

Charles

Table 2 gives “the probability that the data comes from a normal distribution”??

Could it be that what Table 2 gives is the p-value? It is commonplace to be incessantly explaining to people who don’t understand math that the p-value is NOT the same as the probability of the null hypothesis. This is called “the prosecutor’s fallacy”; Google that term!

Given the null hypothesis of the defendant’s innocence, the probability of a test statistic at least as extreme as the one observed is 1 in 1000. So the prosecutor’s fallacy is the mistaken idea that that means there’s a 1 in 1000 chance of innocence. Consider: Suppose the culprit is one member of a population of 500,000. If something happens 1 time out of 1000 to each person, then there are 500 people in that population for whom the p-value is at least as extreme as what was observed. That means the defendant is one of the 500 people who could be the culprit.

Michael,

You are absolutely correct. I have now made the corrections in the appropriate places on the webpage. Thanks for identifying the error.

Charles

I’ve done a few tests using your RS resources pack this and I’m afraid to say I’m detecting a bug of sorts, SWTEST(R1) doesn’t always return the same as SWPROB(n,W) – the latter giving the correct result. I’m not sure if you actually checked this for different values when testing the algorithm? Really not trying to be ungrateful, it’s a brilliant add-in, but I just noticed that in “SHAPIRO(A4:A15) = .874 and SWTEST(A4:A15) = SWPROB(15,.874) = .0419” the range A4:A15 wouldn’t provide a value of 15 for n unless I’m mistaken?

Again, thanks for all your work on the site/excel package!

my example data, starting with a label in A1 :

—————————-

sample1: 2.8078385

sample2: 6.22198918

sample3: 100

sample4: 58.555133

sample5: 9.0669786

sample6: 2.2813688

sample7: 0.6727113

W: =SHAPIRO(B2:B8)=0.7118325

incorrect p-value: =SWTEST(B2:B8)=0.782674

(from what I can see) correct p-value: =SWPROB(7,B32)=0.005

From what I can see in the p table for n=7, W=0.7118… lies between p=0 and p=0.01, i.e. p=0.005 is feasible but 0.782674 ain’t.

I think I’m just going to use SWPROB for now!

Dear Louis,

Thanks for finding this error. There was an error in my implementation of the Royston algorithm for performing the Shapiro-Wilk test for normality for samples between 4 and 11 elements. I believe that I have now fixed this in the latest release of the Real Statistics Resource Pack which I just put on the website. If you download and install this version (release 1.7.3), you should find that SWTEST(B2:B8) = .004981. The value using the original SW algorithm is calculated by SWTEST(B2:B8,False) = .005.

Please note that the following Shapiro-Wilk functions have both Royston and original SW versions: SHAPIRO(R1,b), SWTEST(R1,b), SWCoeff(n,j,b). If b is True or is omitted then the Royston algorithm is used. If b is False then the original SW algorithm is used. The SWCoeff(n,j,False) version is new. I just added it to the software. It gives the a coefficients found in the SW Coefficient Table on webpage http://www.real-statistics.com/statistics-tables/shapiro-wilk-table/. I will be updating the website shortly to explain this new function.

Caution should be used when employing either version of the SW Test for very small samples (less than 15 or 20) since the results are not completely accurate.

Thanks again for identifying the error and sorry for any inconvenience it caused.

Charles

Hello Louis,

For Example 2 on the webpage http://www.real-statistics.com/tests-normality-and-symmetry/statistical-tests-normality-symmetry/shapiro-wilk-test/, we have the following results SHAPIRO(A4:A18, False) = .874 and SWTEST(A4:A18, False) = SWPROB(15,.874) = .0419. The range used is A4:A18 and not A4:A15, and so a sample size of 15 is correct.

Please note that to use the original Shapiro-Wilk algorithm you need to specify False as the second parameter. The website was not clear about this. I have now revised the website to make this clearer. If you leave out the second parameter, you will get SHAPIRO(A4:A18) = .874 and SWTEST(A4:A18) = 0387, which are the results using the Royston algorithm. Obviously in this case there wasn’t a great difference.

Charles

I do not understand the answer to Touseef. The CLT says that the sampling distribution of the mean is approx. Normal for large random sample. It says nothing about the distribution of the sample values. If you are sampling from a non-Normal population, the sample distribution will not be Normal no matter how large the sample is, right?

Dmitry,

The CLT says that the sample distribution will indeed be approximately normal for large enough samples even if the population distribution is not normal.

Charles

Hi,

Thank you so much for posting this! It was really helpful and easy to understand. My only question is in question one: how do you interpolate the W values? Is there an equation you used?

Hi Molly,

Currently, I don’t do anything particularly sophisticated. I simply perform a linear interpolation for the W values. Even if the value is not precise, it is much higher than the p-value of .05, and so we cannot reject that the data is normally distributed.

Because so many people have asked for the Shapiro-Wik test for samples larger than 50, yesterday I added a new version of the SW test which doesn’t use interpolation and supports sample sizes of at least 5,000. This is available in the current version of the Real Statistics Resource Pack (R1.7.1).

Charles

Dear Admin,

Thanks for the information you provided about SW test. As i am not having the statistic background so i have a small question as when the random samples increase up to n=500 suppose, how would we get the weights “a” as the table only provides “n” up to 50 only?

I would appriciate if you could provide the answer.

Hi Touseef,

I got the “a” weights from the original paper of Shapiro and Wilk in 1965. In that paper they only provided weights up to n = 50. If it is truly a random sample, then by the Central Limit Theorem for large values of n (usually n > 50 is more than sufficient), the sample will be approximately normally distributed and so doesn’t need to be tested for normality (again provided the sample is truly randomly selected).

Charles

Your information is really helpfull, thank you for that!

I have a question, pretty elemental, but I need an answer. In example number 1 I’m following you until the interpolation, how did you do that? I mean, table number 2 doesn’t follow a lineal function, so i tried to put it into a logaritmic function and it didn’t work.

I would apreciate your answer, I really need it.

Thank you.

Hi Javiera,

It is a good question. I simply used a linear interpolation. As you pointed out, the table doesn’t represent a linear function, but the results will usually be good enough. I will probably use a more sophisticated approach in the future, but for now I wanted to keep it simple.

Charles