In cell B11, I want to display the last value in a column of numbers. The first number is in cell B27. The last number is in cell Bxx. The number of rows is periodically increased (as more data is added) but I always want to display (in cell B11) the latest value to be added to column B. I hope this makes sense.
I am running the latest version of LibreOffice.
What formula should I enter in cell B11?
=LOOKUP(2;1/ISNUMBER(B27:B999);B27:B999)
or whatever the hypothetical maximum end position instead of B999 might be.
2 Likes
Adapting @erAck formula over my answer to your other question:
If there are no more data after Bxx: =LOOKUP(2;1/ISNUMBER(B27:B1048576);B27:B1048576). Again, you would not be abble to insert more rows (above or after) to the spreedsheet.
If there are more data after Bxx: =LOOKUP(2;1/ISNUMBER(B27:Bxx);B27:Bxx). And in Bxx you should add a reminder like “Add data above this row”. Then when you fill the cells until xx-1, insert more rows or cells above Bxx.
Thanks to @erAck and LeroyG for your answers. This resolves my question.
LeroyG, I would appreciate some explanation of the formula (for the education of a Calc novice).
I understand “ISNUMBER(B27:B999)” but do not understand “2;1/”.
Not shure. Hope @erAck could help more than me. Worth of a new question.
“If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion”. LOOKUP Help
If ISNUMBER could return TRUE≈1 or FALSE≈0. And 1/0 results to be #DIV/0!.
From my tests “2” could be replaced with “1”.
LOOKUP() forces its arguments into array mode. In this ISNUMBER(...) returns an array of 1 and 0 values, then 1/ISNUMBER(...) produces an array of 1 and #DIV/0 error values. LOOKUP() has the behaviour of silently ignoring error values in such array, thus looking up the value 2 returns the position of the last 1 value. Looking up 1 instead in this case works as well, but internally more elements are compared to find the last matching value, whereas for value 2 the search does a complete binary search to rule out blocks of data, which is faster.
3 Likes
Thank you @erAck. Not sure I have the ability to fully understand this explanation but it gives me a much better idea of how the formula works. I shall study the relevant LibreOffice Help file.
I very much appreciate the speed and quality of support that I have received from this Forum.