Created attachment 159526 [details] formula 'large' behave different between Version 6.1.6.3 and Version 6.4.0.3 I just realize that formula 'LARGE' behave has change dramatically between Version 6.1.6.3 and Version 6.4.0.3. I post an spreadsheet with an example. If you open with version 6.1.6.3 you will see columns E,F,H,I get the same answer but if you open the sheet with version 6.4.0.3 columns F and I fail to work. Everything is explained in cell A of the sheet. Maybe I am missing something, if it's the case I apologize. Thanks PS: All work done through time in previous versions of Calc is getting wrong answers when moved to version 6.4.0.3 (and my boss is considering leaving linux and calc to go back to MS Office)
Hi, it's me again. Update news. In the same sheet I have added a column with labels instead of range names and same happens. I ver 6.1.6.3 everything is working fine but in version 6.4.0.3 everything is wrong. Thanks, Ernesto
Created attachment 159527 [details] same file as before but with labels
(In reply to Ernesto Bosch from comment #0) > If you open with version 6.1.6.3 you will see columns E,F,H,I get the same > answer but if you open the sheet with version 6.4.0.3 columns F and I fail > to work. all formula results in columns F,I are: 30 already reproducible with: Version: 6.2.8.2 (x64) Build-ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE Calc:
seems to have started with: commit e22ab5e6f6b0ea49231ca454a567133996306116 [log] author Takeshi Abe <tabe@fixedpoint.jp> Tue Oct 30 18:03:10 2018 +0900 committer Eike Rathke <erack@redhat.com> Thu Nov 15 22:12:01 2018 +0100 tree 449d95b120c58a26ab8e0b589701c325c2579030 parent 973a3dd9623107c18c6765d0b247aa34018a0447 [diff] Resolves: i#32345 Make LARGE()/SMALL() return an array ... if the second parameter is an array. This change follows their specification in ODF 1.2. Change-Id: I45c8923f462e9477e1234b47e39dcdd8d2198784 Reviewed-on: https://gerrit.libreoffice.org/62541 Tested-by: Jenkins Reviewed-by: Eike Rathke <erack@redhat.com> cygdrive/d/sources/bibisect/bibisect-win32-6.2 $ git bisect good 84f5ddce4b293c2961284547118c58cae5492633 is the first bad commit commit 84f5ddce4b293c2961284547118c58cae5492633 Author: Norbert Thiebaud <nthiebaud@gmail.com> Date: Thu Nov 15 16:36:58 2018 -0800 source e22ab5e6f6b0ea49231ca454a567133996306116 source e22ab5e6f6b0ea49231ca454a567133996306116 :040000 040000 78bc616af2691d7f6ee5534015b66991b06fffc2 71284b2717a63388f0acc534e17a10e1c5976a28 M instdir /cygdrive/d/sources/bibisect/bibisect-win32-6.2 $ git bisect log # bad: [32300ab0da959bbff1f09736ff655937319e7e3e] source 5f01fe15eb2661f1f9ce12d1d99dc2a705b462ee # good: [b0a56ec98b1368cb5e3e531e0b3f69565af91609] source 3a801799536e6870f2fb111b1cc00b9575a35a39 git bisect start 'master' 'oldest' # good: [696d8ce3656c1a7018e3647f381d01de8432cb19] source 43125f3b0b18b8d9bac9bd459fa234cb5ff9ddb4 git bisect good 696d8ce3656c1a7018e3647f381d01de8432cb19 # good: [313291982701f81331b6569203682bbb5da03ad9] source 922f935f9ddefcfc50d0bbf805c97ea3f9d5c5e6 git bisect good 313291982701f81331b6569203682bbb5da03ad9 # bad: [fa54824924d41aef8e5032a08cefde0a4c1d478b] source d3e4c06ccb3aa4bb9078077a49dc2b2795835a5c git bisect bad fa54824924d41aef8e5032a08cefde0a4c1d478b # good: [c75a7d7620be8cae959afe1d8b1540b2f25854a2] source d278f5cc5a239b6fed7b5a73917caa6d6473e5c6 git bisect good c75a7d7620be8cae959afe1d8b1540b2f25854a2 # bad: [3b78902da688a0163c5f6b8c3c7c524571580b4d] source 58fcd98bd0b66281c6c8a7b55054d8341c2da394 git bisect bad 3b78902da688a0163c5f6b8c3c7c524571580b4d # bad: [27bf7a137322bc6aaf82d0a6bf0e6edc84704762] source caf9cd9389b30cd6eb93735d89aae90c1d835f5c git bisect bad 27bf7a137322bc6aaf82d0a6bf0e6edc84704762 # good: [a5513e401c161e968b8a1bbf473e732379679f8f] source 5f8bae548fd84c26cd05af3563e6b6ba200edf74 git bisect good a5513e401c161e968b8a1bbf473e732379679f8f # good: [be33bfe12594d1ccbfded746ba62d03c28a9ff6f] source d197d524c5292cf6d66be802ec7d1fed78e30416 git bisect good be33bfe12594d1ccbfded746ba62d03c28a9ff6f # bad: [b24238e3ae2acfe1c2d9ab6ad83f6f6bf2021c96] source 525ed5d1fcb89412f0b80be0b1e35410b048c337 git bisect bad b24238e3ae2acfe1c2d9ab6ad83f6f6bf2021c96 # good: [fe088fc629f0d007651c950404ed1264b7efdec1] source 21c41659969cba4dc50d3208fb1736032c971661 git bisect good fe088fc629f0d007651c950404ed1264b7efdec1 # good: [077fe64ad3617f09bd5c7ef952cda7a29837ae32] source 0d3f1ed35a1f211d459b5da8eba83b404b2d8e31 git bisect good 077fe64ad3617f09bd5c7ef952cda7a29837ae32 # good: [94eecb1ddc570e0f8c253b2d48d415cca763d228] source 1d988778095ecbe84f1a1002511377d0708b3443 git bisect good 94eecb1ddc570e0f8c253b2d48d415cca763d228 # bad: [84f5ddce4b293c2961284547118c58cae5492633] source e22ab5e6f6b0ea49231ca454a567133996306116 git bisect bad 84f5ddce4b293c2961284547118c58cae5492633 # good: [fa9bfddba775b7d4fc68e63a169cda079b7dd640] source 973a3dd9623107c18c6765d0b247aa34018a0447 git bisect good fa9bfddba775b7d4fc68e63a169cda079b7dd640 # first bad commit: [84f5ddce4b293c2961284547118c58cae5492633] source e22ab5e6f6b0ea49231ca454a567133996306116
Where is the bug? Current behavior seams to be conforming the ODF 1.2 standard. https://help.libreoffice.org/6.4/en-US/text/scalc/01/04060183.html?DbPAR=CALC#bm_id3149530 So is the bug in 6.2.8 and newer or in older versions? Best regards. JBF
Sorry for my lack of understanding, but I don't understand why ODF 1.2 should made obsolete the all old way of working (I mean copy-and-paste). Maybe there was something wrong with the way to spreadsheet modelling using LARGE/SMALL functions that I ignore. Today it seems to me that ODF 1.2 it's "fixing what it was unbroken". If the is no bug I shall have to throw away years of work. I would really appreciate if someone can explain why LARGE is no fitted anymore for copy-and-paste and have to be used as a matrix formula only. Best regards (In reply to Jean-Baptiste Faure from comment #5) > Where is the bug? Current behavior seams to be conforming the ODF 1.2 > standard. > https://help.libreoffice.org/6.4/en-US/text/scalc/01/04060183. > html?DbPAR=CALC#bm_id3149530 > > So is the bug in 6.2.8 and newer or in older versions? > > Best regards. JBF
Hi Ernesto the work of the function with arrays was solved in https://bz.apache.org/ooo/show_bug.cgi?id=32345, LibreOffice version 6.2 https://wiki.documentfoundation.org/ReleaseNotes/6.2/pl#Changed_spreadsheet_functions, this is way you see the change from 6.1 to 6.4. IMPOV works fine now, all cells in column F are equal also in column I, so all have the same result, from value in B40. Delete all cells exception the first in column F or I and introduce the first cell as array, the result it's the expected for me. I don't think it is a bug, the bug was before.
Created attachment 159539 [details] limitations with new ODF 1.2 starndard as respecto to LARGE/SMALL functions
Hi m.a.riosv: Thanks for your answer, but (there is always a 'but') as you can see in the new attach file in Sheet1b with the old way and labels I was able to model large (very large) variable length sets of data just filling the blanks (yellow in the spreadsheet) without to redo de spread itself. Now, for every set I'll have to redo de formulae. Anyway, I'm thinking to move all the workload to python. I's a pity my boss (at the lab will not be able anymore to work by himself)
Hi m.a.riosv: Thanks for your answer, but (there is always a 'but') as you can see in the new attach file in Sheet1b with the old way and labels I was able to model large (very large) variable length sets of data just filling the blanks (yellow in the spreadsheet) without to redo the spread itself. Now, for every set I'll have to redo de formulae. Anyway, I'm thinking to move all the workload to python. I's a pity my boss (at the lab will not be able anymore to work by himself) Last comment: it feel to me that with new standard a big loss of flexibility has been achieved.
Created attachment 159541 [details] Sample file modified. Forgive me, but I think the issue is being overcomplicated. Please take a look to your file modified, see how some named ranges has been modified to have a relative references.
Thanks m.a.riosv, you solution it's pristine! Clearly I missed the point. Your expertise it's very useful. Ernesto (In reply to m.a.riosv from comment #11) > Created attachment 159541 [details] > Sample file modified. > > Forgive me, but I think the issue is being overcomplicated. > > Please take a look to your file modified, see how some named ranges has been > modified to have a relative references.