<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body><table border="1" cellspacing="0" cellpadding="8">
<tr>
<th>Bug ID</th>
<td><a class="bz_bug_link
bz_status_UNCONFIRMED "
title="UNCONFIRMED - Formula to Get value of last non-empty cell does not return expected result"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=117016">117016</a>
</td>
</tr>
<tr>
<th>Summary</th>
<td>Formula to Get value of last non-empty cell does not return expected result
</td>
</tr>
<tr>
<th>Product</th>
<td>LibreOffice
</td>
</tr>
<tr>
<th>Version</th>
<td>6.0.3.2 release
</td>
</tr>
<tr>
<th>Hardware</th>
<td>x86-64 (AMD64)
</td>
</tr>
<tr>
<th>OS</th>
<td>Mac OS X (All)
</td>
</tr>
<tr>
<th>Status</th>
<td>UNCONFIRMED
</td>
</tr>
<tr>
<th>Severity</th>
<td>minor
</td>
</tr>
<tr>
<th>Priority</th>
<td>medium
</td>
</tr>
<tr>
<th>Component</th>
<td>Calc
</td>
</tr>
<tr>
<th>Assignee</th>
<td>libreoffice-bugs@lists.freedesktop.org
</td>
</tr>
<tr>
<th>Reporter</th>
<td>stuporglue@gmail.com
</td>
</tr></table>
<p>
<div>
<pre>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
<a href="https://exceljet.net/formula/get-value-of-last-non-empty-cell">https://exceljet.net/formula/get-value-of-last-non-empty-cell</a> 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</pre>
</div>
</p>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>