Hello all,
My very first post. happy days.
I am trying to select the last 5 cells that contain a value (number) from a row (very long row with lots of gaps).
Ideally I would like to get the last 5 values for a number of rows from sheet 1 and have those end up in sheet 2 (a lovely summary with the last 5 results).
I can update manually but if I could automate this so that values are sent to sheet 2 as I update sheet 1 that would be fantastic.
thank you.
What would be “the last 5 values for a number of rows”; the last 5 numbers across an arbitrary number of rows, or the last 5 values of one row for an arbitrary number of rows, or …?
Hi.
Apologies if this was not clear.
I want to get the value for the last 5 cells in any given row.
By that I mean I want to extract whatever is written in the last 5 cells of a row number or characters or mixed.
The problem is I have gaps in the rows. That is some cells are blank. So I need to tell the spreadsheet to ignore empty cells in the row and only get the last 5 cells that actually contain something.
Hope this is a bit clearer.
thanks everyone
To lookup the last value within one given row:
=LOOKUP(2;1/(Sheet1.42:42<>"");Sheet1.42:42)
which retuns the last value in row 42 on Sheet1.
If you have mixed text and numeric values within a row and want the last numeric value then
=LOOKUP(2;1/ISNUMBER(Sheet1.42:42);Sheet1.42:42)
All this needs at least LibreOffice 6.1 to work.
Update: 2019-06-25T20:38+02:00
To obtain the last 5 numeric values of one row use
=LOOKUP(LARGE(IF(ISNUMBER(Sheet1.42:42);COLUMN(Sheet1.42:42));{5,4,3,2,1});COLUMN(Sheet1.42:42);Sheet1.42:42)
entered as array formula (close with Shift+Ctrl+Enter instead of just Enter), it will then spread over 5 columns. Note that within the inline array {5,4,3,2,1} the array column separator (here , comma) needs to be used, configured under Tools → Options → Calc → Formula, which depends on the locale. The formula returns #VALUE! error in all array elements if less than 5 numeric values are available.
can this be adapted to get a range? last 5 cells for a row?