Tuesday, June 3, 2014

Normal Distribution’s CDF in Excel 2010 and Excel 2013

This is one of the following eight articles on the normal distribution in Excel

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

The Normal Distribution’s

CDF (Cumulative

Distribution Function)in

Excel

The normal distribution’s CDF (Cumulative Distribution Function) equals the probability that sampled point from a normal-distributed population has a value UP TO X given the population’s mean, µ, and standard deviation, σ.

The normal distribution’s CDF is expressed as F(X,µ,σ).

normal distribution, cdf, cumulative distribution function, excel, excel 20101, excel 2013, statistics
(Click on Image To See a Larger Version)

Unlike the normal distribution’s PDF, the CDF has no convenient closed form of its equation, which is the integral just shown.

In Excel 2010 and beyond, the normal distribution’s CDF must be calculated by the following Excel formula:

F(X,µ,σ) = NORM.DIST(X,µ,σ,TRUE)

The Excel formula parameter “TRUE” indicates that the formula is cumulative, i.e., it is calculating the normal distribution’s CDF (Cumulative Distribution Function) and not its PDF (Probability Density Function).

Prior to Excel 2010, the normal distribution’s PDF was calculated in Excel by this formula:

F(X,µ,σ) = NORMDIST(X,µ,σ,TRUE)

Statistical formulas that worked in Excel versions prior to 2010 will also work in Excel 2010 and 2013.

Note that the CDF has asymptotic values of 0 as X decreases and an asymptotic value of 1 as X increases as shown in the following Excel-generated image:

normal distribution, cdf, cumulative distribution function, excel, excel 20101, excel 2013, statistics
(Click on Image To See a Larger Version)

The normal distribution’s CDF has a value of exactly 0.5 when X equals the population mean. This indicates that 50 percent of the entire area under the normal distribution’s PDF is contained under the curve before X reaches a value of the population mean. The underlying meaning is that a randomly-sample point from a normally-distributed population has a 50 percent chance of having a value less than or equal the population mean. This can be seen in the Excel-generated graph of the normal distribution’s PDF as follows:

normal distribution, cdf, cumulative distribution function, excel, excel 20101, excel 2013, statistics
(Click on Image To See a Larger Version)

This CDF always has a value of 0.15866 when X is one standard deviation below the mean. This indicates that 15.866 percent of the area under the normal distribution’s PDF curve occurs before X reaches the value of the point that is one standard deviation below the population’s mean. The underlying meaning is that a randomly-sample point from a normally-distributed population has a 15.866 percent chance of having a value less than or equal the value that is one standard deviation below the population mean.

This CDF always has a value of 0.84135 when X is one standard deviation above the mean. This indicates that 84.135 percent of the area under the normal distribution’s PDF curve occurs before X reaches the value of the point that is one standard deviation below the population’s mean. The underlying meaning is that a randomly-sample point from a normally-distributed population has an 84.135 percent chance of having a value less than or equal the value that is one standard deviation below the population mean.

A randomly-selected point from a normally-distributed population has a 68.269 percent chance of having a value X that is within one standard deviation above or below the mean (84.135 – 15.866 = 68.269). In other words, 68.269 percent of normally-distributed data lie within one standard deviation of the mean. Similar analysis shows that approximately 95 percent of all normally-distributed data lie within two standard deviations of the mean and 99.7 percent of the data are within three standard deviations of the mean. This rule is often referred to as the Empirical Rule or the 68-95-99.5 Rule.

 

Normal Distribution CDF Example in

Excel

Determine the probability that a randomly-selected variable X taken from a normally-distributed population has the value of UP TO 5 if the population mean equals 10 and the population standard deviation equals 5.

X = 5

µ = 10

σ = 5

F(X,µ,σ) = NORM.DIST(X,µ,σ,TRUE)

F(X=5,µ=10,σ=5) = NORM.DIST(5,10,5,TRUE) = 0.15866

There is a 15.866 percent chance that randomly-selected X equals UP TO 5 if X is taken from a normally-distributed population with a population mean µ = 10 and population standard deviation σ = 5. The CDF diagram of this normal distribution curve also shows the probability of X at X = 5 to that value in the following Excel-generated image.

normal distribution, cdf, cumulative distribution function, excel, excel 20101, excel 2013, statistics
(Click on Image To See a Larger Version)

The following PDF diagram of this normal distribution curve shows 15.866 percent of the total area under the bell-shaped curve that is to the left of X = 5.

.normal distribution, cdf, cumulative distribution function, excel, excel 20101, excel 2013, statistics
(Click on Image To See a Larger Version)

 

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!

No comments:

Post a Comment