Error using SUM function and indirect cells

[selrahc]

When using indirect cell references in Calc, the SUM function returns incorrect calculation result.

If I have $64.25 in cell A1 (defined as currency),

And in cells B1 to B5 I have the following calculations:
B1 =1.5*$A$1
B2 =1.0*$A$1
B3 =1.5*$A$1
B4 =2.0*$A$1
B5 =1.5*$A$1

(Pls note I have also tried B1 =1.5*A1, etc)

Then, when I add this function in cell B6:

=SUM(B1:B5)

The result is $481.88 when is should be $481.89 - and this error gets progressively worse the more cells that are summed. I've noted calculations out by 3 to 4 cents at least. Although this seems like a minor problem, when I have hundreds of these calculations in a spreadsheet, it can cause major headaches if each is out by a few cents (and makes my billing and invoicing a money-losing issue)

Is there some way around this?

BTW I noticed the same error occur if I use similar methods in Writer.

Any help is appreciated!

Regards
Charles

[noranthon]

WYSINNWYG. What you see is not necessarily what you get. You are seeing rounded numbers in B1 to B5. Calc is adding unrounded numbers - the ones you would see if your formatting displayed enough decimal positions. To get WYSIWYG, you either change the formatting or round your formulas (ROUND, ROUNDUP, ROUNDDOWN or MROUND).

[selrahc]

[David]

This is like the old story of the clever programmer who stashed all the dropped decimal amounts into his own account. It doesn't work that way. If you define as currency the values are each rounded up visually to two decimals, but not in storage. If you round, then the rounded values are stored, and you are rounding UP.

When I do the same in Excel I still get $481.88 in the expressed sum. If you leave the numbers unformatted [general] the resulting sum is 481.875, as it should be, and that is rounded to 481.88 in either case if formatted as currency.

The problem in both cases is the visual appearance from rounding up several values, the sum being calculated on the stored values will not then match. If you force them to [by rounding] there is a good appearance of agreement, but inherent inaccuracy.

Still, be cautious. If you round UP all of hundreds of values you will not get an accurate accounting. Refer please to my introductory sentence. The real calculated sum is 481.88, not 481.89. I'm not an accountant by asny stretch of the imagination, but know that if I did the same with rocket trajectories there would have been no landing on the moon.

While on the topic, I've done the same, even knowing better: I used a tape to mark [along the tape] the bottom of some half-length wall studs I was putting to a cement wall. Then I decided on a measured stick to mark off the top. Each use added an inherent error. When I installed the studs with good strong cement nails, I stepped back to look. Then I saw each one tilting on the right more and more. I had to remove and replace all but the first. A hard lesson.

David.