# 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.

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

May 13, 2024
April 25, 2024
April 19, 2024

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

1. RyanT says:

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. Jeffrey Divino says:

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.

1. James Carmichael says:

Hi Jeffrey…We will review your feedback and suggestion.

3. Jeffrey Divino says:

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.