Reply to thread

Message: [QUOTE="joeu2004, post: 7409648"] It is not unusual for =A1-A2 to suggest different results from COUNTIF(A1:A2;A1). I misdirected you in my first response. You should have written: try =A1-A2-0 formatted as Scientific. My guess: that is not 0.00E+00, even though =A1-A2 is. I will explain below. But first.... You need to accept the fact that the values are truly different (as I explain below). Alternatively, you can make them the same by rounding values with varied number of fraction digits using --TEXT(A1;"0,00000000000000E+00"). There are 14 zeros in the fraction part. The double-negative converts the text result to numeric. (Note: Although 14 fraction digits is the maximum precision, I would suggest using only 13 zeros in the TEXT expression. I have encountered some anomalous results when formatting to 15 significant digits, the maximum that Excel will display.) It might be prudent to convert the 122880 values in their cells. Suppose A1 has the formula =B1/C1. Change that to =--TEXT(B1/C1;"0,00000000000000E+00"). Alternatively, put =--TEXT(A1;"0,00000000000000E+00") into a parallel column, and reference the parallel column for all dependent calculations. Or replace copy the parallel column and use paste-special-value to replace the original column; then you can delete the parallel column. Alternatively, you can emulate COUNTIF using SUMPRODUCT as follows (normally-entered by pressing just Enter as usual): =SUMPRODUCT(--(TEXT(A1:A10000;"0.00000000000000E+00")=TEXT(A1;"0.00000000000000E+00"))) ----- Explanation.... First, a basic explanation of the way that Excel represents numbers. See [URL]http://support.microsoft.com/kb/78113[/URL] for details. (Caveat: But KB 78113 has many misstatements in an attempt to over-simplify the technical details.) In a nutshell, most non-integers cannot be represented exactly. For example, if you retype the numbers above (do not copy-and-paste the cell values), the following shows their exact values internally (note that I use period to separate integer and fraction parts, whereas you use comma; and I use comma to separate the first 15 significant digits). 20.7286008849557 = 20.7286008849556,99967804321204312145709991455078125 20.7286008849558 = 20.7286008849557,99443787327618338167667388916015625 The point is: there is usually more precision than Excel displays. Consequently, it is not unusual for two cells to display what appear to be the same value, yet __some__ Excel expressions treat them as different because they really. The operative word is "some". The inconsistency is due to Excel's half-baked attempt to make them seem the same, namely the dubious heuristic that is poorly described under the misleading title "Example When a Value Reaches Zero" in KB 78113. As a consequence, =A1-A2 might return exactly zero, but =A1-A2-0 might not(!). Similarly, =IF(A1=A2,TRUE) might return TRUE, but =IF(A1-A2=0,TRUE) might return FALSE(!). Moreover, COUNTIF, MATCH, and LOOKUP functions might return "no match". In your case, the two cells display different values, but =A1-A2 returns zero as if they are the same. That is even more counter-intuitive. But the root cause and principles are the same. To demonstrate your particular situation, I suggest that you try the following experiment in a new workbook. Enter the following values and formulas in the cells indicated. A1: =C2=C3 A2: =C2-C3=0 A3: =COUNTIF(C2:C3,C2) A4: =C2-C3 A5: =C2-C3-0 B1: =INT(LOG(C1,2))-52-AND(B2<0,INT(C1)=C1) B2: 8 B3: =B2+7 C1: 20.7286008849557 C2: =C1+B2*2^B1 C3: =C1+B3*2^B1 Format A4 and A5 as Scientific. B2 can be any integer from 8 to 14, and B3 can add any integer from 1 to 7. C2 will display 20.7286008849557, and C3 will display 20.7286008849558. A4 will display 0.00E+00, suggesting (incorrectly) that that C2 and C3 are the same. Again, the incorrect "exact zero" is due to the arbitrary "correction" described in KB 78113. But A5 will display some very small number like -2.49E-14, demonstrating that C2 and C3 are indeed different. Likewise, COUNTIF returns 1 instead of 2 for the same reason. (FYI, the surprise for me is that A1 returns FALSE in this example. I thought it would be TRUE whenever A4 displays exact zero. In fact, that is the case when B2 is -14 to 7 and when B2 is 15 to 35. But I digress....) I hope that helps you understand the problem. Let me know if you need further explanation. It is very confusing for many people. [/QUOTE]

Verification