Strange results

  • Thread starter Thread starter jkrons
  • Start date Start date
J

jkrons

I im working with Excel 2007. I have these numbers

-10.555,12
-14.600,60
-16.341,07
-75.259,65
-27.445,85
-7.899,05
135.760,27
16.341,07

That I like to add. The result should be 0, which is what I get, if i
use =SUM(B19:B26)

Unfortunately the numbers are part of a larger amount, and is
determined by another number in a nother column, so I have to use
SUMIF. My SUMIF looks like this =SUMIF(A19:A26,9334,B19:B26)
but the result of this is -7,27596E-12.

I have seen this before when trying to add calculated numbers (due to
decinmal errors), but in this case all the numbers are typed, not
calculated.

Any ideas why this happens (actually it does for a lot of the
calculations in the column.

Jan
 
You state "but in this case all the numbers are typed, not calculated" and
you seem to assume that there can therefore be no rounding error.
The rounding error that causes this problem is not the general rounding
problem resulting from taking the result an a calculation and rounding it to
so many decimal places.

Rather it the rounding error that comes about when decimal numbers (number
to base 10) are converted to binary numbers (numbers to base 2). Because the
convention being followed (IEEE 754) allows for a finite number of data
points, there are some numbers (and 0,1 is an example) that cannot be
exactly represented in IEEE binary.

To gain a full understanding of this topic, read one or two of the following
Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us

(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm

Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1

Good reading from T Valko
http://blogs.msdn.com/excel/archive...es-excel-give-me-seemingly-wrong-answers.aspx

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
REMINDER: These newsgroups are about to die. We will all meet again at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel
 
Back
Top