Statistical FunctionsIntermediate

Find Each Site's Longest Unplanned Outage with MAXIFS

An incident log mixes planned maintenance with genuine breakdowns, and the biggest number in it is usually a planned shutdown nobody needs to explain. MAXIFS takes the largest value only from the rows that pass every condition you give it, so one formula can ask for the worst breakdown at each site and ignore the rest.

Task:

You are the operations analyst for a parcel distribution network, preparing the October reliability review. The incident log in A2:D11 records every time a site's sorting line stopped, whether it was a planned maintenance window or an unplanned breakdown, and for how many minutes. In G2, use MAXIFS to return the longest unplanned outage at the site in F2, then copy it down to G5. Planned stoppages must not count.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

11 rows × 7 columns4 cells you fill in
ABCDEFG
1DateSiteTypeMinutes DownSiteLongest Unplanned (min)
210/2/2026LeedsUnplanned42Leeds
310/4/2026BristolPlanned180Bristol
410/7/2026GlasgowUnplanned15Glasgow
510/9/2026LeedsPlanned240Cardiff
610/13/2026CardiffPlanned90
710/15/2026BristolUnplanned67
810/20/2026LeedsUnplanned118
910/23/2026GlasgowUnplanned54
1010/27/2026BristolUnplanned31
1110/29/2026CardiffPlanned120
What this exercise teachesMay contain the answer

The review is about failures, and the single biggest number in the log is not one: Leeds' 240 minutes and Bristol's 180 were maintenance windows booked weeks in advance. A MAXIFS with only the site condition returns exactly those, so the formula looks right and reports a planned shutdown as each site's worst breakdown. Adding the second pair, column C against "Unplanned", means a row has to pass both tests before its minutes are even considered, and the largest of what is left is the answer: 118 at Leeds, 67 at Bristol, 54 at Glasgow. Glasgow is the useful check here, because both its incidents were unplanned and so the second condition changes nothing; the condition only shows its worth on sites that mix the two. Cardiff returns 0, and that is a real answer, not an error: MAXIFS gives 0 when no row passes every condition, which here means Cardiff had no unplanned outages at all in October. The flip side is that 0 is also what you would see if the site name in F5 were misspelled, so a 0 is worth a glance at the log before it goes in the report as a clean month. The $ signs keep the three log ranges fixed while F2 moves down with the formula, so one formula serves every site.