This guide will explain how to perform a one-proportion Z-test in Excel.
We can use the one proportion Z-test to test if the proportions of categories in a single variable differ significantly from a known population proportion.
The one proportion Z-test requires your dataset to have a variable that could be one of two values.
The Z-test also requires a few assumptions about your data.
First, the sample being tested must have come from a random selection. Next, each of your data points must be independent of the others.
Let’s take a look at a quick example where we can perform a one-proportion Z-test.
Suppose you want to prove the claim that at least 95% of cars produced by a factory contain zero initial defects. In any given year, the factory produces 100,000 vehicles of different types.
To investigate the truth of this claim, you conducted a random check of 5,000 vehicles manufactured by the factory. You later determine that the sample population includes 4,850 cars without any initial defects. How can we determine the likelihood that our claim is true in Excel?
We can perform a one-proportion Z-test in Excel by finding the test statistic given the hypothesized proportion and the actual sample proportion.
The t-statistic tells us how far a given value is from the hypothesized value. We’ll later compute the corresponding p-value to determine whether we should accept or reject our earlier claim.
Now that we know when to use a one-proportion Z-test in Excel, let’s learn how to set it up on an actual sample spreadsheet.
A Real Example of Using the One-Proportion Z-test
The following section provides two examples of a one-proportion Z-test. We will also explain the formulas and tools used in these examples.
In the first example, we want to prove that a given sample has a positivity rate of 85%. When we took a random sample with a sample size of 50, we found 40 positive observations.
First, we divided the frequency of positive observations by the sample size to get a sample proportion of 0.8 or 80%.
Next, we computed the test statistic and p-values to see if there is a statistically significant difference between the observed sample proportion and the hypothesis.
We can find the test statistic using the following formula:
=(C6-C2)/SQRT(C2*(1-C2)/C3)
We can find the p-value with this formula:
=NORM.S.DIST(C7;TRUE)
The first argument of the NORM.S.DIST function must be the result of our test statistic formula. Since the p-value is not less than 0.05, we can say that we do not have sufficient evidence yet to reject the claim that our population has a positivity rate of 85%.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
Use our sample spreadsheet to test out how the p-value and test statistic changes given different results.
If you’re ready to try performing your own one-proportion Z-test, head over to the next section to read our step-by-step breakdown on how to do it!
How to Perform a One-Proportion Z Test in Excel
This section will guide you through each you need to perform a one-proportion Z-test in Excel.
We’ll first learn how to find the sample proportion that we’ll compare with the overall population proportion. Next, we’ll use a custom formula to find the test statistic given the sample results. We will then find the p-value of our measurements using the NORM.S.DIST function.
Follow these steps to perform the one-proportion Z-test in Excel:
- First, we’ll need to write down certain values for our Z-test. We’ll need to get the test or population proportions we are trying to prove. Next, we’ll need to indicate the sample size used for our random sample. Lastly, we’ll need the frequency or the number of positive observations.
- Since we want to compare the test and sample proportions, we must first solve for the latter. We can find the sample proportion by dividing the number of positive observations by the sample size.
- Hit the Enter key to return the result.
- Next, we’ll use the formula
=(C6-C2)/SQRT(C2*(1-C2)/C3)to determine the test statistic.
- The test statistic will determine how close an observed sample parameter is to a hypothesized value.
- We will use the
NORM.S.DISTfunction to determine how likely the test statistic value will be.
- Since the p-value is less than 0.05, we can reject the claim and conclude that the population has a proportion lower than 95%.
Frequently Asked Questions (FAQ)
Here are some frequently asked questions about the one-proportion Z-test:
- When is it appropriate to use the one-proportion Z-test?
We can use this test if we want to know the difference between two categorical variables. The categorical variable must only have two options. For example, we can have a variable labeled ‘clicked_on_ad’ that can either be ‘Yes’ or ‘No’
This step-by-step guide is a quick introduction to performing a one-proportion Z-test in Microsoft Excel.
You may use the one-proportion Z-test to accept or reject a hypothesis about a given population proportion.
The Z-test is just one example of the many statistical methods you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
