Calc formula producing a value off by .01

[KaptainCalc]

So I joined the forum thinking I would be able to upload a file but I see that is not the case. The lack of “karma” is not because I am unwilling to contribute to the community, it is because I try in earnest to solve my own issues rather than ask the community at the first hiccup.

Anyway, my problem that I have been unable to solve on my own is the following:

I have a Calc file that I use to verify a complex calculation of employee wages. I was recently trying to clean up some of the formulas to reflect my improved understanding of Calc and to make them easier to read. The previous formulas worked before and I could confirm this by balancing the final payment calculated. The formula I have now produces the same result as the previous formula.

Unfortunately, in my efforts to confirm the new formula functionality with a fictitious scenario I have found that it computes the result off by -$0.01. I have three currency values that I add in a cell that should balance to an amount of another cell. I suspect it has something to do with one of the amounts I enter that ends in “.50.” When I tab out of the cell it changes to “.5”. It is when this value is entered that the amount results in the -$0.01 issue. I have made sure that all the relevant cells are formatted to currency and two decimal places.

This is the case for both the formulas. I am sure I have calculated the amount correctly as I have even parsed out the formula into segments so I can confirm there was no minute error in the calculation. I have also performed the calculations manually. I’m curious if anyone else has encountered this incorrect calculation of -$0.01. Hopefully I am not overlooking something obvious.

I was concerned this might be due to recently switching from OO but I opened the file in OO as well and get the same issue.

[LeroyG]

See if Precision as shown is checked in menu Tools - Options… - LibreOffice Calc - Calculate.

Add Answer is reserved for solutions.

Press edit below your question if you want to add more information.

[KaptainCalc]

THIS WORKED! THANK YOU SO MUCH! I was so frustrated and this setting change fixed my problem. Thank you kind stranger for taking the time.

[LeroyG]

I’m happy for you. So, I post it as an answer. You can accept it.

[newbie-02]

hello @KaptainCalc,

be aware that plenty (most?) spreadsheet results are not! exact in an absolute manner,

both for a short representation on screen and for the conversion of the decimal numbers usually used by humans into computer-understandable binary numbers - and back - the values are rounded and thus falsified,

in both systems the representation of values is not continous but somewhat ‘granular’ - as you are limited to limited amounts of digits - thus results which fall inbetween the ‘exact representable’ values need rounding which often occurs on dividing,

and the possible values in the binary world are not all the same as in the decimal world, thus additional rounding occurs,

the fact that you can specify different procedures or options - such as ‘precision as shown’ - can shift the effects and bring better results for certain tasks/cases, it cannot solve the problem in general,

take e.g. 1, divide by three, and take the result times three (all cells formatted for display as X,YY), without ‘precision as shown’ you will get 1 as result because the other ‘threes’ after 0,33 are calculated by calc without displaying them, with ‘precision as shown’ you will get 0,99 and will feel the result as ‘wrong’,

(even in the version that results in ‘1’, this 1 is probably not ‘real’, but an approximate display of a ‘crooked’ value which results from the decimal - binary - decimal conversions, more precisely from the decimal - binary-floating-point - decimal conversions)

one widespread and well beloved problem: VAT calculation in billing, if you calculate the VAT for single items and sum up you get more ‘rounding influence’ than if you calculate it for the net. total,

so: choose your poison, but be aware that there is little real truth or accuracy in today’s spreadsheets,

this problem is not limited to calc, but is affecting nearly all actual systems and calculating programs, developers invest much effort to minimize the effects, those of libreoffice are always caught between correctness and ‘ex$el compatibility’, sometimes they shovel bigger new holes …

[LeroyG]

See if Precision as shown is checked in menu Tools - Options… - LibreOffice Calc - Calculate.

More LibreOffice Help on Calculate.

Check the mark () to the left of the answer that solves your question.