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.
Those statements repeatedly show my point:
Any child, even at primary school can tell you the answer is 0.0001
As shown in https://math.boisestate.edu/~calhoun/teaching/matlab-tutorials/lab_19/html/lab_19.html , this only tells that those who answer have not enough math education, having no idea about applied and finite-precision math.
I’m a Masters graduate, I find my comments very credible
That is definitely great! “I believe I’m right (who could even guess!) - take that as a proof!”
Unfortunately we find these forums don’t have qualified people
Nice to see this argument towards people trying to describe you what you refuse to understand (and here I’m not talking about myself)
This is just yet another example of the way those with computer science degrees, cannot provide what the user expects
This is just another false statement. All of those you are talking to are those who help users. Some had at some point questions related to this: see tdf#63242 (comment 10). Some are fixing related bugs because they understand the problems of users (e.g., tdf#129606). If you simply refuse to listen, that is not a proof, and trying to make others look “not suited to the task” is of course so nice from a person who tried to accuse an opponent in ad hominem attack based on the opponent stating the obvious.
The thing is Mike, if you’re a qualified software developer, you too will understand it is trivial to fix. Unfortunately repliers here just want to describe the current broken behaviour.
Someone even said it was a performance issue, this just gets laughable. No user will ever notice the difference in calculation and displaying accurately the decimal places. Unless there is maybe 1,00,000 of these on a sheet.
Well, I’ve politely explained this several times. I won’t comment further. Can someone close this topic?
Sorry, but I think you misunderstand, I’m not seeking an understanding of the current behaviour. I explained why the current behaviour is unsuitable. I’m not seeking any workarounds for the current quirky behaviour.
With time, legacy applications that don’t understand what users require, will be replaced by spreadsheet applications that do. Fingers crossed for the future
I’m not seeking an understanding of the current behaviour. I explained why the current behaviour is unsuitable. I’m not seeking any workarounds for the current quirky behaviour
Oh nice! so you are totally clueless. You didn’t even try to read the site’s use rules and what is it for (available from the site’s homepage: look for “How to use the Ask site”). This is the site for user questions (“how do I …”). So typical for such an expert!
Someone even said it was a performance issue, this just gets laughable. No user will ever notice the difference in calculation and displaying accurately the decimal places. Unless there is maybe 1,00,000 of these on a sheet
So you could just start with “I don’t know what spreadsheets are used for in reality, and (mis)use it for my Casio calculator”.
In accounting, you never use double, you only use integer and you must choose your unit. If you’re counting $, €, £ or any other currency with subunits, your arithmetic unit for calculations must be ¢ or p. To display with decimal places, you format the number with a custom string.
Using integer in accountancy is safe. I’ll never trust your invoices, tax documents or ledgers if you routinely use floating-point in them. You’ll be faced with the nightmare of rounding, even in data entry.
A spreadsheet has uses outside accountancy, this is why floating-point numbers are offered for cases where the order of magnitude cannot be represented in computer integers.
As a software developer, you have to make a design decision as to which representation is best appropriate for the problem and accept the consequences of a bad choice. Don’t blame other for this choice.
This is odd, I see Mike while not supporting this fix here, has filed my bug report on libreoffice before I could get around to filing it. Ironic.
Carefully read ajlittoz message, not really sure what point ajlittoz is making. However, I still hope a good spreadsheet can be developed to represent decimal places correctly. It’s trivial, someone will do it.
@richy3009: as any program, Calc has limitations. Integer numbers can be represented exactly if they are within range (but beware of arithmetic operations which could drastically limit the operational range – that(s what numerical analysis is made for). With IEEE-754, you have 53 bits of accuracy, i.e. 15 decimal digits. This should be enough for accountancy, even to compute percentages. Warning! Order of calculations can make a difference.
If you ever overflow this range, even in intermediate results, you are no longer with integer numbers but with rounded approximate floating-point numbers. Then, you’ll experience the trouble you’re trying to fix.
The simplest fix (safest et quickest to get) is to reconsider your spreadsheet to use integer arithmetic. As a software developer, you shouldn’t be disqualified for the task. Just care for range overflow and don’t use functions which will automatically force you into floating-point.
(continued)
After any division, immediately ROUND(result,0) or TRUNC(result,0) to remain in integer arithmetic.
Don’t use the financial functions because they are based on exponentiation and logarithm. If you must build an amortizing table, do it as a separate document and don’t reinject the results into your accounting ledger without rounding or truncating to integer cents. Never use $ or € with fractional cents otherwise, you’re again floating point.
I see Mike while not supporting this fix here, has filed my bug report on libreoffice before I could get around to filing it. Ironic
I never said that the sample you presented here is correct. I only told you that your statements about used datatypes are wrong, and I keep saying that.
We don’t just dismiss any cases when users report “strange” results of calculations. We investigate them, although in most cases, they are simple floating-point rounding error, which is not a bug (and I don’t see any change here in a foreseeable future). However, in this specific case, the investigation shown that one of the numbers was converted not to the double it should, but to a next close value. That is a bug, and it should be fixed.
However, in this case, a simple approach like using locale-dependent C/C++ functions like atof or operator>> would introduce an unacceptable overhead, and the fast standard function is not yet available for the supported compilers. Thus, some fast yet robust home-built routine is needed. Which is, despite your claim, not a trivial operation. And no, using slow routines is not acceptable, since there are lots of spreadsheets with tens of millions calculations, taking minutes to load, and minutes (or at least tens of seconds) to calculate. It’s not about an empty sheet with 10 simple formulas.
FTR: tdf#130725.