NORMSINV

Lean Tools and Supplies

The power of using Excel for statistical calculations is often overlooked, however, as people have discovered the need to use well designed tools, Excel has become a valuable tool in doing so.

If you haven't considered using it for this purpose, it would be worth your time to briefly explore how to use Excel to create “bell-shaped” curves (otherwise known as normal curves). In statistics, these curves occur quite frequently but can be difficult to implement in some cases; especially with larger data. In such instances, the power of Excel and its effectiveness come to play because it provides several useful functions (such as NORMSINV, NORMSDIST (z), NORMINV (probability, mean, standard_dev), STANDARDIZE (x, mean, standard_dev), NORMDIST (x, mean, standard_dev, cumulative), and more that can handle this.

Normal Distribution Functions Explained

NORMSINV (probability) - NORMSINV is a Microsoft Excel function that can be used to produce the inverse of the cumulative standardized normal distribution. In other words, NORMSINV is a probability function that returns a value x such that the integral from minus infinity to x of the standard normal distribution function is equal to P and P must be greater than 0 but less than 1.

The mathematical formulation is given by:

begin{displaymath} mbox{{it X}, such that} ; P = int_{-infty}^{X} frac{1}
{sqrt{2pi}} e^{-frac{t^2}{2}} dt end{displaymath}

To implement the NORMSINV using Excel, you basically enter the "probability that a value x is up to..." and it returns that value x (in terms of "sigmas", because it is the standardized distribution with average 0 and sigma 1). For example: NORMSINV (0.5)=0, NORMSINV (0.00135)=-3, NORMSINV (0.9772)=2. NORMSINV (0) and NORMSINV (1) will return error, because they correspond to - infinite sigmas and +infinite sigma’s.

NORMSDIST (x)- On the other hand, the NORMSDIST function translates the number of standard deviations (x) into cumulative probabilities. This can be illustrated as below:

NORMSDIST (-1) = 15.87%
NORMSDIST (+1) = 84.13%

This means, the probability of a value being within one standard deviation of the mean is the difference between these values, or 68.27%. This range is represented by the shaded area of the chart.

In addition to all the information above, it might pay to note that NORMSINV is the inverse of NORMSDIST function. Furthermore, a number of cases where these normal curves occur are listed below, but may sometimes vary. They are:

• Many Six-Sigma calculations assume normal distribution.
• The number of staff turnover over a certain period is normally distributed
• The number of defects in products as service has a normal distribution
• Most measurement errors are assumed to be normally distributed.
• The number of products and services sold over certain period in time assumes normal distribution
• The measure of material used in a manufacturing process is usually normally distributed over time.
• The width and weight of manufactured products are usually said to be normally distributed.
• The number of customers, products delivered, and services rendered often assume a normal distribution depending on season or time of the year.