hypothesis testing normal distribution excel

NORMDIST function

Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing.

Important:  This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.

For more information about the new function, see NORM.DIST function .

NORMDIST(x,mean,standard_dev,cumulative)

The NORMDIST function syntax has the following arguments:

X      Required. The value for which you want the distribution.

Mean      Required. The arithmetic mean of the distribution.

Standard_dev      Required. The standard deviation of the distribution.

Cumulative      Required. A logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.

If mean or standard_dev is nonnumeric, NORMDIST returns the #VALUE! error value.

If standard_dev ≤ 0, NORMDIST returns the #NUM! error value.

If mean = 0, standard_dev = 1, and cumulative = TRUE, NORMDIST returns the standard normal distribution, NORMSDIST.

The equation for the normal density function (cumulative = FALSE) is:

When cumulative = TRUE, the formula is the integral from negative infinity to x of the given formula.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Shapiro-Wilk and other normality tests in Excel

Why do we need to run a normality test.

Normality tests enable you to know whether your dataset follows a normal distribution. Moreover, normality of residuals is a required assumption in common statistical modeling methods . Normality tests involve the null hypothesis that the variable from which the sample is drawn follows a normal distribution. Thus, a low p-value indicates a low risk of being wrong when stating that the data are not normal. In other words, if p-value < alpha risk threshold, the data are significantly not normal.

How do normality tests work?

We calculate the test statistic below on our dataset :

If its values are below the bounds defined in the Shapiro-Wilk table for a set alpha threshold, then the associated p-value is less than alpha and the null hypothesis is rejected and the data does not follow a normal distribution.

Dataset for Shapiro-Wilk and other normality tests

The data represent two samples each containing the average math score of 1000 students.

Setting up a Shapiro-Wilk and other normality tests

Interpreting the results of a Shapiro-Wilk and other normality tests

In conclusion, in this tutorial, we have seen how to test two samples for normality using Shapiro-Wilk and Jarque-Bera tests. These tests did not reject the normality assumption for the first sample and allowed us to reject it for the second sample.

Was this article useful?

Similar articles

• Kernel density estimation in Excel
• Multi-way crosstabs tutorial in Excel
• Matrix operations with Excel tutorial
• Create an intelligent pivot table in Excel
• Biserial correlations tutorial in Excel
• Skewness and Kurtosis in Excel

Expert Software for Better Insights, Research, and Outcomes

Teach yourself statistics

How to Test for Normality: Three Simple Tests

Many statistical techniques (regression, ANOVA, t-tests, etc.) rely on the assumption that data is normally distributed. For these techniques, it is good practice to examine the data to confirm that the assumption of normality is tenable.

With that in mind, here are three simple ways to test interval-scale data or ratio-scale data for normality.

• Check descriptive statistics.
• Generate a histogram.
• Conduct a chi-square test.

Each option is easy to implement with Excel, as long as you have Excel's Analysis ToolPak.

The Analysis ToolPak

To conduct the tests for normality described below, you need a free Microsoft add-in called the Analysis ToolPak, which may or may not be already installed on your copy of Excel.

To determine whether you have the Analysis ToolPak, click the Data tab in the main Excel menu. If you see Data Analysis in the Analysis section, you're good. You have the ToolPak.

If you don't have the ToolPak, you need to get it. Go to: How to Install the Data Analysis ToolPak in Excel .

Descriptive Statistics

Perhaps, the easiest way to test for normality is to examine several common descriptive statistics. Here's what to look for:

• Central tendency. The mean and the median are summary measures used to describe central tendency - the most "typical" value in a set of values. With a normal distribution, the mean is equal to the median.
• Skewness. Skewness is a measure of the asymmetry of a probability distribution. If observations are equally distributed around the mean, the skewness value is zero; otherwise, the skewness value is positive or negative. As a rule of thumb, skewness between -2 and +2 is consistent with a normal distribution.
• Kurtosis. Kurtosis is a measure of whether observations cluster around the mean of the distribution or in the tails of the distribution. The normal distribution has a kurtosis value of zero. As a rule of thumb, kurtosis between -2 and +2 is consistent with a normal distribution.

Together, these descriptive measures provide a useful basis for judging whether a data set satisfies the assumption of normality.

To see how to compute descriptive statistics in Excel, consider the following data set:

Begin by entering data in a column or row of an Excel spreadsheet:

Next, from the navigation menu in Excel, click Data / Data analysis . That displays the Data Analysis dialog box. From the Data Analysis dialog box, select Descriptive Statistics and click the OK button:

Then, in the Descriptive Statistics dialog box, enter the input range, and click the Summary Statistics check box. The dialog box, with entries, should look like this:

And finally, to display summary statistics, click the OK button on the Descriptive Statistics dialog box. Among other outputs, you should see the following:

The mean is nearly equal to the median. And both skewness and kurtosis are between -2 and +2.

Conclusion: These descriptive statistics are consistent with a normal distribution.

Another easy way to test for normality is to plot data in a histogram , and see if the histogram reveals the bell-shaped pattern characteristic of a normal distribution. With Excel, this is a a four-step process:

• Enter data. This means entering data values in an Excel spreadsheet. The column, row, or range of cells that holds data is the input range .
• Define bins. In Excel, bins are category ranges. To define a bin, you enter the upper range of each bin in a column, row, or range of cells. The block of cells that holds upper-range entries is called the bin range .
• Plot the data in a histogram. In Excel, access the histogram function through: Data / Data analysis / Histogram .
• In the Histogram dialog box, enter the input range and the bin range ; and check the Chart Output box. Then, click OK.

If the resulting histogram looks like a bell-shaped curve, your work is done. The data set is normal or nearly normal. If the curve is not bell-shaped, the data may not be normal.

To see how to plot data for normality with a histogram in Excel, we'll use the same data set (shown below) that we used in Example 1.

Begin by entering data to define an input range and a bin range. Here is what data entry looks like in an Excel spreadsheet:

Next, from the navigation menu in Excel, click Data / Data analysis . That displays the Data Analysis dialog box. From the Data Analysis dialog box, select Histogram and click the OK button:

Then, in the Histogram dialog box, enter the input range, enter the bin range, and click the Chart Output check box. The dialog box, with entries, should look like this:

And finally, to display the histogram, click the OK button on the Histogram dialog box. Here is what you should see:

The plot is fairly bell-shaped - an almost-symmetric pattern with one peak in the middle. Given this result, it would be safe to assume that the data were drawn from a normal distribution. On the other hand, if the plot were not bell-shaped, you might suspect the data were not from a normal distribution.

Chi-Square Test

The chi-square test for normality is another good option for determining whether a set of data was sampled from a normal distribution.

Note: All chi-square tests assume that the data under investigation was sampled randomly.

Hypothesis Testing

The chi-square test for normality is an actual hypothesis test , where we examine observed data to choose between two statistical hypotheses:

• Null hypothesis: Data is sampled from a normal distribution.
• Alternative hypothesis: Data is not sampled from a normal distribution.

Like many other techniques for testing hypotheses, the chi-square test for normality involves computing a test-statistic and finding the P-value for the test statistic, given degrees of freedom and significance level . If the P-value is bigger than the significance level, we accept the null hypothesis; if it is smaller, we reject the null hypothesis.

How to Conduct the Chi-Square Test

The steps required to conduct a chi-square test of normality are listed below:

• Specify the significance level.
• Find the mean, standard deviation, sample size for the sample.
• Define non-overlapping bins.
• Count observations in each bin, based on actual dependent variable scores.
• Find the cumulative probability for each bin endpoint.
• Find the probability that an observation would land in each bin, assuming a normal distribution.
• Find the expected number of observations in each bin, assuming a normal distribution.
• Compute a chi-square statistic.
• Find the degrees of freedom, based on the number of bins.
• Find the P-value for the chi-square statistic, based on degrees of freedom.
• Accept or reject the null hypothesis, based on P-value and significance level.

So you will understand how to accomplish each step, let's work through an example, one step at a time.

To demonstrate how to conduct a chi-square test for normality in Excel, we'll use the same data set (shown below) that we've used for the previous two examples. Here it is again:

Now, using this data, let's check for normality.

Specify Significance Level

The significance level is the probability of rejecting the null hypothesis when it is true. Researchers often choose 0.05 or 0.01 for a significance level. For the purpose of this exercise, let's choose 0.05.

Find the Mean, Standard Deviation, and Sample Size

To compute a chi-square test statistic, we need to know the mean, standared deviation, and sample size. Excel can provide this information. Here's how:

Define Bins

To conduct a chi-square analysis, we need to define bins, based on dependent variable scores. Each bin is defined by a non-overlapping range of values.

For the chi-square test to be valid, each bin should hold at least five observations. With that in mind, we'll define four bins for this example, as shown below:

Bin 1 will hold dependent variable scores that are less than 4; Bin 2, scores between 4 and 5; Bin 3, scores between 5.1 and 6; and and Bin 4, scores greater than 6.

Note: The number of bins is an arbitrary decision made by the experimenter, as long as the experimenter chooses at least four bins and at least five observations per bin. With fewer than four bins, there are not enough degrees of freedom for the analysis. For this example, we chose to define only four bins. Given the small sample, if we used more bins, at least one bin would have fewer than five observations per bin.

Count Observed Data Points in Each Bin

The next step is to count the observed data points in each bin. The figure below shows sample observations allocated to bins, with a frequency count for each bin in the final row.

Note: We have five observed data points in each bin - the minimum required for a valid chi-square test of normality.

Find Cumulative Probability

A cumulative probability refers to the probability that a random variable is less than or equal to a specific value. In Excel, the NORMDIST function computes cumulative probabilities from a normal distribution.

Assuming our data follows a normal distribution, we can use the NORMDIST function to find cumulative probabilities for the upper endpoints in each bin. Here is the formula we use:

P j = NORMDIST (MAX j , X , s, TRUE)

where P j is the cumulative probability for the upper endpoint in Bin j , MAX j is the upper endpoint for Bin j , X is the mean of the data set, and s is the standard deviation of the data set.

When we execute the formula in Excel, we get the following results:

P 1 = NORMDIST (4, 5.1, 2.0, TRUE) = 0.29

P 2 = NORMDIST (5, 5.1, 2.0, TRUE) = 0.48

P 3 = NORMDIST (6, 5.1, 2.0, TRUE) = 0.67

P 4 = NORMDIST (999999999, 5.1, 2.0, TRUE) = 1.00

Note: For Bin 4, the upper endpoint is positive infinity (∞), a quantity that is too large to be represented in an Excel function. To estimate cumulative probability for Bin 4 (P 4 ) with excel, you can use a very large number (e.g., 999999999) in place of positive infinity (as shown above). Or you can recognize that the probability that any random variable is less than or equal to positive infinity is 1.00.

Find Bin Probability

Given the cumulative probabilities shown above, it is possible to find the probability that a randomly selected observation would fall in each bin, using the following formulas:

P( Bin = 1 ) = P 1 = 0.29

P( Bin = 2 ) = P 2 - P 1 = 0.48 - 0.29 = 0.19

P( Bin = 3 ) = P 3 - P 2 = 0.67 - 0.48 = 0.19

P( Bin = 4 ) = P 4 - P 3 = 1.000 - 0.67 = 0.33

Find Expected Number of Observations

Assuming a normal distribution, the expected number of observations in each bin can be found by using the following formula:

Exp j = P( Bin = j ) * n

where Exp j is the expected number of observations in Bin j , P( Bin = j ) is the probability that a randomly selected observation would fall in Bin j , and n is the sample size

Applying the above formula to each bin, we get the following:

Exp 1 = P( Bin = 1 ) * 20 = 0.29 * 20 = 5.8

Exp 2 = P( Bin = 2 ) * 20 = 0.19 * 20 = 3.8

Exp 3 = P( Bin = 3 ) * 20 = 0.19 * 20 = 3.8

Exp 3 = P( Bin = 4 ) * 20 = 0.33 * 20 = 6.6

Compute Chi-Square Statistic

Finally, we can compute the chi-square statistic ( χ 2 ), using the following formula:

χ 2 = Σ [ ( Obs j - Exp j ) 2 / Exp j ]

where Obs j is the observed number of observations in Bin j , and Exp j is the expected number of observations in Bin j .

Find Degrees of Freedom

Assuming a normal distribution, the degrees of freedom (df) for a chi-square test of normality equals the number of bins (n b ) minus the number of estimated parameters (n p ) minus one. We used four bins, so n b equals four. And to conduct this analysis, we estimated two parameters (the mean and the standard deviation), so n p equals two. Therefore,

df = n b - n p - 1 = 4 - 2 - 1 = 1

Find P-Value

The P-value is the probability of seeing a chi-square test statistic that is more extreme (bigger) than the observed chi-square statistic. For this problem, we found that the observed chi-square statistic was 1.26. Therefore, we want to know the probability of seeing a chi-square test statistic bigger than 1.26, given one degree of freedom.

Use Stat Trek's Chi-Square Calculator to find that probability. Enter the degrees of freedom (1) and the observed chi-square statistic (1.26) into the calculator; then, click the Calculate button.

From the calculator, we see that P( X 2 > 1.26 ) equals 0.26.

Test Null Hypothesis

When the P-Value is bigger than the significance level, we cannot reject the null hypothesis. Here, the P-Value (0.26) is bigger than the significance level (0.05), so we cannot reject the null hypothesis that the data tested follows a normal distribution.

Excel Tutorial: How To Test For Normal Distribution In Excel

Introduction.

Understanding and testing for normal distribution in data is crucial in the field of statistical analysis. Data that follows a normal distribution pattern is essential for making accurate predictions, drawing meaningful conclusions, and making informed decisions. In this blog post, we will walk you through the process of testing for normal distribution in Excel , equipping you with the necessary tools to ensure the reliability of your data analysis.

Key Takeaways

• Understanding and testing for normal distribution in data is crucial for accurate predictions and informed decision making in statistical analysis.
• Excel's Data Analysis Toolpack provides the necessary features to test for normal distribution in a data set.
• Interpreting the results of normality tests is essential for determining if a data set follows a normal distribution.
• Non-normal data can have significant impact on decision making and statistical analysis and should be identified and addressed.
• Testing for normal distribution in Excel is important for enhancing data analysis skills and ensuring the reliability of data analysis.

Understanding Normal Distribution

A. Definition of normal distribution and its characteristics

• Definition: Normal distribution, also known as Gaussian distribution, is a bell-shaped probability distribution that is symmetric around the mean. It is characterized by its mean and standard deviation.
• Characteristics: The normal distribution curve is smooth and continuous, with the mean, median, and mode all being equal. It has a 68-95-99.7 rule, which states that approximately 68% of the data falls within one standard deviation of the mean, 95% within two standard deviations, and 99.7% within three standard deviations.

B. Importance of testing for normal distribution in data sets

• Identifying Patterns: Testing for normal distribution helps in identifying patterns in the data and understanding the distribution of values.
• Assumptions in Statistical Tests: Many statistical tests, such as t-tests and ANOVA, assume that the data is normally distributed. Therefore, testing for normal distribution is crucial in determining the appropriateness of these tests.

C. Explanation of how normal distribution affects statistical analysis

• Validity of Results: If the data is not normally distributed, it can affect the validity of statistical analysis results, leading to incorrect conclusions.
• Choice of Statistical Tests: The shape of the distribution influences the choice of statistical tests, and knowing whether the data is normally distributed helps in selecting the appropriate test for analysis.

Excel Tutorial: How to Test for Normal Distribution in Excel

Using excel's data analysis toolpack.

Excel's Data Analysis Toolpack is a powerful tool that provides various statistical and data analysis functions. One of the key features of the Toolpack is the ability to test for normal distribution in a dataset. This can be particularly useful for researchers, analysts, and decision-makers who need to assess the distribution of their data.

Overview of Excel's Data Analysis Toolpack and its features

The Data Analysis Toolpack in Excel offers a wide range of statistical and data analysis functions, including descriptive statistics, hypothesis testing, regression analysis, and more. It provides a user-friendly interface for performing complex statistical analyses without the need for advanced programming knowledge.

Some of the key features of the Data Analysis Toolpack include:

• Descriptive Statistics: This feature allows users to calculate measures of central tendency, dispersion, and other descriptive statistics for their data.
• Histograms: The Toolpack provides an easy way to create histograms for visualizing the distribution of data.
• Normality Tests: Users can perform various tests to determine whether their data follows a normal distribution.

Step-by-step guide on how to access the Data Analysis Toolpack in Excel

Accessing the Data Analysis Toolpack in Excel is a straightforward process. Follow these steps to enable the Toolpack:

• Step 1: Open Excel and navigate to the "Data" tab on the ribbon.
• Step 2: Click on the "Data Analysis" option in the "Analysis" group.
• Step 3: If you do not see the "Data Analysis" option, you may need to install the Toolpack. Go to "File > Options > Add-Ins" and select "Excel Add-ins" in the "Manage" box. Click "Go" and check the "Analysis ToolPak" box, then click "OK" to enable it.

Instructions on how to use the Toolpack to test for normal distribution in a data set

Once the Data Analysis Toolpack is enabled, you can use it to test for normal distribution in your data set. Follow these steps:

• Step 1: Select the range of data for which you want to test the normal distribution.
• Step 2: Go to the "Data" tab, click on "Data Analysis," and select "Descriptive Statistics" from the list of available tools.
• Step 3: In the "Descriptive Statistics" dialog box, enter the input range (the data range you selected) and check the "Summary statistics" and "Normality tests" options. Click "OK" to perform the analysis.
• Step 4: The output will include the results of the normality tests, such as the Anderson-Darling test, Kolmogorov-Smirnov test, and Shapiro-Wilk test. You can use these results to determine whether your data follows a normal distribution.

Performing Normality Tests in Excel

When working with data in Excel, it is essential to determine if the data follows a normal distribution. Excel provides several built-in functions to test for normality, which can help in making informed decisions about statistical analysis and modeling. In this tutorial, we will walk through the different normality tests available in Excel, provide a step-by-step guide on how to perform these tests, and offer examples for interpreting the results.

Excel offers various functions to test for normality, including Shapiro-Wilk test, Anderson-Darling test, Kolmogorov-Smirnov test, and Chi-squared test. Each of these tests has its own assumptions and limitations, and it is important to understand the differences between them before selecting a test for analyzing the normality of your data.

1. Shapiro-Wilk test

• It is used to test for normality based on the sample data provided.
• It is suitable for small to moderate sample sizes.

2. Anderson-Darling test

• This test is more sensitive to deviations in the tails of the distribution.
• It is suitable for larger sample sizes.

3. Kolmogorov-Smirnov test

• This test compares the cumulative distribution function of the sample data with the normal distribution.
• It is suitable for continuous distributions.

4. Chi-squared test

• It is used to test for normality based on observed and expected frequencies.
• It is suitable for categorical data.

To perform normality tests in Excel, you can use built-in functions such as =NORM.DIST, =CHISQ.TEST, =NORM.S.DIST, and =NORM.INV. These functions allow you to conduct different normality tests and obtain p-values to determine whether the data is normally distributed.

Here is a step-by-step guide to performing a normality test using the Shapiro-Wilk test as an example:

• Enter your sample data into a column in Excel.
• Use the =SHAPIRO.TEST function to calculate the test statistic and p-value for the Shapiro-Wilk test.
• Based on the obtained p-value, make a decision on the normality of the data.

After performing a normality test in Excel, it is crucial to interpret the results to determine whether the data follows a normal distribution. For instance, if the p-value is greater than the significance level (e.g., 0.05), we can conclude that the data is normally distributed. On the other hand, if the p-value is less than the significance level, we reject the hypothesis of normality and consider the data to be non-normally distributed.

Interpreting the Results

When testing for normal distribution in Excel, it is crucial to effectively interpret the results in order to make informed decisions about the data set and its use in statistical analysis.

• Understanding the p-value: The p-value obtained from the normality test provides a measure of how likely it is that the data set is normally distributed. A low p-value (usually less than 0.05) suggests that the data set significantly deviates from a normal distribution.
• Assessing skewness and kurtosis: In addition to the p-value, examining the skewness and kurtosis of the data set can provide further insights into its distribution. High skewness or kurtosis values may indicate non-normality.
• Considering sample size: It is important to consider the size of the data set when interpreting the results. With large sample sizes, even minor deviations from normality may lead to significant results.
• Visual inspection: One of the simplest methods to determine normality is by visually inspecting the data distribution using histograms or Q-Q plots. These visual tools can provide a quick assessment of normality.
• Statistical tests: Excel offers various statistical tests such as Shapiro-Wilk test, Anderson-Darling test, and Kolmogorov-Smirnov test to quantitatively assess the normality of a data set.
• Impact on inferential statistics: The assumption of normality is fundamental in many statistical analyses such as t-tests, ANOVA, and regression. Deviations from normality can affect the validity and accuracy of these analyses.
• Validity of findings: Interpreting the results of normality tests ensures that the findings and conclusions drawn from the data set are reliable and trustworthy.
• Identifying potential data transformation: If the data set is found to be non-normal, considering alternative data transformation methods may be necessary to meet the normality assumption for statistical analyses.

The Impact of Non-Normal Data

When conducting statistical analysis, it is crucial to consider the distribution of the data. In many cases, the assumption of normality is made for the data being analyzed. However, when the data is not normally distributed, it can have significant impacts on the validity of the analysis and the decisions made based on the results.

Non-normal data can lead to biased results, as many statistical tests and procedures are based on the assumption of normality. This can result in erroneous conclusions and inaccurate predictions. Additionally, non-normal data can affect the reliability of statistical models, leading to misleading inferences.

For example, if non-normal data is not appropriately identified and addressed, it can lead to incorrect business decisions based on flawed analyses. In fields such as finance, healthcare, and manufacturing, relying on non-normal data for decision-making can result in serious consequences.

Identifying non-normal data is essential for ensuring the accuracy and validity of statistical analyses. By recognizing and addressing non-normality, researchers and analysts can avoid making incorrect assumptions and drawing faulty conclusions. This can ultimately lead to more reliable and trustworthy results, ultimately improving decision-making processes.

A. As we wrap up, it's important to remember the significance of testing for normal distribution in Excel. By ensuring that your data follows a normal distribution, you can confidently carry out various statistical analyses and make informed decisions based on the results.

B. I encourage you to utilize the tutorial we've provided to enhance your data analysis skills. Understanding how to test for normal distribution in Excel will undoubtedly benefit you in your professional and academic pursuits.

C. Lastly, it cannot be emphasized enough how important normal distribution is in statistical analysis and decision making. By being well-versed in this concept and knowing how to apply it using Excel, you will be better equipped to tackle complex data sets and derive meaningful insights.

Immediate Download

MAC & PC Compatible

Free Email Support

Related aticles

The Benefits of Excel Dashboards for Data Analysts

Unlock the Power of Real-Time Data Visualization with Excel Dashboards

Unlocking the Potential of Excel's Data Dashboard

Unleashing the Benefits of a Dashboard with Maximum Impact in Excel

Exploring Data Easily and Securely: Essential Features for Excel Dashboards

Unlock the Benefits of Real-Time Dashboard Updates in Excel

Unleashing the Power of Excel Dashboards

Understanding the Benefits and Challenges of Excel Dashboard Design and Development

Leverage Your Data with Excel Dashboards

Crafting the Perfect Dashboard for Excel

An Introduction to Excel Dashboards

How to Create an Effective Excel Dashboard

• Choosing a selection results in a full page refresh.

• school Campus Bookshelves
• menu_book Bookshelves
• perm_media Learning Objects
• login Login
• how_to_reg Request Instructor Account
• hub Instructor Commons
• Download Page (PDF)
• Download Full Book (PDF)
• Periodic Table
• Physics Constants
• Scientific Calculator
• Reference & Cite
• Tools expand_more
• Readability

selected template will load here

This action is not available.

9.3: A Single Population Mean using the Normal Distribution

• Last updated
• Save as PDF
• Page ID 20074

All hypotheses tests have the same basic steps:

• The alternative hypothesis, \(H_{a}\), never has a symbol that contains an equal sign.
• The alternative hypothesis, \(H_{a}\), tells you if the test is left, right, or two-tailed. It is the key to conducting the appropriate test.
• In a hypothesis test problem, you may see words such as "the level of significance is 1%." The "1%" is the preconceived or preset \(\alpha\). The statistician setting up the hypothesis test selects the value of α to use before collecting the sample data. If no level of significance is given, a common standard to use is \(\alpha = 0.05\).
• When you calculate the \(p\)-value and draw the picture, the \(p\)-value is the area in the left tail, the right tail, or split evenly between the two tails. For this reason, we call the hypothesis test left, right, or two tailed.
• Never, ever, Accept the Null Hypothesis.
• Thinking about the meaning of the \(p\)-value: A data analyst (and anyone else) should have more confidence that he made the correct decision to reject the null hypothesis with a smaller \(p\)-value (for example, 0.001 as opposed to 0.04) even if using the 0.05 level for alpha. Similarly, for a large p -value such as 0.4, as opposed to a \(p\)-value of 0.056 (\(\alpha = 0.05\) is less than either number), a data analyst should have more confidence that she made the correct decision in not rejecting the null hypothesis. This makes the data analyst use judgment rather than mindlessly applying rules.
• Determine the conclusion : What does the decision mean in terms of the problem given?

Direction of Tail

Example \(\pageindex{1}\).

\(H_{0}: \mu \geq 5, H_{a}: \mu < 5\)

Test of a single population mean. \(H_{a}\) tells you the test is left-tailed. The picture of the \(p\)-value is as follows:

Exercise \(\PageIndex{1}\)

\(H_{0}: \mu \geq 10, H_{a}: \mu < 10\)

Assume the \(p\)-value is 0.0935. What type of test is this? Draw the picture of the \(p\)-value.

left-tailed test

Example \(\PageIndex{2}\)

\(H_{0}: \mu \leq 0.2, H_{a}: \mu > 0.2\)

This is a test of a single population proportion. \(H_{a}\) tells you the test is right-tailed . The picture of the p -value is as follows:

Exercise \(\PageIndex{2}\)

\(H_{0}: \mu \leq 1, H_{a}: \mu > 1\)

Assume the \(p\)-value is 0.1243. What type of test is this? Draw the picture of the \(p\)-value.

right-tailed test

Example \(\PageIndex{3}\)

\(H_{0}: \mu = 50, H_{a}: \mu \neq 50\)

This is a test of a single population mean. \(H_{a}\) tells you the test is two-tailed . The picture of the \(p\)-value is as follows.

Exercise \(\PageIndex{3}\)

\(H_{0}: \mu = 0.5, H_{a}: \mu \neq 0.5\)

Assume the p -value is 0.2564. What type of test is this? Draw the picture of the \(p\)-value.

two-tailed test

Full Hypothesis Test Examples

Example \(\pageindex{4}\).

Jeffrey, as an eight-year old, established a mean time of 16.43 seconds for swimming the 25-yard freestyle, with a standard deviation of 0.8 seconds. His dad, Frank, thought that Jeffrey could swim the 25-yard freestyle faster using goggles. Frank bought Jeffrey a new pair of expensive goggles and timed Jeffrey for 15 25-yard freestyle swims. For the 15 swims, Jeffrey's mean time was 16 seconds. Frank thought that the goggles helped Jeffrey to swim faster than the 16.43 seconds. Conduct a hypothesis test using a preset α = 0.05. Assume that the swim times for the 25-yard freestyle are normal.

\(P\)-value Solution

Determine the hypothesis :

Since the problem is about a mean, this is a test of a single population mean.

For Jeffrey to swim faster, his time will be less than 16.43 seconds. So the claim will be that he can swim it in less time than 16.43 seconds.

\(H_{0}: \mu \geq 16.43\)

\(H_{a}: \mu < 16.43\) (claim)

The "\(<\)" in the alternative hypothesis tells you this is left-tailed.

Calculate the evidence :

Use the Standard Normal Distribution since the population standard deviation is given.

Calculate the test statistic using the same formula as a \(z\)-score using the Central Limit Theorem.

\[z=\frac{\bar{x}-\mu}{\frac{\sigma}{\sqrt{n}}}\nonumber\]

\(\mu = 16.43\) comes from \(H_{0}\) and not the data. \(\sigma = 0.8\) and \(n = 15\). Which gives

\[z=\frac{16-16.43}{\frac{0.8}{\sqrt{15}}}=\frac{-0.43}{\frac{0.8}{3.87298}}=\frac{-0.43}{0.20656}=-2.0817\nonumber\]

Now calculate the p-value based on the test statistic found.

This is a left-tailed test, so use the Excel formula \(=\text{NORM.S.DIST}(z,\text{true})\).

In this case, we found \(z\), which is the test statistic, to be \(z=-2.0817\).

Use the Excel formula \(=\text{NORM.S.DIST}(-2.0817,\text{true})=0.0187\).

So the \(p\text{-value} = 0.0187\). This is the area to the left of the sample mean, which is given as 16.

Make a decision:

Interpretation of the \(p-\text{value}\): If \(H_{0}\) is true, there is a 0.0187 probability (1.87%) that Jeffrey's mean time to swim the 25-yard freestyle is 16 seconds or less. Because a 1.87% chance is small, the mean time of 16 seconds or less is unlikely to have happened randomly. It is a rare event.

\(\mu = 16.43\) comes from \(H_{0}\). Our assumption gives \(\mu = 16.43\).

\(\alpha\) is the minimum area that could be considered to make our result significant.

Compare \(\alpha\) and the \(p\text{-value}\)

• If \(p\text{-value}\) is less than the \(\alpha\) then we will Reject \(H_0\).
• If \(\alpha\) is less than the \(p\text{-value}\) then we will Fail to Reject \(H_0\).

\(\alpha = 0.05\) and \(p\text{-value} = 0.0187\), so \(p\text{-value}<\alpha\)

Since \(p\text{-value}<\alpha\), reject \(H_{0}\).

Conclusion:

This means that you reject \(\mu \geq 16.43\).

There is sufficient evidence to support the claim that Jeffrey's mean swim time for the 25-yard freestyle is less than 16.43 seconds.

Critical Value Solution

Determine the hypothesis (Same as the \(P\)-value solution) :

Calculate the critical value. Use the Standard Normal Distribution, Critical Value, Right-tail Excel formula: \(=\text{NORM.S.INV}(\alpha)\).

In this problem, the \(\alpha=0.05\), so use \(=\text{NORM.S.INV}(0.05)=-1.64485\)

Graph the critical value and the test statistic along the number line of the Standard Normal Distribution graph.

Since this is left-tailed, everything less than the critical value, \(\text{CV}=-1.64485\) will be the rejection region.

Since the test statistic, \(z=-2.0817\) is less than the critical value, \(\text{CV}=-1.64485, the decision will be to Reject the Null Hypothesis.

Conclusion (Same as the \(P\)-value solution):

The Type I and Type II errors for this problem are as follows :

The Type I error is to conclude that Jeffrey swims the 25-yard freestyle, on average, in less than 16.43 seconds when, in fact, he actually swims the 25-yard freestyle, on average, in 16.43 seconds. (Reject the null hypothesis when the null hypothesis is true.)

The Type II error is that there is not evidence to conclude that Jeffrey swims the 25-yard free-style, on average, in less than 16.43 seconds when, in fact, he actually does swim the 25-yard free-style, on average, in less than 16.43 seconds. (Do not reject the null hypothesis when the null hypothesis is false.)

Exercise \(\PageIndex{4}\)

The mean throwing distance of a football for a Marco, a high school freshman quarterback, is 40 yards, with a standard deviation of two yards. The team coach tells Marco to adjust his grip to get more distance. The coach records the distances for 20 throws. For the 20 throws, Marco’s mean distance was 45 yards. The coach thought the different grip helped Marco throw farther than 40 yards. Conduct a hypothesis test using a preset \(\alpha = 0.01\). Assume the throw distances for footballs are normal. Use the critical value method.

For Marco to throw farther, his distance will be greater than 40 yards. So the claim will be that he can throw farther than 40 yards.

\(H_{0}: \mu \leq 40\)

\(H_{a}: \mu > 40\) (claim)

The "\(>\)" in the alternative hypothesis tells you this is right-tailed.

Calculate the critical value. Use the Standard Normal Distribution, Critical Value, Right-tail Excel formula: \(=\text{NORM.S.INV}(1-\alpha)\).

In this problem, the \(\alpha=0.01\), so use \(=\text{NORM.S.INV}(1-0.01)=2.3263\)

\(\mu = 40\) comes from \(H_{0}\) and not the data. \(\sigma = 2\) and \(n = 20\). Which gives

\[z=\frac{45-40}{\frac{2}{\sqrt{20}}}=\frac{5}{\frac{2}{4.4721}}=\frac{5}{0.4472}=11.1803\nonumber\]

Since this is right-tailed, everything greater than the critical value, \(\text{CV}=2.3263\) will be the rejection region.

Since the test statistic, \(z=11.1803\) is greater than the critical value, \(\text{CV}=2.3263\), the decision will be to Reject the Null Hypothesis.

This means that you reject \(\mu \leq 40\).

There is sufficient evidence to support the claim that the change in Marco's grip improved his throwing distance to give a mean throw distance is greater than 40 yards.

Example \(\PageIndex{5}\)

A college football coach thought that his players could bench press a mean weight of 275 pounds. It is known that the standard deviation is 55 pounds. Three of his players thought that the mean weight was great than that amount. They asked 30 of their teammates for their estimated maximum lift on the bench press exercise. The data ranged from 205 pounds to 385 pounds. The actual different weights are given below

Conduct a \(p\)-value hypothesis test using a 2.5% level of significance to determine if the bench press mean is more than 275 pounds.

Since the problem is about a mean weight, this is a test of a single population mean.

\(H_{0}: \mu \leq 275\)

\(H_{a}: \mu > 275\) (claim)

The "\(>\)" in the alternative hypothesis tells you this is a right-tailed test.

Calculate the test statistic using the same formula as the \(z\)-score using the Central Limit Theorem.

\(\mu = 275\) comes from \(H_{0}\) and not the data. \(\sigma=55\) and \(n=30\). The problem does not give the sample mean, so that will need to be calculated using the data.

Enter the data into Excel, and use the Excel formula \(=\text{AVERAGE}()\) to find \(\bar{x}=286.2\).

\[z=\frac{286.2-275}{\frac{55}{\sqrt{30}}}=\frac{11.2}{\frac{2}{5.4772}}=\frac{11.2}{10.04}=1.11536\nonumber\]

Now calculate the \(p\)-value based on the test statistic found.

This is a right-tailed test, so use the Excel formula \(=1-\text{NORM.S.DIST}(z,\text{true})\).

In this case, we found \(z\), which is the test statistic, to be \(z=1.11536\).

Use the Excel formula \(=1-\text{NORM.S.DIST}(1.11536,\text{true})=0.132348\).

So the \(p\text{-value} = 0.132348\).

Interpretation of the p -value: If \(H_{0}\) is true, then there is a 0.1331 probability (13.23%) that the football players can lift a mean weight of 286.2 pounds or more. Because a 13.23% chance is large enough, a mean weight lift of 286.2 pounds or more is not a rare event.

Make a decision :

\(\alpha = 0.025\) and \(p\)-value \(= 0.1323\)

Since \(\alpha < p\text{-value}\), do not reject \(H_{0}\).

Conclusion: At the 2.5% level of significance, from the sample data, there is not sufficient evidence to conclude that the true mean weight lifted is more than 275 pounds.

The hypothesis test itself has an established process. This can be summarized as follows:

• Determine \(H_{0}\) and \(H_{a}\). Remember, they are contradictory.
• Find the evidence: Draw a graph, calculate the test statistic, and use the test statistic to calculate the \(p\text{-value}\). (A z -score and a t -score are examples of test statistics.)
• Compare the preconceived α with the p -value, make a decision (reject or do not reject H 0 ).
• Write a clear conclusion using English sentences.

Notice that in performing the hypothesis test, you use \(\alpha\) and not \(\beta\). \(\beta\) is needed to help determine the sample size of the data that is used in calculating the \(p\text{-value}\). Remember that the quantity \(1 – \beta\) is called the Power of the Test . A high power is desirable. If the power is too low, statisticians typically increase the sample size while keeping α the same.If the power is low, the null hypothesis might not be rejected when it should be.

Exercise \(\PageIndex{5}\)

Assume \(H_{0}: \mu = 9\) and \(H_{a}: \mu < 9\). Is this a left-tailed, right-tailed, or two-tailed test?

This is a left-tailed test.

Exercise \(\PageIndex{6}\)

Assume \(H_{0}: \mu \leq 6\) and \(H_{a}: \mu > 6\). Is this a left-tailed, right-tailed, or two-tailed test?

Exercise \(\PageIndex{7}\)

Assume \(H_{0}: p = 0.25\) and \(H_{a}: p \neq 0.25\). Is this a left-tailed, right-tailed, or two-tailed test?

This is a two-tailed test.

Exercise \(\PageIndex{8}\)

Draw the general graph of a left-tailed test.

Exercise \(\PageIndex{9}\)

Draw the graph of a two-tailed test.

Exercise \(\PageIndex{10}\)

A bottle of water is labeled as containing 16 fluid ounces of water. You believe it is less than that. What type of test would you use?

Exercise \(\PageIndex{11}\)

Your friend claims that his mean golf score is 63. You want to show that it is higher than that. What type of test would you use?

a right-tailed test

Exercise \(\PageIndex{12}\)

A bathroom scale claims to be able to identify correctly any weight within a pound. You think that it cannot be that accurate. What type of test would you use?

Exercise \(\PageIndex{13}\)

You flip a coin and record whether it shows heads or tails. You know the probability of getting heads is 50%, but you think it is less for this particular coin. What type of test would you use?

a left-tailed test

Exercise \(\PageIndex{14}\)

If the alternative hypothesis has a not equals ( \(\neq\) ) symbol, you know to use which type of test?

Exercise \(\PageIndex{15}\)

Assume the null hypothesis states that the mean is at least 18. Is this a left-tailed, right-tailed, or two-tailed test?

Exercise \(\PageIndex{16}\)

Assume the null hypothesis states that the mean is at most 12. Is this a left-tailed, right-tailed, or two-tailed test?

Exercise \(\PageIndex{17}\)

Assume the null hypothesis states that the mean is equal to 88. The alternative hypothesis states that the mean is not equal to 88. Is this a left-tailed, right-tailed, or two-tailed test?

• Data from Amit Schitai. Director of Instructional Technology and Distance Learning. LBCC.
• Data from Bloomberg Businessweek . Available online at www.businessweek.com/news/2011- 09-15/nyc-smoking-rate-falls-to-record-low-of-14-bloomberg-says.html.
• Data from energy.gov. Available online at http://energy.gov (accessed June 27. 2013).
• Data from Gallup®. Available online at www.gallup.com (accessed June 27, 2013).
• Data from Growing by Degrees by Allen and Seaman.
• Data from La Leche League International. Available online at www.lalecheleague.org/Law/BAFeb01.html.
• Data from the American Automobile Association. Available online at www.aaa.com (accessed June 27, 2013).
• Data from the American Library Association. Available online at www.ala.org (accessed June 27, 2013).
• Data from the Bureau of Labor Statistics. Available online at http://www.bls.gov/oes/current/oes291111.htm .
• Data from the Centers for Disease Control and Prevention. Available online at www.cdc.gov (accessed June 27, 2013)
• Data from the U.S. Census Bureau, available online at quickfacts.census.gov/qfd/states/00000.html (accessed June 27, 2013).
• Data from the United States Census Bureau. Available online at www.census.gov/hhes/socdemo/language/.
• Data from Toastmasters International. Available online at http://toastmasters.org/artisan/deta...eID=429&Page=1 .
• Data from Weather Underground. Available online at www.wunderground.com (accessed June 27, 2013).
• Federal Bureau of Investigations. “Uniform Crime Reports and Index of Crime in Daviess in the State of Kentucky enforced by Daviess County from 1985 to 2005.” Available online at http://www.disastercenter.com/kentucky/crime/3868.htm (accessed June 27, 2013).
• “Foothill-De Anza Community College District.” De Anza College, Winter 2006. Available online at research.fhda.edu/factbook/DA...t_da_2006w.pdf.
• Johansen, C., J. Boice, Jr., J. McLaughlin, J. Olsen. “Cellular Telephones and Cancer—a Nationwide Cohort Study in Denmark.” Institute of Cancer Epidemiology and the Danish Cancer Society, 93(3):203-7. Available online at http://www.ncbi.nlm.nih.gov/pubmed/11158188 (accessed June 27, 2013).
• Rape, Abuse & Incest National Network. “How often does sexual assault occur?” RAINN, 2009. Available online at www.rainn.org/get-information...sexual-assault (accessed June 27, 2013).

Contributors and Attributions

Barbara Illowsky and Susan Dean (De Anza College) with many other contributing authors. Content produced by OpenStax College is licensed under a Creative Commons Attribution License 4.0 license. Download for free at http://cnx.org/contents/[email protected] .

Monday, June 2, 2014

Normal approximation of the binomial distribution in excel 2101 and excel 2013.

This is one of the following four articles on the Binomial Distribution in Excel

• Overview of the Binomial Distribution in Excel 2010 and Excel 2013
• Solving Problems With the Binomial Distribution in Excel 2010 and Excel 2013
• Normal Approximation of the Binomial Distribution in Excel 2010 and Excel 2013
• Distributions Related to the Binomial Distribution

Normal Approximation of the Binomial Distribution in Excel

For certain values of n and p, the binomial distribution can be closely approximated by the normal distribution. This allows for quick and easy normal-distribution-based hypothesis testing and confidence interval creation for sample data that are binomially distributed.

There are several different guidelines specifying the ranges of n and p that are appropriate for approximation of the binomial distribution with the normal distribution. The normal approximation of the binomial distribution works when n is large enough and p and q are not close to zero.

The most widely-applied guideline is the following: np > 5 and nq > 5. Other sources state that normal approximation of the binomial distribution is appropriate only when np > 10 and nq > 10. this manual will utilize the first rule-of-thumb mentioned here, i.e., np > 5 and nq > 5.

Each unique binomial distribution is completely described by its two parameters n (number of trials) and p probability of success in each trial). Each unique normal distribution is completely described by its two parameters μ (mean) and σ (standard deviation).

The Excel formula to calculate the PDF for the normal distribution at X is given as follows:

NORM.DIST(X,μ,σ,FALSE)

The Excel formula to calculate the PDF for the binomial distribution at X is given as follows:

BINOM.DIST(X,n,p,FALSE)

This is closely approximated for the appropriate ranges of n and p with the normal distribution in Excel as follows:

NORM.DIST(X,np,SQRT(npq),FALSE)

The CDF is calculated by substituting TRUE for FALSE in the Excel formula.

Following is a comparison of the normal approximation of the binomial distribution as n increases. Both the PDF and the CDF for binomial distribution and the normal distribution’s approximation are calculated. The PDF is very close even at low values of n. The CDF remains significantly different in this case until n has reached 80.

Continuity Correction

The continuity correction is an addition of 0.5 to the normal CDF’s X value. This makes the normal CDF more closely approximate the binomial CDF. The continuity correction of 0.5 is added to X to calculate the normal CDF in the following Excel chart. It can be seen that the normal CDF is a significantly better approximation of the binomial CDF with the continuity correction than without it.

A continuity correction factor of +0.5 is applied to the X value when using a continuous function (the normal distribution) to approximate the CDF of a discrete function (the binomial distribution).

The binomial’s CDF is calculated in Excel by this formula:

BINOM.DIST(X,n,p,TRUE)

The normal approximation would now be calculated by the following formula with the continuity correction of 0.5 added to X

NORM.DIST(X+0.5,np,SQRT(npq),TRUE)

The continuity correction is much less important than it used to be. Exact values of the binomial’s PDF and CDF can be calculated with specific Excel formulas. The normal approximation of the binomial distribution allows for quick and easy normal-distribution-based analysis tools such as hypothesis testing and confidence intervals to be applied to binomially-distributed data. The continuity correction is not used when performing these techniques.

Excel Master Series Blog Directory

Click Here To See a List Of All Statistical Topics And Articles In This Blog

You Will Become an Excel Statistical Master!

2 comments:

Before you start gambling for real money aici you should take the time to read the rules enough times to know the game inside and out. Without a solid understanding of the basics, you won’t be able properly grasp advanced strategy.

I enjoy reading this post. This is so informative. lanai screening Port Charlotte, FL

Blog Directory

Become an excel statistical master.

It's a Full Easy-To-Follow MBA Course in Business Statistics ALL IN EXCEL & MUCH Clearer Than Your Text Book

Advanced Statistical and Solver Optimization Consulting Service - Fast and Completed in Excel

We Respect Your Email Privacy

Need Advanced Statistical or Solver Optimization Consulting? I Can Help. Email Me At: mark at ExcelMasterSeries.com Expert and Professional All Work Completed in Excel So You Can Work With The Final Data On Your Computer

Or Subscribe by Email

Enter your email address:

Statistical Topics and Articles In Each Topic

• Creating a Histogram With the Histogram Data Analysis Tool in Excel
• Creating an Automatically Updating Histogram in 7 Steps in Excel With Formulas and a Bar Chart
• Creating a Bar Chart in 7 Steps in Excel 2010 and Excel 2013
• Combinations in Excel 2010 and Excel 2013
• Permutations in Excel 2010 and Excel 2013
• Overview of the Normal Distribution
• Normal Distribution’s PDF (Probability Density Function) in Excel 2010 and Excel 2013
• Normal Distribution’s CDF (Cumulative Distribution Function) in Excel 2010 and Excel 2013
• Solving Normal Distribution Problems in Excel 2010 and Excel 2013
• Overview of the Standard Normal Distribution in Excel 2010 and Excel 2013
• An Important Difference Between the t and Normal Distribution Graphs
• The Empirical Rule and Chebyshev’s Theorem in Excel – Calculating How Much Data Is a Certain Distance From the Mean
• Demonstrating the Central Limit Theorem In Excel 2010 and Excel 2013 In An Easy-To-Understand Way
• Overview of the t- Distribution
• Overview of Hypothesis Tests Using the Normal Distribution in Excel 2010 and Excel 2013
• One-Sample z-Test in 4 Steps in Excel 2010 and Excel 2013
• 2-Sample Unpooled z-Test in 4 Steps in Excel 2010 and Excel 2013
• Overview of the Paired (Two-Dependent-Sample) z-Test in 4 Steps in Excel 2010 and Excel 2013
• Overview of t-Tests: Hypothesis Tests that Use the t-Distribution
• 1-Sample t-Test in 4 Steps in Excel 2010 and Excel 2013
• Excel Normality Testing For the 1-Sample t-Test in Excel 2010 and Excel 2013
• 1-Sample t-Test – Effect Size in Excel 2010 and Excel 2013
• 1-Sample t-Test Power With G*Power Utility
• Wilcoxon Signed-Rank Test in 8 Steps As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013
• Sign Test As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013
• 2-Independent-Sample Pooled t-Test in 4 Steps in Excel 2010 and Excel 2013
• Excel Variance Tests: Levene’s, Brown-Forsythe, and F Test For 2-Sample Pooled t-Test in Excel 2010 and Excel 2013
• Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro Wilk Tests For Two-Sample Pooled t-Test
• Two-Independent-Sample Pooled t-Test - All Excel Calculations
• 2- Sample Pooled t-Test – Effect Size in Excel 2010 and Excel 2013
• 2-Sample Pooled t-Test Power With G*Power Utility
• Mann-Whitney U Test in 12 Steps in Excel as 2-Sample Pooled t-Test Nonparametric Alternative in Excel 2010 and Excel 2013
• 2- Sample Pooled t-Test = Single-Factor ANOVA With 2 Sample Groups
• 2-Independent-Sample Unpooled t-Test in 4 Steps in Excel 2010 and Excel 2013
• Variance Tests: Levene’s Test, Brown-Forsythe Test, and F-Test in Excel For 2-Sample Unpooled t-Test
• Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro-Wilk For 2-Sample Unpooled t-Test
• 2-Sample Unpooled t-Test Excel Calculations, Formulas, and Tools
• Effect Size for a 2-Independent-Sample Unpooled t-Test in Excel 2010 and Excel 2013
• Test Power of a 2-Independent Sample Unpooled t-Test With G-Power Utility
• Paired t-Test in 4 Steps in Excel 2010 and Excel 2013
• Excel Normality Testing of Paired t-Test Data
• Paired t-Test Excel Calculations, Formulas, and Tools
• Paired t-Test – Effect Size in Excel 2010, and Excel 2013
• Paired t-Test – Test Power With G-Power Utility
• Wilcoxon Signed-Rank Test in 8 Steps As a Paired t-Test Alternative
• Sign Test in Excel As A Paired t-Test Alternative
• Hypothesis Tests of Proportion Overview (Hypothesis Testing On Binomial Data)
• 1-Sample Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013
• 2-Sample Pooled Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013
• How To Build a Much More Useful Split-Tester in Excel Than Google's Website Optimizer
• Chi-Square Independence Test in 7 Steps in Excel 2010 and Excel 2013
• Overview of the Chi-Square Goodness-of-Fit Test
• Chi-Square Goodness- of-Fit Test With Pre-Determined Bins Sizes in 7 Steps in Excel 2010 and Excel 2013
• Chi-Square Goodness-Of-Fit-Normality Test in 9 Steps in Excel 2010 and Excel 2013
• F-Test in 6 Steps in Excel 2010 and Excel 2013
• Normality Testing For F Test In Excel 2010 and Excel 2013
• Levene’s and Brown- Forsythe Tests: F-Test Alternatives in Excel
• Overview of Correlation In Excel 2010 and Excel 2013
• Pearson Correlation in 3 Steps in Excel 2010 and Excel 2013
• Pearson Correlation – Calculating r Critical and p Value of r in Excel
• Spearman Correlation in 6 Steps in Excel 2010 and Excel 2013
• z-Based Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
• t-Based Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
• Minimum Sample Size to Limit the Size of a Confidence interval of a Population Mean
• Confidence Interval of Population Proportion in 2 Steps in Excel 2010 and Excel 2013
• Min Sample Size of Confidence Interval of Proportion in Excel 2010 and Excel 2013
• Overview of Simple Linear Regression in Excel 2010 and Excel 2013
• Complete Simple Linear Regression Example in 7 Steps in Excel 2010 and Excel 2013
• Residual Evaluation For Simple Regression in 8 Steps in Excel 2010 and Excel 2013
• Residual Normality Tests in Excel – Kolmogorov-Smirnov Test, Anderson-Darling Test, and Shapiro-Wilk Test For Simple Linear Regression
• Evaluation of Simple Regression Output For Excel 2010 and Excel 2013
• All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013
• Prediction Interval of Simple Regression in Excel 2010 and Excel 2013
• Basics of Multiple Regression in Excel 2010 and Excel 2013
• Complete Multiple Linear Regression Example in 6 Steps in Excel 2010 and Excel 2013
• Multiple Linear Regression’s Required Residual Assumptions
• Normality Testing of Residuals in Excel 2010 and Excel 2013
• Evaluating the Excel Output of Multiple Regression
• Estimating the Prediction Interval of Multiple Regression in Excel
• Regression - How To Do Conjoint Analysis Using Dummy Variable Regression in Excel
• Logistic Regression Overview
• Logistic Regression in 6 Steps in Excel 2010 and Excel 2013
• R Square For Logistic Regression Overview
• Excel R Square Tests: Nagelkerke, Cox and Snell, and Log-Linear Ratio in Excel 2010 and Excel 2013
• Likelihood Ratio Is Better Than Wald Statistic To Determine if the Variable Coefficients Are Significant For Excel 2010 and Excel 2013
• Excel Classification Table: Logistic Regression’s Percentage Correct of Predicted Results in Excel 2010 and Excel 2013
• Hosmer- Lemeshow Test in Excel – Logistic Regression Goodness-of-Fit Test in Excel 2010 and Excel 2013
• Overview of Single-Factor ANOVA
• Single-Factor ANOVA in 5 Steps in Excel 2010 and Excel 2013
• Shapiro-Wilk Normality Test in Excel For Each Single-Factor ANOVA Sample Group
• Kruskal-Wallis Test Alternative For Single Factor ANOVA in 7 Steps in Excel 2010 and Excel 2013
• Levene’s and Brown-Forsythe Tests in Excel For Single-Factor ANOVA Sample Group Variance Comparison
• Single-Factor ANOVA - All Excel Calculations
• Overview of Post-Hoc Testing For Single-Factor ANOVA
• Tukey-Kramer Post-Hoc Test in Excel For Single-Factor ANOVA
• Games-Howell Post-Hoc Test in Excel For Single-Factor ANOVA
• Overview of Effect Size For Single-Factor ANOVA
• ANOVA Effect Size Calculation Eta Squared in Excel 2010 and Excel 2013
• ANOVA Effect Size Calculation Psi – RMSSE – in Excel 2010 and Excel 2013
• ANOVA Effect Size Calculation Omega Squared in Excel 2010 and Excel 2013
• Power of Single-Factor ANOVA Test Using Free Utility G*Power
• Welch’s ANOVA Test in 8 Steps in Excel Substitute For Single-Factor ANOVA When Sample Variances Are Not Similar
• Brown-Forsythe F-Test in 4 Steps in Excel Substitute For Single-Factor ANOVA When Sample Variances Are Not Similar
• Two-Factor ANOVA With Replication in 5 Steps in Excel 2010 and Excel 2013
• Variance Tests: Levene’s and Brown-Forsythe For 2-Factor ANOVA in Excel 2010 and Excel 2013
• Shapiro-Wilk Normality Test in Excel For 2-Factor ANOVA With Replication
• 2-Factor ANOVA With Replication Effect Size in Excel 2010 and Excel 2013
• Excel Post Hoc Tukey’s HSD Test For 2-Factor ANOVA With Replication
• 2-Factor ANOVA With Replication – Test Power With G-Power Utility
• Scheirer-Ray-Hare Test Alternative For 2-Factor ANOVA With Replication
• Two-Factor ANOVA Without Replication in Excel 2010 and Excel 2013
• Randomized Block Design ANOVA in Excel 2010 and Excel 2013
• Single-Factor Repeated-Measures ANOVA in 4 Steps in Excel 2010 and Excel 2013
• Sphericity Testing in 9 Steps For Repeated Measures ANOVA in Excel 2010 and Excel 2013
• Effect Size For Repeated-Measures ANOVA in Excel 2010 and Excel 2013
• Friedman Test in 3 Steps For Repeated-Measures ANOVA in Excel 2010 and Excel 2013
• Single-Factor ANCOVA in 8 Steps in Excel 2010 and Excel 2013
• Creating a Box Plot in 8 Steps in Excel
• Creating a Normal Probability Plot With Adjustable Confidence Interval Bands in 9 Steps in Excel With Formulas and a Bar Chart
• Chi-Square Goodness-of-Fit Test For Normality in 9 Steps in Excel
• Kolmogorov-Smirnov, Anderson-Darling, and Shapiro-Wilk Normality Tests in Excel
• Mann-Whitney U Test in 12 Steps in Excel
• Wilcoxon Signed-Rank Test in 8 Steps in Excel
• Sign Test in Excel
• Friedman Test in 3 Steps in Excel
• Scheirer-Ray-Hope Test in Excel
• Welch's ANOVA Test in 8 Steps Test in Excel
• Brown-Forsythe F Test in 4 Steps Test in Excel
• Levene's Test and Brown-Forsythe Variance Tests in Excel
• Chi-Square Independence Test in 7 Steps in Excel
• Chi-Square Goodness-of-Fit Tests in Excel
• Chi-Square Population Variance Test in Excel
• Tukey's HSD Post Hoc Test in Excel
• Tukey-Kramer Post Hoc Test in Excel
• Games-Howell Post Hoc Test in Excel
• Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013
• Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013
• Interactive Graph of the Chi-Square Distribution in Excel 2010 and Excel 2013
• Interactive Graph of the t-Distribution in Excel 2010 and Excel 2013
• Interactive Graph of the t-Distribution’s PDF in Excel 2010 and Excel 2013
• Interactive Graph of the t-Distribution’s CDF in Excel 2010 and Excel 2013
• Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013
• Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013
• Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013
• Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013
• Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013
• Solving Uniform Distribution Problems in Excel 2010 and Excel 2013
• Solving Multinomial Distribution Problems in Excel 2010 and Excel 2013
• Solving Exponential Distribution Problems in Excel 2010 and Excel 2013
• Solving Beta Distribution Problems in Excel 2010 and Excel 2013
• Solving Gamma Distribution Problems in Excel 2010 and Excel 2013
• Solving Poisson Distribution Problems in Excel 2010 and Excel 2013
• Maximizing Lead Generation With Excel Solver
• Minimizing Cutting Stock Waste With Excel Solver
• Optimal Investment Selection With Excel Solver
• Minimizing the Total Cost of Shipping From Multiple Points To Multiple Points With Excel Solver
• Knapsack Loading Problem in Excel Solver – Optimizing the Loading of a Limited Compartment
• Optimizing a Bond Portfolio With Excel Solver
• Travelling Salesman Problem in Excel Solver – Finding the Shortest Path To Reach All Customers
• Overview of the Chi-Square Population Variance Test in Excel 2010 and Excel 2013
• Simplifying Excel Pivot Table and Pivot Chart Setup
• Top 10 Excel SEO Functions - You'll Like These
• Forecasting With Exponential Smoothing in Excel
• Forecasting With the Weighted Moving Average in Excel
• Forecasting With the Simple Moving Average in Excel
• Simplifying Excel Lookup Functions: VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE, and OFFSET
• VLOOKUP - Just Like Looking Up a Number in a Telephone Book
• VLOOKUP To Look Up a Discount in a Distant Database
• Measures of Central Tendency in Excel
• Simplifying Excel Functions: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, and AVERAGEIFS
• Simplifying Excel Form Controls: Check Box, Option Button, Spin Button, and Scroll Bar
• Scenario Analysis in Excel With Option Buttons and the What-If Scenario Manager
• Simplifying Goal Seek in Excel

How to Calculate Normal Distribution Probabilities in Excel

A normal distribution is the most commonly used distribution in all of statistics.

To calculate probabilities related to the normal distribution in Excel, you can use the NORMDIST function, which uses the following basic syntax:

• x : The value of interest in the normal distribution
• mean : The mean of the normal distribution
• standard_dev : The standard deviation of the normal distribution
• cumulative : Whether to calculate cumulative probabilities (this is usually TRUE)

The following examples show how to use this function to calculate probabilities related to the normal distribution.

Example 1: Calculate Probability Less than Some Value

Suppose the scores for an exam are normally distributed with a mean of 90 and a standard deviation of 10.

Find the probability that a randomly selected student receives a score less than 80.

The following screenshot shows how to use the NORMDIST() function in Excel to calculate this probability:

The probability that a randomly selected student receives a score less than 80 is 0.1587 .

Example 2: Calculate Probability Greater than Some Value

Find the probability that a randomly selected student receives a score greater than 80.

To find this probability, we can simply do 1 – NORMDIST() in Excel as follows:

The probability that a randomly selected student receives a score greater than 80 is 0.1587 .

Example 3: Calculate Probability Between Two Values

Find the probability that a randomly selected student receives a score between 87 and 93.

To find this probability, we can subtract the larger value of NORMDIST() from the smaller value of another NORMDIST() in Excel as follows:

The probability that a randomly selected student receives a score between 87 and 93 is 0.2358 .

Additional Resources

The following tutorials explain how to perform other tasks related to the normal distribution in Excel:

How to Generate a Normal Distribution in Excel How to Calculate Z-Scores in Excel How to Make a Bell Curve in Excel

How to Calculate Cronbach’s Alpha in SAS (With Example)

How to label variables in sas (with example), related posts, how to create a stem-and-leaf plot in spss, how to create a correlation matrix in spss, how to convert date of birth to age..., excel: how to highlight entire row based on..., how to add target line to graph in..., excel: how to use if function with negative..., excel: how to use if function with text..., excel: how to use greater than or equal..., excel: how to use if function with multiple..., pandas: how to rename only the last column....