Hi I can’t attach a file, so pasting the values
0.0042
0.0043
=A2-A1
0.0000999999999999994
^^Expected 0.0001
Is precision really that bad?
Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3;
Locale: en-GB (en_GB.UTF-8); Calc: group
What is bad with a (relative) precision of about 6.0E-15?
Please read answers to question LibreOffice Calc can't even add decimals to 2dp accurately and the comments therein (esp. the links provided by @mikekaganski ).
^^Expected 0.0001
It’s a shame to need to use my calculator, to do (0.0043-0.0043) It doesn’t have any trouble. Why not update Calc to be modern and support better precision ?
Don’t rant before you learned a bit about mathematics.
“It’s a shame to need to use my calculator, to do (0.0043-0.0043).” Yes it would be. But of course we both can do this calculation by mental arithmetic. This doesn’t change the fact that 0.0043 (=43/10000) has no exact dyadic representation, and that your processor doesn’t calculate with a pencil on paper in the decimal system.
Thank you for your reply. Not sure if you are familiar with Double precision. This is a 64bit number - should work fine! It’s a shame LibreOffice is not using a regular Double precision floating point number format.
//g++ -Wall -o double double.cpp
#include <cstdio>
int main()
{
double a = 0.0042;
double b = 0.0043;
double result = b-a;
printf("%6.6f\n", result);
}It gets boring. In C printf used with that format specifier outputs a number formatted to a text using the specified format. What did you get in Calc for =TEXT(0.0043-0.0042;“0.000000”)? It’s absurd. And yes, I went into the details of IEEE 754 Double some time ago for sensible reasons.
Any software calculating with Double in dyadic, but getting input and displaying (or printing) in decimal needs to convert. With respect to the supposed expectations of assumed users there will also be some rounding. Formatting implies rounding mostly. This will result in different display by different software for the same result, in specific if formatted differently. A spreadsheet uses implicit formatting if no instructed otherwise.
Should I actually need to convert your numbers manually to 52 (+1implicit) bit mantissae, and to perform dyadic subtraction? Did you check my claim that neither 0.0042 nor 0.0043 have exact dyadic equivalents?
This must be enough now
Thank you for the reply. The code was C++ rather than C. If I change LibreOffice to format as #,##0.0000 it does show the correct answer. Just bizare, in 2020 such a simple thing is not displayed correctly. It’s very simple maths. Stop defending bad code! Use whatever precision is necessary to calculate accurately and display for users
The calculation itself isn’t done by Calc. In a spreadsheet every number is - independent of the way it’s displayed - represented in IEEE 754 ‘Double’ which is the relevant international standard and every calculation is performed by the floatingpoint unit of your computer’s processor.
In accordance with mathematics any representation of numbers with a fixed number of digits (dyadic digits in this case) cannot deliver arbitrary precision.
For the example you gave two facts are relevant:
-1- Fractional values cannot be represented in the dyadic system if the smallest usable denominator not is a power of 2.
-2- The result of a subtraction of two numbers of same order of magnitude and similar value (equal leading digits) suffers from “numeric cancellation”: The higher significant positions cancel out one another, and insignificant zeroes are drawn in.
“Is precision really that bad?” Surely you are joking. The result you quoted is precise to 14 decimal digits. Nonetheless numeric cancellation may become a problem with real applied mathematics under specific circumstances.
By the way: You may remember that you learned in school what tiny subset of “all the numbers” has an exact representation with decimal figures.
=== Editing 2020-02-17 about 12:50 UTC===
Since I had the tools made at a former occasion I created this demonstrating spreadsheet concerning the topic. Of course I cannot give any guarantees because I hadn’t a Casio calculator at hand.
Hi, thanks for replying. I’m actually a software developer. Are you familiar with Double precision in C++? Easy example, works perfectly in double precision C++ not like this bug in LibreOffice
$ g++ -Wall -o double double.cpp
$ ./double
0.000100
//g++ -Wall -o double double.cpp
#include <cstdio>
int main()
{
double a = 0.0042;
double b = 0.0043;
double result = b-a;
printf("%6.6f\n", result);
}Don’t worry. Other softwae developers also don’t know and understand everything they should. Concerning the representation of numbers the developers of LibO are well informed as far as I can judge.
In the original post, you show the Calc result to 19 decimal places, then you post C++ code with a printf format string that displays to only six decimal places. Format your Calc cell to display only six places also, and the (displayed) result will be the same.
Sigh. @richy3009: LO does use double precision 64-bit numbers - as explained in the answer I gave to the question you were pointed to by @anon73440385 above. If you knew something more substantial about double than its name, you possibly knew the difference with 32-bit float, which only has ~7 decimal digit precision - vs ~16 decimals in double (which is what you are seeing and ranting about).
Yes, if I manually format to #,##0.0000 it works. But why not simply give the correct answer?
This bug impacts millions of users who have to manually set the display format.
Any software engineer can do this. I would do it, it’s trivial to store the whole integer and decimal places accurately. Why show millions of users something other than the correct answer. Don’t use the CPU’s Double if it doesn’t work. You’re a software developer right? You know it is trivial to implement a bignum class. Stop defending bad code.
You know it is trivial to implement a bignum class
Facepalm. /me stops conversation with a person who pretends to be a software developer, and lacks basic understanding of principles, like performance considerations for applications designed to do high-volume math as their main duty. Just citing the FAQ again.
ad hominem - used by online trolls.
You sound confused, if my Casio calculator can display correctly, and LibreOffice can’t it’s just incompetence. Stop defending broken code.
This finally is superb humor. Who would have expected such an outburst of spirit here?
For those still interested in facts I will attach a demonstrating spreadsheet to my answer. Some visiting this thread probably never saw a real IEEE Double.
Forgive richy3009, please rethink it and search for a deeper knowledge on this matter, it’s heavy to see your audacity in front of persons that truly know about what there are talking.
When I started using computers, some decades ago, computers where used mainly for scientific applications and learning numerical analysis was a required minor within other software courses. Academic education now focuses on the internet and graphic applications. Shame that basic concepts such as radix conversions within limited-size numbers is no longer sketched in the curricula, at least to warn users against the belief of infinite precision.
That said, I bet Casio calculator also use IEEE-754 format but they display with a fixed number of decimal places (probably determined by the format of the input numbers to avoid to give a non-relevant residue as the result).
Thank you for your sheet Lupp. I think you may be missing he point. Ask any chartered accountant, or software engineer if it is appropriate to display the result to of
0.0042 - 0.0043 as 0.0000999999999999994
Any child, even at primary school can tell you the answer is 0.0001
You may misunderstand our thinking. I’m doing accounts, we expect an spreadsheet to work in decimal, not in 2s compliment, mantissa, exponent, I’ve course we are all well aware of Double precision. I’m a Masters graduate, I find my comments very credible. Unfortunately we find these forums don’t have qualified people. May I politely ask, do any of you have commits in the source for LibreOffice?
I can see you’ve taken a computer science approach, displaying to users, accountants strange numbers, instead of the obvious example. This is just yet another example of the way those with computer science degrees, cannot provide what the user expects. What is worse, you blame the user for not understanding!
you blame the user for not understanding
No. But one thing is when it’s someone just not knowing (and then describing things to that person is a good thing); and another is when the person publicly announces to be a software developer, having understanding on the topic; and that changes the discussion to another level, changing expectations to that person (but still an attempt to describe things is made by many); when that person additionally asserts that the software code is bad and broken, and “I would do it, it’s trivial to store the whole integer and decimal places accurately”, showing lack of understanding despite the announced level of competence, it’s sad - and disrespectful, and impolite, and actually it hurts personally.