How to Calculate the Average of Recent Weekday Values in Excel
Question details
The user wants to calculate the average for a specific weekday based only on a defined number of its most recent occurrences within a dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing time-series or tracking data where a rolling average of specific days (like the past 3 Mondays) is required.
- Observed behavior
- Requires a dynamic formula combining modern array functions to accurately filter dates and extract trailing records for calculation.
Ensure your dataset is organized in an Excel Table (e.g., named 'Demo') with dedicated columns for the values to average and the days of the week. Verify you are using a version of Excel that supports dynamic array functions (Excel 365, Excel 2021, or Excel 2024).
Use FILTER and TAKE Functions to Calculate Recent Averages
This method utilizes dynamic array functions to filter the dataset by the desired weekday and then extracts the specified number of most recent values from the bottom of the list to calculate the average.
The LET function allows you to define variables within the formula to keep the syntax clean and readable. The FILTER function narrows the dataset to only rows matching the specified weekday. Finally, the TAKE function extracts rows from the end of the filtered array when supplied with a negative number, which are then processed by the AVERAGE function.
Format your dataset as an Excel Table. Ensure you have a column for your numerical values (e.g., 'Actual NCO') and a column that identifies the weekday (e.g., 'Day of Week').
Define two reference cells in your worksheet: one for the target weekday (e.g., 'Monday', referred to as 'input') and another for the number of recent occurrences you want to average (e.g., '3', referred to as 'previous').
Select the cell where you want the calculated average to appear and enter the following formula: =LET(filtered,FILTER(Demo[Actual NCO],Demo[Day of Week]=input,0),AVERAGE(TAKE(filtered,-previous)))
Press Enter to execute the formula. The result will dynamically update whenever new rows are added to the bottom of your data table.
Calculate Complex Averages Easily with WPS Office
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, TAKE, and LET. This makes it incredibly easy to perform complex data analysis, such as calculating rolling averages for recent weekdays, without needing complex legacy formulas.
- 1. Open your data file: Launch WPS Spreadsheet and open your workbook containing the weekday data.
- 2. Select the target cell: Click on the cell where you want to display the recent weekday average.
- 3. Input the array formula: Type the formula =LET(filtered,FILTER(Demo[Actual NCO],Demo[Day of Week]=input,0),AVERAGE(TAKE(filtered,-previous))).
- 4. Press Enter: Hit Enter to instantly calculate the dynamic average based on your most recent entries.
Frequently Asked Questions
What if my Excel version doesn't support the TAKE function?
If you are using an older version of Excel (like Excel 2019 or earlier), the TAKE and FILTER functions are not available. You would need to use a more complex array formula combining the AVERAGE, IF, and LARGE or INDEX functions to achieve a similar result.
Why is the formula returning a #CALC! error?
The #CALC! error typically occurs if the FILTER function cannot find any rows matching your specified weekday in the dataset. Check your 'input' cell to ensure there are no typos or trailing spaces and that it exactly matches the text in your 'Day of Week' column.
Can I use this formula for other criteria besides weekdays?
Yes. You can easily modify the FILTER condition (Demo[Day of Week]=input) to target any other column and criteria, such as filtering for specific months, product categories, or employee names, while still extracting the most recent values.
