Excel · worked task guide
Excel Rolling Average with Conditions: Last 7 Days
Use AVERAGEIFS with a date lower bound, a next-day exclusive upper bound, and your region or product criteria. For an as-of date in G2, the last seven calendar days run from INT(G2)-6 through timestamps before INT(G2)+1. This averages matching numeric observations, including zeros.
A conditional rolling average needs two decisions before the formula: what moves the window, and which observations belong in it. Here the window follows an explicit as-of date and keeps only one region and one product. Microsoft’s AVERAGEIFS reference lists Excel 2016 among its supported versions; this workflow needs no dynamic-array functions.
Define the seven-day window
For an as-of date of 8 September 2026, include 2–8 September. The lower bound is INT(G2)-6, or midnight at the start of 2 September. The upper bound is INT(G2)+1, midnight at the start of 9 September, with a strict < comparison. Subtracting 6 gives seven calendar dates when the as-of date is included.
This admits a reading at 23:59:59 on 8 September and rejects one at 00:00 on 9 September. A criterion of "<="&G2 would stop at midnight on 8 September if G2 contains only a date, losing most of that day’s timestamps. INT removes an accidental time component from the anchor; see Microsoft’s INT reference.
This is a window of calendar dates, including the whole as-of day. It is not the previous 168 hours ending at a particular clock time, the last seven worksheet rows, or the last seven matching records.
Copy the example and its criteria
On a blank worksheet, paste this four-column example into A1. Leave D7 empty: its missing measurement is part of the check. Formulas use English function names and comma separators; adjust them for your Excel language and regional separator if needed.
Timestamp Region Product Units
=DATE(2026,9,1)+TIME(9,0,0) East Widget 10
=DATE(2026,9,2) East Widget 0
=DATE(2026,9,3)+TIME(12,0,0) East Widget 20
=DATE(2026,9,4)+TIME(9,0,0) West Widget 100
=DATE(2026,9,5)+TIME(15,0,0) East Gadget 80
=DATE(2026,9,6)+TIME(18,0,0) East Widget
=DATE(2026,9,7)+TIME(11,0,0) East Widget 10
=DATE(2026,9,8)+TIME(23,59,59) East Widget 30
=DATE(2026,9,9) East Widget 200Then paste the following two-column parameter block into F1. This places the as-of date in G2, the region in G3 and the product in G4.
Parameter Value
As-of date =DATE(2026,9,8)
Region East
Product Widget- Format A2:A10 as
dd mmm yyyy hh:mm:ssand G2 as a date. Keep the underlying values numeric. - Check
=ISNUMBER(A2)and repeat down the timestamp range; every timestamp should return TRUE. G2 should also be numeric. - Keep the Units entries as numbers, with D7 genuinely blank. Do not type the word “blank” or enter a zero in that cell.
- Paste the formula below into G6, outside the source ranges, and format G6 as General or Number.
DATE and TIME construct the example’s numeric timestamps without asking Excel to interpret an ambiguous typed date. Microsoft documents the day serial in DATE and the fractional day in TIME.
Average the rows that satisfy every condition
=AVERAGEIFS($D$2:$D$10,$A$2:$A$10,">="&(INT($G$2)-6),$A$2:$A$10,"<"&(INT($G$2)+1),$B$2:$B$10,$G$3,$C$2:$C$10,$G$4)| Argument | Purpose |
|---|---|
| D2:D10 | Numeric measurements to average. |
| A2:A10, >= INT(G2)-6 | Include the first calendar day from midnight. |
| A2:A10, < INT(G2)+1 | Include the entire as-of day and exclude later dates. |
| B2:B10, G3 | Keep the selected region: East. |
| C2:C10, G4 | Keep the selected product: Widget. |
The conditions combine with AND: an observation needs the right date, region and product. All ranges cover the same rows. To filter only by region, remove the final pair ,$C$2:$C$10,$G$4; leaving G4 blank does not mean “all products”.
Verify the result: 15, not 12
| Row | Units | Included in the average? |
|---|---|---|
| 2 | 10 | No: 1 September is before the window. |
| 3 | 0 | Yes: lower boundary, with a numeric zero. |
| 4 | 20 | Yes: date, region and product match. |
| 5 | 100 | No: West instead of East. |
| 6 | 80 | No: Gadget instead of Widget. |
| 7 | Empty | Its labels match, but there is no numeric measurement to average. |
| 8 | 10 | Yes: date, region and product match. |
| 9 | 30 | Yes: 8 September at 23:59:59 is before the upper bound. |
| 10 | 200 | No: exactly the excluded midnight on 9 September. |
The four numeric observations are 0, 20, 10 and 30. Their sum is 60, so 60/4 = 15. The blank is ignored; the zero remains in both the sum and the observation count. Replacing D7’s blank with zero produces 60/5 = 12, which is a different dataset.
Change G2 to =DATE(2026,9,9). The window advances to 3–9 September: the numeric zero on 2 September drops out and 200 on 9 September enters. The four values become 20, 10, 30 and 200, so the expected average is 260/4 = 65. Restore the original anchor after the check.
Keep the window rolling as your data grows
For a report that follows the current calendar, put =TODAY() in G2. Its date changes when Excel recalculates, so keep an explicit date for a historical report that must stay reproducible. Microsoft describes that recalculation behavior in its TODAY reference.
The demonstration formula stops at row 10. Extend every source range to the same final row when adding observations, or convert the source to an Excel Table and use its column references. Do not assume new rows outside D2:D10 are already included. A date window does not require sorting; membership depends on timestamps and criteria rather than row position.
No qualifying numeric observations
Set G3 to South, which is absent from the sample. The unwrapped formula should return #DIV/0!; the average has no qualifying numbers. This is different from a valid average of zero.
After verifying your ranges and inputs, use the following optional display wrapper if a report needs a label:
=IFERROR(AVERAGEIFS($D$2:$D$10,$A$2:$A$10,">="&(INT($G$2)-6),$A$2:$A$10,"<"&(INT($G$2)+1),$B$2:$B$10,$G$3,$C$2:$C$10,$G$4),"No numeric matches")IFERROR catches other formula errors too. A “No numeric matches” label is therefore a presentation choice, not a diagnosis: check the unwrapped result before hiding an error. Do not substitute zero for missing data unless that is your report’s explicit rule.
What this rolling average measures
Is it the average of seven daily totals?
No. Each matching numeric row receives equal weight. If one day has ten observations and another has one, this formula gives those days different total weight. For an average of seven daily totals, first construct one total per calendar day and decide whether a day without records means zero or missing data.
Is it the last seven matching records?
No. A busy day may contribute several rows and an empty day may contribute none. Selecting the last N records requires an ordering and record-count rule; changing the date subtraction does not accomplish that.
Can the anchor contain a time?
Yes, but INT deliberately removes it here: the entire anchor’s calendar day is included. If you need an exact clock-time cutoff, define a timestamp window instead. Use a consistent timezone for source timestamps before comparing them.
Microsoft documentation reviewed . Original fixture and independently calculated expected results; no native Excel execution is claimed.