Thank you for taking the time to answer. I really appreciate it. About the problem. See below.
2009/2/4 JOE Conner <[email protected]> > Ely Schoenfeld wrote: > >> Hello all. >> >> I discovered something odd with OOo 3.0.1 Calc. >> >> Hope someone could verify this for me. >> >> a) Put in two different cells the numbers: >> >> 18282.34 (For example A1) >> -18282.73 (For example A2) >> >> b) Add them in another cell. (For example A3) >> Doesn't matter if you use the "SUM" formula or just use a simple "+" sign. >> >> If I add more decimals to the cell format, I get something like -0.390000, >> that's ok. >> >> But If I copy the last cell and then paste special with only text, numbers >> and dates checked, I obtain: >> -0.389999999999418 >> >> You can test that the problem IS affecting further calculations like this. >> >> a) Add 0.005 to the result (A3 in my example) >> >> You will obtain -0.38 if you are showing 2 decimals. >> >> b) Write exactly -0.39 in a new cell, and then add the same 0.005 to it. >> >> You will obtain -0.39 if you are showing 2 decimals. >> >> In both cases, if you add more decimals to the cells, you will obtain the >> same: -0.385 >> But if you copy and paste special with only text, numbers and dates >> checked, you would obtain in the first case -0.384999999999418 and -0.385 in >> the second one. >> >> I'm attaching an odt file here, hope it passes through. >> >> In case it's relevant, this happened on a: >> >> Windows XP Version 2002, sp3 >> 32Bit Centrino Duo processor >> >> Should I open an issue about this? (Couldn't find one) >> >> Thank you all. >> >> Ely. >> > <<SNIP>> > You are using base ten numbers that are internally represented in base 16 > hexadecimal. Rounding errors are inevitable unless specialized precautions > are used. > > For example if you want two places of accuracy, think currency calculations > for example, you can use =ROUND(X;2). > Yes, I need 2 decimal precision. If I leave those "precision errors" around, I get differences at the end of calculation as you would expect. So it's difficult sometimes to find why. Well... Now I know. I tested this with smaller numbers and everything worked just like it should have. I will have to add the "ROUND" function to a lot of sums, in order to avoid precision errors. wow!. I was already trying to fix the problem using ROUND in some places, like multiplications and divisions, but I steel had differences in my calculation (bigger than 0.01) at the end. Now I see that I have to add round to some places that I didn't thought to. > > In your example cell a10 displays the correct two digit display. If you > increase the digit display you will see that the actual number calculated > is really =0.38500000 and with the common mathematical rule of rounding > of which anything that is half or greater is rounded to the next greater > number you can see that -0.38 is indeed correct. > Yes, you are right. > > If you need multiprecision arithmetic, then I suggest you open the file > X-NUMBERS which I have attached which has macros to deal with > multipresision. > Thank you so much for this. it looks great. Just one question... how can I "recalculate" all the cells after I activate the module containing the new functions? When I press F9 the "About XNumbers" sheet steel shows "#NAME?" where the new functions are used. I need to go to each one of those cells and press F2 and then add a space at the end and press enter to get the actual value. I did read somewhere to add the "random" function to cells containing functions added by the user, that forces recalculation. Is there some other fix? Thanks agian. Ely. > Joe Conner, Poulsbo, WA USA > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] >
