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