Summary: | CALC AUTOFILTER Numerical values duplicated in the drop down pane | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | Colin <that.man.colin> |
Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | UNCONFIRMED --- | ||
Severity: | normal | CC: | erack, heiko.tietze |
Priority: | medium | ||
Version: | 7.2.1.2 release | ||
Hardware: | All | ||
OS: | Windows (All) | ||
Whiteboard: | |||
Crash report or crash signature: | Regression By: | ||
Bug Depends on: | |||
Bug Blocks: | 103512, 109324 | ||
Attachments: | Zip with png and ods |
Description
Colin
2021-11-01 19:18:11 UTC
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?;)) |