Description: TL;DR: Formula =LOOKUP(2,1/(NOT(ISBLANK(A1:A$1))),A1:A$1) results in #DIV/0 instead of showing the last non-blank value in the column. LibreOffice Version: Version: 6.0.3.2 Build ID: 8f48d515416608e3a835360314dac7e47fd0b821 CPU threads: 4; OS: Mac OS X 10.13.4; UI render: default; Locale: en-US (en_US.UTF-8); Calc: group Background: I'm operating on the assumption that formulas should work the same in Calc as in Excel. I followed an Excel tutorial found here https://exceljet.net/formula/get-value-of-last-non-empty-cell to get the last value in a sparsely filled column. It did not work in . I tested in Excel (15.30) and it did work If it's not supposed to work, maybe because it relies on a glitch in Excel, then please disregard. To Reproduce: Sparsely fill Column A with data, Enter the forumla above into cell B1 and drag to extend it down so that the formula should capture various of the sparse values. Notes: 1. I will attach a sample file to this issue. 2. I had an old (c. 2015) version of LibreOffice installed until today, and dragging this formula crashed that old version every time. With 6.0.3.2 it does not crash. Steps to Reproduce: 1.Sparsely fill part of Column A with data, can be a mix of numeric and text. 2. Enter the formula =LOOKUP(2,1/(NOT(ISBLANK(A1:A$1))),A1:A$1) into cell B1 and drag down to apply it to additional cells in the B column. Ensure that it will pass by multiple values in column A Actual Results: #DIV/0!, except on numeric cells Expected Results: Show the last non-blank value from Column A in each cell of column B Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
Created attachment 141364 [details] Example showing formula not working
It works in excel, not in LO. Confirm with Version: 6.1.0.0.alpha0+ Build ID: 5289441ffa227f5f358ca4dc28df416be922aa66 CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk3; https://www.excelforum.com/excel-formulas-and-functions/1043140-explain-lookup-2-1-a2-a10-d2-b2-b10.html https://exceljet.net/formula/get-value-of-last-non-empty-cell Not sure if it's bug or not. Adding cc to devs.
This is almost a duplicate of bug 116216 but with two additional quirks. 0. Use master / 6.1 alpha as a prerequisite for the following 1. Instead of =LOOKUP(2,1/(NOT(ISBLANK(A1:A$1))),A1:A$1) write =LOOKUP(2,1/(NOT(ISBLANK(A$1:A1))),A$1:A1) and pull/copy that down. It seems the wrong order in the references adds to some confusion, e.g. if =LOOKUP(2,1/(NOT(ISBLANK(E6:E$3))),E6:E$3) in G6 is replaced with =LOOKUP(2,1/(NOT(ISBLANK(E$3:E6))),E$3:E6) the expected result is returned. Having done so, the formulas in the range G3:G6 return the expected result. 2. The lookup-vector has to be sorted, and error values usually sort behind numeric and string values. Starting from G7 down the vector is not sorted anymore as it is {1,#DIV/0,#DIV/0,#DIV/0,1,...} which yields arbitrary results when queried with a binary search algorithm. 2a) Excel seems to either completely ignore error values in the case of LOOKUP, at least in this constellation, or it ignores that the range is not strictly sorted. This seems to be nowhere specified. It just "happens" to be an implementation detail. In fact in OOXML ECMA-376-1:2016 18.17.7.202 LOOKUP error values are not mentioned at all. (which they are also not for 18.17.7.343 VLOOKUP but appear to be significant there).
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6db5e1bdd67531fab14b375d6c624237d38de297 Related: tdf#117016 DoubleRefToVars: always put in order It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cc2c11c2e4a99adf00d184507d6925c9af37e1bd&h=libreoffice-6-1 Related: tdf#117016 DoubleRefToVars: always put in order It will be available in 6.1.0.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
A polite ping to Eike Rathke: Is this bug fixed? if so, could you please close it as RESOLVED FIXED ? Otherwise, Could you please explain what's missing? Thanks
It's not fixed, the commits were only related to comment 3 case #1. Missing is the actual behaviour that Excel either ignores error values (or just #DIV/0! in this case?) or internally sorts an array before evaluating.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cbdcf8955b6bd1def3e6e8f728bd93746b8df203 Resolves: tdf#117016 omit error values from an interim array in LOOKUP() It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4f35cb303583a66de3b5df548bcad9c24f126f34 tdf#117016 unit test for LOOKUP() interim array omitted errors It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ce91b05fd79e34bbbb07cbec75f2fedf8b1960f3 Avoid unnecessary copy of vector if nothing omitted, tdf#117016 follow-up It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3aa566566923e3a2d582d72c1141774056bb9554 Consolidate duplicated code, use VectorMatrixAccessor, tdf#117016 follow-up It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=72db8ca7d9506b313e78427676c02dd9cb4b4773 SCCOLROW instead of long, tdf#117016 follow-up It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
The file from comment 1 works as expected on: Version: 6.2.0.0.alpha0+ Build ID: e005ab5d40d358adb75a64e140d46f4bf605647d CPU threads: 4; OS: Linux 4.15; UI render: GL; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-09-15_02:08:38 Locale: ro-RO (ro_RO.UTF-8); Calc: threaded