I have this Google Sheets formula that I encountered in this answers here
answer1, answer2, and I couldn't find the support page for it of Google Sheets.
In layman's terms, please explain how this works.
=A5:INDEX(A2:A,COUNTA(A2:A))
| Values | Formula |
|---|---|
| 23 | 36 |
| 42 | 19 |
| 12 | 26 |
| 36 | 10 |
| 19 | |
| 26 | |
| 10 |
I was provided with this refrence to this excel's support page by the @TheMaster.
based on your image...
=A5:INDEX(A2:A,COUNTA(A2:A))
is literally translatable as
=A5:A8
where A5: is start of the range INDEX(A2:A is column of the range end, and COUNTA(A2:A) counts how many cells are not empty in range A2:A
| formula | transcript |
|---|---|
| =A5: | =A5: |
| INDEX(A2:A, | A |
| COUNTA(A2:A)) | 8 |
note that standalone =COUNTA(A2:A) results in 7 but within the formula its 8 due to INDEX being offset
while it is short it is not reliable in case there are empty cells within A2:A range. therefore it is recommended to use:
=A5:INDEX(A:A, MAX((A:A<>"")*ROW(A:A)))
it's a choice of preference but the above is same as:
=INDIRECT("A5:A"&MAX((A:A<>"")*ROW(A:A)))
