Function Problems

How to Average the Previous Four Weeks in Excel Excluding the Current Week

Tauseeq MagsiTauseeq Magsi Nov 3, 2026 871 views

Question details

Calculate the average of scores from the exact four weeks preceding the current date, without including any data from the ongoing current week.

How to Average the Previous Four Weeks in Excel Excluding the Current Week
Product
Excel
Device & OS
not provided
Scenario
Tracking weekly performance metrics where a rolling 4-week historical average needs to be calculated dynamically based on today's date.
Observed behavior
A dynamic formula is required to automatically identify the current week based on the date, step back to extract the prior 4 weeks' range, and compute the average.
Before you start

Ensure your weekly date column is sorted in ascending order (oldest to newest), as the MATCH function requires sorted data to accurately locate the current date's position.

Solution 1Recommended

Use a Dynamic AVERAGE, INDEX, and MATCH Formula

Combine these functions to dynamically locate the current week's position and extract the specific range of the preceding four weeks.

The MATCH function finds the relative position of today's date within your date range. By using the INDEX function, you can shift this position backward to select specific rows. Shifting back by 4 rows provides the start of your 4-week range, and shifting back by 1 row provides the end of the range, effectively skipping the current week.

1
Organize your dataset

Ensure your dates are listed in column B (e.g., B2:B9) and sorted from oldest to newest. Enter your weekly scores or values in column C (e.g., C2:C9).

2
Select the target cell

Click on the cell where you want the calculated 4-week rolling average to be displayed.

3
Enter the formula

Type the following formula into the formula bar: =AVERAGE(INDEX($C$2:$C$9,MATCH(TODAY(),$B$2:$B$9,1)-4,1):INDEX($C$2:$C$9,MATCH(TODAY(),$B$2:$B$9,1)-1,1))

4
Apply and test

Press Enter. The formula will calculate the average of the 4 weeks prior to the current date and will automatically update as the calendar weeks advance.

Formula Breakdown: The '-4' dictates the start of the range (four weeks ago), while the '-1' sets the end of the range (last week), ensuring the ongoing week is strictly excluded.
WPS Spreadsheet Solutions

Calculate Rolling Averages Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports dynamic lookup and statistical functions, including AVERAGE, INDEX, and MATCH. You can easily build auto-updating reports and tracking dashboards with zero compatibility issues.

  1. 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your weekly tracking worksheet containing the dates and scores.
  2. 2. Input the dynamic formula: Select your target cell and paste the AVERAGE, INDEX, and MATCH formula provided in the recommended solution.
  3. 3. Format your results: Use the Number Format dropdown in the Home tab to adjust the decimal places for a cleaner average display.
100% compatible with Microsoft Excel formulas, functions, and formattingLightweight architecture ensures fast performance even with massive datasetsRich data visualization tools to chart your rolling 4-week averagesCompletely free to use with a highly intuitive user interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return an #N/A or #REF! error?

This usually happens if the TODAY() date is completely outside your list of dates, or if the dataset has less than 4 past weeks recorded. If you are currently in week 3 of your dataset, attempting to shift back 4 weeks will force INDEX to look for a row that doesn't exist, causing a #REF! error.

Can I average the previous 4 weeks including the current week?

Yes. To include the current week, change the start offset to -3 and remove the end offset. The formula becomes: =AVERAGE(INDEX($C$2:$C$9,MATCH(TODAY(),$B$2:$B$9,1)-3,1):INDEX($C$2:$C$9,MATCH(TODAY(),$B$2:$B$9,1),1)).

Does this formula work if my weekly dates are in descending order?

No. The MATCH function with a match_type of 1 requires the lookup array to be sorted in strictly ascending order (oldest to newest) to find the correct closest match. If your dates are descending, you will need to sort them first or change the match_type to -1, which requires distinct formula logic.

How can I change the formula to average the previous 6 weeks?

To average the previous 6 weeks, modify the formula by changing the '-4' offset in the first INDEX function to '-6'. Leave the second offset as '-1' to continue excluding the current week.