How to Calculate Average Weekly Sales Before and After a Date
Question details
The user needs to calculate the average weekly sales for specific time periods—such as five weeks prior to a facility closure and four weeks following a reopening—using date boundaries and facility identifiers.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing business performance by comparing sales data from periods immediately surrounding an operational event, like a closure and reopening.
- Observed behavior
- Requires a formula capable of filtering a dataset by facility ID and multiple dynamic date conditions to compute an accurate average.
Ensure your date column is formatted as actual dates rather than text, and verify that your sales data does not contain hidden errors or text strings that could disrupt mathematical calculations.
Use AVERAGEIFS with Date Boundaries
Use the AVERAGEIFS function to calculate averages based on multiple criteria, including facility IDs and specific date ranges constructed using basic date arithmetic.
The AVERAGEIFS function evaluates multiple conditions before averaging a range of cells. By subtracting or adding days to a target date (e.g., subtracting 35 days for a 5-week period, or adding 28 days for a 4-week period), you can dynamically establish "greater than or equal to" and "less than" date boundaries within the formula.
Ensure your dataset is organized consistently. For this example, assume Facility IDs are in Column A, Dates are in Column B, and Sales figures are in Column C. In separate reference cells, input your target Facility ID (e.g., E2), Closure Date (F2), and Reopening Date (G2).
Select an empty cell for the pre-closure average and enter the formula: =AVERAGEIFS($C:$C,$A:$A,E2,$B:$B,">="&F2-35,$B:$B,"<"&F2). This formula averages the sales in Column C only if the facility matches E2 and the date in Column B falls within the 35 days strictly prior to the closure date.
Select another cell for the post-reopening average and enter the formula: =AVERAGEIFS($C:$C,$A:$A,E2,$B:$B,">="&G2,$B:$B,"<"&G2+28). This calculates the average sales for the 28 days beginning exactly on the reopening date.
Easily Calculate Complex Sales Averages in WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced statistical and logical functions like AVERAGEIFS. You can efficiently analyze sales trends, closure impacts, and date-based criteria with ease.
- 1. Open your sales data: Launch WPS Office, open Spreadsheet, and load the workbook containing your daily or weekly sales records.
- 2. Enter the AVERAGEIFS function: Select a target cell, type =AVERAGEIFS(, and follow the on-screen formula syntax guide to easily select your sales range, criteria ranges, and target dates.
- 3. Apply and analyze: Press Enter to calculate your targeted weekly average, then use the fill handle to drag the formula down for other facilities in your list.
Frequently Asked Questions
Why does my AVERAGEIFS formula return a #DIV/0! error?
A #DIV/0! error in AVERAGEIFS indicates that no records met all of your specified criteria. Double-check that your facility ID matches exactly, the dates fall within the specified boundaries, and there are non-blank sales figures for that period.
Can I calculate the average for months instead of weeks?
Yes. Instead of adding or subtracting a fixed number of days, you can use the EDATE function within your criteria. For example, to set a boundary of exactly one month prior, you can use ">="&EDATE(F2, -1) in your formula.
Does AVERAGEIFS include blank cells or zeroes in the calculation?
AVERAGEIFS automatically ignores completely blank cells when calculating the average. However, if a cell contains a zero (0), it will be counted as a valid data point, which will bring the overall average down.
