How to Perform a Sign Test in Excel (Step-by-Step)


A sign-test is a non-parametric test that is used to determine whether a population median is equal to some value.

The following step-by-step example shows how to perform a sign test in Excel.

Step 1: Enter the Data

Suppose a manufacturing plant claims to produce widgets that weigh 50 pounds. To test this, an inspector goes out to the plant and randomly measures the weight of 20 widgets.

He then enters the following weights for each widget:

We can perform a sign test to determine if the median weight is significantly different from 50 pounds.

Step 2: Calculate the Signs

Next, let’s calculate the signs of each widget using the following rules:

  • If the weight of a widget is less than 50, assign it a sign of -1
  • If the weight of a widget is equal to 50, assign it a sign of 0
  • If the weight of a widget is greater than 50, assign it a sign of 1

We’ll use the following formula in Excel to do so:

Step 3: Calculate the P-Value of the Test

Lastly, we’ll use the following formulas to calculate the total positive signs and negative signs and calculate the corresponding p-value of the sign test:

The sign test uses the following null and alternative hypotheses:

  • H0: Population median weight = 20 pounds
  • HA: Population median weight ≠ 20 pounds

Since the p-value is not less than .05, we fail to reject the null hypothesis. This means we don’t have sufficient evidence to say that the true median weight of widgets produced is different than 50 pounds.

Note: In this example, we multiplied the p-value by two since we performed a two-sided test. We also used the smaller of the negative and positive counts since we used a two-sided test.

Additional Resources

The following tutorials explain how to perform other common statistical tests in Excel:

How to Conduct a One Sample t-Test in Excel
How to Conduct a Two Sample t-Test in Excel
How to Conduct a Paired Samples t-Test in Excel
How to Perform a Normality Test in Excel

Featured Posts

4 Replies to “How to Perform a Sign Test in Excel (Step-by-Step)”

  1. Hey Zach, are your hypotheses meant to state 50 pounds instead of 20 pounds?
    So…
    H0: Population median weight = 50 pounds
    HA: Population median weight ≠ 50 pounds

  2. Shouldn’t N be “=COUNT(B2:B21)” , rather than a SUM function of the signs? Why are you excluding the ties from the data set? They are observations too.

  3. Also, in this binomial distribution function, “TRUE” reports the cumulative probability of meeting or exceeding the critical value. Multiplying that value by 2, as you do in your tutorial, can yield P-values that exceed 1.0, which is impossible! I think the 2-tailed is built-in.

Leave a Reply

Your email address will not be published. Required fields are marked *