General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Why your SUM() is a penny off...

Sometimes when working with financial formulas, you'll find that your SUM()s don't add up exactly to the sum of the displayed values. Usually this is because XL's display engine is rounding values in the cells to 2 digits. It's important to remember that unless you have Precision as displayed selected (Tools/Options/Calculation for WinXL, Preferences/Calculation for MacXL), the number format has nothing to do with the value stored in the cell which is used for subsequent calculations.

For instance, a value of 1.234 will display as $1.23 when formatted as currency. If you had two such values, 1.234 + 1.234 = 2.468, which will display as $2.47, not $2.46 as you'd expect from the displayed values.

One way to ensure that the displayed values and the values used for calculation are the same is to use the Precision as displayed setting, mentioned above. This is a global setting, however, which may introduce unintended effects elsewhere in your workbook.

Another way is to use XL's ROUND() function. Instead of

    B1:    $123.45
    C1:    4.56%
    D1:    =B1 * C1  ==> 5.62932

but displays as $5.63, which may cause real or perceived problems later, use

    D1:    =ROUND(B1 * C1, 2)   ==> 5.63

Note: as long as you're only doing integer multiplication and addition, ROUND() should work well. Division, though, can lead to another error:

    A1: 10.00			
    A2: =ROUND(A1/3, 2)     ==> 3.33
    A3: =ROUND(A1/3, 2)     ==> 3.33
    A4: =ROUND(A1/3, 2)     ==> 3.33
    A5: =SUM(A2:A4)         ==> 9.99, not 10.00

To correct this you can use something like:

    A4: =A1-SUM(A2:A3)      ==> 3.34
    A5: =SUM(A2:A4)         ==> 10.00

but for a large column of numbers, the error can be significant, so you may need to spread the "error" to multiple cells, or choose a different strategy entirely.

Unfortunately, there's no hard and fast rule for how to deal with this problem. The key is to be aware that it exists, and to design your workflow to handle the error in an appropriate way.

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

For a full discussion of XL's precision and rounding, please see MVP Chip Pearson's treatment.