Issue with COUNTIF "greater than" with numeric values to 6 decimal places

[JugglerJAF]

I have a series of numbers, sorted in order, each of which has 6 decimal places. If I use the formula =COUNTIF($A$2:$A$22,"<"&A2) in cell B2 and copy it down, I get sequential values of 0 to 20 as expected (the first row returning zero as there are no numbers smaller than it in the range, the second returning 1 because there's 1 smaller number and so on) - so far so good. However...

If I change the "<" operator to ">" and copy the amended formula for all rows, then the results expected should be 20 to 0, and while it starts off ok, it returns an incorrect value for just one row about half way down the column.

B9 and B10 have the expected values of 13, and 12 respectively, but B11 which should return a value of 11, actually returns a value of 12. All other rows then continue correctly from B12 (returning 10) down to B22 (returning 0).

If I use "<" or ">=", then the values calculated are all as expected, but if I use ">" or "<=", then I get the duplicated value on row 11. Strange.

The issue is definitely related to the number of decimal places as it doesn't occur if the numbers are to 5 decimal places, and I've resolved it for now by multiplying my initial numbers by 10 and doing the COUNTIF formula on the amended numbers.

Has anyone else ever encountered an issue like this (I'm running Office 365 on Windows 10)?

The values being used in the calculation and the results I've obtained are as follows if anyone else wants to try on their version of Excel.

VALUE	COUNTIF <	COUNTIF >	COUNTIF >=	COUNTIF <=
45481.590407	0	20	21	1
45481.590408	1	19	20	2
45988.640245	2	18	19	3
49580.780656	3	17	18	4
50326.000078	4	16	17	5
51611.340030	5	15	16	6
52895.700222	6	14	15	7
52938.600221	7	13	14	8
53109.540663	8	12	13	9
55135.080724	9	12	12	9
56952.070111	10	10	11	11
58068.000156	11	9	10	12
58068.000287	12	8	9	13
59701.790365	13	7	8	14
72409.810070	14	6	7	15
76462.060432	15	5	6	16
83019.160635	16	4	5	17
98144.640182	17	3	4	18
203366.880139	18	2	3	19
578508.480601	19	1	2	20
671661.940753	20	0	1	21

 

[Alex Blakenburg]

When I copy your table into my Excel the cutover number that is causing the issue shows up as being 55135.0807239999.
Round(A11,6) and Roundup(A11,6) do nothing although Rounddown(A11,6) does remove the trailing 999.
If you use the Round(A11,5) it works as you would expect.

I am pretty sure I have struck this once before and used something like this to overcome it worked for both > and the <= scenario.
A11+0.0000000001

Clearly not ideal.

I am also on Excel 365 & Windows 10

 

[KRice]

I noticed the same peculiar behavior described by Alex. The value in cell A11 has six decimal places (55135.080724)---your table doesn't include any hidden number formatting that would conceal more digits. Yet when I copy that value into Excel 2019 and format the cell to show more decimal places it becomes 55135.0807239999 automatically. That doesn't directly explain the COUNTIF issues, but I find it interesting and it raises other questions. I duplicated the same incorrect COUNTIF behavior in Excel 2019. If you format the A column to show 10 or more decimal places (I think Excel is limited to 15 digits total anyway), the "24" at the end of the A11 value changes to 239999, but that is the only value in column A that does that...all of the others simply pad 0's for the additional decimal places. Here is a version where the "24" automatically changed to 239999, and incorrect results are generated for the > and <= cases. Simply appending a "0" to the end of 239999 causes the formulas to return the correct results. I suspect this is one example of a bug involving limitations on numeric to binary conversions.

MrExcel20210428.xlsx
	A	B	C	D	E
1	VALUE	COUNTIF <	COUNTIF >	COUNTIF >=	COUNTIF <=
2	45481.59040700000	0	20	21	1
3	45481.59040800000	1	19	20	2
4	45988.64024500000	2	18	19	3
5	49580.78065600000	3	17	18	4
6	50326.00007800000	4	16	17	5
7	51611.34003000000	5	15	16	6
8	52895.70022200000	6	14	15	7
9	52938.60022100000	7	13	14	8
10	53109.54066300000	8	12	13	9
11	55135.08072399990	9	12	12	9
12	56952.07011100000	10	10	11	11
13	58068.00015600000	11	9	10	12
14	58068.00028700000	12	8	9	13
15	59701.79036500000	13	7	8	14
16	72409.81007000000	14	6	7	15
17	76462.06043200000	15	5	6	16
18	83019.16063500000	16	4	5	17
19	98144.64018200000	17	3	4	18
20	203366.88013900000	18	2	3	19
21	578508.48060100000	19	1	2	20
22	671661.94075300000	20	0	1	21
Sheet3

Cell Formulas
Range	Formula
B2:B22	B2	=COUNTIF($A$2:$A$22,"<"&$A2)
C2:C22	C2	=COUNTIF($A$2:$A$22,">"&$A2)
D2:D22	D2	=COUNTIF($A$2:$A$22,">="&$A2)
E2:E22	E2	=COUNTIF($A$2:$A$22,"<="&$A2)