This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
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 ( for WinXL, 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.
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
For a full discussion of XL's precision and rounding, please see MVP Chip Pearson's treatment.