Array FunctionsIntermediate

Count Parcels by Delivery Band with FREQUENCY

A courier review asks how many parcels arrived inside each promise, not the average delivery time. FREQUENCY counts a whole column into bands in one formula, reading each limit as "up to and including", and spills one extra row for everything past the last limit, which is exactly where the late parcels end up.

Task:

You look after fulfilment for an online homeware shop, and the courier's contract is up for renewal. Ten parcels from last week, with the hours each took from dispatch to the customer's door, are in A2:B11. The courier promises next-day delivery as up to 24 hours, two-day as up to 48 and three-day as up to 72; anything slower counts as late. The band limits are in E2:E4. In F2, enter one formula that fills F2:F5 with the number of parcels in each band: next day, two days, three days and late.

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 × 6 columns4 cells you fill in
ABCDEF
1ParcelHours to deliverDelivery bandUp to (hours)Parcels
2PX-4021118Next day24
3PX-4021226Two days48
4PX-4021322Three days72
5PX-4021451Late
6PX-4021548
7PX-4021624
8PX-4021773
9PX-4021830
10PX-4021968
11PX-4022096
What this exercise teachesMay contain the answer

The question is not "how fast was the courier on average" but "how many parcels kept each promise", and that is a count into bands. FREQUENCY(data, bins) reads each bin as the top of a band, up to and including it, with the bottom of the band being the bin before. So 24 counts everything from nothing up to 24 hours, 48 counts what is over 24 and up to 48, and 72 counts what is over 48 and up to 72. That inclusive top is what makes the boundaries come out right: PX-40216 at exactly 24 hours met the next-day promise and is counted there, and PX-40215 at exactly 48 is a two-day delivery, not a late one. Then FREQUENCY returns one more count than there are bins, for everything above the last limit. That extra row is not a quirk to work around; it is the late band, so three limits give four answers and the formula fills F2:F5 without a fourth limit to invent. PX-40217 at 73 hours misses the three-day promise by an hour and lands there with the 96-hour parcel. Writing four COUNTIFS gets the same numbers, but each one has to repeat both of its limits, and the easiest mistake in that version is to use >= on one side and <= on the other and count a boundary parcel twice. The counts always add up to the ten parcels, which is a quick check that nothing fell between two bands.