Ask Your Question
0

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

asked 2014-03-07 22:38:35 +0200

mreky2 gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 06:58:55.260268

Comments

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

m.a.riosv gravatar imagem.a.riosv ( 2014-03-07 23:17:10 +0200 )edit

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.

mreky2 gravatar imagemreky2 ( 2014-03-07 23:55:54 +0200 )edit

This is called for example a Java BigDecimal ( https://docs.oracle.com/javase/7/docs... ) - 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: https://stackoverflow.com/questions/3...

Jacques Guddebuer gravatar imageJacques Guddebuer ( 2019-06-03 22:56:37 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2014-03-08 00:12:00 +0200

oweng gravatar image

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.

edit flag offensive delete link more

Comments

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).

alexeymuranov gravatar imagealexeymuranov ( 2018-04-05 18:03:01 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-03-07 22:38:35 +0200

Seen: 645 times

Last updated: Mar 08 '14