Click here to Skip to main content
15,936,517 members
Articles / Programming Languages / C# 4.0

Excel Function: NORMSDIST(z)

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
21 Jun 2012CPOL4 min read 42.6K   505   7   1
The program presented here provides a simple way to obtain area under the standard Normal curve less than z.


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


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.

 Image 1

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.

Image 2 

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

Image 3 

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

 Image 4

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.


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));

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

  1. erf(x)

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)));


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.

z NORMSDIST(z) fr this program Excel 2002 and earlier Excel 2003 and later
0 0.5000000 0.5000000 0.5000000
0.2 0.5792597 0.5792597 0.5792597
-0.2 0.4207403 0.4207403 0.4207403
-1 1.58655E-01 1.58655E-01 1.58655E-01
-2 2.27501E-02 2.27501E-02 2.27501E-02
-3 1.34997E-03 1.34997E-03 1.34990E-03
-4 3.16860E-05 3.16860E-05 3.16712E-05
-5 2.87105E-07 2.87105E-07 2.86652E-07
-7 1.28814E-12 1.28808E-12 1.27981E-12

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.


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).


20th June 2012: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Malaysia Malaysia

Comments and Discussions

GeneralMy vote of 5 Pin
Jacen Solo29-Jul-20 9:38
Jacen Solo29-Jul-20 9:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.