Google-sheets – How to make the ISBLANK function work

Use the following notation:

=AVERAGE(A$1:A5)

Copying this formula, will yield:

=AVERAGE(A$1:A7)

Absolute references explained:

• $A$1; Both the column and row reference are fixed.
• $A1; Only the column reference is fixed.
• A$1; Only the row reference is fixed.

Reference: Absolute reference

Here is one way to achieve the desired effect: get the value in the last non-empty cell in column A above the current row.

B2  =OFFSET(A1,-LEN(REGEXEXTRACT(JOIN("|",A$1:A1),"\|*$")),0)

(And propagate down the B column). This works under the assumption that none of your column A cells end with character | (if they are dates, they really shouldn't). Another character could be used instead of |.

What the command does:

1. JOIN("|",A$1:A1) joins the content of column A, above the current row, separating the values with |.
2. REGEXEXTRACT(...,"\|*$") extracts only the characters | at the end of the string. (This particular character needs to be escaped with a backslash in a regex.) The idea is that these characters correspond to blank cells that we must jump over.
3. LEN takes the length of the result, i.e., the number of blank cells at the end.
4. OFFSET jumps up by that many rows.