Ask Your Question
0

Calc formula producing disparate result from separate calculator app?

asked 2019-05-10 05:33:14 +0200

appreciatethehelp gravatar image

updated 2019-06-19 14:27:29 +0200

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!

edit retag flag offensive close merge delete

Comments

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

KH gravatar imageKH ( 2019-05-10 06:34:08 +0200 )edit

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.

Lupp gravatar imageLupp ( 2019-06-19 15:25:33 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-10 06:35:58 +0200

KH gravatar image

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.

edit flag offensive delete link more

Comments

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?

appreciatethehelp gravatar imageappreciatethehelp ( 2019-05-10 06:40:30 +0200 )edit

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.

ajlittoz gravatar imageajlittoz ( 2019-05-10 09:00:47 +0200 )edit

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.

KH gravatar imageKH ( 2019-05-10 19:43:44 +0200 )edit

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

ajlittoz gravatar imageajlittoz ( 2019-05-10 19:49:47 +0200 )edit

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.

KH gravatar imageKH ( 2019-05-10 20:27:46 +0200 )edit

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.

ajlittoz gravatar imageajlittoz ( 2019-05-10 20:43:22 +0200 )edit

Interesting, good to know.

KH gravatar imageKH ( 2019-05-12 05:45:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-05-10 05:33:14 +0200

Seen: 37 times

Last updated: Jun 19