GNOME Bugzilla – Bug 671632
Regarding the logic function: "If ... Then ... else ... "
Last modified: 2012-03-11 21:43:15 UTC
I am writing a text for young people and adults about spreadsheets. From an exercise proposed in the text, students are making contact with the "logical operation": If [condition] then [Decision1] else [Decision2] This "action" is not working in some cells [not all]. For example, entered in cell B5 was the "formula": "=-19.6+23.9" (without the quotes) in cell D5 type the numeric value was 4.3, was entered in cell E5 the formula "=if($D5=B5;1;-0.5)" (without the quotes), but the value that Gnumeric put in that cell is "-0.5" when it should be "1". This occurs in other cells and other cells is the result expected. Thanks for your attention
The problem here is the assumption that you are working with (mathematical) real numbers. None of -19.6, 23.9, and 4.3 are exactly representable in the number system used. (See http://en.wikipedia.org/wiki/IEEE_754 for details.) Let "r" be the function that rounds from a traditional real number to the number system used. What you are doing is B5 = r(r(-19.6) + r(23.9)) D5 = r(4.3) B5 is not the same as D5. B5-D5 is -3/(2^50). That's a small number, but it is not 0. So far all spreadsheet you will meet agree. However, Excel and OpenOffice will lie to you and say that the two numbers are the same on the assumption that the difference is caused by rounding errors. That hides your particular problem, but creates a new set of problems elsewhere. In old-style calculator terms, this is the same as not getting 1 when you type 1 / 3 * 3 =. *** This bug has been marked as a duplicate of bug 551796 ***
*** Bug 671850 has been marked as a duplicate of this bug. ***
*** Bug 671851 has been marked as a duplicate of this bug. ***
*** Bug 671853 has been marked as a duplicate of this bug. ***