hypothesis formula in excel

Six Sigma & SPC Excel Add-in

• Questions? Contact Us
• 888-468-1537

Statistical Analysis in QI Macros

Statistics wizard, data normality, hypothesis tests, test of means, equivalence tests, test of variances, test of proportion, test relationship, non-parametric tests.

Hypothesis Testing Cheat Sheet

Knowledge Base | Online User Guide

• Free 30-Day Trial
• Powerful SPC Software for Excel
• Who Uses QI Macros?
• What Do Our Customers Say?
• QI Macros SPC Software Reviews
• SPC Software Comparison
• Control Chart
• Histogram with Cp Cpk
• Pareto Chart
• Automated Fishbone Diagram
• Gage R&R MSA
• Data Mining Tools
• Statistical Analysis - Hypothesis Testing
• Chart and Stat Wizards
• Lean Six Sigma Excel Templates
• Technical Support - PC & Mac
• QI Macros FAQs
• Upgrade History
• Submit Enhancement Request
• Data Analysis Services
• Free QI Macros Webinar
• Free QI Macros Video Tutorials
• How to Setup Excel for QI Macros
• Free Healthcare Data Analytics Course
• Free Lean Six Sigma Webinars
• Animated Lean Six Sigma Video Tutorials
• Free Agile Lean Six Sigma Trainer Training
• Free White Belt Training
• Free Yellow Belt Training
• Free Green Belt Training
• QI Macros Resources
• QI Macros Knowledge Base | User Guide
• Excel Tips and Tricks
• Lean Six Sigma Resources
• QI Macros Monthly Newsletter
• Improvement Insights Blog
• Buy QI Macros
• Quantity Discounts and W9
• Hassle Free Guarantee

QI Macros Reviews CNET Five Star Review Industry Leaders Our Customers

Home » Statistical Analysis Excel » Hypothesis Testing

Struggling with Hypothesis Testing in Excel?

Qi macros makes hypothesis testing easy, even if you don't know anything about statistics.

Run Any Hypothesis Test using QI Macros

• Select your data.
• Click on QI Macros menu > Statistical Tools > the test you want
• QI Macros will do the math and analysis for you.

What is a Hypothesis Test?

A hypothesis test helps identify ways to reduce costs and improve quality. Hypothesis testing asks the question: Are two or more sets of data the same or different, statistically.

For companies working to improve operations, hypothesis tests help identify differences between machines, formulas, raw materials, etc. and whether the differences are statistically significant or not. Without such testing, teams can run around changing machine settings, formulas and so on causing more variation. These knee-jerk responses can amplify variation and cause more problems than doing nothing at all.

Three Types of Hypothesis Tests

• Classical Method - comparing a test statistic to a critical value
• p Value Method - the probability of a test statistic being contrary to the null hypothesis
• Confidence Interval Method - is the test statistic between or outside of the confidence interval

How to Conduct a Hypothesis Test

• Define the  null (H0) and an alternate (Ha) hypothesis .
• Conduct the test.
• Calculate the test statistic and the critical value (t-Test, F-test, z-Test, ANOVA, etc.).
• Calculate a p value and compare it to a significance level (a) or confidence level (1-a).
• Interpret the results to determine if you "cannot reject null hypothesis (accept null hypothesis)" or "reject the null hypothesis."

QI Macros for Excel Makes Hypothesis Testing as Easy as 1-2-3!

QI Macros adds a new tab to Excel's menu:

• Just input your data into an Excel spreadsheet and select it.
• Click on QI Macros menu , Statistical Tools and the test you want to run (t test, f test, z test, ANOVA, etc.).  If you are not sure which test to run, QI Macros Stat Wizard will analyze your data and run the possible tests for you.
• QI Macros performs all of the calculations AND interprets the results for you:

QI Macros Will Also Draw Charts to Help You Visualize the Differences in Your Data Sets

Cheat Sheet to Help You Interpret the Results Yourself

Stop struggling with hypothesis tests start conducting hypothesis tests in just minutes., download a free 30-day trial. run hypothesis tests now, qi macros can draw these charts too.

• SPC Software for Excel
• Free 30 Day Trial
• On-line Tech Support
• QI Macros Reviews
• Free QI Macros Training
• Privacy Policy

KnowWare International, Inc. 2696 S. Colorado Blvd., Ste. 555 Denver, CO 80222 USA Toll-Free: 1-888-468-1537 Local: (303) 756-9144

Statistics Made Easy

The Complete Guide: Hypothesis Testing in Excel

In statistics, a hypothesis test is used to test some assumption about a population parameter .

There are many different types of hypothesis tests you can perform depending on the type of data you’re working with and the goal of your analysis.

This tutorial explains how to perform the following types of hypothesis tests in Excel:

• One sample t-test
• Two sample t-test
• Paired samples t-test
• One proportion z-test
• Two proportion z-test

Let’s jump in!

Example 1: One Sample t-test in Excel

A one sample t-test is used to test whether or not the mean of a population is equal to some value.

For example, suppose a botanist wants to know if the mean height of a certain species of plant is equal to 15 inches.

To test this, she collects a random sample of 12 plants and records each of their heights in inches.

She would write the hypotheses for this particular one sample t-test as follows:

• H 0 :  µ = 15
• H A :  µ ≠15

Refer to this tutorial for a step-by-step explanation of how to perform this hypothesis test in Excel.

Example 2: Two Sample t-test in Excel

A two sample t-test is used to test whether or not the means of two populations are equal.

For example, suppose researchers want to know whether or not two different species of plants have the same mean height.

To test this, they collect a random sample of 20 plants from each species and measure their heights.

The researchers would write the hypotheses for this particular two sample t-test as follows:

• H 0 :  µ 1 = µ 2
• H A :  µ 1 ≠ µ 2

Example 3: Paired Samples t-test in Excel

A paired samples t-test is used to compare the means of two samples when each observation in one sample can be paired with an observation in the other sample.

For example, suppose we want to know whether a certain study program significantly impacts student performance on a particular exam.

To test this, we have 20 students in a class take a pre-test. Then, we have each of the students participate in the study program for two weeks. Then, the students retake a post-test of similar difficulty.

We would write the hypotheses for this particular two sample t-test as follows:

• H 0 :  µ pre = µ post
• H A :  µ pre ≠ µ post

Example 4: One Proportion z-test in Excel

A  one proportion z-test  is used to compare an observed proportion to a theoretical one.

For example, suppose a phone company claims that 90% of its customers are satisfied with their service.

To test this claim, an independent researcher gathered a simple random sample of 200 customers and asked them if they are satisfied with their service.

• H 0 : p = 0.90
• H A : p ≠ 0.90

Example 5: Two Proportion z-test in Excel

A two proportion z-test is used to test for a difference between two population proportions.

For example, suppose a s uperintendent of a school district claims that the percentage of students who prefer chocolate milk over regular milk in school cafeterias is the same for school 1 and school 2.

To test this claim, an independent researcher obtains a simple random sample of 100 students from each school and surveys them about their preferences.

• H 0 : p 1 = p 2
• H A : p 1  ≠ p 2

Published by Zach

Leave a reply cancel reply.

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

How to Do Hypothesis Tests With the Z.TEST Function in Excel

• Statistics Tutorials
• Probability & Games
• Descriptive Statistics
• Inferential Statistics
• Applications Of Statistics
• Math Tutorials
• Pre Algebra & Algebra
• Exponential Decay
• Worksheets By Grade
• Ph.D., Mathematics, Purdue University
• M.S., Mathematics, Purdue University
• B.A., Mathematics, Physics, and Chemistry, Anderson University

Hypothesis tests are one of the major topics in the area of inferential statistics. There are multiple steps to conduct a hypothesis test and many of these require statistical calculations. Statistical software, such as Excel, can be used to perform hypothesis tests. We will see how the Excel function Z.TEST tests hypotheses about an unknown population mean.

Conditions and Assumptions

We begin by stating the assumptions and conditions for this type of hypothesis test. For inference about the mean we must have the following simple conditions:

• The sample is a simple random sample .
• The sample is small in size relative to the population . Typically this means that the population size is more than 20 times the size of the sample.
• The variable being studied is normally distributed.
• The population standard deviation is known.
• The population mean is unknown.

All of these conditions are unlikely to be met in practice. However, these simple conditions and the corresponding hypothesis test are sometimes encountered early in a statistics class. After learning the process of a hypothesis test, these conditions are relaxed in order to work in a more realistic setting.

Structure of the Hypothesis Test

The particular hypothesis test we consider has the following form:

• State the null and alternative hypotheses .
• Calculate the test statistic, which is a z -score.
• Calculate the p-value by using the normal distribution. In this case the p-value is the probability of obtaining at least as extreme as the observed test statistic, assuming the null hypothesis is true.
• Compare the p-value with the level of significance to determine whether to reject or fail to reject the null hypothesis.

We see that steps two and three are computationally intensive compared two steps one and four. The Z.TEST function will perform these calculations for us.

Z.TEST Function

The Z.TEST function does all of the calculations from steps two and three above. It does a majority of the number crunching for our test and returns a p-value. There are three arguments to enter into the function, each of which is separated by a comma. The following explains the three types of arguments for this function.

• The first argument for this function is an array of sample data. We must enter a range of cells that corresponds to the location of the sample data in our spreadsheet.
• The second argument is the value of μ that we are testing in our hypotheses. So if our null hypothesis is H 0 : μ = 5, then we would enter a 5 for the second argument.
• The third argument is the value of the known population standard deviation. Excel treats this as an optional argument

Notes and Warnings

There are a few things that should be noted about this function:

• The p-value that is output from the function is one-sided. If we are conducting a two-sided test, then this value must be doubled.
• The one-sided p-value output from the function assumes that the sample mean is greater than the value of μ we are testing against. If the sample mean is less than the value of the second argument, then we must subtract the output of the function from 1 to get the true p-value of our test.
• The final argument for the population standard deviation is optional. If this is not entered, then this value is automatically replaced in Excel’s calculations by the sample standard deviation. When this is done, theoretically a t-test should be used instead.

We suppose that the following data are from a simple random sample of a normally distributed population of unknown mean and standard deviation of 3:

1, 2, 3, 3, 4, 4, 8, 10, 12

With a 10% level of significance we wish to test the hypothesis that the sample data are from a population with mean greater than 5. More formally, we have the following hypotheses:

• H 0 : μ= 5
• H a : μ > 5

We use Z.TEST in Excel to find the p-value for this hypothesis test.

• Enter the data into a column in Excel. Suppose this is from cell A1 to A9
• Into another cell enter =Z.TEST(A1:A9,5,3)
• The result is 0.41207.
• Since our p-value exceeds 10%, we fail to reject the null hypothesis.

The Z.TEST function can be used for lower tailed tests and two tailed tests as well. However the result is not as automatic as it was in this case. Please see here for other examples of using this function.

• What Is a P-Value?
• Example of Two Sample T Test and Confidence Interval
• Hypothesis Test Example
• Hypothesis Test for the Difference of Two Population Proportions
• An Example of a Hypothesis Test
• Functions with the T-Distribution in Excel
• The Runs Test for Random Sequences
• How to Conduct a Hypothesis Test
• Chi-Square Goodness of Fit Test
• How to Use the NORM.INV Function in Excel
• What Is the Difference Between Alpha and P-Values?
• How to Find Degrees of Freedom in Statistics
• Robustness in Statistics
• Calculating a Confidence Interval for a Mean
• How to Use the STDEV.S Function in Excel
• How to Construct a Confidence Interval for a Population Proportion

By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts.

• Ph: 800 -274-2874
• Email: [email protected]

SPC for Excel Software

• Download the Free Demo
• Statistical Tools in SPC for Excel
• Control Charts
• Process Capability
• Gage R&R Analysis
• Teaching Guides
• SPC Seminar
• SPC Facilitator Seminar
• Process Capability Improvement Seminar
• SPC Knowledge Base
• SPC for Excel and Industries
• Customer Stories
• SPC for Excel Around the World
• SPC for Excel Help
• Videos – SPC for Excel – Statistical Tools
• Videos – Statistical Analysis
• Revision History
• Hypothesis Testing
• SPC KNOWLEDGE BASE

So, a hypothesis is just a statement of theory.  It may or may not be true.  A drug company can claim that a new drug is better at decreasing blood pressure.   You may claim that the diet plan you created helps people lose more weight than a nationally known diet plan.  All these things are just statements – just hypotheses. 

The hypothesis is the starting point.  From there, we have to test the hypothesis and reach a decision if the hypothesis is probably true or probably false.  Note the word “probably.”  There is always variation – so there is always a chance for you to make the wrong decision.  This month’s publication takes a look at the five steps involved in conducting a hypothesis test.

In this issue:

• The problem
• A brief pause for the standard normal distribution
• Formulate the null hypothesis and the alternative hypothesis
• Determine the significance level
• Collect the data and calculate the sample statistics
• Calculate the p value for the hypothesis test
• Compare the p value to the desired significance level

Quick Links

You can download this publication as a pdf here .

The Problem

The average coating thickness is 5 mil.  You want to be sure that the coating thickness remains the same before you will approve the process change. 

The team wants to perform a hypothesis test to prove that the average coating thickness will not change.  The team will go through the basic five steps of hypothesis testing:

The details of the five steps are shown below.  However, before those steps are covered, a review of the standard normal distribution is needed.  This will be required when we do some calculations.

A Brief Pause for the Standard Normal Distribution

We need to digress a moment here because we will need to make use of a special case of the normal distribution – when the average = 0 and the standard deviation = 1. This special case is called the standard normal distribution and is shown in Figure 1.

Figure 1: Standard Normal Distribution

For this distribution, the area under the curve from -∞ to +∞ is equal to 1.0. In addition, the area under the curve is proportional to the fraction of measurements that fall in that region. These two facts can used to help determine the fraction of measurements that fall above some value (such as a specification limit), below some value, or between two values.

z=  (x- μ)/σ

where x is some value, μ is the average, and σ is the standard deviation of the x values.  The value of z (the z score) is simply how many standard deviations a value, x, is from the average. 

For example, suppose x is 1.5 standard deviations below the average.  In this case, z = -1.5.  The area below z = -1.5 is the percentage of x values that are more than 1.5 standard deviations below the average.  For z = -1.5, that area is 6.68% as is shown in Figure 1.   If z = 1.5, then the area above z = 1.5 is the percentage of x values that are more than 1.5 standard deviations above the average.  This area is also 6.68%.  

To find the percentage of data within z = -1.5 and z = 1.5, you simply use the fact that the area under the curve is 100%, so the percentage of data between the two z values is 100 – 6.68 – 6.68 = 86.64%.  You can determine these percentages from a table of z values (see our publication on the normal distribution ) or by using Excel’s NORMSDIST function.

These percentages can also be viewed as probabilities, e.g., the probability of getting a result that is less than -1.5 standard deviations below the average is 0.0668.  We will make use of this knowledge below.  Now back to the steps in hypothesis testing.

Step 1: Formulate the Null Hypothesis and Alternative Hypothesis

So the null hypothesis (H 0 ) is that the process change will not impact the average coating thickness; the average coating thickness (μ) will remain at 5.  This is usually written as:

Now for the alternative hypothesis, which is denoted by H 1 .  The alternative hypothesis is that the process change will have an effect on the average coating thickness and the average coating thickness will not equal 5.  This is usually written as:

This is called a two-sided hypothesis test since you are only interested if the mean is not equal to 5.  You can have one-sided tests where you want the mean to be greater than or less than some value.  

Step 2: Determine the Significance Level You Want

The significance level is important in hypothesis testing.  It is the probability of rejecting the null hypothesis when it is true.  This probability is denoted by α.  Typical values of  α include 0.05 and 0.01.  You decide that you want α to be 0.05.  This means that there is only a 5% of chance of rejecting the null hypothesis when it is actually true. 

Step 3: Collect the Data and Calculate the Sample Statistics

X   = average coating thickness = 5.06

s = standard deviation of the coating thickness = 0.20

We have our statistics.  How do you decide to accept or reject the null hypothesis?  The way you do this is to assume that the null hypothesis is true and then determine the probability (p value) of getting this sample average.  If the p value is large, it means that there is large probability of getting an average thickness of 5.06 with a standard deviation of 0.20 when the null hypothesis is true and you will accept that the null hypothesis is probably true.  But if the probability of getting these statistics is small, you will assume that the null hypothesis is probably not true and reject it in favor the alternative hypothesis.

Step 4: Calculate the p Value

To determine this probability, you will need to consider your sampling distribution.    The distribution of sample averages tends to be normal when the sample size is large enough.  We will use this assumption here.  So, your sampling distribution is represented by all the possible sample averages of sample size 25 from the population of coating thicknesses.  This normal distribution is shown in Figure 2.

Figure 2: Normal Distribution for Sample Averages

The highest point on the curve is the average.  The population average of the sample averages (μ X ) is equal to the population average, μ, so we have just used μ in Figure 1.  The standard deviation of the sample averages is denoted by σ X .

To be able to draw your sampling distribution, you need to know μ X   and  σ X .  Since you assumed that the null hypothesis is true, μ X   = 5.0.  The standard deviation of the sample averages is given by:

where σ is the population standard deviation and n is the sample size.     You don’t know what the population standard deviation is, but you have an estimate from the sample statistics.  The standard deviation of the 25 samples was 0.2.  You can use this as the population standard deviation.

σ X =σ/√n =  s/√n=0.2/√25=0.04

Now you can draw the sampling distribution and add the sample average as shown in Figure 3.

Figure 3: Sampling Distribution

Now we return to the z score.  Remember, the z score is a measure of how many standard deviations the sample average ( X  )is from the population average (μ).   For this example, the z value is calculated as:

z=  ( X -μ)/σ X =(5.06-5)/.04=.06/.04=1.5

So, 5.06 is 1.5 standard deviations away from the average.    As shown above, the probability of getting a result that is 1.5 standard deviations away from the average is 0.0668.  Remember, this a two-side test, so you didn’t care if the difference was above or below the average.  So, the probability of getting an average that is more than 1.5 standard deviations away from the average is 2(0.0668) = 0.1336 or 13.36%.  This is the p value:

p value = 0.1336

Remember what the p value represents.  You assumed that the null hypothesis is true.  The p value is the probability of getting this result (or a more extreme result) if the null hypothesis is true.

Step 5:  Compare the p value to the Desired Significance Level

In step 2, we set the significance level at 0.05.  Since our p value is greater than this, we conclude that the coating thickness was not impacted by the process change.  We accept the null hypothesis as probably being true.  If the p value had been less than 0.05, we would rejected the null hypothesis and said that the process change did impact the coating thickness.

This newsletter has taken a look at how to perform hypothesis testing.  The five steps are:

• Determine the significance level you want

The normal distribution was used to demonstrate how hypothesis testing is done.  You will not always be dealing with the normal distribution but the process is essentially the same.  One item that is still to be discussed is how to select the sample size.  This will be the subject of a later publication.

Visit our home page

SPC Training

SPC Consulting

Ordering Information

Thanks so much for reading our publication. We hope you find it informative and useful. Happy charting and may the data always support your position.

Dr. Bill McNeese BPI Consulting, LLC

Connect with Us

Basic statistics.

• Item Analysis
• Analysis of Individual Values (ANOX)
• Nonparametric Techniques for Comparing Processes
• Nonparametric Techniques for a Single Sample
• Descriptive Statistics
• Interpretation of Alpha and p-Value
• Just Because There is a Correlation, Doesn’t Mean ….
• Deciding Which Distribution Fits Your Data Best
• Distribution Fitting
• Box-Cox Transformation
• What? My Data are Not Normal?
• Are Skewness and Statistics Useful Statistics – Revisited
• How Many Samples Do I Need?
• Anderson-Darling Test for Normality
• Polls, Sample Size, and Error Margins
• Normal Probability Plots
• Normal Distribution
• Are the Skewness and Kurtosis Useful Statistics?
• Inspecting Supplier Material
• Explaining Standard Deviation

Comments (4)

"These percentages can also be viewed as probabilities, e.g., the probability of getting a result that is less than -1.5 standard deviations below the average is 0.668."Should this be 0.0668, not 0.668?

Hello, Your are correct.  Had it 0.0668 in several places, but had it 0.668 in that one place.  Proved once again that 100% inspection does not guarantee no mistakes.  Thanks for pointing it out to me.

Wonderful explanation. 

Well explained. Best simplified i ever read

Leave a Reply Cancel reply

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