Calculate a moving average in excel where the lookback (window) duration is a variable that changes based on another cell's changing values

Question

I am trying to calculate a moving average of a dataset where the moving window time duration is not fixed, but changes over time based on numbers in a different column. Using the screenshot I have attached as a reference, I Start with a data set in Column A (this can be any data set) and then I use the EXCEL common way of calculating a moving average in column B [=average(A2:A6)] to construct a 5 period moving window just as an example. What I want to do is instruct the moving average to use a duration/window size (A2:Ax) based on the numbers in Column C so that the lookback period is altered over time. As you can see in Column C, this desired lookback period gradually rises over time, getting up to an 8 period lookback window, so that as the dataset gets larger, the moving window duration also gets larger (maxing out at 8 periods in my example here). None of the specific data matters, only the process.

Answer 1

There are multiple approaches that you can take to achieve your desired result, including: (i) by hand, (ii) INDIRECT(), (iii) OFFSET(), (iv) combining with BYROW(). Given that you only provided a screenshot, I created some random data. The final output looks as follows:

(i): formula in cell D6:

=AVERAGE(A5:A6)

This is presumably the most inefficient and error-prone way because you need to shift the window manually but helps to verify that everything works as expected.

(ii): formula in cell E6:

=AVERAGE(INDIRECT(CONCAT(ADDRESS(ROW(),1),":",ADDRESS(ROW()-C6,1))))

In this approach, we use mainly the ADDRESS() function to create the specific range (window specified in column C) over which we want to compute the average.

(iii) formula in cell F6:

=AVERAGE(OFFSET($A$1,ROW()-1-C6,0,C6+1,1))

This approach uses the OFFSET function to offset the values depending on the window in column C.

(iv) formula in cell G2:

=BYROW(A2:INDEX(A:A, COUNTA(A:A)),
LAMBDA(row,
LET(window, INDEX(C:C, ROW(row)),
IF(ISBLANK(window), "", AVERAGE(OFFSET(A1, ROW(row) - 1 - window, 0, window + 1, 1))))))

This is the most advanced and also my preferred approach. It basically also relies on the OFFSET() idea, but through the BYROW function we create a spilling array, which means we put the formula only in cell G2 and it spills as far down as we have data. Thus, it is fully automated and adjusts to the data. How does this formula work?

First, we define dynamically the array over which we want to spill and apply the formula. Since our values start in cell A2, we start with this and then count the number of non-emtpy cells using COUNTA() function. As a next step, we need to specify the parameters/calculations inside the LAMBDA statement. I use here for better readability also the LET() statement as it allows us to define the window and use it then as a parameter. In the end, the actual formula is applied by row, only if the window size is non-empty.

For completeness, here the data used:

data	5d move	window	by hand	indirect	offset	byrow
0.95
0.72
0.26
0.64
0.40	0.59	1	0.52	0.52	0.52	0.52
0.25	0.45	1	0.32	0.32	0.32	0.32
0.38	0.38	1	0.31	0.31	0.31	0.31
0.17	0.36	2	0.26	0.26	0.26	0.26
0.87	0.41	2	0.47	0.47	0.47	0.47
0.64	0.46	2	0.56	0.56	0.56	0.56
0.60	0.53	2	0.70	0.70	0.70	0.70
0.76	0.61	2	0.67	0.67	0.67	0.67
0.71	0.72	3	0.68	0.68	0.68	0.68
0.07	0.56	3	0.54	0.54	0.54	0.54
0.76	0.58	3	0.58	0.58	0.58	0.58
0.46	0.55	3	0.50	0.50	0.50	0.50
0.18	0.44	3	0.37	0.37	0.37	0.37
0.25	0.35	4	0.35	0.35	0.35	0.35
0.83	0.50	4	0.50	0.50	0.50	0.50
0.40	0.42	4	0.42	0.42	0.42	0.42

Answer 2

You can apply the formula in D6 and fill down.

=AVERAGE(OFFSET(A6,0,0,-C6))