Excel Function: NORMSDIST(z)

• Download source - 18.9 KB

Introduction

NORMSDIST(z) returns the probability that the observed value of a standard normal random variable will be less than or equal to z. A standard normal random variable has mean 0 and standard deviation 1 (and also variance 1 because variance = standard deviation squared).

The purpose of this article is to describe the NORMSDIST function in Microsoft Excel. This article illustrates how the function is computed. This article also compares the results of the program introduced here

1. with the results of the function for Excel 2002 and earlier versions of Excel
2. with the results of the function for Excel 2003 and later versions of Excel

Background

If X is a normal random variable with mean μ and standard deviation σ, then we write X~N(μ, σ) where σ > 0. The probability that X takes on a value between a and b is given by the integral below.

The integral cannot be computed in terms of simpler functions. However it is interesting to see that this Normal distribution function is connected to another function called Error function (erf). This relation was given in the "Handbook of Mathematical Functions" by Abramowitz and Stegun. It was stated in formula 7.1.22 as follow.

where m is mean and σ is standard deviation. Note that the μ is replaced by m in this formula. Also note that a now is -∞ and b now is x.

If m = 0, σ = 1 and x = z, we can state the right hand side of formula 7.1.22 as

 And function erf(x) can be computed using formula 7.1.26 from the handbook as follow.

This rational approximation is from C. Hastings, Jr., Approximations for digital computers, Princeton University Press, N.J. 1955. The epsilon (error) is less than 1.5 x 10^-7, or in another words accurate to 7 decimal places.

Requirement

To run the code, you need to have the following:

• .NET Framework 2.0 and above
• Microsoft Visual Studio 2005 if you want to open the project files included in the download project

Using the Code

We envisage that the user will perform the following code to get the desired results for NORMSDIST(z) when z = 0, 0.2, -0.2, -1, -2, -3, -4, -5 & -7.

Here are some typical user’s code in Main method:

Console.WriteLine("Value for NORMSDIST(0) is: {0}.", NORMSDIST(0));
Console.WriteLine("Value for NORMSDIST(0.2) is: {0}.", NORMSDIST(0.2));
Console.WriteLine("Value for NORMSDIST(-0.2) is: {0}.", NORMSDIST(-0.2));
Console.WriteLine("Value for NORMSDIST(-1) is: {0}.", NORMSDIST(-1));
Console.WriteLine("Value for NORMSDIST(-2) is: {0}.", NORMSDIST(-2));
Console.WriteLine("Value for NORMSDIST(-3) is: {0}.", NORMSDIST(-3));
Console.WriteLine("Value for NORMSDIST(-4) is: {0}.", NORMSDIST(-4));
Console.WriteLine("Value for NORMSDIST(-5) is: {0}.", NORMSDIST(-5));
Console.WriteLine("Value for NORMSDIST(-7) is: {0}.", NORMSDIST(-7));
Console.ReadKey();

Two functions (or methods, in Object-Oriented terms) are implemented:

1. erf(x)
2. NORMSDIST(z)

erf(x) is a function that calculates the value of error function of x. It is constructed based on formula 7.1.26. This method is chosen among formula 7.1.25 - 7.1.28 as it gives the smallest "error" which is less than 1.5x10^-7. However the direct calculation of erf(x) has been modified with a technique called Horner's method for better efficiency (See Stand-alone error function erf(x). Thanks Cook for enlightening me on this :).

The erf(x) function is constructed as follows:

private static double erf(double x)
    {
        //A&S formula 7.1.26
        double a1 = 0.254829592;
        double a2 = -0.284496736;
        double a3 = 1.421413741;
        double a4 = -1.453152027;
        double a5 = 1.061405429;
        double p = 0.3275911;
        x = Math.Abs(x);
        double t = 1 / (1 + p * x);
        //Direct calculation using formula 7.1.26 is absolutely correct
        //But calculation of nth order polynomial takes O(n^2) operations
        //return 1 - (a1 * t + a2 * t * t + a3 * t * t * t + a4 * t * t * t * t + a5 * t * t * t * t * t) * Math.Exp(-1 * x * x);

        //Horner's method, takes O(n) operations for nth order polynomial
        return 1 - ((((((a5 * t + a4) * t) + a3) * t + a2) * t) + a1) * t * Math.Exp(-1 * x * x);
    }

Using Equation (A), the NORMSDIST(z) function is shown as follow.

public static double NORMSDIST(double z)
    {
        double sign = 1;
        if (z < 0) sign = -1;
        return 0.5 * (1.0 + sign * erf(Math.Abs(z)/Math.Sqrt(2)));
    }

Comparison

The following table compares the results of NORMSDIST(z) given by this program

1. with the results of the function NORMSDIST for Excel 2002 and earlier versions of Excel
2. with the results of the function NORMSDIST for Excel 2003 and later versions of Excel

Results from part (i) and (ii) can be obtained from here.

From the results above, we can see that NORMSDIST(z) function in this program is comparable with Excel 2002 and earlier. Results were essentially accurate to 7 decimal places, which is sufficient for most practical examples.

Conclusion

The program presented here provides a simple way to obtain Excel function NORMSDIST(z) which is used to find area under the standard Normal curve less than z, where z is real number. An application of this stand-alone NORMSDIST function will be shown in my following article on Process Performance part-4. For previous articles on Process Performance, see here (part-1, part-2, part-3).

History

20th June 2012: Initial post