Quirky behavior of oocalc
A quirky behavior of OpenOffice.org Calc was observed. I uploaded the sample spreadsheet here [link to .ods] .
Would be great to know if someone else can reproduce it.
The first 3 columns have three dates and cell D2 uses the DAYS function to find the difference between the dates. Since the result is accurate, we also put that in cell D4 by-hand. We repeat a similar set of actions for E2 and E4.
Having done that, we divide E2 by D2 and E4 by D4 to obtain the results in F2 and F4. The results in G2 and G4 are obtained by F2 by 15 and F4 by 15 respectively. The contents of G6 are input by-hand.
For the cells H2, H4 and H6, the data is input by hand and I2, I4 and I6 are obtained by multiplying H2, H4 and H6 by G2, G4 and G6 respectively. Check the results obtained 
All of the above would be obvious if you check the formula bits in the spreadsheet.
The real fun of course is in the remaining rows. G8 is obtained as a result of the expression F8*15 (note: G2 was 15*F2) which is somewhat different and thus we end up with different results on I8.
Check out the spreadsheet. It is fun. The OO.o issuezilla does not seem to work for me, so shall file a bug later.
Broken here too (Debian testing, OOO 2.4.1):
http://img207.imageshack.us/my.php?image=ooolq8.png
Reply
Dominik Reply:
October 15th, 2008 at 7:37 pm
That’s just the rounding… this is no bug…
356/380 = 0.942105263157895
356/380 != 0.94
Reply
Yevgen Muntyan
15 Oct 08 at 6:53 pm
For me:
* both F2 and F4 hold 0.9421052632… which is not quite 0.94
* both G2 and G4 hold 14.1315789474… which is neither 14.3 not 14.10
It seems the calculations are correct.
Reply
Patrys
15 Oct 08 at 7:26 pm
Uhm, a similar thing appened to Excel.
It’s all about fractional numbers, decimal digits and their rappresentation, I think…
In F2 you do =E2/D2, 358/380
If you do this subdivision with a calculator, the result is 0,94210526315789473684210526315789
Now, Calc use as default for numbers a format with only two digits after comma. so, you will see an approximation of the value (here 0,94)
In F8 you handwrite 0,94, wich isn’t equal to the result of E2/D2, but it is its rappresentation.
When you get this two value moltiplied by an huge 48000, the approximation error become bigger and more visible.
if you select all your cells and do “format”->”cells” and increase “decimal places” to, say, 15, you will see more decimal digit and were the math “went wrong”
(sorry for my english and typos)
Reply
Fab
15 Oct 08 at 7:57 pm
I forgot:
http://img355.imageshack.us/my.php?image=ooorh3.jpg
Reply
Fab
15 Oct 08 at 8:00 pm
Not a bug.
The value of F2 is 0.9421053…
It is displayed as 0.94 because the cell isn’t formatted to show more decimal places.
Similarly, the value of G2 is 14.1315789…
Reply
Malcolm Parsons
15 Oct 08 at 8:05 pm
You need a better brain.
Input in any cell:
=480000*15*358/380
You’ll get 6783157.89, as in the sample .ods. This is what OOCalc is computing, there is no need for such a complex setup as in your example.
Now input in another cell:
=480000*15*0.94
Obviously, you’ll get 6768000, the same as in your sample document.
Why is that so? Simply because 358/380 does not make 0.94, what is displayed as “0.94” is the standard number format with 2 digits after the digital point! In full truth, 358/380 = 0.94210526315789473684 or, with the current precision in OOCalc, 0.94210526315789500000, and this is why 6783157.89 != 6768000.
Folks, your Problem Exists Between Keyboard And Chair, not in OOCalc! What you have displayed in a given cell is one thing, and a formula that gets propagated is another thing.
Reply
Béranger
15 Oct 08 at 8:19 pm
http://beranger.org/index.php?page=diary&2008/10/15/14/36/28-sometimes-it-s-pebkac-not-oo-org
Reply
Rahul Sundaram
15 Oct 08 at 8:51 pm
using round() to force the decimal places seems to fix it. eg. change the F2 formula to round(e2/d2;2)
Reply
smohan
15 Oct 08 at 9:04 pm
You’ve simply lost precision. Calc/Spreadsheets carry more precision in their calculations than may be displayed in the cells.
Select the column G and click the button to add decimal places a few times. What you copied down is not exactly the same number as was calculated.
Reply
markrian
15 Oct 08 at 9:18 pm
I don’t see the problem. Yes you get different values, but that’s because your calculated cells (the ones with forumlas) contains higher precision than the ones you entered yourself. Try changing the formatting of the cells and you’ll see the difference.
I did the calculations on a standard calculator and found no errors…
or am I missing something?
Reply
sankarshan Reply:
October 16th, 2008 at 4:58 am
Nope you are not. As a comment states, it is best to use round().
Reply
Isak Savo
15 Oct 08 at 9:21 pm
It is not broken.
Expand the formatting for all of the values in column ‘F’ to multiple decimal places… say, 10
It is the formatting of the cells and the number decimal places… a common gotcha with spreadsheets where calculated values are used in subsequent computations and then compared to hand entered values with less decimal places..
What you see on the screen isn’t exactly what the underlying numerical value is.
Reply
sankarshan Reply:
October 16th, 2008 at 4:57 am
Isn’t what I see on the screen supposed to be the underlying numerical value ?
Reply
Tarique Sani Reply:
October 16th, 2008 at 10:06 am
I would be very pissed if what i see on the screen was the underlying numerical value.
I would say you are confusing content with presentation.
Reply
sankarshan Reply:
October 16th, 2008 at 11:55 am
Well, not having used a spreadsheet in a while, I would have expected the underlying calculated value to be what gets displayed as the presented value. But, figured out what was wrong
John Poelstra
15 Oct 08 at 9:40 pm
This is not an Error: 358/380=0.942105263 not 0.94!
Ckeck your cell formatting.
Reply
noName
15 Oct 08 at 10:36 pm
The result is not accurate, 358 / 380 = 0,942105263…
Reply
smeagol
16 Oct 08 at 2:38 am
This is not a bug.
F2 != F4
G2 != G4
You can put some checks to make sure that your assumptions are correction. Just like assertions in source code.
For example, to check if cell G4 and G6 are really equal, following check could be used.
=IF(G4=G6;”SAME”;”DIFFERENT”)
———————–
Also try changing precision (decimal places) used for display purpose.
Tools -> Options –> OO.Calc -> View
Also make sure to increase column widths to properly display higher precision values.
Reply
vcp
16 Oct 08 at 3:38 pm