Case study: carrier on-time performance
From: Priya Raman, logistics lead, Tidewater Home Goods (a fictional company)
About: Carrier review
We split April shipments evenly across three carriers. Before we renew one contract, tell me how many days late each delivery was, which shipments were on time, each carrier's on-time rate, how late their late shipments ran on average, and which carrier was the most reliable.
A case study ends with a decision, and the decision has to come from the numbers. Here the numbers start as dates: Excel stores dates as day counts, so delivered minus promised is the number of days between them.
From there it's counting with conditions: COUNTIF and COUNTIFS per carrier, a rate, and AVERAGEIFS for lateness when a shipment is late. The final step returns the answer with a formula, so it updates when the data does.
=MAX(E2-D2,0)
=COUNTIFS($B$2:$B$19,I2,$G$2:$G$19,"Yes")
Days late floored at zero, and a two-condition count per carrier.