Calc formula producing disparate result from separate calculator app?

English
, ,
#1

Hi all, there’s a cell in a budget document I am using (cell C10) containing the figure $160.63. Another cell multiplies this cell’s value by 52 (using the formula =C10*52), which produces the result $8352.63. This is the only formula existing in this cell- there are no other factors that might affect the result.

This is strange as when I run the same calculation through my calculator app I get the result $8352.76 - a small difference, but a different result nonetheless.
I’m definitely inputting the figures correctly as I got the same result three times, and verified that it isn’t the calculator app as a different calculator app produced the same result: $8352.76.

How is it possible that this result differs from the one generated by the Libreoffice calculation, other than Libreoffice calculations somehow calculating incorrectly? It’s a tad concerning!

#2

I can’t reproduce this on my system. I get 8352.76 both ways.

#3

If you calculate currency amounts to be displayed in a respective format with a fix number of decimals, always ROUND explicitly (Up? DOWN?) meeting your needs.

#4

Ah, thinking just a bit more, check that the number you see in the cells isn’t being rounded from something with more decimal places. The formula bar should show the true value.

#5

Thanks for the response, something similar entered my mind recently…
However I have an issue: when I select the $8352.76 cell, the formula bar only contains the cell’s formula; how do I view the “true” result?

#6

Then, if there’s a formula, you can’t be sure what you see is the exact value of the formula. To eliminate ambiguity, edit your formula to: =TRUNC( <your_formula >; 2) or =ROUND( <your_formula >; 2)

This way, what you see will be the effective value of the cell.

#7

I’m talking about is the first cell, $160.63. I think the value shown is being rounded up, making the result of your formula lower than you would expect from looking at the source cell, but correct compared to the true source value. If the rounding problem was in the formula cell, the difference in the result couldn’t be more than $0.01.

#8

No, the difference after multiplication is capped by 0.01*52 = 0.52. You get 0.13, meaning the stray digits are on the order of 0.0025

#9

I think I was a bit unclear. I think the shown value in the source cell is the result of extra digits in the true value being rounded up. I agree, the cap on the resulting difference in the result cell is 0.52. But adding rounding or truncation in the result cell is too late. If by chance the source cell is also a formula cell, then OP could add one the functions you suggested to the source cell, assuming they don’t mind losing accuracy in the final result.

#10

When it goes to accounting, real mathematical accuracy is nonsense. Bankers, accountants or clerks will always request payment in currency units and its official subunits. Accountancy is integer by design with a 0.01 minimal quantum (0.001 in some currencies). Therefore, you must always insure on intermediate results with accounting visibility (i.e. figures listed in a form usied as a base for other calculation such as tax) that they are stated with a financial meaningness, that is they can be physically paid.

I know, this is no math truth, but “accounting truth”. The common function is ROUND() so that there is compensation on the average.

#11

Interesting, good to know.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.