I have a contiguous range of cells in a column, all containing a formula. Some cells will have zero values and some will not.
How do I find the address of the last cell containing a non-zero value?
Just for curiosity: Why do you need the ADDRESS?
A full address should contain the sheetname. Is this what you want?
Or do you even … ?
In this instance, the just the cell address is sufficient. A more complete explanation of the task I’m working on is in another reply below.
As one of many options
=ADDRESS(SUMPRODUCT(MAX(IF(A:A=0;0;ROW(A:A))));1;4)
1 Like
Thank you, this is a good approach.
How would you modify this formula if you were only searching a range of, say, C20:C30, instead of all of column A?
Replace both occurrences of A:A with the desired range, specify the second parameter ADDRESS() as COLUMN(cell from same range)
=ADDRESS(SUMPRODUCT(MAX(IF(C20:C30=0;0;ROW(C20:C30))));COLUMN(C20);4)
3 Likes
@byslexic Judging by the title and the text of the question, you asked about the address of the cell.
Judging by your comments, you are interested in the row number. Is your goal still not clear - to find the row number in the sheet or to find the offset from the beginning of the analyzed range?
1 Like
To clarify, I am trying to arrive at the address of the cell containing the last non-zero number in a column. However, since I do know the column already, and I have a defined range, finding the row number is sufficient for me to accomplish the rest.
mikekaganski offered my favorite solution so far, =LOOKUP(2;1/(P7:P13<>0);ROW(P7:P13))
My final formula takes the resulting row number, then with ADDRESS creates an actual cell reference, then INDIRECT and OFFSET gives me the contents of the related cell I actually want. And IFERROR returns a blank in case there’s no data at all.
=IFERROR(OFFSET(INDIRECT( ADDRESS( LOOKUP(2,1/(P7:P13<>0),ROW(P7:P13)),COLUMN(P7))) ,0,-7, ) ,"")
(I’m sure there is a more elegant solution here as well, which I’m glad to accept if one was offered. )
If you don’t want to be able to change the selection (the focus) to the found cell, you don’t need the address. Using OFFSET() or INDEX() should then always be simpler and less error-prone.
Only (next to) if you want to “jump” to a found cell using the HYPERLINK(() function, the address is of additional use.
Since the offset -7 columns from the range P7:P13 is a column I, the formula for finding the associated value is reduced to
=INDEX(I:I;MAXIFS(ROW(P7:P17);P7:P17;"<>0"))
2 Likes
=LOOKUP(2;1/(NOT(ISBLANK(B:B)));ROW(B:B))
find the number of last row with value,
=LOOKUP(2;1/(NOT(ISBLANK(B:B)));B:B)
get the value of last row with value.
Adding row number to the column letters serves for a range.
This formula also works with excel.
On bug https://bugs.documentfoundation.org/show_bug.cgi?id=116216, Eike solved to get the function working in LibreOffice.
1 Like
Although I like the brevity of it, this appears to not work.
I adjusted the formula to read
=LOOKUP(2,1/(NOT(ISBLANK(P7:P13))),ROW(P7:P13))
However, the result is always 13 regardless if that row is zero or not. I suspect because none of the cells are blank. They all contain a formula which may or may not result in Zero.
Suggestions on a fix?
=LOOKUP(2;1/P7:P13;ROW(P7:P13))
2 Likes
I am amazed this works! Would you mind taking a moment to explain a little how this works? I can’t grasp how the LOOKUP function is arriving at the correct row.
I’m afraid this can’t work, if only by accident (or on strictly limited data set). 1/P7:P13 is not guaranteed to produce either a number below 2, or an error, on which this trick is based. And if your cells have something positive 0.5 or smaller, it will misbehave. Try on this set:
| P | |
|---|---|
| 7 | 1 |
| 8 | 0 |
| 9 | 0,25 |
| 10 | |
| 11 | 0 |
| 12 | |
| 13 |
=LOOKUP(2;1/(P7:P13<>0);ROW(P7:P13))
3 Likes
Thank you for that example. You are correct, on numbers smaller than .5 it does not work correctly. My dataset would not have a problem with this as I’ll never have very small or negative numbers, but it’s better to be accurate & foolproof regardless.
Your fix does seem to solve the <.5 issue, but I still don’t really understand what the “trick” is. Can you explain how LOOKUP is finding only the row containing the last non-zero value?
Operator <> will return TRUE (1) or FALSE (0).
The expression 1/(P7:P13<>0) in the array context (at the second argument of LOOKUP, which takes an array there) produces a vector
1/1|1/0|1/1|1/0|1/0|1/0|1/0
which becomes
1|#DIV/0!|1|#DIV/0!|#DIV/0!|#DIV/0!|#DIV/0!
LOOKUP expects a sorted vector, and it ignores errors in it. Thus, it looks for the 2 in the vector consisting of 1s and errors, and returns the last item not greater than the criterion. It will never find a 2 in it, so it will return the result corresponding to the last 1.
1 Like
Thanks, I see what you did there now. That’s a very interesting use of LOOKUP.