Created attachment 98698 [details] Example spreadsheet Problem description: Steps to reproduce: 1. In B1:Y1 create a row of 24 integer scores that are not unique 2. In B3:Y3 create a row of 24 random U(1) values as tie-breakers 3. In B4:Y4 sum these rows to create a list of unique values 4. In B5:Y5 rank these with =COUNTIF($B4:$Y4,">"&B4) Current behavior: Some rank numbers appear twice and some are missing. Expected behavior: Visual inspection confirms that the list is unique and so the rank should be unique, yet some numbers appear twice and some are missing. In the attached spreadsheet, 21 appears twice, saying that there are 21 values in that list that are greater than both 0.890526344 and 1.1193137951, yet one of those is greater than the other. The row actual rank shows the actual ranking. Operating System: Ubuntu Version: 4.1.3.2 release
Hi Dave, thanks for reporting. I think this is an issue on the max precision, fifteen digits, and your numbers are beyond that. Marked as duplicate of https://bugs.freedesktop.org/show_bug.cgi?id=37923, a request for enhacement. Please if you are not agree, reopen it. *** This bug has been marked as a duplicate of bug 37923 ***
It's not the same bug. COUNTIF fails to make the correct distinction between numbers that differ by 0.0216 and 0.22878 - that's many, many orders of magnitude greater than floating point precision. Furthermore, SMALL makes the distinction correctly.
Created attachment 98818 [details] Sample file to test I have reorganize in vertical data in your, to make more visible, at least for me. Adding a ROUND() to the formula in column C to choose decimal places, makes more visible that the issue have their source in the precision. Changing decimal places in C1 between 14 to 15 hides or shows the error. In this case with the number 0,119313795119524 now in B10, reducing a decimal to this number works fine. The problem is with values 0,119313795119524 and 0,119313795119525 with 0,119313795119523 and 0,119313795119526 In any case I think it's better keeps under control when we are working on the precision limits. I don't know if it is really a bug or a corner case with the precision limits, but to be sure, changed to new, hoping some dev can verify.
Created attachment 98828 [details] Reformatted example
The fact that ROUND makes the problem go away doesn't address the fact that COUNTIF gets it wrong, and LARGE or SMALL gets it right. See my second attachment. correct COUNTIF number rank rank Error Difference 1.1676675775 18 18 1.0422684914 1.1409266053 19 19 0.0267409722 1.1193137951 20 21 X 0.0216128102 0.8905263441 21 21 0.2287874511 Where COUNTIF makes a mistake in ranking the numbers, the difference between the numbers is either 0.0216128102 or 0.2287874511. This is not a difference at the floating-point-precision level. It may be due to an error in the floating-point precision comparison that COUNTIF uses (and LARGE and SMALL do not). That makes it a bug, not an enhancement.
Never confirmed by QA team - moving to UNCONFIRMED to get confirmation. Thanks all
With version: 4.2.7.2, Linux I have correct results. Closing as worksforme. Please check with newer version. file COUNTIFBug2.ods number correct rank COUNTIF rank Error Difference 1,1676675775 18 18 1,0422684914 1,1409266053 19 19 0,0267409722 1,1193137951 20 20 0,0216128102 0,8905263441 21 21 0,2287874511