The next step is to add an Excel Add-in function, using the XLL+ Function Wizard. In this example, we're going to write a function to return the cumulative normal (Gaussian) distribution.

Note: Readers familiar with Excel may ask why we are writing a cumulative normal distribution function when Excel already contains the NORMSDIST() function. There are two reasons:

1. It is useful to have a function whose formula is precisely the same as that used elsewhere in a library, inside other functions. This can make testing more precise and straightforward.
2. NORMSDIST() is inaccurate at extreme values, and the inverse function NORMSINV() fails beyond 8 standard deviations. In Excel 2003 and 2007, the functions are better implemented than in older versions of Excel, but can still be improved upon.
3. Most, importantly, it makes a good example function.

It is good practise to put all important business functions in separate functions that are not Excel-dependent. If you do this, you will be able to reuse the code unchanged in other environments. That is what we will do here. The code for a stand-alone implementation of the cumulative normal distribution and its inverse is shown below.

Code for stand-alone functions

The Normal() and CumNormal() functions cannot fail, so they simply return their result.

InverseCumNormal() can fail if the input is out of range, so it returns 1 for success and 0 for failure. The inverted value is passed back via the pointer result.

#include <math.h> 
 
// Normal distribution function 
double Normal(double x)
{
    #define SQRT2PI 2.50662827463 
    return exp(-x * x / 2.0) / SQRT2PI;
}

// Cumulative normal distribution function 
double CumNormal(double x)
{
    #define gamma   0.2316419 
    #define a1      0.319381530 
    #define a2     -0.356563782 
    #define a3      1.781477937 
    #define a4     -1.821255978 
    #define a5      1.330274429 
 
    double k;

    if (x < 0.0 ) 
    {
        return 1.0 - CumNormal(-x);
    }
    else
    {
        k = 1.0 / (1.0 + gamma * x);
        return 1.0 - Normal(x) * ((((a5 * k + a4) * k + a3) * k + a2) * k + a1) * k;
    }
}

// Inverse cumulative normal function 
// Returns 1 for success, 0 for failure 
int InverseCumNormal(double u, double* result)
{
    int i;
    double Y, num, den;

    static double p[] = {
        -0.322232431088,
        -1.0,
        -0.342242088547,
        -0.0204231210245,
        -0.0000453642210148
    };
    static double q[] = {
        0.099348462606,
        0.588581570495,
        0.531103462366,
        0.10353775285,
        0.0038560700634
    };

    if (u <= 0.0 || u >= 1.0)
        return 0;

    if (fabs(u - 0.5) < 10e-8) {
        *result = 0.0;
        return 1;
    }

    if (u < 0.5) {
        InverseCumNormal(1.0 - u, result);
        *result *= -1.0;
        return 1;
    }

    Y = sqrt(-log((1.0 - u)*(1.0 - u)));
    num = p[4];
    den = q[4];

    for (i=3; i>=0; i--)
    {
        num = num*Y + p[i];
        den = den * Y + q[i];
    }

    *result = Y + num / den;
    return 1;
}

Adding the stand-alone functions

In Visual Studio, open the file Tutorial1.cpp, and add the code above at the end of the file.

Tip: It is a very bad idea to type in all the code shown above. You can copy it from here and paste into your source file, or you can find all the code for this tutorial in the Samples/Tutorial1 sub-directory.

All the important code is now written. All we need to do is to generate the Excel add-in function, and plug it into a stand-alone function.

Next: Define the function >>