Define a range of cells. Like Shift + Click

Question

With this formula =AVERAGE(A1:F5)
I want to do something like this =AVERAGE(A1:A1+5)

Where I can define a range of cells.
I tried OFFSET formula but it does ctrl+click where I need Shift+click.

Answer 1

OFFSET is Volatile I prefer INDEX:

=AVERAGE(INDEX(A:A,1):INDEX(A:A,5))

You can then replace the 1 and 5 with a cell reference in which you put the desired row numbers.