[Libreoffice-bugs] [Bug 117016] New: Formula to Get value of last non-empty cell does not return expected result

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=117016

            Bug ID: 117016
           Summary: Formula to Get value of last non-empty cell does not
                    return expected result
           Product: LibreOffice
           Version: 6.0.3.2 release
          Hardware: x86-64 (AMD64)
                OS: Mac OS X (All)
            Status: UNCONFIRMED
          Severity: minor
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: stuporglue at gmail.com

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

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180415/e230be6d/attachment.html>