Function Problems

How to Calculate the Average for the Previous X Months in Excel

Huma Ashraf Ch Oct 12, 2026 869 views

Question details

The user needs a dynamic formula to calculate a rolling average for a specific number of trailing months (e.g., the last 3 months) based on today's date.

How to Calculate the Average for the Previous X Months in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing monthly usage or sales data stored in columns with date headers, and requiring an automated way to evaluate the trailing average.
Observed behavior: A combination of formulas is needed to dynamically reference the past X months from the current date and compute the average of those specific columns.

Before you start

Ensure your header row contains valid date values (e.g., 01/01/2023) rather than just text strings (e.g., 'January'). Format the cells as Dates so the formula can correctly evaluate the timeframes.

Solution 1Recommended

Use AVERAGEIFS with EDATE and TODAY Functions

Combine AVERAGEIFS to calculate the mean based on multiple criteria, TODAY to identify the current date, and EDATE to define the starting month boundary.

This method evaluates the dates in your header row against a dynamic rolling date range. It includes only the columns where the date falls strictly before today and on or after exactly X months prior.

Identify Data Ranges

Locate the row containing your header dates (e.g., F1:Q1) and the corresponding row containing the values you want to average (e.g., F2:Q2).

Enter the Formula

Select the target cell where you want the average to appear and type the following formula: =AVERAGEIFS(F2:Q2, F1:Q1, ">="&EDATE(TODAY(),-3), F1:Q1, "<"&TODAY())

Adjust the Number of Months

To calculate the average for a different timeframe, change the '-3' in the EDATE function to your desired number of trailing months (e.g., use '-6' for the previous six months).

Use AVERAGEIFS with EDATE and TODAY Functions

Dynamic Updates: Because the TODAY() function calculates the current system date, this formula will automatically shift the rolling average forward whenever you open the workbook on a new day.

Advanced Formulas in WPS Spreadsheet

Easily Calculate Rolling Averages with WPS Office

WPS Spreadsheet fully supports advanced array functions and criteria-based calculations like AVERAGEIFS, EDATE, and TODAY. You can seamlessly track monthly trends and build dynamic rolling averages.

1. Open your Dataset: Launch WPS Spreadsheet and open the file containing your monthly tracking data.
2. Insert the Formula: Click on the destination cell and enter the formula: =AVERAGEIFS(F2:Q2,F1:Q1,">="&EDATE(TODAY(),-3),F1:Q1,"<"&TODAY())
3. Calculate Instantly: Press Enter to execute the formula and instantly view your dynamic 3-month rolling average.

100% compatible with Microsoft Excel formulas and .xlsx filesBuilt-in function wizard helps you effortlessly build complex formulasLightweight architecture ensures smooth calculation of large datasetsFree to use for daily data analysis and professional reporting

Frequently Asked Questions

Can I include the current ongoing month in the rolling average?

Yes. To include the current month, adjust the second condition in the formula from "<"&TODAY() to "<="&EOMONTH(TODAY(),0). This includes all dates up to the very last day of the current month.

Why is my formula returning a #DIV/0! error?

A #DIV/0! error occurs if there are no date values in your header row that match the criteria (e.g., no data falls within the previous X months), or if your date headers are formatted as text strings instead of actual Excel serial dates.

How do I calculate a rolling average for the previous X days instead of months?

When calculating by days, you do not need the EDATE function. Simply subtract the number of days directly from the TODAY() function. For example, use ">="&TODAY()-30 as your criteria for the past 30 days.

Excel & Data Problems