Max of prior n cells truncating to first row

Question

Say I have 1000 rows and 1 column (e.g. A1:A1000) of data. I want to define a column in a new column B such that it contains the maximum value of n prior cells (e.g. 5) in A including current row.

The part I'm having trouble with is defining the cell range such that it doesn't produce a #REF! error at first rows (first 4 rows in this example).

So as a simple example

    A       B
1   3.0     3.0      #REF!
2   1.1     3.0      #REF!
3   0       3.0      #REF!
4   2.5     3.0      #REF!
5   2       3.0      =MAX(A1:A5)
6   1       2.5      =MAX(A2:A6)
7   4       4        =MAX(A3:A7)

...

I tried for any 'B' cell "=MAX(A3:A7)" (in row 7) but when I copy it to cell 2 for example it displays a REF error (probably because the range went off the top).

I don't want to have to manual enter the truncated ranges in the first rows - in actuality there'd be 100s.

Answer 1

With INDEX and some math:

=MAX(A1:INDEX(A:A,MAX(ROW()-4,1)))