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

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
272
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.

VALUECOUNTIF <COUNTIF >COUNTIF >=COUNTIF <=
45481.590407020211
45481.590408119202
45988.640245218193
49580.780656317184
50326.000078416175
51611.340030515166
52895.700222614157
52938.600221713148
53109.540663812139
55135.080724912129
56952.07011110101111
58068.0001561191012
58068.000287128913
59701.790365137814
72409.810070146715
76462.060432155616
83019.160635164517
98144.640182173418
203366.880139182319
578508.480601191220
671661.940753200121
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
842
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,138
Office Version
  1. 2019
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,133,616
Messages
5,659,866
Members
418,535
Latest member
Ajith55

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top