last non-empty row of another sheets, how *row() works?-CodePudding

=INDEX(Jun!C:C, MAX((Jun!C:C<>"")*ROW(Jun!C:C)))

Above is what I found on the web. With this formula, I can get the value of the last row in sheet Jun.

Could you explain how MAX((Jun!C:C<>"")*ROW(Jun!C:C)) works? I tried to study it alone but merely got a constant value of 1. It means only when it was put inside INDEX can work as we expect.

CodePudding user response：

index() is an array enabling function which means that the expression in the second parameter will be evaluated for all rows in the column Jun!C:C, one row at a time, obtaining as many results as there are rows in that column:

=index(Jun!C:C, max((Jun!C:C<>"") * row(Jun!C:C)))

The (Jun!C:C<>"") expression will give a false on empty rows and true on rows that are not blank, and that Boolean gets coerced to a 0 or 1 when it is multiplied by the row number given by row(Jun!C:C). Many of these results will be zero because all blank rows will have (Jun!C:C<>"") ≃ 0. The max() function then picks the largest result of them all.

The max() expression is in the second parameter to index(), which tells which row to get in the array specified by the first parameter, i.e., in the column Jun!C:C. The row number gets set to the maximum row number where the value in the cell on that row is not blank nor a zero-length text string.

The end result is that the formula gets the value in the last cell that has visible content in column Jun!C:C.

My understanding is that the earliest reference to the pattern was in Sheets Challenge #1 in the Google Docs Help Forum in 2015-01-09:

=INDEX(A:A,MAX(IF(LEN(TRIM(A:A)),ROW(A:A))))  (44) Yogi
=INDEX(A:A,MAX(ROW(A:A)*(A:A<>"")))           (34) --Hyde
=INDEX(LOOKUP(2,IF(A:A<>"",1),A:A))           (34) AD:AM
=INDEX(A:A,MATCH(2,IF(A:A<>"",1)))            (33) AD:AM
=SORT(A:A,(A:A<>"")*ROW(A:A),)&""             (32) 2n9
= SORT(A:A,ROW(A:A)*(A:A<>""),0)              (31) Isai
= SORT(A:A,ROW(A:A)*(A:A<>""),)               (30) 2n9
= SORT(A:A,A:A<>"",,ROW(A:A),)                (29) Isai

See index().

CodePudding user response：

To explain what =INDEX(Jun!C:C, MAX((Jun!C:C<>"")*ROW(Jun!C:C))) does, you have to understand some basic concepts:

Assume your A:A has 5 columns, but only row 1-3 has a value,

A
A1
A2
A3

if you put the formula =ArrayFormula(A:A) in B1, you get this:

A	B
A1	A1
A2	A2
A3	A3

A:A is an Array, it contains values as the same number as the length of the A column,

Now, what happen if we change the formula =ArrayFormula(A:A) to =ArrayFormula(A:A<>"")?

You get this:

A	B
A1	TRUE
A2	TRUE
A3	TRUE
	FALSE
	FALSE

<> in excel and google sheet act as a NOT operator, when you type in a formula A1<>10, it means you are testing the value in A1 to see if it is not equal to 10, if it is 10, you get FALSE, if it isn't, you get TRUE.

=ArrayFormula(A:A<>"") will test every cell in the A Column and see if they contains an EMPTY Value or not, in Excel and Google sheet, Empty is also considered as a kind of value.

TRUE and FALSE are called Boolean value, which in Excel and Google sheet, has the same weight as 1 and 0, you can tell by warping up TRUE or FALSE with the INT() function, it will return 1 and 0.

ROW() function return the index of a given row, if you apply this formula =ArrayFormula(ROW(A:A)) to C Column of our test data, you will get this result:

A	B	C
A1	TRUE	1
A2	TRUE	2
A3	TRUE	3
	FALSE	4
	FALSE	5

As mentioned earlier, TRUE and FALSE have the same weight as 1 and 0. A basic Math concept is, when you multiple any number by 1, it returns the same number, and when you multiple any number by 0, the result is always 0.

Now, let's do the calculation in D Column by this formula =ArrayFormula(B:B*C:C), this is what you will see:

A	B	C	D
A1	TRUE	1	1
A2	TRUE	2	2
A3	TRUE	3	3
	FALSE	4	0
	FALSE	5	0

As you can see, D4 and D5 become 0, since B4 and B5 is FALSE, and FALSE has the same weight as 0, so when we multiply B4 and B5 with C4 and C5, it returns 0.

finally, MAX() function will return the biggest number of an array, so if you put down this formula =MAX(D1:D5) somewhere into our sample data, you will get 3 as the result, which means ROW number 3 is the last row with a value in Column A.