countif function

[serhat]

I have two values something like those;
0,0424063491580365
0,0424063491580364

If I subtract them I got zero
but İf I count them with Countif function in a list I got these tw
walues as different, i.e countif(A:A;A1) results in 1 in A:A anothe
value (0,0424063491580364) exists.
How can I solve this problem

 

[joeu2004]

I have two values something like those;
0,0424063491580365
0,0424063491580364
If I subtract them I got zero but f I count
them with Countif function in a list I got these
two walues as different

I don't believe you get exactly zero. If they are in A1 and A2, calculate
=A1-A2 in A3, then compare =A3=0. It should return FALSE.

(Note: There is a reason why I avoid =A1-A2=0. Long story.)

I suspect you think their difference is zero only because of formatting.
When A3 is formatted as General, I get 1.04083E-16, a very small number.

Anyway, if you want to treat them as equal, you should use ROUND. But you
cannot use ROUND directly with COUNTIF. You could write:

=SUMPRODUCT(--(ROUND(A1:A10000,14)=A1))

But there are some subtle differences between that and COUNTIF, especially
when A1:A10000 contains numeric text.

Note: It is "bad practice" to write COUNTIF(A:A,A1), even though you can.
It is not too bad using Excel 2003 and earlier. But with Excel 2007 and
later, Excel must do 1+ million comparisons. It is unlikely you ever have
that many rows. Instead of A:A, choose a reason range like A1:A10000 or
even A1:A100000.

 

[serhat]

I have two values something like those;
0,0424063491580365
0,0424063491580364
If I subtract them I got zero but f I count
them with Countif function in a list I got these
two walues as different-

I don't believe you get exactly zero. If they are in A1 and A2
calculate
=A1-A2 in A3, then compare =A3=0. It should return FALSE.

(Note: There is a reason why I avoid =A1-A2=0. Long story.)

I suspect you think their difference is zero only because of formatting

When A3 is formatted as General, I get 1.04083E-16, a very smal
number.

Anyway, if you want to treat them as equal, you should use ROUND. Bu
you
cannot use ROUND directly with COUNTIF. You could write:

=SUMPRODUCT(--(ROUND(A1:A10000,14)=A1))

But there are some subtle differences between that and COUNTIF
especially
when A1:A10000 contains numeric text.

Note: It is "bad practice" to write COUNTIF(A:A,A1), even though yo
can.
It is not too bad using Excel 2003 and earlier. But with Excel 2007 an

later, Excel must do 1+ million comparisons. It is unlikely you eve
have
that many rows. Instead of A:A, choose a reason range like A1:A10000 o

even A1:A100000.

dear joeu thakns for your reply.
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

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 value
(exatly 122880 values) and there are many different decimal numbers an
by making round some different values may become equa

 

[joeu2004]

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....

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,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.

 

[joeu2004]

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

[....]
You need to accept the fact that the values are truly different (as I
explain below).

I just realized that I was misled by your subject line, "countif function".

Presumably, your issue is not (or should not be) with COUNTIF. After all,
your example numbers are visibly different. So of course COUNTIF(A1:A2;A1)
should return 1.

Instead, your issue is (or should be) with A1-A2 returning exactly zero,
despite the visible difference.

I did explain all that in previous posting. And I alluded to the remedy.
But just to re-iterate and emphasize....

If you want to avoid that dubious heuristic described in KB 78113, you can
do several things.

Change formulas of the form =A1-A2 to =(A1-A2) or =A1-A2-0.

Surprisingly, even just adding parentheses defeats that heuristic. It is as
if parentheses are an operator; so the "last operation" is not subtraction
from the point of view of the heuristic.

Likewise, comparisons of the form IF(A1=A2,...) should be changed to
IF(A1-A2=0,...).

 

[joeu2004]

PPS.... I wrote:

Instead, your issue is (or should be) with A1-A2 returning exactly zero,
despite the visible difference. [....]
If you want to avoid that dubious heuristic described in KB 78113, you can
do several things.

Change formulas of the form =A1-A2 to =(A1-A2) or =A1-A2-0.

Surprisingly, even just adding parentheses defeats that heuristic. It is
as if parentheses are an operator; so the "last operation" is not
subtraction from the point of view of the heuristic.

Likewise, comparisons of the form IF(A1=A2,...) should be changed to
IF(A1-A2=0,...).

But there will be times when the inconsistency goes the other way. That is,
COUNTIF, MATCH and LOOKUP functions will behave as if values are different,
but the values visibly look the same when formatted to 15 significant
digits, and =A1-A2 seems to bear that out (it returns exactly zero).

So it still might be prudent to use --TEXT(...,"0.00000000000000E+00") when
you want to be sure that "what you see is what you get".

 

[joeu2004]

B1: =INT(LOG(C1,2))-52-AND(B2<0,INT(C1)=C1)

Argh! I tried to get fancy with a last-minute edit without full thinking
about and testing it.

For now, just write:

B1: =INT(LOG(C1,2))-52

That will work "well enough" for all values in C1.

For powers of 2, we can a little better when B2<0. But the AND expression
failed to check for powers of 2 per se.

It can be done easily enough. But I suspect I am providing TMI and beating
a dead horse.