Description of the changes to the Analysis ToolPack (ATP) for Microsoft Office Excel 2007

This article describes the changes in the Analysis ToolPack (ATP) for Microsoft Office Excel 2007.

The functions that were a part of the ATP are now part of the native function library in Excel 2007. This change has the following advantages:

• Lets the ATP functions work across locales and behave like native functions in certain languages.
• Lets the tooltips appear for the ATP functions.
• Eliminates the need for the ATP add-in for spreadsheets that only use the ATP functions.
• Uses the same syntax to programmatically call the built-in functions and the ATP functions in the object model.

Because the ATP functions have been moved to the native Microsoft Excel function library, the results of these functions may be different in Excel 2007 than the results were in Microsoft Office Excel 2003 for certain inputs. An explanation of these changes and their results are explained in the following sections.

Rounding

All the ATP functions used C methods to covert between text and number values in Excel 2003 and earlier versions of Excel. In Excel 2007, the ATP functions now use Excel's routines to convert between text and numeric values. This change only makes a difference when some precision is lost. For example, some precision may be lost if you use a number formatted as text that exceeds 15 digits of accuracy. In these cases, Excel 2007 will truncate the result instead of rounding the result as in earlier versions of Excel.

Built-in functions

The built-in functions in Excel 2007 use an internal mathematics library to compute mathematical operations. The internal mathematics library is kept consistent in all versions of Excel.

The MOD function, the LCM function, and the GCD function

Because of switching to the internal mathematics library of the mod (modulus) function, the LCM (least common multiple) and GCD (greatest common denominator) functions will return correct answers over a larger domain than they did in earlier versions of Excel. Therefore, in some cases where Excel 2003 would return #NUM! or an incorrect result, Excel 2007 will provide a correct result.

Sin and Cosine

The ATP code in Excel 2003 uses the C run-time functions for calculating Sin and Cosine. For multiples of pi, Excel 2007 does not return exactly zero because of the accuracy limitations of floating point math. The same results can be expected with Cosine on multiples of pi/2.

In Excel 2007, the internal mathematics library calculates Sin and Cosine. Additionally, the internal mathematics library does not return exactly zero in these cases. However, the result is also not equal to the values that occurred in earlier versions of Excel.

This change may cause slightly different answers for certain inputs to the following functions:

• BESSELI
• BESSELJ
• BESSELK
• BESSELY
• IMSIN
• IMCOS
• IMEXP
• IMSQRT
• IMPOWER

Pi/2 constant

The ATP code in Excel 2003 uses the constant 1.5707963267448899 as the value of pi/2. However, Excel 2007 uses the constant 1.5707963267948966 as the value of pi/2.This change causes a slightly different result. However, the result is more accurate.

For example, the following formula returns a result of -9.4091401336982E-14-512i in Excel 2007:However, in earlier versions of Excel, this formula returns a result of -1.00984914874758E-011-512i

Formatting for imaginary numbers

As part of making the ATP functions built-in in Excel 2007, functions that return imaginary numbers now use the different rules for using scientific notation as the other versions of Excel.

For example, the following formula returns the result of 2.23+0.02i in Excel 2007: However, in earlier versions of Excel, the formula returns a result of 2.23+2E-002i.

This change affects the following functions in Excel 2007:

• IMSIN - Additionally, this function is affected by the Sin and Cosine change.
• IMCOS - Additionally, this function is affected by the Sin and Cosine change.
• IMSQRT - Additionally, this function is affected by the Sin and Cosine change.
• IMEXP - Additionally, this function is affected by the Sin and Cosine change.
• IMPOWER - Additionally, this function is affected by the Sin and Cosine change.
• IMAGINARY
• IMREAL
• IMARGUMENT
• IMCONJUGATE
• IMDIV
• IMLN
• IMLOG10
• IMLOG2
• IMPRODUCT
• IMSUB
• IMSUM

Gamma function

The ATP functions of earlier versions of Excel now use the Excel internal mathematics library to calculate gamma functions. This change causes a difference in a calculation that occurs at the 16th decimal place in gamma calculations and in the 15th decimal place in the final results.

For example, the following formula returns a result of 0.777297410872743 in Excel 2007: However, in earlier versions of Excel, the formula returns a result of 0.777297410872742.

The following functions require gamma calculations:

• ERF
• ERFC

Power function

The ATP functions of earlier versions of Excel now use the Excel internal mathematics library to calculate exponents.

For example, the following formula returns a result of 5.6532585945698% in Excel 2007: However, in earlier versions of Excel, the formula returns a result of 5.65325859456989%.

The following functions use exponent calculations and may be affected by this change:

• All base change functions, A2B where A,B belong to {Hex, Dec, Oct, Bin} and A <> B
• IMPOWER
• SERIESSUM
• XNPV and XIRR
• All TBILL functions
• All Dollar functions
• Nominal and Effect
• Duration and MDuration
• OddFPrice and OddFYield
• WEEKNUM
• CUMPRINC and CUMIPMT
• PRICE and YIELD

ATP functions in Excel 2007

The following table lists all the ATP functions that are available in earlier versions of Excel that are now native functions in Excel 2007.