It's supposed to be zero but for some reason the result produces a peculiar series of digits at the end.
Also, here's the .xlsx of it: http://83.212.111.36/Book1.xlsx
It's supposed to be zero but for some reason the result produces a peculiar series of digits at the end.
Also, here's the .xlsx of it: http://83.212.111.36/Book1.xlsx
This happens because of the limited precision of floating point numbers representation on computers and cannot be fixed because it is an intrinsic problem of the way number are represented on a computer. There might be workarounds, such as round() or a clever implementation of the formulas to reduce error propagations.
Excel uses 8-byte (64 bit) floating point representation, meaning that it can achieve a maximum accuracy of about 10^-15. The inaccuracy can then spread across the steps of a calculation.
I suggest that you read this article on Wikipedia that investigates this topic.
Self post: It's a documented limitation for floating point arithmetic: http://support.microsoft.com/kb/78113
IMO it should be warning more visibly when the results can be so blatantly erroneous to not be considered a bug, but anyway.
A workaround is to use round().