XLL+ Class Library (6.3)

Error values in Excel

Once you've rebuilt the add-in (Shift+Ctrl+B in VS 2005), you can test it in Excel as shown below.

Notice how #NUM! is returned if Probability is out of range.

Accuracy of NORMSINV2

The cumulative distribution algorithms supplied here for the examples are not very accurate. They are better than those supplied in older versions of Excel, but not as good as the algorithms used in Excel 2003 onwards.

For a much better algorithm, look at the excellent QuantLib library at http://quantlib.org, in particular at the InverseCumulativeNormal class, which uses both Acklam's approximation and Halley's method to produce a number that is consistently better than the built-in Excel function, NORMSINV. (You should #define REFINE_TO_FULL_MACHINE_PRECISION_USING_HALLEYS_METHOD in normaldistribution.cpp to get the best accuracy available.)

Next: Throwing exceptions >>