Statistical & Aggregation
Maximum and Minimum Value by Calendar Month (MAXIFS, MINIFS & EOMONTH)
Find the peak high and trough low within any calendar month using MAXIFS and MINIFS bounded by first-of-month and EOMONTH(firstOfMonth, 0), distinguishing empty months from genuine zero readings.
Bound calendar months cleanly with EOMONTH and guard empty months
Matching only MONTH(date) = 3 accidentally pools March 2025 and March 2026 together. Anchoring each summary row on its first-of-month date M (such as 2026-03-01 in E2) defines an exact year-and-month window [E2, EOMONTH(E2, 0)] that handles 28-, 29-, 30-, and 31-day months automatically.
One subtle Excel trap: when zero records fall inside the target month, MAXIFS and MINIFS both return 0 rather than #N/A, which can look like a genuine 0°C sensor reading or $0 peak. Checking COUNTIFS(dates, ">=" & E2, dates, "<=" & EOMONTH(E2, 0)) = 0 first separates empty months from real zero observations.
| Reading Date | Peak Demand (MW) | Target Month Start (E2) | Monthly Result |
|---|---|---|---|
| 2026-02-28 | 495 | 2026-03-01 | Excluded (February) |
| 2026-03-01 | 410 | 2026-03-01 | Included (Month start boundary) |
| 2026-03-18 | 530 | 2026-03-01 | Monthly Max = 530 MW |
| 2026-03-31 | 385 | 2026-03-01 | Monthly Min = 385 MW |
| 2026-04-01 | 560 | 2026-03-01 | Excluded (April) |
Guarded Monthly Maximum (MAXIFS + EOMONTH)
Returns the highest value in B2:B6 within the calendar month starting at E2, or "No Data" if that month has zero observations.
=LET(mEnd, EOMONTH(E2, 0), IF(COUNTIFS(A2:A6, ">=" & E2, A2:A6, "<=" & mEnd) = 0, "No Data", MAXIFS(B2:B6, A2:A6, ">=" & E2, A2:A6, "<=" & mEnd)))Monthly Peak-to-Trough Spread (MAXIFS Minus MINIFS)
Computes the intra-month range between the highest and lowest readings inside [E2, EOMONTH(E2, 0)].
=LET(mEnd, EOMONTH(E2, 0), MAXIFS(B2:B6, A2:A6, ">=" & E2, A2:A6, "<=" & mEnd) - MINIFS(B2:B6, A2:A6, ">=" & E2, A2:A6, "<=" & mEnd))Try the example
Use the original values or change them to test your understanding.