Why does this Google Sheets formula for the last non-empty cell in a column work?

user_14124043 0
Viewed 685

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.

array-formulas google-sheets-formula google-sheets max arrays
2 Answers

  1. 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.

  2. 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.

  3. Take the maximum value of that, which is the row number (call it X) of the last non-empty row.

  4. 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.

user_47511730 
=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)
user_56326290
Related