Auto-SUM Not adding correctly

[Liz]

I have a problem. When I use Auto-SUM or simply add up
the cells they don't add up correctly. It's usually out
by a penny. I've manually added them on a calculator and
the amount in excell is wrong. I've tried changing the
number of decimal places but that still doesn't do it. If
anybody knows what can be done please let me know.

Thanks,

 

[Judy Freed]

Try This:

Select Tools, Options, then on the Calculations tab, select "Precision As
Displayed".

 

[EZ Money]

"Precision as Displayed" does not alter the fact that the value is or may be
off by a cent. The value (or some part of it) is derived from a calculation
which is being rounded. The display does not alter the underlying value;
look in the formula bar when you are in the cell and see.

 

[J.E. McGimpsey]

Changing format doesn't change the value XL stores in the cell - and
by default it's the stored value that summed, not the displayed
value.

Your cells are undoubtedly calculated, so that while they display,
say 1.02, the stored value may be 1.015102. If you have a number of
values like that, you can generate some rather large apparent errors.

You can make XL use the displayed values if you choose
Tools/Options/Calculation and check the Precision as displayed
checkbox. Realize, however, that is a global setting, so it will
affect all your calculations.

You can also wrap your calculations in the ROUND() function. For
example, if your current function is

=A1/B1 ==> 1.015102

then instead use

=ROUND(A1/B1,2) ===> 1.02

This will work fine if you're adding. However, it can lead to some
inconsistencies. For instance:

A1: 1.00
A2: =ROUND(A1/3,2) ==> 0.33
A3: =ROUND(A1/3,2) ==> 0.33
A4: =ROUND(A1/3,2) ==> 0.33
A5: =A1 - SUM(A2:A4) ==> 0.01

To compensate, you can assign the "extra" to one (or more) cells:

A1: 1.00
A2: =ROUND(A1/3,2) ==> 0.33
A3: =ROUND(A1/3,2) ==> 0.33
A4: =A1-SUM(A2:A3) ==> 0.34
A5: =A1 - SUM(A2:A4) ==> 0.00

 

[Liz]

I've tried it and it didn't work. The amount is out by
another penny. Anyother suggestions?

Thanks,

 

[Greg Lovern]

Hi Liz,

Another reason this can happen is because the numbers being summed are
converted from decimal (base 10) to binary (base 2) so that the
microprocessor can perform the arithmetic, then the sum is converted
back from binary to decimal for display in Excel. Since many numbers
that can be represented exactly in decimal can only be represented
approximately in binary (and vice versa), sometimes calculations are
slightly incorrect. With enough repetition (as when summing a large
number of cells), the inaccuracies can build up to a large amount.

The two ideas you've been given so far are often helpful but never
bulletproof. The only bulletproof way to avoid the problem is to never
convert the numbers to binary in the first place, and instead perform
the calculations in decimal.

Excel can't do that on it's own, but my Excel Add-In called
xlPrecision can. You can download the free edition of xlPrecision from
http://PrecisionCalc.com.

Since xlPrecision does not use the microprocessor to perform its
arithmetic, it's slower than Excel's calculations. But it's fast
enough that you might not notice the difference.

Hope this helps,


Greg Lovern
http://PrecisionCalc.com
Get Your Numbers Right