15,936,517 members
Articles / Programming Languages / C# 4.0

# Excel Function: NORMSDIST(z)

Rate me:
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.

## 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:

C#
```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)`
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.

 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.

## 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

Written By
Foundasoft.com
Malaysia
Consultant