Created attachment 97504 [details] The file with the bug We are in progress migrating some PC from XP/MSO2003 to Win7/LO4.1, and converting a (set of) file we have faces a strange things, that seems really a bug. The attached ODS file was converted barely from excel (opened, then save as) and apart some cosmetic change seems to work well. On the third sheet (I intermedia) the strange happen. In cell 17-20/A-D there's 4 FIND() formula, and two of them return erroneous value in respect to excel. After fiddling a bit, i've found the if i simply copy the FORMULA TEXT (not the cell, but the formula; or, the same, i retype it) the correct output appear. You can see in the attachment column F that have the same formula but the correct result. Seems to me that the import filter imported correctly the ''external representation'' of the formula, but wrongly import the ''internal'' one. I'm seeking feedback. Thanks. PS: i've opened the file also on Ubuntu, LO version 1.4.3.2 and depicted the same bug. Operating System: Windows 7 Version: 4.1.1.2 release
Hi Marco, thanks for reporting. Please what are the expected values for: 'I intermedia'.C17:C20 'I intermedia'.D17:D20
Sorry, i'm on Easter holiday now and i cannot seek feedback from the collegue that manage that sheets. AFAI remember, they are correct, but please wait Wednesday for the precise answer...
Ahem, now i'm think i've understood your question. Sorry. i've wrote: In cell 17-20/A-D there's 4 FIND() formula, and two of them return erroneous value in respect to excel. but i meant: In cell 17-20/D-E there's 4 FIND() formula, and two of them return erroneous value in respect to excel. sorry for the typo. ;-(
This are the values with 4.1.6.2 D17: 60% D18: 20% D19: <5% D20: 95% What are the values with excel In the help: " If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion. "
I've opened the file in LO 4.1.6.2 (windows), and still column D-E and column F differ in row 19, while the formula inside is the same. Yes, the right (and excel) answer is: D17: 60% D18: 20% D19: <5% D20: 95% I've not understood your note («If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion.»), i'm simply saying that the same formula in two cell lead to different result. Thanks.
(In reply to m.a.riosv from comment #4) > This are the values with 4.1.6.2 > D17: 60% > D18: 20% > D19: <5% > D20: 95% Contrary to this, the values under CLASSIFICAZIONE % for me show: 5% 20% 80% 95% Setting to NEW. Win 7 64-bit Version: 4.5.0.0.alpha0+ Build ID: 772befa08f02b08da1ab493896a0744bc73781c7 TinderBox: Win-x86@51-TDF, Branch:MASTER, Time: 2014-11-23_22:39:02 Ubuntu 14.10 64-bit Version: 4.5.0.0.alpha0+ Build ID: 772befa08f02b08da1ab493896a0744bc73781c7 TinderBox: Linux-rpm_deb-x86_64@46-TDF-dbg, Branch:master, Time: 2014-11-23_23:25:11
Yep, no problem on 3.5. Still reproducible on 4.5 from Jan 08 2015. Mario: were you using Linux to test on 4.1? I'll add a bibisectRequest. Ubuntu 14.10 64-bit LibreOffice 3.5.0rc3 Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
Created attachment 112058 [details] Screenshot Win7x64 After do a hard recalc.
bibisect result ("git bisect log"): # bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e # good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932 git bisect start 'latest' 'oldest' # bad: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb git bisect bad e02439a3d6297a1f5334fa558ddec5ef4212c574 # bad: [8f4aeaad2f65d656328a451154142bb82efa4327] source-hash-1885266f274575327cdeee9852945a3e91f32f15 git bisect bad 8f4aeaad2f65d656328a451154142bb82efa4327 # good: [369369915d3582924b3d01c9b01167268ed38f3b] source-hash-45295f3cdceb4c289553791071b5d7f4962d2ec4 git bisect good 369369915d3582924b3d01c9b01167268ed38f3b # good: [6fce03a944bf50e90cd31e2d559fe8705ccc993e] source-hash-47e4a33a6405eb1b5186027f55bd9cb99b0c1fe7 git bisect good 6fce03a944bf50e90cd31e2d559fe8705ccc993e # bad: [da317333e5675622f55c9dda17396c659af65320] source-hash-15af925c254f27046427de70a59011e2ac3d6bdb git bisect bad da317333e5675622f55c9dda17396c659af65320 # good: [daa21bbd8c7b50e2ca1c2cbed0e39f0e7b5a1cb2] source-hash-6b11a18071254a443c8fe7e7b0b1c95b0f9fd35e git bisect good daa21bbd8c7b50e2ca1c2cbed0e39f0e7b5a1cb2 # good: [a08143f4bae3d6658dd756b42b6f343298d1f48c] source-hash-b7822657fa67e7265d07f5852057e975e9efae0d git bisect good a08143f4bae3d6658dd756b42b6f343298d1f48c # good: [5255e1fbf1f3fa3ca61c4db3005940205577863c] source-hash-63c004fc3f0bc53ce888ef012f7993b9203a7503 git bisect good 5255e1fbf1f3fa3ca61c4db3005940205577863c # bad: [7be7cf83087144563a18000acdae82c8fd6f4872] source-hash-d59024b652ccfaf7247da113ec36788fe260de74 git bisect bad 7be7cf83087144563a18000acdae82c8fd6f4872 # good: [ad874a5319e9f68e6b3a974e44de838b8a0a82e1] source-hash-4b4ca8030285bd66526ff5bb2b6ea5a75a6c6bc7 git bisect good ad874a5319e9f68e6b3a974e44de838b8a0a82e1 # first bad commit: [7be7cf83087144563a18000acdae82c8fd6f4872] source-hash-d59024b652ccfaf7247da113ec36788fe260de74
Spreadsheets (both Excel and ODS) store cached values for each cell alongside the formulae. The cached values are then used to avoid having to recalculate the whole file on each load. At some point in the past, it seems that the situation existed that LO didn't pay attention to the cached values, and also possibly wrote them incorrectly in some circumstances. The range of commits covered by comment 9 are where the handling of the cached values changed, and we started to trust what was written and not always recalculate on load Unfortunately it seems what we have here is a file which has incorrect cached values which we are now trusting to be correct. I don't see any evidence that there is a current issue with converting from Excel, and the solution for any individual file like this one is just to hard recalculate (Ctrl+Shift+F9) and save again. If there is any such issue with a file that is newly converted to ODS with a current release (4.4 or later) then of course we should investigate further, but on the assumption that there is no currently existing problem here I'm going to close this bug. -> RESOLVED WORKSFORME
(In reply to Matthew Francis from comment #10) > Spreadsheets (both Excel and ODS) store cached values for each cell > alongside the formulae. The cached values are then used to avoid having to > recalculate the whole file on each load. > At some point in the past, it seems that the situation existed that LO > didn't pay attention to the cached values, and also possibly wrote them > incorrectly in some circumstances. > The range of commits covered by comment 9 are where the handling of the > cached values changed, and we started to trust what was written and not > always recalculate on load Many thanks for the explanation. > Unfortunately it seems what we have here is a file which has incorrect > cached values which we are now trusting to be correct. I don't see any > evidence that there is a current issue with converting from Excel, and the > solution for any individual file like this one is just to hard recalculate > (Ctrl+Shift+F9) and save again. I can confirm that LO 4.2.7.2 on ubuntu, with a Ctrl+Shift+F9, recalculate correctly the cells. I can confirm that on windows also, but on windows i've set 'OOXMLRecalcMode' property now and sheets get recalculated on open, so i've simply opened it and was correct (version 4.2.8.2). > If there is any such issue with a file that is newly converted to ODS with a > current release (4.4 or later) then of course we should investigate further, > but on the assumption that there is no currently existing problem here I'm > going to close this bug. I suggest to force a recalculation importing an excel file (or ask about it, or adding a prefs to do it), so we are sure that just conferted excel files does not suffer from ''cached data poisoning''. Thanks.
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]