Unsubscribe please
-----Original Message----- From: Twayne [mailto:twa...@twaynesdomain.com] Sent: 08 March 2010 08:13 PM To: users@openoffice.org Subject: [users] HTML Post Re: Problem with sum in Calc In news:2f0009191003060131y43d70980k3def31d974793...@mail.gmail.com, Harold Fuchs <hwfa.openoff...@googlemail.com> typed: > On 5 March 2010 18:20, AG > <computing.acco...@googlemail.com> wrote: > >> Harold Fuchs wrote: >> >>> On 3 March 2010 19:36, AG >>> <computing.acco...@googlemail.com> wrote: >>> >>> >>> >>>> JOE Conner wrote: >>>> >>>> >>>> >>>>> On 3/3/2010 10:52 AM, AG wrote: >>>>> >>>>> >>>>> >>>>>> 11.73 + 7 + 15.75 + 24.68 + 17.5 + 21.18 + 20.65 + >>>>>> 17.85 + 19.25 + 23.1 + >>>>>> 12.6 + 10.5 + 23.8 >>>>>> >>>>>> >>>>>> >>>>> TOOLS -> OPTIONS -> OPENOFFICE.ORG CALC -> CALCULATE -> >>>>> Right Panel, do you have PRECISION AS SHOWN checked on >>>>> one of the machines? >>>>> >>>>> Joe Conner, Poulsbo, WA USA >>>>> >>>>> >>>> >> <snip> >>>>> >>>> Joe >>>> >>>> Cheers for the rapid response. Neither of our machines >>>> had that option enabled. However, even after enabling >>>> the "precision as shown" the error persists on my >>>> partner's version of Calc. >>>> >>>> Thanks >>>> >>>> AG >>>> >>>> >>>> >>> >>> Were the numbers entered directly from the keyboard or >>> are they the results >>> of calculations based on other data within the >>> spreadsheet? If the latter, the chances are it's a >>> rounding error. Try increasing the number of decimal >>> places shown in the columns/rows in which the numbers >>> apppear. That will show how your numbers have been >>> rounded, if at all. Adding the more precise >>> values and rounding the result may reveal the problem. >>> >>> >>> >> Harold >> >> Apologies for the delay in responding. Thanks for the >> idea - my partner was ahead of me there and had already >> tried it to five decimals in an attempt to track it. The >> numbers were entered manually - i.e. not copied and pasted >> - so there should not have been any chance of a formatting >> error creeping in. >> >> Still drawing a blank - and it appears to be happening as >> well on other spreadsheets she is using, both for work as >> well as for testing these problems. Again, on my machine, >> all of the sheets add up to what they should do, so the >> problem is local to either her machine (perhaps the >> motherboard system clock is off, which may impact) or the >> build of the OOo is corrupted (although this seems such a >> minor corruption to have gotten through). In short, we >> are both still stumped, so further ideas and suggestions >> are most welcomed. >> > > I managed to create a tiny spreadsheet that exhibits the > problem: > 1.456 1.46 1.46 > 2.345 2.35 2.35 > 4.567 4.57 4.57 > 8.368 8.37 8.38 > > The first column shows how I entered the data [the last row > shows the result of =sum(..)]; the second row shows how it > and the =sum(...) appears under default (2 decimal places) > formatting; the third shows the result of the procedure I > describe below. > > Please try the following, assuming your values are in > column A and that column B is spare - adjust as appropriate: > > 1. in B1 enter the formula =INT((A1+0.005)*100)/100 > 2. copy B1 down column B for as many rows as are > necessary to cover all the values > 3. in the *next* row in column B enter the formula > =SUM(B1:Bn) where "n" is the number of values > 4. compare this result to your previous one - the sum of > column A. > > The formula in #1 above is designed to remove any rounding > errors in column A and put the result in column B. If the > sum in column B is the same as that in column A and if that > sum is wrong, then I'd say you've found a bug :-( Harold, Haven't been in a classroom in a lonnnnnggg time, but I fail to see the significance of adding 0.005 to each manually user entered number in the leftmost column to get column B. It is of course, going to make any digits after the decimal point round upwards, even when it shouldn't: e.g. 123.111 becomes 123.116 or, if one exposes one more decimal places, 123.1160. That's incorrect, as rounding will either leave a number as is, or increase it by 1 at the relevant decimal place. 123.111 should round to 123.11 or 123.1 or 123, but never 123.116 or 123.17 because that creates only a round-up. Each additional entry will then increase the summation errors very substantially with a list of more than a few digits to add up. Perhaps you can enlighten me as to the specific point you're trying to make with the second column where you add 0.005 to each manual entry? 1.4560 1.4600 1.46 2.3450 2.3500 2.35 4.5670 4.5700 4.57 8.3680 8.3800 8.37 Sums in column B is incorrect for what was entered, but not incorrect for the data Calc would use. 4 Dec Plcs 4 Dec Plcs Ea +.005 2 Dec Plcs 1.456 1.46 1.456 2.345 2.35 2.345 4.567 4.57 4.567 8.368 8.38 8.37 Sums. Again, column B is no longer the same as A. Sum (a7:a9) sum(b7:b9) Sum a7:a9 Ea + .005 in middle column First Column, all 3 dec places, same as yours; typed numbers and summing. Second Column, 4 - 2 decimal places resp top and bottom, using your formula and summing. Third Column, 2 - 3 dec places resp, top to bottom, and summed. Summing the numbers in Calc or Excel, 8.368 sum for column 1 sums correctly to rounded off 8.37 in Column 3, for a 2 dec place summation. You also said: " ...#1 above is designed to remove any rounding > errors in column A and put the result in column B... " But I fail to see the relevence of it. Perhaps I'm blind but I also don't see a "#1", but I think I know what you meant. Trouble is, adding 0.005 to each number is going to force each number to round to the next higher, whether it should round upwards or not. If you start with 1 and add 5, =6, rounding off at that decimal place is going to happen. Same for 0 thru 4 and even does a rollover at 5 and above. How can that possibly be intended to account for rounding errors in Column A? What it really does is make the more like integers than rounded numbers. Whatever precision the numbers are typed in as is the precision used in calculations, regardless of how many digits (and resulting rounding) you choose to have display. 1.5 1.5 1.5 2.3 2.4 2.3 4.6 4.6 4.6 8.4 8.4 8.4 Note that, with 3 or more decimal places, the numbers as originally entered are still used in the calculation. One simply has to be careful to consider the effects of rounding vs # digits to display. Other than the apparent error by adding 0.005 to each entry, the sums are indeed the same. Excel treats these numbers the same way. What did I miss in the point you wanted to make? 1.5 1.5 1.5 2.3 2.4 2.3 4.6 4.6 4.6 8.3680 8.3800 8.3680 -- Regards, Twayne Newsgroups are great places to get assistance. But always verify important information with other sources to be certain you have a clear understanding of it and that it is accurate. --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
