Issue 108441 - Functions to return first or last non null value
Summary: Functions to return first or last non null value
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.0.3
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2010-01-18 23:34 UTC by scottinsocal
Modified: 2017-05-20 09:56 UTC (History)
3 users (show)

See Also:
Latest Confirmation in: 4.1.0-dev
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description scottinsocal 2010-01-18 23:34:07 UTC
I've switched to OpenOffice and love it. I gave away my license for Office2007,
because I don't see myself going back. However, there's a feature for cell
formulas that I've needed several times, and it would give you one up on MS.

Last() and First()

Given a range of cells in a row or column, it would return the first and last
entry in the list. Optional: allow a second parameter that would specify top to
bottom/left to right or bottom to top/right to left listing. Have it return
whatever value is there - number, date, string, whatever. An extra added bonus
would be stripping of intermixed null values. I need the last value in a random
length list of items several times a day, and I've got a fairly complex - and
fragile - formula I use for it. Personally I've never needed a First() value,
but if there's a Last() it only makes sense that there would also be a First().

=Last(A1:Z1,0) (read the values in cells A1 to Z1, return the last valid entry
in the list, reading top to bottom)

=Last(A1:Z1,1) (read the values in cells A1 to Z1, return the last valid entry
in the list, reading bottom to top)

If the second parameter isn't passed, assume TtB and LtR order.

I don't program regularly in any language that would be useful to you, but it
seems like it would be fairly easy to read the values in, convert to an array,
strip null values, and return the last entry. Or first.
Comment 1 discoleo 2010-01-19 17:50:13 UTC
The generic issue is finding the last entry in a list/spreadsheet. Although Calc
does not support selecting a whole row or column (it always generates a
cellrange with start & stop-cells), lets suppose it is possible to select a
generic row or column. I will symbolize this as [Row_n] and [Column_n] (this is
different from Rows[1:MaxRow]).

The issue is then finding the last element in [Row_n] or in [Column_n].

The more generic range:
 [Row_ri:Row_rj][Column_ci:Column_cj] can be decomposed into:

 MAX(Column_ci:Column_cj] between the Rows [ri, rj]
 MAX(Row_ri:Row_rj] berween the Columns [ci, cj]
depending on request to have the last horizontal, or last vertical element.

Actually, the more accurate function is:
 MAX(1:Column_cj] - MAX(1:(Column_ci-1)) between the Rows [ri, rj]
Comment 2 Edwin Sharp 2014-01-10 12:15:21 UTC
Moved to: