ZERO is NOT ZERO ?

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 ?????

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

HEY....why complicate a simple thing ????
PUT the numbers in my Q in a 1 Cell, and make the cell format to 1 million digits OK ?
it is ZERO and it have to be ZERO point 1 millions ZEROS. why it' s not like that ?

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,2-6,72-5,1-10,8-12,6-2,76) this returns: 75,56
when putting it like: =(21,8+23,98+2,5)+(-10,5+0,2-6,72-5,1-10,8-12,6-2,76) then a zero is returned
when putting it like: =21,8+0,2+23,98+2,5+(-10,5-6,72-5,1-10,8-12,6-2,76) then a zero is returned

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

roos01 :  you are missing the issue and your comments are not to the point AT ALL. if you can't understand what it's all about - please lay off this one. thanks.

this is a bit strange.

put all data in own cells but value = 3,55271E-15 using SUM(A11). (have excel 97 on NT here)

usiing next: =SUM(A1:A4)+SUM(A5:A10) returns 0. did check: =A11=0 this returns TRUE

Jeroen

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.

Thanks Jeroen!

Sounds like MS needs to get their (Edited by Computer101 :) together.

Kind of scary, seems like such simple spreadsheet situation. Go figure..

Sorry, AnswerTheMan, welcome to MS-World :))

Does anyone else have some ideas? I and Jeroen appear to be fresh out..

Regards to all,
Justin

There is a similar bug about boolean-values, check it out at this page:

http://www.j-walk.com/ss/excel/odd/odd25.htm

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/excel/rounding.htm
http://en.wikipedia.org/wiki/IEEE_floating-point_standard

Thanks derekpapesch! 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. I'm not in the habit of shooting the messenger :)

Points to derekpapesch, AnswerTheMan, IMHO.

Thanks again,
Justin

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.

Thanks for the grade!
At the end (when understanding the question)  I found it myself amazing that such behaviour exist. Especialy Im normaly not looking in such a direction for answers. Learned something new. Thanks,

Jeroen

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

can't you just format the cells to show only 1 decimal place?  That would give you 4.8 every time.