Moving Average formula in Excel not autofilling in table

Question

I recently made an excel table to keep track of my weight, and I wanted to add a column for the 7-day moving average.

The issue I'm now having is that when I create a new item, the Moving Average column doesn't automatically fill in. I used this reference to develop the Moving Average formula: Data analysis was available at https://www.deskbright.com/excel/rolling-average-in-excel/.

Here is the moving average formula:

The problem is that when I create a new entry, like this

The Average Weight formula autocompletes, whereas the Moving Average formula does not. The 7-Day Average column's entries for 6/28 and 6/29 in the image are blank. The formula displayed is for the average weight and uses a standard 7-day workweek rather than rolling.

I know it's very easy to fill it out by dragging the formula down, but is there a way to have it autocomplete?

Answer 1

You could use AVERAGEIFS to make the formula auto-fill and COUNTIFS to guarantee that you only return a number if the table contains the correct date range for a 7-day moving average:

=IF(COUNTIFS(A:A,">="&A2-6,A:A,"<="&A2)<>7,"",AVERAGEIFS(B:B,A:A,">="&A2-6,A:A,"<="&A2))