How to average values between two dates
✓ Verified in LibreOffice 25.8.7.3The mean of values within a date range — average order value in a period, average daily total for a month.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =AVERAGEIFS(B2:B100,A2:A100,">="&D1,A2:A100,"<="&D2) | Same pattern as SUMIFS but averages. Returns #DIV/0! if no rows fall in the range. |
| Google Sheets | =AVERAGEIFS(B2:B100,A2:A100,">="&D1,A2:A100,"<="&D2) | Identical. |
| LibreOffice Calc | =AVERAGEIFS(B2:B100,A2:A100,">="&D1,A2:A100,"<="&D2) | Identical. |
How it works
AVERAGEIFS works exactly like SUMIFS with two date bounds, but takes the mean of the qualifying rows instead of the total: the two rows inside the July 24-31, 2026 window are 100 and 40, averaging 70 (the out-of-range row is skipped). Reference the bounds from cells (D1/D2) with the ">="&D1 concatenation to make the period adjustable. One thing to guard: if no rows fall in the range, AVERAGEIFS returns #DIV/0! — wrap in IFERROR(...,0) or IFERROR(...,"-") if an empty period is possible. Add more range/criteria pairs (region, product) to average a slice within the period.
Verified, not just documented
We ran =AVERAGEIFS(B2:B4,A2:A4,">="&DATE(2026,7,24),A2:A4,"<="&DATE(2026,7,31)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 70 — exactly the expected result. Every formula here is confirmed by actually executing it.