Simple COUNTIF does not work

[jimmylavino]

Hello,

I have a very strange problem with Excel 2003 running under Windows 7 64-bit. I am using a very simple COUNTIF statement to count the occurrences of calculated values. For some reason, COUNTIF does not return the expected results.

For example, I have the following data:

Cell A1: 5
Cell B1: 4.9
Cell C1: =A1-B1

Cell C1 returns "0.1".

In Cell A4 I have a COUNTIF formula of "=COUNTIF(C1:C1, 0.1)". This returns "0", when I expected "1".

If I manually enter "0.1" in Cell C1 instead of relying on the formula, COUNTIF correctly returns "1".

If I change Cell B1 from "4.9" to "4.8" (and restore the formula under Cell C1 to return "0.2") and change my COUNTIF to "=COUNTIF(C1:C1, 0.2)", it correctly returns "1".

What am I doing wrong? I tried this on two other computers running Excel 2003 and in Google Docs and get the same result. I assume it is a syntax problem, but can't figure out what.

Thanks in advance.

 

[Jonmo1]

Welcome to the board...

It's not you, it's a floating point precision problem (search google for explaination of it)

Change your formula in C1 to round the result..
=ROUND(A1-B1,1)

Change the 1 to the number of significant digits you want the result rounded to.

Hope that helps.

 

[Saagar]

Very strange.. I was able to recreate the same problem.

Instead of countif, I used IF(C1=0.1,"yes","no") and the result was "no".

I then used IF(C1<0.1,"yes","no") and the result was "yes".

If C1 was 0.1 instead of a formula, then it was fine. For some reason, if the result of a subtraction is 0.1, excel is treating it is slightly less than 0.1.

Somebody else on the forum might know if this is a known bug.

 

[Aladin Akyurek]

Very strange.. I was able to recreate the same problem.

Instead of countif, I used IF(C1=0.1,"yes","no") and the result was "no".

I then used IF(C1<0.1,"yes","no") and the result was "yes".

If C1 was 0.1 instead of a formula, then it was fine. For some reason, if the result of a subtraction is 0.1, excel is treating it is slightly less than 0.1.

Somebody else on the forum might know if this is a known bug.

As jonmo1 has pointed out, it's the well-known floating point precision problem.

Try:

=IF(ROUND(C1,1)=0.1,"yes","no")