Selecting a variable size range of non empty cells in a fixed bigger range using only vanilla excel function (without vba)

Question

I have a global range, let's say for instance A1:A18, that contains value of the same type (only integers or only real numbers) and maybe also empty cells.

Data is dynamically outputted (through an excel function fetching data) in this range : sometimes the whole range contains non empty cells, sometimes not, with the following properties :

• both ranges of empty cells (range 2) and non-empty cells (range 1) are "one-piece" ranges
• the global range starts with the range 1

Now, in some cell not in the global range, I have the formula =F(A1:18) where F works only for ranges with non empty cells. If my range A1:A18 finishes with empty-cell (and this may well happen), the formula is going to throw an error.

Imagine range 1 is A1:A4. By which (necessarily matrix) formula G(A1:A18,...) depending only on range A1:18, on official excel functions (that is not user-defined excel functions) and not on VBA could I replace the range A1:18 in =F(A1:18) so that =F(G(A1:A18,...)) would in fact calculate =F(A1:A4) ? In others terms : which formula (with aforementioned conditions) gives dynamically A1:A4 from A1:A18 ?

I have also the same question with "#N/A" instead of empty and with "#VALUE!" instead of "empty".

Answer 1

G(A1:A18)=OFFSET(A1,0,0):OFFSET(A1,COUNTA(A1:18)-1,0)does the job