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

JugglerJAF

Active Member
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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Alex Blakenburg

Well-known Member
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

Well-known Member
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
ABCDE
1VALUECOUNTIF <COUNTIF >COUNTIF >=COUNTIF <=
245481.59040700000020211
345481.59040800000119202
445988.64024500000218193
549580.78065600000317184
650326.00007800000416175
751611.34003000000515166
852895.70022200000614157
952938.60022100000713148
1053109.54066300000812139
1155135.08072399990912129
1256952.0701110000010101111
1358068.000156000001191012
1458068.00028700000128913
1559701.79036500000137814
1672409.81007000000146715
1776462.06043200000155616
1883019.16063500000164517
1998144.64018200000173418
20203366.88013900000182319
21578508.48060100000191220
22671661.94075300000200121
Sheet3
Cell Formulas
RangeFormula
B2:B22B2=COUNTIF(\$A\$2:\$A\$22,"<"&\$A2)
C2:C22C2=COUNTIF(\$A\$2:\$A\$22,">"&\$A2)
D2:D22D2=COUNTIF(\$A\$2:\$A\$22,">="&\$A2)
E2:E22E2=COUNTIF(\$A\$2:\$A\$22,"<="&\$A2)

Replies
15
Views
165
Replies
3
Views
70
Replies
1
Views
38
Replies
1
Views
85
Replies
3
Views
136

1,141,770
Messages
5,708,436
Members
421,570
Latest member
BaileyJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back