Excel - formula to calculate average for variable number of rows in table?

Question

I have a table where the value in column B is the average of some preceding values in column A.

I want to use a named range "DaysToCount" to vary the number of rows used for the average. I've tried using ADDRESS and CONCATENATE to build the AVERAGE function like this:

AVERAGE(CONCATENATE(ADDRESS(ROW()-DaysToCount+1,2),":B",ROW())))

It gives me a #VALUE error. Using the Evaluate formula tool I can see it evaluates down to something like this:

AVERAGE("B3:B12")  

What I want is this:

AVERAGE(B3:B12)

Is there a way to fix this - or should I try another approach?

Answer 1

The way to use formulas to build cell references inside a function is to use INDIRECT. I was able to accomplish my goal using this formula:

AVERAGE(INDIRECT("B" & ROW()-DaysToCount+1 & ":B"&ROW()))

Answer 2

The short way is something like this. Let's assume that this formula will be in B7:

=AVERAGE(OFFSET(B7,(-1 * (DaysToCount-1)),-1,DaysToCount))

I don't always like that because of the explicit cell reference there. But getting rid of that takes a bit more effort. You can replace it with one of the techniques here.