[Solved] Incorrect results from simple math

Discuss the spreadsheet application

[Solved] Incorrect results from simple math

Postby ttimelo » Sun Apr 01, 2018 11:11 pm

First, apologies if this is the wrong place to ask this.

I'm using LibreOffice 6.0.2.1.0+.

I stumbled across a strange result when I subtracted one cell from another. Specifically, I have 1118.77 in A1, 1120.21 in B1, and =B1-A1 in C1. When I click on C1, it shows at the bottom of the window Average: -1.44000000000005; Sum: -1.44000000000005. Why does this happen? To be clear the cell it shows the correct result of -1.44, but the sum shown at the bottom is off.

If I replace the cells with 118.77 and 120.21 the sum shows up correct at the bottom, but 218.77 and 220.21, it shows -1.44000000000005 again.
Last edited by Hagar Delest on Wed Apr 04, 2018 10:37 pm, edited 1 time in total.
Reason: tagged [Solved].
LibreOffice: 6.0.2.1.0+, Linux: 4.15.13-1-ARCH
ttimelo
 
Posts: 3
Joined: Sun Apr 01, 2018 10:49 pm

Re: Incorrect results from simple math

Postby FJCC » Mon Apr 02, 2018 12:24 am

This is a limitation of doing math on a computer. The computer does math in base-2, not the base-10 notation that is used for display, and is limited to a certain number of digits in a number. The computer cannot express certain numbers exactly, though they may look "simple" in decimal notation. It is the same problem that decimal notation has expressing the value of 1/3. It cannot be done exactly with a finite number of digits. This can lead to tiny errors in common calculations and is a big concern when a great number of calculations are needed.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7091
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Incorrect results from simple math

Postby Zizi64 » Mon Apr 02, 2018 12:35 am

Use the round function in your calculation:

Code: Select all   Expand viewCollapse view
=ROUND(B1-A1;2)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Incorrect results from simple math

Postby RusselB » Mon Apr 02, 2018 2:14 am

A couple of other options you could try are in Tools -> Options -> OpenOffice Calc -> Calculate
Namely Precision as shown and/or Limit decimals for general number format.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4933
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Incorrect results from simple math

Postby ttimelo » Mon Apr 02, 2018 7:31 am

FJCC wrote:This is a limitation of doing math on a computer. The computer does math in base-2, not the base-10 notation that is used for display, and is limited to a certain number of digits in a number. The computer cannot express certain numbers exactly, though they may look "simple" in decimal notation. It is the same problem that decimal notation has expressing the value of 1/3. It cannot be done exactly with a finite number of digits. This can lead to tiny errors in common calculations and is a big concern when a great number of calculations are needed.


I understand that the math is done in base-2, but why are the results not consistant? As an experiment, I put 40.01 in A1, 40.02 in A2, selected both and dragged down until results started to break and they did at A12 where the result became 40.1200000000001. I do the same with 30.01 in C1, 30.02 in C2, select both and drag down. I drag to 40.12 and result is not broken. I keep going past 50 and it is still not broken. So what happened?
LibreOffice: 6.0.2.1.0+, Linux: 4.15.13-1-ARCH
ttimelo
 
Posts: 3
Joined: Sun Apr 01, 2018 10:49 pm

Re: Incorrect results from simple math

Postby Zizi64 » Mon Apr 02, 2018 9:44 am

I understand that the math is done in base-2, but why are the results not consistant?


Because you can type-in two STRINGS but not two NUMBERS. (4 0 . 0 1 and 4 0 . 0 2)
The AOO will convert the typed-in strings to numeric values before it make any calculation. (And the FILL function must calculate the increase value based on the first two VALUES...) The difference is 0.01.

The number 0.01 (in decimal) seems as a "simple" number... But it equals (in the binary system) to

2^(-7) + 2^(-9) + 2^(-13) + 2^(-14) + 2^(-15) +2^(-16) + 2^(-18) + 2^(-20) + 2^(-21) + 2^(-22) ...
displayed it as a binary number: 0.0000001010001111010111

The sum of the displayed parts above equals 0.009999990463257
Read: a 22 bit binary numeric representation can not equals to the "simple" two decimals 0.01 number.
(22 bit, because the visible parts above have a coefficient value 1, and the invisible parts (2^(-1), 2^(-2), 2^(-3), ...etc) have a coefficient with 0 value.

The AOO/LO Calc works with 64 bit floating point numbers. They are really "wide" numbers, but they are not "infinite wide" representations.

It is pure mathematics, and it is not related DIRECTLY to the quality of the AOO/LO Calc (or any other software)...
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Incorrect results from simple math

Postby Zizi64 » Mon Apr 02, 2018 10:00 am

Here is a better explanation:
http://www.exploringbinary.com/why-0-po ... ing-point/

and you can try it here (type-in 0.1 or 0.01 and convert it):
http://www.exploringbinary.com/binary-converter/
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Incorrect results from simple math

Postby ttimelo » Mon Apr 02, 2018 1:59 pm

Thank you for the explanations and links. I've never noticed this before with other programs, I guess because they lied to me better? :lol:

Converted through that site, 30.01, 30.02, 40.01, and 40.02 gave:

11110.00000010100011110101110000101000111101011100001010001111010111...
11110.00000101000111101011100001010001111010111000010100011110101110...

101000.00000010100011110101110000101000111101011100001010001111010111...
101000.00000101000111101011100001010001111010111000010100011110101110...

I can't make out the difference in binary. But there was also a decimal to floating point converter. Perhaps LO uses the same conversion? It gave:

30.010000000000001563194018672220408916473388671875
30.019999999999999573674358543939888477325439453125

40.00999999999999801048033987171947956085205078125
40.02000000000000312638803734444081783294677734375

From that, the difference between 40.02 and 40.01 is wider than 30.02 and 30.01. If that wider difference is being used by fill and each iteration retains that little extra, it explains why after 12 iterations an unexpect (to me) result can show up. But it is curious that the less wide .01 doesn't seem to cause a similar issue.
LibreOffice: 6.0.2.1.0+, Linux: 4.15.13-1-ARCH
ttimelo
 
Posts: 3
Joined: Sun Apr 01, 2018 10:49 pm

Re: Incorrect results from simple math

Postby Zizi64 » Mon Apr 02, 2018 3:19 pm

I guess because they lied to me better?

Yes. The other name of the such lying is: Rounding.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Incorrect results from simple math

Postby Zizi64 » Mon Apr 02, 2018 3:32 pm

Perhaps LO uses the same conversion?

Not same, but similar. The Floating point type representation is more difficult. You want to use numbers between (for example) 0.0000000123 to 1230000000.0 . Both of these numbers have 11 digits. Right? But what about the numbers like 1230000000.0000000123 ? You can represent them with 20 digit.
The floating point numbers descript the numbers with a mantissa, an exponent, and the +/- sign of the mantissa, and the exponent, Therefore the huge numbes and the very low value number will be described with same 64 bit width "numbers":

https://en.wikipedia.org/wiki/Double-pr ... int_format

That means for you: When you add a huge and a very low value number, somtime the decimal fraction part of result number will be lost!


Otherwise the 64 bit Floating point representation is enough for the most precise engineering calculations too. And the Calc uses such type of the numbers.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Incorrect results from simple math

Postby Alex1 » Tue Apr 03, 2018 12:45 am

40.01 needs one bit more for its integer part than 30.01. So 30.01 has one bit more available for its fraction, causing greater precision.
AOO 4.1.5 & LO 5.0.6 on Windows XP & 7
Alex1
Volunteer
 
Posts: 603
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: Incorrect results from simple math

Postby keme » Tue Apr 03, 2018 12:01 pm

For perspective:
Some 25 years ago a colleague of mine created a timesheet to keep track of personal working hours. All the office employees with flexible hours started using it. Three and a half years later, and all within a week or so, more than half of the users suddenly complained that they "lost" one minute.

This was just after an upgrade of Excel from v4 to v5, so naturally everyone blamed a bug in the software. Not so. Accumulated rounding error it was.

While this happened with MS Excel, it would have been the same with OpenOffice Calc (except ... at the time it would of course have been StarOffice Calc).
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3103
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Incorrect results from simple math

Postby Zizi64 » Tue Apr 03, 2018 4:37 pm

40.01 needs one bit more for its integer part than 30.01.

It is true in fixedpoint type representation of the numbers. But the AOO/LO uses floating point numbers

Here is the bit sequence of both numbers (as double precision floating point numbers):
Code: Select all   Expand viewCollapse view
Decimal_sign___exponent_______________________mantissa__________________________
40.01 = __0___10000000100___0100000000010100011110101110000101000111101011100001
30.01 = __0___10000000011___1110000000101000111101011100001010001111010111000011

the difference IS NOT one bit in this case!

30.01 : Most accurate representation = 3.00100000000000015631940186722E1 it is more than 30.01
40.01 : Most accurate representation = 4.00099999999999980104803398717E1 it is less than 40.01

There is no way to achieve higher precision with the double precision (64 bit) floating point numbers!

you can try it:
http://www.binaryconvert.com/result_dou ... 8046048049
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Incorrect results from simple math

Postby jrkrideau » Wed Apr 04, 2018 12:50 pm

I believe that if one needs extreme precision beyond what one can achieve with floating point numbers one needs to move to something like the commercial packages Maple or Mathematica. If memory holds, the Open Software Sage may also do this.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3682
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Incorrect results from simple math

Postby Lupp » Wed Apr 04, 2018 1:14 pm

@jrkrideau: You surely wanted to point to the Sage mathematical software (See https://www.sagemath.org/). Users searching for "Sage" on the web may be led to the commercial software offered by the Sage Group.

(For the bit of math I still do, I personally use wxMaxima. I don't know Sagemath and can't compare the solutions therefor.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Incorrect results from simple math

Postby jrkrideau » Wed Apr 04, 2018 3:31 pm

Lupp wrote:@jrkrideau: You surely wanted to point to the Sage mathematical software (See https://www.sagemath.org/). Users searching for "Sage" on the web may be led to the commercial software offered by the Sage Group.

(For the bit of math I still do, I personally use wxMaxima. I don't know Sagemath and can't compare the solutions therefor.)

\
Oh, thanks Lupp, I was not aware of the commercial Sage software. My only experience with the https://www.sagemath.org/ software was downloading it for a friend. And I don't think she ever needed it while she was here. It looks like wx Waxima would have been as good a choice as Sagemath but I stumbled over Sage first.

I have never had a use for it. I am not that sophisticated mathematically.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3682
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Incorrect results from simple math

Postby keme » Wed Apr 04, 2018 8:17 pm

By the looks of it, all the titles listed above, and quite a few others, offer "arbitrary precision". IOW, you can have as many decimals as you like. This does not eliminate the rounding error. It will still be a potential threat on the last digits, and sums may not add up as expected. We just push it farther away from view.

We are used to think in decimal, where any fraction can be represented exactly as long as the denominator is a product of 2's and 5's.Computers do it in binary, where fractions where the denominator is a product of 2's can be represented exactly. Other fractions will lose precision when using binary arithmetic.

I did a bit of software testing/investigation for the school some years back, and remember using some software which could calculate with any rational fraction without losing precision. It did this by keeping separate numerator and denominator stored for each variable, and performed fraction calculations. I think it must have been Maxima or R, but I am not absolutely certain.
User avatar
keme
Volunteer
 
Posts: 3103
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Incorrect results from simple math

Postby Lupp » Wed Apr 04, 2018 9:38 pm

We were wandering way off the original topic. Nonetheless all the mathematical software that was mentioned can work with integers beyond the limitations of the standards of computer arithmetic (processor-bound). Rational numbers are (by default) represented as cancelled fractions then. This again cannot strip off, however, the fetters concerning irrational numbers except for very special subsets.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Incorrect results from simple math

Postby jrkrideau » Wed Apr 04, 2018 10:04 pm

keme wrote: I think it must have been Maxima or R, but I am not absolutely certain.

Not R, at least not base R. The R FAQ has an entry---Why are these two numbers not equal?---for the precision problem. Floating point accuracy is the same in R & Calc.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3682
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests