Average with parametric cell in Google Spreadsheet

Question

I'm desperately looking for a way to write a function like this:

=SUM(A1:CELL(B1))/COUNT(A1:CELL(B1))

where CELL(B1) contains a text, for example "A37".

In this way, I can do the average only in the cell that I want consider for now. I now seems a strange request, because I could just change directly the formula, but in my case I have to use this value "A37" in a lot of formulas, and so I'm looking for a way to use parametric cell.

Answer 1

As I understand your requirement:

=SUM(INDIRECT("a1:"&B1))/COUNT(INDIRECT("a1:"&B1))  

is possible, but does not seem advisable.