Why the Miscalculations?

Question

Level 1

0 points

Why the Miscalculations?

It is very odd, i think there must be a bug somewhere that i wish would be fixed.

In a combination of four calculations whose end result should equal zero, I am getting a report like this: -4.54747E-13

None of the values i have entered have more than two decimal places.
All calculations are Sums or Differences.

Now normally, when i get a balance i just check my work and find a mistake. But not when we're talking about E-13.

I have found several ways around this error, it isn't causing my anything but minor annoyance. Still.

These are the calculations i use if anyone wants to know :

Step one: Several sets of original numbers are added & subtracted to get several subtotals. (some are negative)
Step two: Subtotals are added to get one Total1
Step three: An Independent number is introduced, some original numbers from step one are subtracted to get Total2
Step four: Total 2 is subtracted from Total1 (which should equal zero)

Mac OS X (10.5.8)

Posted on Jul 20, 2010 9:08 PM

Reply

Answer 1

KOENIG Yvan

Level 8

41,807 points

Jul 20, 2010 10:46 PM in response to Shyla QH

Question asked and responded many times.

What you describe is the normal consequence of the way computer tret decimal number (according the IEEE rules).

You must use the ROUND function to get rid of this normal behaviour.

Yvan KOENIG (VALLAURIS, France) mercredi 21 juillet 2010 07:46:51

Reply

Answer 2

Badunit

Level 7

26,560 points

Jul 21, 2010 12:00 PM in response to Shyla QH

Yes, that can be annoying but there is nothing that needs to be "fixed".

As Yvan said, it is a result of how computers do floating point math in accordance with the IEEE standard. There can be very very small errors. Some applications (such as Excel) deviate from the standard by having algorithms that massage the results in accordance with customer expectations. An example would be to give you the zero you are expecting. Others (such as Numbers), do not deviate from the standard. More often than not, the "customer expectation" method is more pleasing but sometimes that very small result is a real result and the "customer expectation" method turns it into a zero. Also, if an application deviates from the standard, the portability of the document (and the formulas in it) is hindered; you may get a different result in a different version of the same application or if imported into a "compatible" application or if you used the same formulas in a program or a script. From a robustness point of view, it is better to write more robust formulas than to rely on the idiosyncrasies of each application to "fix" the results.

One method for dealing with currency is to first multiply everything by 100 so there are no decimals.

An easy example of what you are seeing is 0.1-0.3+0.2=2.77E-17
but, when each element is multiplied by 100: 10-30+20=0

Reply

Answer 3

KOENIG Yvan

Level 8

41,807 points

Jul 22, 2010 3:16 AM in response to Badunit

Hello Badunit

I know professionnals of accountings which make all their calculations upon cents of Euro to get rid of this kind of problem.

Yvan KOENIG (VALLAURIS, France) jeudi 22 juillet 2010 12:16:32

Reply

Answer 4

Badunit

Level 7

26,560 points

Jul 25, 2010 5:16 PM in response to KOENIG Yvan

I can understand why. Accountants do not like to be out of balance, even if only by a fraction of a femto-euro.

Reply

Answer 5

KOENIG Yvan

Level 8

41,807 points

Jul 26, 2010 8:52 AM in response to Badunit

From 1975 to 2003, I ran a pottery workshop and used the ROUND() function in AppleWorks.
I found that it was easier than working in centimes of Francs then in cents of Euros.
The ROUND() function does flawlessly it's duty.

I had two problems in 28 years. They where related to an other problem : a bug putting the auto-recalculate feature on strike.
This odd bug was only corrected in AppleWorks 6.2.9 (correction introducing bugs in others areas).

Yvan KOENIG (VALLAURIS, France) lundi 26 juillet 2010 17:52:46

Reply