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.

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

That’s just the rounding… this is no bug…

356/380 = 0.942105263157895

356/380 != 0.94

Reply

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

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

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

using round() to force the decimal places seems to fix it. eg. change the F2 formula to round(e2/d2;2)

Reply

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

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

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 🙂

This is not an Error: 358/380=0.942105263 not 0.94!

Ckeck your cell formatting.

Reply

The result is not accurate, 358 / 380 = 0,942105263…

Reply

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