I was looking for a solution to find the last non-empty cell in a column.
I found the following solution at Google Docs Editor Help
INDEX(C:C;MAX(ROW(C:C)*(C:C<>""))
This solution works perfectly fine and even works withe empty cells and headers - but I can't figure out how it works. I tried to analyse the suggestion by cutting it up into its parts and looking at the return values separately - but then I couldn't reproduce the desired results.
Can somebody explain what's happening? I am clearly missing something.
=ARRAYFORMULA(ROW(C:C))
will output row numbers like:
1
2
3
4
5
6
7
...
=ARRAYFORMULA(C:C<>"")
will output TRUE
if cells in C column are not empty (otherwise) FALSE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
FALSE
...
=ARRAYFORMULA(ROW(C:C)*(C:C<>""))
will do this (note that TRUE = 1
and FALSE = 0
in PC logic)
1 × TRUE = 1
2 × TRUE = 2
3 × TRUE = 3
4 × FALSE = 0
5 × TRUE = 5
6 × FALSE = 0
7 × FALSE = 0
... × ... = ...
=ARRAYFORMULA(MAX(ROW(C:C)*(C:C<>"")))
will output the highest number so in this case:
5
now INDEX
is type of ARRAYFORMULA
so this will work too:
=INDEX(MAX(ROW(C:C)*(C:C<>"")))
now we move MAX(...)
part into 2nd INDEX
argument which stands for row and as 1st argument we enter our range we want to map:
=INDEX(C:C, MAX(ROW(C:C)*(C:C<>"")))
this translates to:
=INDEX(C:C, 5)
which means: "return cell on 5th row in C column"
to answer your question why =ROW(C:C)*(C:C<>"")
returns only single value - its because there is no command to process array so basically this is equal to:
=ROW(C1)*(C1<>"")
and result can be 0 or 1 - depends on if arguments are
TRUE × TRUE = 1
TRUE × FALSE = 0
FALSE × TRUE = 0
FALSE × FALSE = 0
and wrapping that into MAX
is like having
=MAX(1)
or:
=MAX(0)
C:C<>""
returns TRUE
if a cell is non-empty, which is converted into 1
when doing arithmetic with it. Similarly, it returns FALSE
or 0 when it's empty.
Then, it's multiplied by ROW(C:C)
, so every empty cell will result in 0, and every non-empty cell in a value equal to its row number.
Take the maximum value of that, which is the row number (call it X) of the last non-empty row.
Now we use INDEX to find the value of the X-th row in column C.
It's indeed a bit strange that things like =MAX(ROW(C:C))
on their own produce 1. I don't have an explicit explanation for this, but I think this is Google Sheets automatically expanding certain formulas over arrays, as mentioned here, because of using INDEX
:
Many array formulas will be automatically expanded into neighboring cells, obviating the explicit use of
ARRAYFORMULA
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With