I think my question is about Variable Ranges in a Formula.

I take several motorcycle trips every summer, ranging in length to 60 days.
Rows for each of 60 potential Days, and lots of Columns for stuff like miles, rain, hours, etc

below the 60th Day/Row are rows of formulae for Each Column like Min, Average, Max, etc

For a short 10 day trip - I just fill in the data on the top 10 rows and IGNORE the rest of the rows.
So I have to edit the formulae for the Last Day/Row Number so they only include the relevant 10 days.
(lots of complex reasons, not evident in this simplification, why just deleting the unused rows will not work.)
For example: =AVERAGE(B1:B10)

I want to just change one cell value (in this example to 10) and thus all formulae would calculate rows 1 thru 10.

For example:

=AVERAGE(B1:B,MysteryFormula(cell-address-with-Last-Row-Number))

everything I try keeps return text not address
and, of course, B and MysteryFormula have to be combined into an Address

thanks, Bob