Is there a way to do fixed-decimal math in Calc?

[mreky2]

Have been fighting this for years. All spreadsheets (including LO Calc) use the standard IEEE or hardware float representation for numbers, then round off for display to the numeric format specified. Works well, but results in occasional “your answer is approximately nnnnn.nnnn” and comparison failure issues. IBM mainframes (and some old Borland languages) had packed-decimal or BCD math that was exact for specified decimal precision and rounding - what a bookkeeper wants. I recently found a decimal data type in Python that does something similar. Is there a way to use that concept in LO Calc without wrapping the whole Python decimal library into an extension of some kind, and figuring out how to call it? NOT a Python expert so this is more a theoretical question than one I can jump into right now.

Understood: this would not be portable outside of LO and possibly OO. Might still be useful.

Thanks.

[mariosv]

Hi @mreky2, just curiosity, please can you share some of the situations bothering you?

[mreky2]

It’s not a real issue currently, but I’ve had problems in the past where comparing the results of 2 calculations involving multiplication/division looking for equal doesn’t work because of differences out in the last few decimal places. There are workarounds, like ROUND or INT before comparison, but inconvenient to remember when working quickly. Also, in some financial work, the results should be exact, not just display rounding. Obviously not big or would have been fixed long ago.

[JacquesGuddebuer]

This is called for example a Java BigDecimal ( BigDecimal (Java Platform SE 7 ) ) - values which are not represented in a binary system with a floating point but in a decimal system with a set precision and defined rounding behaviour. The fact that Spreadsheets in 2019 do not explicitly support such a type can only be called “problematic” at best. If a professional accounting package used floats to represent monetary amounts the company responsible would hopefully be sued silly. See also: floating point - Why not use Double or Float to represent currency? - Stack Overflow

[newbie-02]

@Jaques:
‘The fact that Spreadsheets in 2019 do not explicitly support such a type can only be called “problematic” at best.’ … thumbs up!

[oweng]

This is a problem of the number of digits that can be stored and is a fundamental limitation of all x86 computers, not just LO. Bug fdo#37923 is one of many that have been reported for this issue. To quote from that bug:

this is not a bug but a mathematical problem. Subtracting two nearly identical numbers is an ill-conditioned problem. It’s problem that every program using floating point numbers has and that can’t really be solved.

Using 64-bit storage has a limit on the number of digits that can be stored.

IBM mainframes (and some old Borland languages) had packed-decimal or BCD math that was exact for specified decimal precision and rounding

Packed decimal simply gives more digits. It will suffer the same rounding error if a number contains more digits than can be stored.

[alexeymuranov]

There is nothing fundamentally limited in storing decimal numbers in a computer. The simplest solution would be to let the user to specify how many digits after the decimal point he wants to keep. I do some bookkeeping in euros where there should be no rounding because all cell should contain a whole number of centimes, and still i get inexact numbers (because even just 0.01 euro is stored as a binary floating point approximation).

[newbie-02]

sorry for late comment, tried to but couldn’t stop myself …

@oweng: memories from some reading: BCD - ‘binary coded decimals’ - can do exact decimal calculations (within the limit of digits), while (binary) floats produce rounding errors against decimals starting with the first decimal digit, that’s a big difference and produces irritations at users not used to other than decimal representations,

[newbie-02]

sorry for late comment, tried to but couldn’t stop myself …

@alexeymuranov: there are! some fundamental limitations: 1. universe is not! infinite, 2. the computers we use are much! smaller, 3. the representations for numbers used in them are even more limited, 4. classically they use dual / binary representations while we are used to decimal numbers, 5. the intersection of numbers that can be represented decimally and! binary exactly is much smaller, 6. especially if you use - as almost all home computers and spreadsheets do - IEEE floating point numbers … 7. especially when using calc which does stricter rounding (to 14 significant digits) than neccessary,

thus either learn to live with rounding errors, !!! use cent values for financial calculations !!! (13 cent instead of 0,13 EUR, that’s exact up to 2^53-1 cent, if you have more money pass some of it to me), request implementation of IEEE 754-2008, or find another solution …

[newbie-02]

‘Packed decimal simply gives more digits. It will suffer the same rounding error if a number contains more digits than can be stored.’
In my opinion, this is an error that should not be allowed to stand:
packed decimals store more digits than simple BCD’s, but both store (bitcoded) ‘digits’ instead of approximated binary values (with which IEEE 754 stores even more, but not ‘digits’ but ‘bits’) BCD’s and packed BCD’s can thus calculate exactly - in a decimal sense - what IEEE 754 in the 1985 variant does not get right on the row, but it is faster!, the variant IEEE -2008 can - correct - but is slower again?

so … there are alternatives, but to rebuild calc completely would be ‘not so easy’,

and … besides problems of IEEE calc injects own ones, one should work on those …
BCD’s will experience ‘cancellation’ as well as IEEE’s, but as the values and the ‘pulled zeroes’ are exact in decimal means they will produce decimal exact results,