Designing an add-in function

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. NORMSDIST() is inaccurate at extreme values, and the inverse function NORMSINV() fails beyond 5 standard deviations.
2. 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 DevStudio, 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 >>