Table of Contents
The process of calculating a 7-day moving average in Excel involves taking the average of a set of data points over a period of 7 consecutive days, and then updating the average as new data points are added. This can be achieved by using the AVERAGE function in Excel, combined with the use of relative and absolute cell references to ensure the correct data points are included in each calculation. The resulting moving average can provide a more accurate representation of trends and patterns in the data, as it smooths out any short-term fluctuations.
Excel: Calculate 7 Day Moving Average
In time series analysis, a 7-day moving average is simply the average value of the 7 days leading up to and including a certain date.
The following example shows how to calculate a 7-day moving average for a dataset in Excel.
Example: Calculate 7 Day Moving Average in Excel
Suppose we have the following dataset that shows the total sales made by some company during 20 consecutive days:
To calculate the 7-day moving average of the sales values, we can type the following formula into cell C8:
=AVERAGE(B2:B8)
We can then click and drag this formula down to each remaining cell in column C:
The values in column C represent the 7-day average of the values in the sales column.
For example, the 7-day average of sales on 1/7/2023 is 10.8571.
We can confirm this is correct by manually calculating the average of sales for the seven days leading up to and including this date:
7-Day Sales Avg. on 1/7/2023: (8+10+12+12+13+10+11) / 7 = 10.571
This matches the value calculated by our formula.
Since we clicked and dragged this formula down to each cell in column C, the formula automatically updated to use the most recent 7 days to calculate each 7-day moving average.
For example, cell C21 uses the range B15:B21 to calculate its 7-day moving average:
Note: We had to type our formula starting in cell C8 because this represented the first date that had 7 days to use for calculating the 7-day moving average.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I calculate a 7-day moving average in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-calculate-a-7-day-moving-average-in-excel/
stats writer. "How do I calculate a 7-day moving average in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-calculate-a-7-day-moving-average-in-excel/.
stats writer. "How do I calculate a 7-day moving average in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-calculate-a-7-day-moving-average-in-excel/.
stats writer (2024) 'How do I calculate a 7-day moving average in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-calculate-a-7-day-moving-average-in-excel/.
[1] stats writer, "How do I calculate a 7-day moving average in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I calculate a 7-day moving average in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.