Arithmetic Inconsistencies

in the course of implementing a checksum column I encountered the following problem, which is more than annoying. Here is my example: cells b1 and c1 contain 2 numbers, cell a1 the sum of b1+c1 as an input!, no formula.

2 calculations to check if the a1 input is correct:
version A =if(b1+c1-a1=0;"ok";"false")
version B =if(b1+c1=a1;"ok";"false")
Both should deliver the same result, but it is not in several cases. e.g. input in cells a1-b1-c1 is 67,65-56,37-11,28 results in "false" according to formula version A and "ok" in version B.
Working with 67,66-56,38-11,28 results in "ok" for both versions. This works for many other variants as well and I am really deeply surprised and began to ask myself if the problem is me but excel as this is a very very basic thing.

I am not a newbie but rather experienced in Excel (>20 years), I use Excel 2002 SP3, settings calc on auto, precision as displayed unchecked.

Can anybody reproduce this behaviour? Any remarks or hints? Or is just my Excel corrupt? Any suggestions are appreciated.

P e t e r

Hi Peter and welcome to the forum,
Do these articles apply or help?
http://www.cpearson.com/excel/rounding.htm
http://support.microsoft.com/kb/214118

Peter, welcome to the Board, going forward please try to keep your thread titles specific to the nature of the problem at hand - I've modified on this occasion.

another link on the topic: http://support.microsoft.com/kb/78113

Originally Posted by Marcol

I don't understand your question can you post a sample workbook?

You seem to be using formula to check 3 cells, but your examples have 4?

I think it's been thoroughly answered but here is the example as shown in a workbook.

Originally Posted by junglegreen

sorry here is the file

Just a note, there is no need to put a + in front of the IF as in your example

=+IF(B10+C10-A10=0,1,0)

You get simply use

=IF(B10+C10-A10=0,1,0)

Originally Posted by junglegreen

Having read the kb's makes me understand the issue somehow, but the suggested workaround does not make me happy at all

@Peter, all I would say to the above is that this is not an Excel specific issue, to quote from the kb link I posted earlier:

Originally Posted by MS

Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers.
IEEE is the Institute of Electrical and Electronics Engineers, an international body that, among other things, determines standards for computer software and hardware.
The 754 specification is a very widely adopted specification that describes how floating-point numbers should be stored in a binary computer.
It is popular because it allows floating-point numbers to be stored in a reasonable amount of space and calculations to occur relatively quickly.
The 754 standard is used in the floating-point units and numeric data processors of nearly all of today's PC-based microprocessors that implement floating-point math, including the Intel, Motorola, Sun, and MIPS processors.

Originally Posted by DonkeyOte

@Peter, all I would say to the above is that this is not an Excel specific issue, to quote from the kb link I posted earlier:

I would like to echo this. It's a problem inherent in numbers. In this case it's due to representing numbers in binary, but you have a similar problem even representing numbers in decimal. For example, you can't represent 1/3 exactly in decimal notation. We know that

1/3 + 1/3 + 1/3 = 1

but because 1/3 is represented as a repeating decimal in base 10, you have to decide on some finite number of places to hold. Then you get

0.3333 + 0.3333 + 0.3333 = 0.9999 ≠ 1

If you do the same calculation in base 3, you get exact numbers:

0.1 + 0.1 + 0.1 = 1

You have the same issue in any language on any computer. Programmers learn not to compare floating point numbers for equality.

This can also lead to discussion of safe numbers and model numbers, which is a bit far afield for this board and I'm also too rusty on it to do it off the top of my head.

What is surprising to me is that there are revisions (10+ years ago) to IEEE 754 that describe base 10 floating point implementations that would resolve 99% (or 1.98 * 2^-1 if you prefer) of these problems, and it's never (to my knowledge) been implemented. From an FPU standpoint, it's more complex than binary floating point, but the days when that was a limitation have long passed.

Considering the ubiquity of Excel, I'd have thought that Microsoft would give Intel a sharp nudge in the ribs.