Statistical FunctionsIntermediate

Score Carriers on Late Deliveries with COUNTIFS and AVERAGEIFS

An average of days late that counts the on-time shipments as zeros answers the wrong question. Adding a ">0" condition to AVERAGEIFS sets the on-time ones aside, so the figure says how late a carrier runs when it is late at all.

Task:

You manage outbound logistics for a furniture distributor and are getting ready for the quarterly carrier review. Column C records how many days late each shipment arrived, with 0 meaning on time. For each carrier listed in E2:E4, use COUNTIFS in column F to count its late shipments (more than 0 days late), and AVERAGEIFS in column G to find its average days late, counting only the shipments that were actually late. Fill in rows 2 through 4.

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 columns6 cells you fill in
ABCDEFG
1ShipmentCarrierDays LateCarrierLate ShipmentsAvg Days Late (late only)
2SH-2101Northline0Northline
3SH-2102Swiftway6Swiftway
4SH-2103Northline2Coastal
5SH-2104Coastal0
6SH-2105Swiftway0
7SH-2106Northline5
8SH-2107Coastal1
9SH-2108Swiftway4
10SH-2109Northline0
11SH-2110Coastal0
What this exercise teachesMay contain the answer

Northline shipped four times and was late twice, by 2 days and 5. Average all four of its shipments and the two on-time zeros pull the result down to 1.75 days, a figure that describes no shipment Northline actually made and makes a carrier that misses by most of a week look nearly punctual. The ">0" pair in AVERAGEIFS($C$2:$C$11,$B$2:$B$11,E2,$C$2:$C$11,">0") removes those zeros before anything is averaged, so the answer is 3.5 days, which is how late Northline runs when it runs late. The two columns are meant to be read together. Northline and Swiftway both have 2 late shipments, but Swiftway's average of 5 days against Northline's 3.5 shows which one hurts more when it slips, while Coastal's single late shipment at 1 day is a much smaller problem than either. Column C appears twice in the AVERAGEIFS on purpose: once as the numbers being averaged and once as the range being tested, which is how one column can both filter and supply the values. The dollar signs keep all three columns of shipment data fixed while E2 moves down to the next carrier.