Include Top

Home | Login | View Cart | Live Help is Live Help

Included Menu

How Do I Perform a One Sample Sign Test & One Sample Wilcoxon Signed Rank Test in Excel Using SigmaXL?

One Sample Sign Test

The sign test is the simplest of the nonparametric tests, and is similar to testing if a two-sided coin is fair. Count the number of positive values (larger than hypothesized median), the number of negative values (smaller than the hypothesized median), and test whether there are significantly more positives (or negatives) than expected. The One Sample Sign Test is a nonparametric equivalent to the parametric One Sample t-Test.

Historically, our Median customer satisfaction score has been 3.5. We would like to see if this has changed, with the results grouped by customer type (H0: Median=3.5, Ha: Median≠3.5, α = 0.05).
  1. Open Customer Data.xls, select Sheet 1 tab. Click SigmaXL > Statistical Tools > Nonparametric Tests>1 Sample Sign. If necessary, check Use Entire Data Table, click Next.
  2. Ensure that Stacked Column Format is selected. Select Overall Satisfaction, click Numeric Data Variable (Y) >>; select Customer Type, click Optional Group Category (X) >>.
  3. Enter 3.5 for the Null Hypothesis H0: Median value. Set Ha as Not Equal To.

  4. 1 Sample Sign Test

  5. Click OK. Results:

  6. 1 Sample Sign Test

    Note the p-values. Customer Type 2 shows a significant change (increase) in Satisfaction Median (p-value < .05), whereas Customer Types 1 and 3 show no change (p-value ≥ .05). While the p-values are not the same as those given by the 1 sample t-Test, the conclusions do match.

One Sample Wilcoxon Signed Rank Test

The Wilcoxon Signed Rank test is a more powerful nonparametric test than the Sign Test, but it adds an assumption that the distribution of values is symmetric around the median. An example of a symmetric distribution is the uniform distribution. Symmetry can be observed with a histogram, or by checking to see if the median and mean are approximately equal. The One Sample Wilcoxon Test is a nonparametric equivalent to the parametric One Sample t-Test.

Historically, our Median customer satisfaction score has been 3.5. We would like to see if this has changed, with the results grouped by customer type (H0: Median=3.5, Ha: Median≠3.5, α = 0.05).

 
  1. Open Customer Data.xls, select Sheet 1 tab (or press F4 to activate last worksheet). Click SigmaXL > Statistical Tools > Nonparametric Tests > 1 Sample Wilcoxon. If necessary, check Use Entire Data Table, click Next.
  2. Ensure that Stacked Column Format is selected. Select Overall Satisfaction, click Numeric Data Variable (Y) >>; select Customer Type, click Optional Group Category (X) >>.
  3. Enter 3.5 for the Null Hypothesis H0:Median value. Keep Ha as Not Equal To.

  4. 1 Sample Wilcoxon

  5. Click OK. Results: 

    1 Sample Wilcoxon Test

    Note the p-values. Customer Type 2 shows a significant change (increase) in Satisfaction Median (p-value < .05), whereas Customer Types 1 and 3 show no change (p-value ≥ .05). Although the p-values are not identical to the sign test and t-Test, the conclusions match. (Note, in the case of Customer Type 2, the Sign Test is preferred since the data is not symmetrical but skewed).
Click here to download a Free 30-Day Trial.