Description: Some numerical values are being presented in duplicate when the autofilter is activated. If everything apart from one of the duplicated values is deselected - keeping both the duplicates selected - then all the resultant cells contain identical data. I have even expanded the column width and formatted the maximum decimal places and it is NOT the notorious anomaly where some numbers have the final digit defined as 1 ie. 2,000000000000001 = 2 If the autofilter pane is activated when the selection has been defined as just the duplicated entry then the display still presents two versions of the same number but only one is activated. Steps to Reproduce: Utilising the attached file, autofilter on column K Observe that some of the numbers are duplicated Clear all and then select both entries for the value 73,6 or any other obvious duplicate OK Examine all the values to satisfy yourself they are indeed identical Activate the filter again and observe that the chosen value is duplicated but only one version is activated Actual Results: Duplicated values in the selection list Expected Results: No duplicated values Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: Version: 7.2.1.2 (x64) / LibreOffice Community Build ID: 87b77fad49947c1441b67c559c339af8f3517e22 CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 176066 [details] Zip with png and ods
(In reply to Colin from comment #0) > I have even expanded the column width and formatted the maximum decimal > places and it is NOT the notorious anomaly where some numbers have the final > digit defined as 1 ie. 2,000000000000001 = 2 It is exactly it. To check: 1. Set column K's filter to display both cases of '73.6' 2. In T278, put formula '=RAWSUBTRACT($K$249;K278)' 3. Drag-copy it down to T1755 4. Observe that unlike other cells, T1755 displays -1.4210854715202E-14 However, I don't know if it's correct to display two entries "named" same, when they denote different values. Either they must have enough precision to allow user tell one from the other, or they should be grouped together. Otherwise, it's impossible to use them in reasonable manner.
(In reply to Mike Kaganski from comment #2) Additionally, the difference here (-1.4E-14) is smaller than 15 significant digits of the two values; this is what makes the two numbers look the same, without trailing non-0 digits, even when 20 decimals shown. This looks like a bug, actually: it should use normal approximate equality check.
(In reply to Mike Kaganski from comment #3) > (In reply to Mike Kaganski from comment #2) > > Additionally, the difference here (-1.4E-14) is smaller than 15 significant > digits of the two values; this is what makes the two numbers look the same, > without trailing non-0 digits, even when 20 decimals shown. This looks like > a bug, actually: it should use normal approximate equality check. Now I'm really confused; Does the formula you prescribed fix the error for me or just prove the error exists AND demonstrate that once again I've learned two new things today. Rounding the preceding values to try to eliminate the .000000000000001 anomaly doesn't solve the problem AND Don't present (mistaken) assumptions as facts ;))
(In reply to Colin from comment #4) > Now I'm really confused; Does the formula you prescribed fix the error for > me or just prove the error exists It is to show that the numbers are different, so for the latter. > AND demonstrate that once again I've > learned two new things today. > Rounding the preceding values to try to eliminate the .000000000000001 > anomaly doesn't solve the problem It does. E.g., in the example that I suggested, if you round the formula in K1755 like '=ROUND(IF(G1755>0;G1755-I1755;"");1)', the double '73.6' entry in the autofilter list will disappear. As to the bug: note another manifestation of the wrong behavior, when selecting *both* '73.6' entries, then opening the filter list again, shows only *one* of these entries selected.
(In reply to Mike Kaganski from comment #5) > (In reply to Colin from comment #4) > > It does. E.g., in the example that I suggested, if you round the formula in > K1755 like '=ROUND(IF(G1755>0;G1755-I1755;"");1)', the double '73.6' entry > in the autofilter list will disappear. Don't misunderstand - not a criticism. I developed the sheet in Libre because it is the more user friendly and intuitive package but in daily use, it has been converted to Excel and then uploaded to Google sheets - simply because Google doesn't play well with ods files. The only reason for the tortuous route is that the "open in google drive" doesn't work and for me, having the sheet on the GDrive absolves me of the issue of preserving a constantly changing sheet against mishap. Being able to access it from other locations is a bonus. My point is that the error doesn't manifest in the GSheets version - is their stuff "open source"? Can it be inspected for inspiration in the resolution of the long decimal anomaly?
Don't see what UX input can add here. Taking the result of a floating number calculation into the auto filter (or any other calculation) requires to consider precision. It would be a user mistake to assume a certain number of decimals. If GSheet (or other) does round it will (silently) fail in those scenarios where the difference is crucial.
(In reply to Heiko Tietze from comment #7) > Don't see what UX input can add here. Taking the result of a floating number > calculation into the auto filter (or any other calculation) requires to > consider precision. It would be a user mistake to assume a certain number of > decimals. If GSheet (or other) does round it will (silently) fail in those > scenarios where the difference is crucial. This user utilised the LO Round() function on each and every preceding value, two of which are simply summed for the third value which appears in the Autofilter. The ERRONEOUS value was examined right out to the user available 15 digits and still didn't demonstrate the long decimal BUG. It would surprise me if NASA are utilising LOCalc for their trajectories to Mars so, making it fit for the normal user's purpose might be considered the objective . Also, it would surprise me if any user would be indexing 1234567890.012345678912345 And 1234567890.012345678912346 I don't really think it's fair to implicate the User. Would you disagree?
Where is the cut-off that distinguish NASA from ordinary users? Ten digits or 5? Me just reviewing the UX part here, if there is a bug as Mike suspects, it should be fixed, of course.
(In reply to Heiko Tietze from comment #9) > Where is the cut-off that distinguish NASA from ordinary users? Ten digits > or 5? > At a guess, the ones without multi-billion dollar budgets who are more dependant upon FOSS. Does that work for you?;))