Average one region over the last seven calendar days
Choose a date window that includes late readings on its final day, ignores missing observations and retains measured zeros.
The data
| Row number | A | B | C |
|---|---|---|---|
| 1 | Timestamp | Region | Units |
| 2 | =DATE(2026,9,1)+TIME(9,0,0) | East | 100 |
| 3 | =DATE(2026,9,2) | East | 0 |
| 4 | =DATE(2026,9,3)+TIME(12,0,0) | East | 20 |
| 5 | =DATE(2026,9,6) | West | 100 |
| 6 | =DATE(2026,9,8)+TIME(23,59,59) | East | 40 |
| 7 | =DATE(2026,9,9) | East | 200 |
| 8 | =DATE(2026,9,6)+TIME(18,0,0) | East |
Paste into A1 on a blank worksheet. DATE and TIME create numeric timestamps; format A2:A8 as dd mmm yyyy hh:mm:ss. Leave C8 empty. Input formulas use English function names and comma separators. Expected values were calculated independently; no native Excel execution is claimed.
Your task
In E2, average the numeric Units for East during the seven calendar dates ending on 8 September 2026, including that whole final day. Use AVERAGEIFS over rows 2:8. Keep C8 genuinely empty.
Your formula goes in E2
The checker compares your formula against the accepted answers — it does not run it. The expected result is shown with the solution so you can verify it in your own spreadsheet.
Why this is the right formula
The matching numeric measurements are 0, 20 and 40: their total is 60 and their count is 3, so the average is 20. The empty C8 does not add another observation. The next-day exclusive upper bound admits 23:59:59 on 8 September and rejects midnight on 9 September. AVERAGEIFS combines every criterion with AND. Missing dates do not automatically contribute zeros, and this is an average of observations rather than seven daily totals. The checker recognises the listed AVERAGEIFS forms; verify an equivalent unlisted formula in your spreadsheet.
Documentation
Documentation reviewed . This is a source review, not an Excel execution test.
Try next
Practise it on a full dataset
Free to use, share and republish — including commercially. Attribution isn't required, but it's what keeps these free to make, and there's one ready to paste below.
Questions
Does this actually run my formula?
The checker normalises case, spaces, argument separators and dollar-sign anchors, then compares your formula with a reviewed list of accepted answers. It does not evaluate the formula. Copy the data into your spreadsheet to verify the expected result independently.
Can a correct formula be rejected?
Yes. The accepted list includes several reviewed approaches, but cannot recognise every equivalent formula. Compare an unlisted solution with the expected result in your spreadsheet and use the page feedback if an accepted answer is missing.
Do I need an account?
No. There is no signup, no progress tracking and nothing stored — everything happens in your browser.