roos01 : tnx. i know what i can do. in fact i can do many other things, BUT i didn't ask what to do.
WHY ZERO is NOT ZERO ?????
Main Topics
Browse All Topics21.8+23.98+2.5-10.5+0.2-6.
is always ZERO. MUST BE.
BUT Microsoft Excel calculates that to be :
0.000000000000003552713678
has Microsoft created a new MATH ????
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Sorry,
I asume that youre values are the result of calculation within a the worksheet. If you put the number format to 2 and then using the values calculated in another formula then it won't leave the shown values as 2 digit values but calculates also with the hidden digits.
example:
A1: = 26.0 + 1.00000000001 and the format is set to 2 digits then the shown result will be: 27,00
B1: = 27,00
C1: = A1-B1
if C1 is not set in format: 2 digits then the value shown will be 0.00000000001
hopre this helps explainingregards,
jeroen
Hi AnswerTheMan,
I've seen this odd spreadsheet behavior as far back as Lotus123 for DOS. FWIW, I've never seen a clear explanation of why it occurs. And still haven't. This behavior can create troubling and hard to find problems in formulas and macros, so I appreciate you're frustration. (I've experienced them too!)
For example, using the values you cited and trying to compare them with another cell set to zero, i.e..
=YourCell = 0
..will return FALSE, clearly not the case.
Formatting is not the issue as far as I can tell, since the underlying values Excel is storing don't change, just their appearance. So, I'm just "chiming in" here; don't know the answer but would love to find out if there's an actual solution (there's plenty of workarounds.)
Consider me subscribed to this thread with you :)
Regards to all,
Justin
When putting youre numbers in 1 cell it returns next error: 3,55271E-15
when putting it in a cell like: =(21,8+23,98+2,5)-(10,5+0,
when putting it like: =(21,8+23,98+2,5)+(-10,5+0
when putting it like: =21,8+0,2+23,98+2,5+(-10,5
Note1: My delimter is , instead of .
Note2: changed 2,5)-(10,5 into 2,5)+(-10,5
I think it has to do with the order of encreasing and decreasing
If you put youre values into 1 cell (not the formulas) and you don't retrieve 0,0000-n I have no clue why you retrieving it that way. I couldn't reproduce it.
Jeroen
Hi roos01,
Try putting AnswerTheMan's values in a consecutive range in a column, let's say column A. Place negatives where need be based on his post. SUM this column in row 11. Same result as the questioner's.
To illustrate, in the cell below the SUM function, try this formula, assuming it's in row 11 column A. Then, in cell A12..
=A11=0
..which will return FALSE. Wrong answer. Any ideas?
Regards,
Justin
Strange indeed, Jeroen.
Just tried the same thing and you are correct. Sounds like this a question for MS. It's unfortunate that we can't be confident when using formulas. Makes our job a bit harder when we don't know if we've made a mistake or just encountered an MS bug.
Out of curiosity, what prompted you to split up the SUM ranges? I wouldn't have even considered that.
Best,
Justin
Ok AnswerTheMan,
I will lay off, just this remark. had same anwer: 3,55271E-15 is same value only different notation.
what I found out that it has to do in which order you subtract the negative number. If you first subtract the numbers with 1 digit and then 2 digits etc. then a clean zero will be returned.
the split up the sum ranges was just to make sure that there is no influence if there is no strange behaviour in the subtraction. Saw it before.
But I can add another one:
Put these values in A1: A10
21,8
0,6
23,98
2,5
-10,5
-5,1
-6,72
-2,8
-12,6
-10,8
this will return: 0,36 as result of the sum =SUM(A1:A10) although =SUM(E1:E4)+SUM(E5:E10) returns Zero.
Looking to this behaviour the answer on the question: "has Microsoft created a new MATH ????" Yes, and AnswerTheMan found the proof.
There is a similar bug about boolean-values, check it out at this page:
http://www.j-walk.com/ss/e
Although I promised not to react anymore (I hope you forgive me AnswerTheMan) I found an site where the same issue is described. And on this site this behaviour is explained. Also is here the ability to donwload an excel workbook which contains more examples of this floating point issue.
Although it is in German
http://www.fhnon.de/mathe-
you might use the translate option of google.
the base of this article is: it is related to the floating point and The machine accuracy is the smallest number, whose addition is still noticed to 1 by the machine . And floating point representation after IEEE standard see
regards,
Jeroen
This isn't a bug, rather it is a computer design issue.
A computer, cannot store most fractional numbers with total accuracy. Computers, in general, use the IEEE (Institute Of Electrical And Electronic Engineers) standard for floating point numbers. This standard provides a way to store fractional numbers in the limited space of an 8-byte number. For most numbers, some approximation must be made.
It's all beyond me but if you are interested you can read up on "IEEE Floating Point Standard". It is not an Excel thing though, you have to be careful with floating point numbers in all programming languages.
Here are some links about it:
http://www.cpearson.com/ex
http://en.wikipedia.org/wi
well, i have nothing tto add but repaet Justin lines :
<<Finally, the clear explanation we were looking for (from the Pearson site). Can't say I'm happy with it, but at least I understand now>>
BUT - as for the points - i'd ratehr give them to roos01 who came FIRST with the IEEE issue.
more then that - after he did'nt understood the Q at all at the begining - he made a fine comeback with the right answer, and i appriciate that.
Thanks all.
Hi
You will note the same phenomenon after fill-dragging down from cells A1 and A2, containing values 1.1 & 1.2 respectively. With the increment of 0.1 added each row down, cell A38 should equal 4.8. No. It shows 4.79999999999999. And so forth down, the effect sometimes disappearing and then reappearing. It has frustrated us much.
Yumatom
Business Accounts
Answer for Membership
by: roos01Posted on 2004-03-18 at 02:29:55ID: 10622522
you could use the function: DecimalPla cesToUse)
'=ROUNDDOWN(NumberToRound,
regards,
jeroen