Saturday, May 31, 2014

Wilcoxon Signed-Rank Test in 8 Steps As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013

This is one of the following six articles on 1-Sample t-Tests in Excel

1-Sample t-Test in 4 Steps in Excel 2010 and Excel 2013

Excel Normality Testing For the 1-Sample t-Test in Excel 2010 and Excel 2013

1-Sample t-Test – Effect Size in Excel 2010 and Excel 2013

1-Sample t-Test Power With G*Power Utility

Wilcoxon Signed-Rank Test in 8 Steps As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013

Sign Test As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013

 

Overview of 1-Sample t-

Test Nonparametric

Alternatives in Excel

There are two nonparametric tests that can be substituted for the one-sample t-Test when normality of the sample or population cannot be verified and sample size is small. These two tests are the Wilcoxon One-Sample, Sign-Rank Test and the Sign Test. The one-sample t-test is used to evaluate whether a population from which samples are drawn has the same mean as a known value. The nonparametric tests evaluate whether the sample have the same median as a known value.

The Sign Test is significantly less powerful alternative to the Wilcoxon One-Sample Signed-Rank test, but does not assume that the differences between the samples and the known value is symmetrical about a median, as does the Wilcoxon One-Sample Signed-Rank test when used as a nonparametric alternative to the one-sample t-test. The Sign Test is non-directional and can be substituted only for a two-tailed test but not for a one-tailed test.

The Wilcoxon test is based upon the sum of rankings of values while the Sign Test is based upon the sum of positive versus negative values.

The Wilcoxon One-Sample Signed Rank is much more powerful (able to detect a difference) than the Sign Test but has a required assumption that sample data are distributed about a median is a relatively symmetric fashion. The Sign Test does not have this assumption.

 

Wilcoxon One-Sample, Signed-Rank

Test in Excel

The Wilcoxon One-Sample, Signed-Rank Test is an alternative to the one-sample t-Test when sample size is small (n < 30) and normality cannot be verified for the sample data or the population from which the sample was taken.

The Wilcoxon One-Sample, Signed-Rank Test calculates the difference between each data point in the sample and the Constant from the t-Test’s Null Hypothesis (186,000 in this case). The absolute values of each difference and ranked and then assigned the sign (positive or negative) that the difference originally had. These signed ranks are summed up to create the Test Statistic W.

Test Statistic W will be approximately normally distributed if the required assumptions are met for this test. The Test Statistic’s z Score can then be calculated and compared with the Critical z value. The decision whether or not to reject the test’s Null Hypothesis is made based on the results of this comparison.

The Null Hypothesis for this test states that the median of the difference population equals a Constant. This is somewhat similar to the Null Hypothesis of the one-sample t-Test which states that the mean of a population equals a Constant.

The Wilcoxon One-Sample, Signed-Rank Test is performed on this data by implementing the following steps:

 

Step 1) Calculate the Difference Between Each Sample Data Point and the Constant to Which the Sample Is Being Compared.

The original Null Hypothesis from the one-sample t-Test stated that the mean monthly retails sales for the stores in a single region is equal to the nation average which is 186,000. The Null Hypothesis for this t-Test was as follows:

H0: x_bar = Constant =186,000

A difference sample consisting of the differences between each sample data point and the Constant (186,000) is created as follows:

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing
(Click On Image and See a larger Version)

 

Step 2) Create the Null and Alternative Hypotheses.

The one-sample t-Test attempts to determine whether the mean monthly retails sales for the stores in a single region is equal to the nation average which is 186,000.

The Wilcoxon One-Sample, Signed-Rank Test attempts to determine whether the median monthly retails sales for the stores in a single region is equal to 186,000.

If the median monthly retail sales for the region’s stores equals 186,000, then the median of the difference will equal zero. The Null Hypothesis is based on this and is stated as follows:

H0: Median_Difference = Constant = 0

The Alternative Hypothesis is non-directional because the test’s overall purpose is to determine only whether or not the regional mean monthly retail sales equals the national average of 186,000. The Alternative Hypothesis for this Wilcoxon One-Sample, Signed-Rank Test will therefore be stated as follows:

H1: Median_Difference ≠ Constant = 0

H1: Median_Difference ≠ 0

 

Step 3) Evaluate Whether the Test’s Required Conditions Have Been Met

The Wilcoxon One-Sample, Signed-Rank Test has the following requirements:

a) Data are ratio or interval but not categorical (nominal or ordinal). This is the case here.

b) Sample size is at least 10.

c) Data of the Difference sample are distributed about a median with reasonable symmetry. Test Statistic W will not be normally distributed unless this assumption is met.

The following Excel-generated histogram shows that the difference data are distributed symmetrically about their median of 14,000:

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing
(Click On Image and See a larger Version)

This histogram and the sample’s median were generated in Excel as follows:

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing (Click On Image and See a larger Version)

 

Step 4 – Record the Sign of Each Difference

Place a “+1” and “-1” next to each non-zero difference. This can be automatically generated with an If-Then-Else statement as follows:

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing
(Click On Image and See a larger Version)

Placing a plus sign (+) next to a number automatically requires a custom number format available from the Format Cell dialogue box. One custom format that will work is the following: “+”#:”-“# . This is demonstrated in following Excel screen shot:

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing (Click On Image and See a larger Version)

 

Step 5 – Sort the Absolute Values of the Differences While Retaining the Sign Associated With Each Difference

Sort both columns based upon column of difference absolute values.

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing
(Click On Image and See a larger Version)

 

Step 6 –Rank the Absolute Values, Attach the Signs, and Sum up the Signed Ranks to Create Test Statistic W.

The absolute values are ranked in ascending order starting with a rank of 1. Absolute values that are tied area assigned the average rank of the tied values. For example, the first four absolute values are 6000. Each of these four absolute values would be assigned a rank of 2.5, which is equal to the average rank of all four, i.e., (1 + 2 + 3 + 4) / 4 = 2.5.

Test Statistic W is equal to the sum of all signed ranks.

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing
(Click On Image and See a larger Version)

 

Step 7 – Calculate the z Score of W

The distribution of Test Statistic W can be approximated by the normal distribution if all of the required assumptions for this test are met. The difference data consists of more than 10 points of ratio data that are reasonably symmetrically distributed about their median. The assumptions are therefore met for this Wilcoxon One-Sample, Signed-Rank Test.

The standard deviation of W, σW, is calculated as follows:

σW = SQRT[ n(n + 1)(2n + 1)/6 ] = 53.57

z Score = ( W – Constant – 0.5) / σW

z Score = ( 110 – 0 – 0.5) / 53.57 = 2.04

The constant is the Constant from the Null Hypothesis for this test, which is the following:

H0: Median_Difference = Constant = 0

The z Score must include a 0.5 correction for continuity because W assumes whole integer values (except in the event of a tie of ranks).

 

Step 8 – Reject or Fail to Reject the Null Hypothesis Based Upon a Comparison Between the z Score and the Critical z Value

Given that α = 0.05 and this is a two-tailed test, the Critical z Value is calculated as follows:

Z Criticalα=0.05,Two-Tailed = ±NORM.S.INV(1 – α/2) = ±NORM.S.INV(0.975)

Z Criticalα=0.05,Two-Tailed = ±1.9599

The Null Hypothesis is rejected if the z Score is further from the standardized mean of zero than the Critical z Values. This is the case here since the z Score (2.04) is further from the standardized mean of zero than the Critical z Values (±1.9599). These results from the Wilcoxon Signed-Rank Test are shown in the following Excel-generated graph:

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing (Click On Image and See a larger Version)

Rejection of the Null Hypothesis for this test can be interpreted to state that there is at least 95 percent certainty that the median of the difference sample does not equal zero. This would mean that there is 95 percent certainty that the median monthly sales of the retail stores in the region does not equal the national average of 186,000.

The results of this Wilcoxon One-Sample, Signed-Rank Test were very similar to the results of the original one-sample t-Test in which the Null Hypothesis was rejected because the t value (2.105) was further from the standardized mean of zero than the Critical t Value (2.093). The results of this t-Test indicate 95 percent certainty that the mean monthly sales of the retail stores in the region does not equal the national average of 186,000.

The results of the t-Test are shown in the following Excel-generated graph of this non-standardized t Distribution:

nonparametric testing, wilcoxon, excel, excel 2010, excel 2013, statistics, t test, t-test,nonparametric,normality testing (Click On Image and See a larger Version)

The Wilcoxon One-Sample Signed-Rank Test detects that the median difference between the region’s retail store monthly sales and the national average is significant at an alpha level of 0.05.

The one-sample t- Test detects that the mean difference between the region’s retail store monthly sales and the national average is significant at an alpha level of 0.05.

 

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)