[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
selrahc
Newbie

Joined: 02 Apr 2006
Posts: 3
Location: AUSTRALIA

 Posted: Sun Apr 02, 2006 8:02 pm    Post subject: Error using SUM function and indirect cells 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
Super User

Joined: 07 Jul 2005
Posts: 3318

 Posted: Sun Apr 02, 2006 8:53 pm    Post subject: 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
Newbie

Joined: 02 Apr 2006
Posts: 3
Location: AUSTRALIA

Posted: Wed Apr 05, 2006 4:47 pm    Post subject:

 noranthon wrote: 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).

Hi and thnx for your suggestion. I went back and used:

=ROUND((1.5*A1);2)

and results now are being calculated accurately. I guess because I had imported this spreadsheet from Excel, I erroneously expected Calc to just *work* the same way.

Thnx again for your valuable input
Charles
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Wed Apr 05, 2006 6:47 pm    Post subject: Re: Error using SUM function and indirect cells 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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum