Strange behavior of the COUNTIF function for ranking data

[billj]

Hello everyone,

We sometimes use the COUNTIF function to rank data such as: =COUNTIF($A$2:$A$707,">"&A3)+COUNTIF($A$2:A3,A3)

This formula worked well for me for a long time. But just today, this formula failed: it made the same ranking of 400 for two cells with different values. Is this a bug of the COUNTIF() function?

The original Excel file is attached here.

[Aardigspook]

I have no idea why it's doing that. If you evaluate each portion of the formula separately in the formula bar, starting with the last 'A400', it comes out with 399 as expected.
It's fixable by adding "="& in front of the last A400:

Please Login or Register  to view this content.

[xlnitwit]

Hi,

I imagine you have encountered a floating point issue with that particular value. =COUNTIF(A400,">"&$A$400) returns 1 rather than 0, which is what causes the error you are seeing.

[billj]

Hi, Aardigspook, may I ask if the formula is equivalent to yours?

=COUNTIF($A$2:$A$707,">"&A400)+COUNTIF($A$2:A400,"="&A400)
=COUNTIF($A$2:$A$707,">"&A400)+COUNTIF($A$2:A400,A400)

The "="& can be omitted in the COUNTIF function.

[billj]

Hi, xlnitwit, it is really strange. The number 0.0835250107681711% is not too small.

What is floating point issue?

[Aardigspook]

Yes, the "="& can be omitted, but if you put it in, you'll find that the correct result is returned. I can't explain why, as the two formulae should work identically.

Edit: what's even weirder is that if you change the value in A400 *, then change it back, the correct rank (399) is then returned.
* e.g. from 0.0835250107681711% to 0.0835250107681712%

[xlnitwit]

In fact you only need f2 and then press Enter and the formula will calculate correctly. How was this data originally entered into the cells?

[billj]

that is really difficult to explain why The data were copied from an Excel 2003 sheet. Maybe the version is too old.

[Aardigspook]

@xlnitwit: Pressing F2 then Enter doesn't change the (incorrect) result when I try it.


When I evaluate just COUNTIF($A$2:A400,A400) (by selecting it in the formula bar and pressing F9), I get:

=COUNTIF($A$2:$A$707,">"&A400)+1

which then evaluates to 400.


If I first evaluate the last A400, I get:

=COUNTIF($A$2:$A$707,">"&A400)+COUNTIF($A$2:A400,0.00083525010768171)

then:

=COUNTIF($A$2:$A$707,">"&A400)+0

which evaluates to 399.

As far as I can tell from some random testing, this only occurs for A400 and only if that's entered directly. If I replace the last A400 with "="&A400 the formula works correctly. Same if it's replaced with A400+0 or A400-0.
Edit (correction): If I replace the last A400 with OFFSET(C400,0,-2), the result is also incorrect - very strange.

Seems like it's some sort of bug - maybe report it on the MS forums and see if you get a response there?

[xlnitwit]

It is the first part of the formula that is incorrect
=COUNTIF($A$2:$A$707,">"&A400)
That part returns 399 and not 398 as it should. The last part should, and does for me, return 1.

F2 and enter resolves correctly in 2010 for me.