AVERAGE formula for dynamic range

Bit of an odd post this because my formulas actually seem to work, I just don't understand how one part can work and just wondering if anyone knows why.

I was wanting to create a formula that locates that last used (non-blank) cell in column in order to create a dynamic AVERAGE formula. In order to find the last non-blank cell and return the row number I used this array formula:

=SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))

This returns the row number of the last non-blank in column A, and seems to work fine. After a bit of Googling I found the following for the dynamic AVERAGE formula:

=AVERAGE(A1:INDEX(A:A,B1))

Where B1 is the location of the SUMPRODUCT formula from before (row no. of last used row). Now this also seems to give the correct answer (correct average from the numbers I've tried) but I actually don't understand how this can work at all and not produce an error? My understanding of the AVERAGE function is that the syntax needs to be either a RANGE from which to take the numbers or number themselves. However in this example it starts with a range (A1) but the second part of the reference (INDEX(A:A,B1) just returns the contents of the last non-blank cell. So in my spreadsheet the last blank cell is A20 and has the number 500 in it, so as far as I know the formula evaluates as:

=AVERAGE(A1:500)

Which makes no sense and if I type that in I just get an error. Yet somehow in formula format it gives the correct average for the dynamic range. I'm curious to know how Excel comes back with the correct answer and also a bit reluctant to use the formula even though it seems to work without understanding why.

Thanks in advance for any insights! :-)