Bad precision in Calc

[mikekaganski]

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)

[mikekaganski]

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.

[richy3009]

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

[mikekaganski]

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

[ajlittoz]

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.

[richy3009]

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.

[ajlittoz]

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

[ajlittoz]

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

[mikekaganski]

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.

[mikekaganski]

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.

[newbie-02]

1. there are! deviations between school math / users expectations on the one side and what calc calculates on the other,
2. recommendations to avoid such are about 30 years old, David Goldberg: ‘What Every Computer Scientist Should Know About Floating-Point Arithmetic’
3. those are generously neglected by calc, namely:
4. 'The standard puts the most emphasis on extended precision, … but strongly recommending that Implementations should support the extended format corresponding to the widest basic format supported, … ’ that would be ‘double-extended’ with 80 bit in intel FPU,
5. ‘However, when using extended precision, it is important to make sure that its use is transparent to the user. … (if) the representation of a displayed value is not rounded to the same precision as the display, then the result of further operations will depend on the hidden digits and appear unpredictable to the user.’