How to calculate a moving average in Excel

Method 1: a trailing average with a relative range

The sample sheet is a shop's daily orders for two weeks in October. The shop was closed on Sunday, October 25, so that date has no row. Daily numbers jump around, especially on weekends, and a moving average smooths them so you can see the trend.

In C4, next to October 21, type =AVERAGE(B2:B4). Fill it down to C13. C5 becomes =AVERAGE(B3:B5), C6 becomes =AVERAGE(B4:B6), and so on: the window moves down one row at a time. Leave C2 and C3 empty, because those days do not have three days behind them.

Do not use dollar signs here. =AVERAGE($B$2:B4) would anchor the top and give a running average of everything so far, which is a different measure.

Choose the window in one cell, blank until it is full

To try 3, 7 or 12 periods without rewriting the formula, put the window size in E2 and let the formula take the last E2 values from a growing range. In Excel 365 and 2024, type this in C2 and fill it down: =IF(ROWS($B$2:B2)<$E$2,"",AVERAGE(TAKE($B$2:B2,-$E$2))).

$B$2:B2 grows by one row each time you fill down, ROWS counts how many values it holds, and TAKE with a negative number keeps the last ones. Until there are enough values, the IF returns "" so the first rows stay blank instead of showing a short, misleading average.

Change E2 to 7 and the column refills: the first value appears on October 27 (49.7) and the last is 57. In Excel 2019 and earlier, which have no TAKE, use the fixed range from Method 1 and start it on the 7th row.

Trailing or centered?

A trailing average uses the current period and the ones before it. It is what you want for dashboards and forecasts, because it only uses data you already have. The cost is lag: a 3-period trailing average runs one period behind the data, and a 7-period one runs three periods behind. In the sample, the trailing 3-day average reaches its first peak, 61, on October 24.

A centered average puts each value in the middle of its window: in C3 type =AVERAGE(B2:B4), so October 20 shows 41.7. The same peak of 61 then lands on October 23, in the middle of the three busy days it summarises. Centered averages suit looking back at past patterns, but the latest period never gets a value, because its window would need data you do not have yet.

Centering is simple for odd windows. For an even window like 12 months, the middle falls between two periods, which is why analysts average two neighbouring 12-month averages (a 2x12 average) when they need it centered.

Last 7 entries or last 7 days?

The sheet has no row for October 25. The last 7 rows therefore cover October 24 to 31, and AVERAGE(B7:B13) returns 57. If you mean the last 7 calendar days, average by date instead. Put the end date in E3 and use AVERAGEIFS with "after the end date minus 7" and "on or before the end date".

That window, October 25 to 31, holds only 6 rows, so the result is the average of 6 days: 54.5. Neither answer is wrong. Pick the one your report means and say so in the heading, for example "average per trading day, last 7 days".

For a live report, put =TODAY() in E3. To average per calendar day, counting the closed day as zero, divide a SUMIFS by 7 instead of using AVERAGEIFS.

Weighted moving average with SUMPRODUCT

A simple moving average gives every day the same weight. A weighted one counts recent days more, so it reacts faster to change. Put the weights in E4:E6 (1 for the oldest day, 3 for the newest), multiply each value by its weight with SUMPRODUCT, and divide by the total weight.

For the last three days (55, 63 and 78 orders), the sum of value times weight is 415, the weights add up to 6, and the weighted average is 69.2, against 65.3 for the simple average. It sits closer to the 78 because the newest day counts three times. Keep the weights in a column the same height as the window, and the weight cells absolute ($E$4:$E$6) when you fill the formula down.

Blanks, zeros and a chart trendline

AVERAGE skips empty cells but counts zeros. If a day's count is missing and you leave it blank, that window averages two values instead of three. If you type 0, it drags the average down as if nobody ordered. Decide which is true, and to show nothing for incomplete windows use =IF(COUNT(B2:B4)<3,"",AVERAGE(B2:B4)).

If you only need to see the trend on a chart, Excel can draw the moving average for you.

1. Make a line chart of the dates and orders.
2. Click the line, then the + button next to the chart, and point to Trendline > More Options.
3. In the Format Trendline pane, choose Moving Average and set Period to 3 or 7.