How to Calculate an Eight-Week Average by Day of the Week in Excel
Question details
The user needs to calculate the average sales for specific days of the week (e.g., all Mondays, all Tuesdays) across an eight-week period to compare different timeframes on a summary sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing average daily sales by weekday across specific eight-week date periods using a summary table.
- Observed behavior
- The current approach using AVERAGEIF combined with INDEX and MATCH formulas only returns the first week's data instead of calculating the full eight-week average.
Ensure your source data contains continuous sales records with valid date formats. Avoid merged cells, as they can interfere with array formulas and PivotTable aggregations.
Restructure Data and Use the AVERAGEIFS Function
By reformatting your data into a flat table layout, you can use AVERAGEIFS to filter calculations by specific weekdays and date ranges simultaneously.
Combining AVERAGEIF with INDEX/MATCH often returns only the first intersecting cell rather than an array of values. Restructuring the data into a tabular format allows the AVERAGEIFS function to evaluate multiple criteria, such as a specific date range (eight weeks) and a specific day of the week.
Ensure your dataset is arranged in columns with distinct headers, such as 'Date', 'Day of Week', and 'Sales'.
If you don't already have a weekday column, create one. Use the formula =TEXT(A2, "dddd") to automatically display the day (e.g., "Monday") based on the date cell.
In your summary sheet, use the formula =AVERAGEIFS(SalesRange, DayOfWeekRange, "Monday", DateRange, ">=Start_Date", DateRange, "<=End_Date"). Replace the text and cell references with your actual 8-week period boundaries.
Unpivot Data with Power Query and Summarize via PivotTable
If your original data is in a cross-tabular format (e.g., weeks as columns), use Power Query to unpivot the data and create a PivotTable to calculate averages easily.
Easily Calculate and Analyze Averages in WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools, including advanced AVERAGEIFS formulas and robust PivotTables, to help you calculate multi-week averages with ease.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your source data file containing the daily or weekly sales records.
- 2. Format data as a table: Highlight your data and press Ctrl+T to convert it into a structured table, ensuring dynamic updates when new weeks are added.
- 3. Insert a PivotTable: Navigate to the Insert tab on the top ribbon and click 'PivotTable'. Select a new worksheet for your 8-week comparison report.
- 4. Configure the report fields: Drag your 'Weekday' field into the Rows section, and your 'Sales' field into the Values section.
- 5. Change calculation to Average: Click on the Sales field in the Values area, select 'Value Field Settings', and change the calculation method from Sum to Average.
Frequently Asked Questions
How do I extract the day of the week from a date in Excel?
You can use the TEXT function to extract the weekday. For example, entering =TEXT(A2, "dddd") will return the full name of the day, such as 'Monday'. Alternatively, the WEEKDAY function returns a number from 1 to 7 representing the day.
Why is my AVERAGEIF formula combined with INDEX and MATCH only returning the first match?
When INDEX and MATCH are used inside an AVERAGEIF formula in a standard way, MATCH stops searching after it finds the very first instance that meets the criteria. This prevents it from aggregating multiple weeks. To aggregate, you need to use AVERAGEIFS on a flattened dataset.
Can I calculate the average for weekends only?
Yes. By adding a helper column using the WEEKDAY function, you can flag Saturdays and Sundays. Then, use AVERAGEIFS where the criteria points to the weekend flags over your specified 8-week date range.
