(Click On Image To See a Larger Version)
This is one of the following eleven articles on creating user-interactive graphs of statistical distributions 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
Overview of the Normal
Distribution
The normal distribution is a family of continuous probability distributions. This is evidenced by the smooth shape of the above graph of a normal distribution’s PDF (Probability Density Function) curve.
The normal distribution is a family of distributions with each unique normal distribution being fully described by its two parameters µ (“mu,” population mean) and s (“sigma,” population standard deviation). The population mean, µ, is a location parameter and the population standard deviation, s, is a scale parameter.
When two different normal distribution curves are plotted on the same set of horizontal and vertical axes, the means determine how shifted one curve is to the left of right of the other curve. The standard deviations detail how much wider or more narrow the first normal curve is than to the second.
The normal distribution is a very commonly occurring continuous probability distribution and is often used in natural and social sciences. The bell-shaped normal distribution curve describes the distribution of many natural phenomena such as the heights or weights of males or females. The normal distribution is also used to describe variables that are the sum of many independent processes and variables whose distributions are not known.
The normal distribution is sometimes referred to as the Gaussian distribution.
Normal Distribution Properties
The normal distribution’s probability density function, f(x), has the following properties:
1) It is symmetric about its population mean µ. Half of the values of a normally-distributed population will be less than (to the left of) the population mean and the other half of the population’s value will be greater than (to the right of) the population mean.
2) Its mode and median are equal to the population mean µ.
3) It is unimodal. This means that it has only one peak, i.e., only one point that is a local maximum.
4) The total area under the normal distribution’s PDF is equal to 1.
5) Each unique normal distribution curve is entirely defined by its two parameters population mean µ and population standard deviation s.
6) The density of the normal distribution’s PDF is the highest at its mean and always decreases as distance from the mean increases.
7) 50 percent of values of a normally-distributed population are less than the population mean and 50 percent of the values are greater than the mean.
8) Approximately 68 percent of the total area under the PDF curve resides within one s from the mean, approximately 95 percent of the total resides within two ss, and approximately 99.7 percent of the total area resides within three ss from the mean. This is sometimes known as the Empirical Rule or the 68-95-99.7 Rule.
9) f(x) is infinitely differentiable.
10) The first derivative of f(x) is positive for all x < µ and negative for all x > µ.
11) The second derivative of f(x) has two inflection points which are located one population standard deviation above and below the population mean. These inflection points are located at x = µ ± s. An inflection point occurs at the point that the 2nd derivative equals zero and changes sign as x continues.
12) It is log-concave. A function f(x) is log-concave if its natural log, ln[f(x)], is concave. A log-concave function does not have multiple separate maxima and its tails are not “too thick.”
Graphing the Distribution’s PDF –
Probability Density Function
Step 1 – Create a Count
The Count becomes the basis for the X and Y values of each data point on the graph. This count will contain 100 points that count from 0 to 100 in increments 1. There are many ways to create a count. This count uses the method ROW() – ROW($B) to increment each cell value in the column by 1.
(Click On Image To See a Larger Version)
Step 2 – Create the X Values
The X values for each data point are created. The X-Axis expander and X-Axis shifter values are part of this calculation because the location and width of the normal distribution’s PDF graph varies significantly when its population mean µ and population standard deviation σ parameters change. The X axis must be properly sized to enable the normal distribution’s PDF curve to be fully visible and fully expanded in the graph.
(Click On Image To See a Larger Version)
Step 3 – Create the Y Values
One Y value is created for each increment of the Count. The Y value of each data point is its PDF value. The normal distribution’s PDF value requires the X value and its two parameters, the population mean µ and population standard deviation σ. The X axis often has to be shifted and expanded in order to view the entire PDF curve fully in a single graph.
(Click On Image To See a Larger Version)
The following Excel-generated graph shows the normal distribution’s PDF (Probability Density Function) for as the X value goes from -5 to 12 with population mean µ = 3 and population standard deviation σ = 2.
The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value.
(Click On Image To See a Larger Version)
The process of creating an Excel area chart and connecting the user inputs to the chart is shown in detail in the section of this manual that provides instructions on how to create an interactive normal distribution PDF curve with outer tails.
Effects of Changing the Mean and Standard Deviation
Increasing the population mean shifts the normal distribution’s PDF curve to the right. Decreasing the population standard deviation decreases the width of the curve.
(Click On Image To See a Larger Version)
Effects of Changing the X-Axis Expander and Shifter
These tools do not change the PDF curve. They change the vantage point so the PDF curve can be viewed more clearly.
(Click On Image To See a Larger Version)
(Click On Image To See a Larger Version)
(Click On Image To See a Larger Version)
Graphing the Normal Distribution’s
CDF – Cumulative Distribution
Function
The following Excel-generated graph shows the normal distribution’s CDF (Cumulative Distribution Function) for µ = 3 and σ = 2 as the X value goes from -5 to 12.
The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value.
(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!
