XLL+ Class Library (7.0)

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 three reasons:
1. It may be useful to have an add-in function whose formula is precisely the same as that used in code elsewhere, such as in a library, or within 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. CopyC++
```#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;
den = q;

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

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