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 theNORMSDIST()function. There are three reasons:

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

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[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; }

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.