Binning: The SLA Report Says 71% Inside Four Hours, the Pivot Says 43%, and the Bands Add to 136%

Fourteen tickets closed last month, 117.50 hours of resolution time between them, and a support contract with one number in it: a rebate is due any month fewer than 60% of tickets close inside four hours.

The dashboard said 71.4%. Comfortable. The pivot table two tabs over said 42.9%, which would be a rebate and an apology. The number the contract is actually written against is 57.1% — under the line, rebate due, and nowhere on either report.

Nobody mistyped an hour. Every version of this report reads the same fourteen numbers. The disagreement is entirely about six tickets that closed at exactly 2.00, 4.00, 8.00 and 24.00 hours, and about the four different ways the bands on the sheet decide which side of an edge a number sitting on the edge belongs to.

What this covers. COUNTIF, COUNTIFS, SUMIF, SUMIFS, FREQUENCY, SUMPRODUCT, INDEX, MATCH, COUNT, COUNTA, MIN, MAX, MEDIAN and ROUND work in every version this century. LET and spilled arrays need Microsoft 365 or Excel 2021; FREQUENCY works everywhere but needs Ctrl+Shift+Enter before 2021, and section 5 says exactly where. Band edges are numbers, so everything here applies unchanged to ageing buckets in days, invoice values in pounds and order sizes in units.

1) Fourteen Tickets and Five Bands

The tickets are in A1:D15, hours in C2:C15. The bands somebody set up are edges in F2:F5 and labels in G2:G6:

Four edges, five bands — that ratio is the first thing to hold on to, and section 5 is about the function that gets it right on its own.

Here are the fourteen rows, sorted by hours, with the six that sit exactly on an edge marked:

Six of fourteen, and it is not bad luck. Resolution times are logged to the quarter-hour and bands are drawn on round numbers, so values land on edges constantly; the same thing happens with 30/60/90-day ageing on invoices dated month-end, and with price bands at 100 and 500. The median of this column is 4.00 exactly, which is to say half the tickets are on or under an edge — the worst possible place for a band to be drawn, and section 9 comes back to it.

🎯 Scenario: Before you build a single band, put =COUNT(C2:C15) and =SUM(C2:C15) somewhere and write the answers down: 14 and 117.50. Every construction in this article is checked against those two numbers, and the check costs two cells. A set of bands whose counts do not add to the row count is not a distribution, it is an assortment.

2) The Bands That Add to 136%

This is what was on the dashboard, one COUNTIFS per band, written the way the labels read:

=COUNTIFS($C$2:$C$15,">=0",$C$2:$C$15,"<=2")    → 5
=COUNTIFS($C$2:$C$15,">=2",$C$2:$C$15,"<=4")    → 5
=COUNTIFS($C$2:$C$15,">=4",$C$2:$C$15,"<=8")    → 4
=COUNTIFS($C$2:$C$15,">=8",$C$2:$C$15,"<=24")   → 4
=COUNTIFS($C$2:$C$15,">24")                     → 1

Five plus five plus four plus four plus one is 19, over fourteen tickets. Each percentage was then taken over the row count:

A column of percentages that adds to 136% is the loudest thing on this sheet and it is still easy to miss, because nobody adds up a percentage column — the eye goes to the individual figures, and 35.7% is a perfectly ordinary number.

The five extra tickets are the boundary rows counted twice. ">=2" and "<=2" both include 2.00, so both 2.00 tickets are in the 0–2 band and the 2–4 band. Same for the two 4.00s across bands two and three, and the single 8.00 across bands three and four. That is five. The 24.00 escapes because the top band was written ">24" — the only band on the sheet with a strict inequality, and the only one that is right.

The number the contract wants is the first two bands: 5 + 5 = 10 of 14, 71.4%, comfortably clear of 60%. The truth is 8 of 14.

🎯 Scenario: Add the count column and put =SUM(band counts)-COUNT(C2:C15) beside it. It must be 0. On the dashboard above it is 5, and that single cell would have said so in the month the bands were built rather than in the month the rebate was queried.

3) The Fix That Loses Six Tickets

The obvious repair, once somebody notices 136%, is to stop using >= and <= on the same value. What gets typed is usually strict at both ends:

=COUNTIFS($C$2:$C$15,">=0",$C$2:$C$15,"<2")     → 3
=COUNTIFS($C$2:$C$15,">2",$C$2:$C$15,"<4")      → 1
=COUNTIFS($C$2:$C$15,">4",$C$2:$C$15,"<8")      → 1
=COUNTIFS($C$2:$C$15,">8",$C$2:$C$15,"<24")     → 2
=COUNTIFS($C$2:$C$15,">24")                     → 1

Total 8, over fourteen tickets. The percentages now add to 57.1%, which does not look like an error at all — it looks like a distribution with a bit missing from the tails, and there is no tail on this data.

The six tickets that vanished are the same six that were doubled a moment ago, plus the 24.00 that the first version got right: 2.00, 2.00, 4.00, 4.00, 8.00 and 24.00 belong to no band, because every band now excludes both of its own edges. Six of fourteen tickets, 43% of the month, silently outside the report.

Two constructions, two opposite failures, and the second one is worse: an over-count of 5 announces itself with a total that cannot be right, and an under-count of 6 produces a table where every number is plausible.

🎯 Scenario: When a band table's total goes down after a fix, check whether it went down by the number of rows sitting on edges. =SUMPRODUCT(--(COUNTIF($F$2:$F$5,$C$2:$C$15)>0)) counts them directly: on this column, 6.

4) Half-Open Is the Only Shape That Works

There is exactly one shape that can neither double-count nor drop, and it is the same shape for every band: inclusive at one end, exclusive at the other, and the same end every time.

=COUNTIFS($C$2:$C$15,"<="&F2)                          → 5
=COUNTIFS($C$2:$C$15,">"&F2,$C$2:$C$15,"<="&F3)        → 3
=COUNTIFS($C$2:$C$15,">"&F3,$C$2:$C$15,"<="&F4)        → 2
=COUNTIFS($C$2:$C$15,">"&F4,$C$2:$C$15,"<="&F5)        → 3
=COUNTIFS($C$2:$C$15,">"&F5)                           → 1

5 + 3 + 2 + 3 + 1 = 14. Every ticket in exactly one band, because the value that ends one band is the value the next band excludes.

Note the criteria construction: ">"&F2, with the operator as text and the cell concatenated on. ">F2" is the literal three characters and matches nothing numeric, which is trap 8 and the single most common reason a data-driven band table returns zeros down the whole column.

Which end you make inclusive is not arbitrary — it is dictated by how the thing being measured is worded. "Resolved within four hours" means 4.00 counts, so four hours is the top of its band and the upper end is the inclusive one. "Over 30 days" means day 30 does not count, so 30 is the top of the band below. Read the contract, then pick the end; do not pick the end and then read the contract.

Taking the upper-inclusive reading, which is what this contract says:

Inside four hours: 5 + 3 = 8 of 14, 57.1%. Under the 60% threshold. The rebate is due, and it was due on the dashboard's own data all along.

There is a second construction worth knowing, because it cannot double-count even if you write it carelessly — count cumulatively and subtract:

=COUNTIF($C$2:$C$15,"<="&F2)   → 5     cumulative at 2
=COUNTIF($C$2:$C$15,"<="&F3)   → 8     cumulative at 4
=COUNTIF($C$2:$C$15,"<="&F4)   → 10    cumulative at 8
=COUNTIF($C$2:$C$15,"<="&F5)   → 13    cumulative at 24

5, 8, 10, 13, and 14 at the end. The differences down that column are 5, 3, 2, 3, 1 — the same five bands, built from four counts that are all the same shape. There is no second criterion to get wrong, and the cumulative column is worth having in its own right: 8 at the four-hour edge is the contract number, read straight off.

And the array version, for when the bands live in a formula rather than a table:

=SUMPRODUCT((C2:C15>2)*(C2:C15<=4))   → 3

🎯 Scenario: Write one band formula, get it right, and fill it down. The dashboard in section 2 has five formulas that were each typed separately, which is why band five is right and the other four are not. A band table with a single fill-down formula and one hand-written formula at each end has exactly two places to make a mistake.

5) FREQUENCY, the Function Built for This

Everything above is a workaround for a function that has been in Excel since 1993:

=FREQUENCY($C$2:$C$15,$F$2:$F$5)   → {5;3;2;3;1}

One formula, four edges, five bands, and the boundary convention already decided: FREQUENCY counts values greater than the previous bin and less than or equal to the current bin. Upper-inclusive, every band, no arguing — which is why it agrees with the section 4 table down to the last ticket.

Four things about it that the help text does not put first:

It returns one more value than you gave it bins. Four edges, five results. That extra element is the overflow band — everything above the last edge — and it is where the 31.50 lives. This is a feature, not a quirk: the thing people forget when hand-rolling bands is the top one, and FREQUENCY cannot forget it.

Before Excel 2021 it is a legacy array formula. Select all five result cells, type the formula, and confirm with Ctrl+Shift+Enter. Typed normally in an older version it returns only the first band and no error at all, so a report showing 5 and four blanks is not a broken function, it is a formula that was entered as an ordinary one. In Microsoft 365 it spills into five cells on its own.

It ignores text and blanks in the data. A ticket logged as "N/A" or left empty is not in any band and is not in the overflow either — it is simply not counted, and the totals still look internally consistent. =COUNT(C2:C15) against =COUNTA(C2:C15) is the tell: both are 14 here, and a gap between them is the number of rows that quietly left the report.

A repeated bin value returns zero. Edges of 2, 4, 4, 8 give a third band asking for values greater than 4 and not greater than 4, which nothing satisfies. It returns 0 rather than an error, and a band reading 0 in a distribution is the least suspicious number there is.

The check that makes all of this safe is one cell:

=SUM(FREQUENCY($C$2:$C$15,$F$2:$F$5))   → 14
=COUNT($C$2:$C$15)                      → 14

And the percentages, which now cannot help but add to 100%:

=FREQUENCY($C$2:$C$15,$F$2:$F$5)/COUNT($C$2:$C$15)

🎯 Scenario: Put the bin edges in a column of cells and point FREQUENCY at that column, never at a typed constant like {2;4;8;24}. Edges that live in cells can be read, changed and audited by someone who does not open the formula bar, and the labels can be built from the same cells — which is section 7.

6) The Pivot Table Bins the Other Way Round

The second report — the one that said 42.9% — is a pivot table with the hours column grouped by 2, then by larger steps. Nobody wrote a formula. It still disagrees.

PivotTable grouping is lower-inclusive. A group labelled "4–8" means from 4 up to but not including 8, so a ticket that closed at exactly 4.00 hours is in the 4–8 group. FREQUENCY puts that same ticket in the 2–4 band. Same column, same edges, opposite convention:

Both total 14. Neither is broken. They are answers to two different questions, and the difference between the two columns is exactly the six rows sitting on edges.

The damage is done by whoever reads the pivot to answer the contract question. Adding the first two rows of the pivot gives 3 + 3 = 6 of 14, 42.9% — but "within four hours" includes 4.00, and both 4.00 tickets are down in the 4–8 row. The pivot's own total is honest; the reading of it is not, because the label "2–4" does not say which end it owns.

Excel's built-in histogram chart is better behaved about this than either: it labels its bins in interval notation, so an axis reading (2, 4] tells you the convention without anyone having to know it. The Analysis ToolPak's Histogram tool follows FREQUENCY — bin value means "up to and including".

🎯 Scenario: Never put a pivot's grouped bands and a FREQUENCY table on the same dashboard. If both must exist, label the bands so the convention is visible — "0.01–2.00", "2.01–4.00" for upper-inclusive data, or interval notation — because "2–4" next to "4–8" is a label that cannot be read correctly by anybody.

7) The Band on the Row, Then the Summary

A spilled array of five numbers is a summary you cannot check row by row. The version that survives a query from a customer puts the band on the ticket:

=INDEX($G$2:$G$6,COUNTIF($F$2:$F$5,"<"&C2)+1)

Read it as: count how many edges are strictly below this ticket's hours, add one, and that is the band's position. For 4.00 hours, only the edge 2 is strictly below, so the answer is band 2 — "2–4 h", upper-inclusive, matching FREQUENCY exactly. For 31.50 all four edges are below, giving band 5. Filled down H2:H15 it labels every row, and the labels come from the same cells the edges do, so a band renamed in G is renamed everywhere.

The summary is then a COUNTIF over a column somebody can read:

=COUNTIF($H$2:$H$15,G2)   → 5, 3, 2, 3, 1

Worth knowing what the more obvious lookup does instead. MATCH with match type 1 finds the largest value less than or equal to the lookup value, which is the lower-inclusive convention — the pivot's:

=INDEX($G$2:$G$6,MATCH(C5,$J$2:$J$6,1))   → "4–8 h" for a 4.00 ticket

where J2:J6 holds the lower bounds — 0, 2, 4, 8, 24 — because a lower-inclusive table is described by the value each band starts at, not the one it ends at. That formula is not wrong; it is answering the pivot's question. If your bands are lower-inclusive by contract — "30 days or more", "£500 and above" — it is the right one, and then it is FREQUENCY you cannot use as it stands. The two conventions each have a natural function, and picking the function first is how people end up with a convention they never chose.

🎯 Scenario: Build the band column first and the summary from it, never five separate COUNTIFS straight into the report. A band column can be sorted, filtered, spot-checked against a ticket somebody is complaining about, and totalled two different ways; a spilled array of five numbers can only be believed.

8) Hours Per Band, Not Just Counts

Counts answer "how many". The next question is always "how much", and the same edges do it with SUMIFS:

=SUMIFS($C$2:$C$15,$C$2:$C$15,"<="&F2)                        → 6.50
=SUMIFS($C$2:$C$15,$C$2:$C$15,">"&F2,$C$2:$C$15,"<="&F3)      → 11.25
=SUMIFS($C$2:$C$15,$C$2:$C$15,">"&F3,$C$2:$C$15,"<="&F4)      → 14.50
=SUMIFS($C$2:$C$15,$C$2:$C$15,">"&F4,$C$2:$C$15,"<="&F5)      → 53.75
=SUMIFS($C$2:$C$15,$C$2:$C$15,">"&F5)                         → 31.50

117.50 to the penny, which is the check from section 1 arriving. And the table says something the count column cannot: one ticket out of fourteen — 7.1% of the month — is 26.8% of the effort. A distribution of counts hides that completely, and the two right-hand bands together are 4 tickets and 72.5% of the hours.

With the band column from section 7 in place, the same figures are a SUMIF, and the reconciliation is one subtraction:

=SUMIF($H$2:$H$15,G2,$C$2:$C$15)          per band
=SUM($C$2:$C$15)-SUM(band totals)   → 0.00

🎯 Scenario: Report count and sum side by side for every band you build. Counts are what the SLA is written in and sums are where the work actually went, and a band table with only one of the two columns will get quoted for the other.

9) Where the Edges Should Have Been

The edges here came from the contract — 4 hours is contractual and not negotiable — but the ones above it, 8 and 24, were picked because they are round. Three cells say whether they are earning their place:

=MIN($C$2:$C$15)      → 0.25
=MEDIAN($C$2:$C$15)   → 4.00
=MAX($C$2:$C$15)      → 31.50
=AVERAGE($C$2:$C$15)  → 8.39

The median is exactly 4.00, sitting precisely on the contractual edge. That is the whole reason this data is so sensitive to the convention: with half the column at or below the edge and two rows exactly on it, moving one ticket across changes the headline by 7.1 percentage points. An edge through the middle of a cluster is an edge that will be argued about every month, and the argument is never won by changing the formula.

The mean of 8.39 against a median of 4.00 says the same thing from the other side — the tail is long and thin, one ticket at 31.50 is dragging the average two bands above where most of the month sits, and any report quoting only the average is quoting the 31.50.

Two habits follow. First, when the edges are yours to choose rather than a contract's, put them where the data is thin — check =COUNTIF($C$2:$C$15,"="&F2) for each edge, which returns 2, 2, 1, 1 here and should ideally be 0. Second, when an edge is contractual and the data piles up on it, say so in the report rather than hoping: "8 of 14 within 4 hours, of which 2 closed at exactly 4.00" is a sentence that ends the argument before it starts.

🎯 Scenario: Before drawing bands on a new column, run MIN, MEDIAN, MAX and a count of rows sitting exactly on each proposed edge. Four cells, and they tell you whether your bands will describe the data or fight it.

10) Four Checks

One: the counts add to the rows. =SUM(band counts)-COUNT($C$2:$C$15) must be 0. The dashboard in section 2 returns 5; the fix in section 3 returns -6; both would have been caught by one cell.

Two: the sums add to the total. =SUM(band totals)-SUM($C$2:$C$15) must be 0.00. Counts and sums fail differently — a band can have the right count and the wrong sum if a criterion points at the wrong column — so both checks earn their cell.

Three: nothing fell out of the data. =COUNTA($C$2:$C$15)-COUNT($C$2:$C$15) must be 0. Anything else is text where a number should be — "N/A", "still open", a number pasted as text from the ticketing export — and every one of those rows is missing from every band with no symptom whatsoever.

Four: the rows on the edges. =SUMPRODUCT(--(COUNTIF($F$2:$F$5,$C$2:$C$15)>0)) returns 6 here. That number is how much your report depends on the convention: at 0, nothing in this article can hurt you; at 6 of 14, the convention is the report.

🎯 Scenario: Put all four checks in a block at the top of the band table, not on a hidden tab. Bands are a model of the data, and these are its tests; the day someone moves an edge from 8 to 6 to make a chart look better, checks one and two are what say whether they moved the edge or broke the table.

11) Twelve Traps

1. >= at both ends of adjacent bands double-counts every value on an edge. Five bands, 19 tickets, 14 rows, and percentages that add to 135.7%.
2. > and < at both ends drops them instead. Total falls to 8 of 14 and every individual number still looks reasonable. The under-count is the more dangerous of the two.
3. Half-open is the only safe shape, and it must be the same end every time. One band written ">="/"<=" in a table of ">"/"<=" puts a single value in two places.
4. ">F2" is literal text. Criteria built from a cell need ">"&F2. The symptom is a column of zeros, which reads like no data rather than a broken formula.
5. FREQUENCY returns one more value than there are bins. Four edges, five results. Enter it over four cells and the overflow band — the 31.50 — is simply not on the report.
6. Before Excel 2021, FREQUENCY needs Ctrl+Shift+Enter. Entered normally it returns the first band only, with no error to say so.
7. FREQUENCY is upper-inclusive; PivotTable grouping is lower-inclusive. Same column, same edges, both totalling 14, and 6 of the 14 tickets in a different row.
8. A repeated bin value returns 0. Edges of 2, 4, 4, 8 give a band asking for "greater than 4 and not greater than 4", and 0 in a distribution looks like a quiet month.
9. FREQUENCY, COUNT and the *IF family all ignore text. A ticket logged "N/A" is in no band and breaks no total. COUNTA minus COUNT is the only thing that sees it.
10. Floating point sits on edges badly. Hours from (resolved-opened)*24 can be 4.000000000000001, which is not "<=4". Round the input — =ROUND((B2-A2)*24,2) — never the criteria.
11. Dates and times are numbers, so ageing buckets are the same problem. A 30/60/90 table built with ">=30" and "<=60" double-counts every invoice exactly 60 days old, and month-end dating guarantees there are some.
12. Edges typed into formulas drift from the labels. The band says "0–4 h" and the formula says "<=3", and nothing in Excel will ever mention it. Edges in cells, labels built from the same cells.

Practice

Using the fourteen rows in the grid above:

1. Four answers to one question. Compute "closed within four hours" four ways — the ">="-both-ends bands, the strict bands, the half-open bands, and the first two rows of a lower-inclusive pivot. You should get 10, 4, 8 and 6. Say which one the contract means and why.
2. Find the sensitive rows. Write the single formula that returns how many tickets sit exactly on a band edge, and then the formula that returns their total hours.
3. The cumulative column. Put =COUNTIF($C$2:$C$15,"<="&F2) beside each edge and read off 5, 8, 10, 13. Which of those four numbers is the contract number, and what is the fifth band's count without writing another formula?
4. Two conventions, one column. Build the band label two ways — with COUNTIF as in section 7 and with MATCH type 1 — and list the rows where the two columns disagree. There should be six.
5. Hours, not tickets. Produce the count and the hours for every band and prove they add to 14 and 117.50. Which single ticket is 26.8% of the month's hours?
6. Move an edge. Change the 8 in F4 to 6 and say, before you recalculate, which band counts change and by how much. Then check whether the four checks in section 10 still return 0.

Summary

A band is a decision about an edge, and the edge is where all the data that matters sits. Six of these fourteen tickets closed at exactly 2.00, 4.00, 8.00 or 24.00 hours, and every version of this report answered the same contractual question differently — 10, then 4, then 6 — while the honest answer, 8, was never on a screen.

So: pick the convention from the wording before you pick the formula, because "within four hours" and "over four hours" are different bands and no function will ask which you meant. Write the bands half-open, inclusive at one end and the same end every time, or hand the whole job to FREQUENCY, which is upper-inclusive by construction and cannot forget the overflow band. Keep the edges in cells and build the labels from those same cells. Put the band on the row before you put a count in a summary. And add the counts up: a band table whose counts do not equal the row count and whose sums do not equal the column total is not describing anything, and it takes one cell each to know.

The alternative is the version this article started with: 71.4% reported, 57.1% earned, five percentages adding to 136%, and a rebate that was due on the dashboard's own numbers from the day it was built.