On 5 March 2010 18:20, AG <[email protected]> wrote: > Harold Fuchs wrote: > >> On 3 March 2010 19:36, AG <[email protected]> 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 Fuchs London, England Please reply *only* to [email protected]
