"serhat" wrote:
Please try these two actual values below which are in my list.
20,7286008849557
20,7286008849558
say A1 and A2
whilst A1-A2=0; countif(A1:A2;A1)=1
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....
"serhat" wrote:
how should I do in order to gget rid of this conflict?
By the way I cannot round them because in my list I have
lots of values (exatly 122880 values) and there are many
different decimal numbers and by making round some different
values may become equal
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
http://support.microsoft.com/kb/78113 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,999678043212043121457099914550781 25
20.7286008849558 =
20.7286008849557,994437873276183381676673889160156 25
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.