COUNTIF - Formula Not Calculating Correctly...

[wakerider017]

I have no idea why the below formula is not working. Numbers in column A are formatted correctly as numbers.

This formula is in cell B2 and copied down to cell B6: =COUNTIF(A$2:A$6,"<"&A2)
An incorrect result is given in cell B5.

I manually typed the results I would expect in column C.

Any ideas??


[TABLE="width: 399"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Incorrect Formula[/TD]
[TD="align: center"]Correct Result[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]-25.3576411748195[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]-8.47000433795198[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]-3.61982188879533[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]-9.68909643012068[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]9.50380974252980[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Did you copy the data in from my first post or did you download the file in the link I provided. If you just copied my data, then it also works for me if I do it like that. Something is not right with the way that number is stored in the original Excel file.

I am not sure what the problem is with that number, but if you select A5, click in the Formula Bar and then hit the Enter Key, the formula reports the correct value for it.

 

[Joe4]

Sorry, but I do not understand why is this occurring? The numbers are not relatively well spread apart, so I'd imagine there must be a technical reason?

You can read the technical reason why it occurs in that link (starting with the "Overview" section).

 

[Rick Rothstein]

You can read the technical reason why it occurs in that link (starting with the "Overview" section).

My first thought was the precision problem, but none of the numbers being compared are close enough to each other where I can see that being the problem. Am I missing something?

 

[wakerider017]

You can read the technical reason why it occurs in that link (starting with the "Overview" section).

Joe I did read through it quickly.
It seems 'precision' can be lost in Excel as numbers are stored as 'Floating Point'.

I guess the part I'm having a hard time with is the fact that -9.68909643012068 is approx 1.2 less than the next lowest number of -8.47000433795198.
I wouldn't think there would be any 'mix-up' when the numbers are that far apart? Or am I not understanding correctly?

 

[Joe4]

My first thought was the precision problem, but none of the numbers being compared are close enough to each other where I can see that being the problem. Am I missing something?

Here's my theory:

In each of the calculations, one number is being compared to itself (how many of A2:A6 and greater than A2), right?
My thought is that something in the way the COUNTIF function works is handling the values in the arguments differently (maybe rounding one but not the other).
It seems to be the only thing that makes any sense to me. Otherwise, it should work as expected.

 

[wakerider017]

My first thought was the precision problem, but none of the numbers being compared are close enough to each other where I can see that being the problem. Am I missing something?

Exactly my thoughts. The numbers are so 'course' I wouldn't think an issue could arise..? I am just testing the count of numbers in the group less than the particular number. Not like I am testing for a precise number as shown in one of the examples on the MS help page...

 

[wakerider017]

Here's my theory:

In each of the calculations, one number is being compared to itself (how many of A2:A6 and greater than A2), right?
My thought is that something in the way the COUNTIF function works is handling the values in the arguments differently (maybe rounding one but not the other).
It seems to be the only thing that makes any sense to me. Otherwise, it should work as expected.

Hmm, but then that would sound like a bug to me. Not an intended result from Excel and MS.

 

[Joe4]

Hmm, but then that would sound like a bug to me. Not an intended result from Excel and MS.

Call it what you like, it is a fact of Excel.

I have seen examples where you subtract a whole number by another whole number, and end up with a value that has a miniscule fractional component like 0.000000000001.
Most of the time, you might not notice. But if you were doing some sort of calculation where you were checking if the difference between two cells was EXACTLY some specific number, it might need to incorrect results, i.e.

=IF(A1-B1=3,"Allowable","Not Allowable")

where it may need to be re-written like this to ensure proper results:

=IF(ROUND(A1-B1,0)=3,"Allowable","Not Allowable")

 

[wakerider017]

Call it what you like, it is a fact of Excel.

I have seen examples where you subtract a whole number by another whole number, and end up with a value that has a miniscule fractional component like 0.000000000001.
Most of the time, you might not notice. But if you were doing some sort of calculation where you were checking if the difference between two cells was EXACTLY some specific number, it might need to incorrect results, i.e.

=IF(A1-B1=3,"Allowable","Not Allowable")

where it may need to be re-written like this to ensure proper results:

=IF(ROUND(A1-B1,0)=3,"Allowable","Not Allowable")

Yep, I've run into situations like that before too (not sure if I was dealing with whole numbers or not, but seemingly easy calculations were not panning out). But like you were saying it usually ended up being a minuscule difference.
There is a difference of ~1.2 between the numbers where the 'error' is occurring here.

I certainly don't want to have to go around including the ROUND function on all formulas I write. Would almost be nice if Excel could allow you to limit the workbook to a certain number of decimals (always rounding to 15 decimals would probably be fine for most), rather than the destructive method of set as displayed.

Oh well. Appreciate the help! At least I know I'm not crazy... maybe

 

[Joe4]

There is a difference of ~1.2 between the numbers where the 'error' is occurring here.

If it is what I think is happening, not quite.

So the formula having issues is:

=COUNTIF(A$2:A$6,"<"&A5)

The value of A5 is: -9.68909643012068
What, if in building the "less than" component, it is experiencing this error/issue, and makes that number slightly larger (by a fraction, i.e. -9.68909643012067).
Then, not only is -25.3576411748195 less than that number, but so is the original number itself.

That is my guess of what might be going on.

I certainly don't want to have to go around including the ROUND function on all formulas I write.

Usually, you don't have to. On sheets where it might make a difference, you may incorporate the "Precision as Displayed" approach instead of updating all important formulas.