Marchwood Care runs nine branches against one number: the share of visits that start on time. The contract pays a service credit for every point below 90%. The dashboard read 91.1%, and the client's own audit read 86.5%.
Nothing on the sheet was broken. Column D divided on-time visits by visits, branch by branch, and every percentage in it was right. The headline cell averaged that column.
AVERAGE has no idea how many visits sit behind each percentage. It adds up eight numbers and divides by eight, so a branch that did 48 visits counts for exactly as much as one that did 1,420.
| Measure | On the dashboard | On the contract |
|---|---|---|
| Headline on-time | 91.1% | 86.5% |
| Formula | =AVERAGE(D2:D10) | =SUM(C2:C10)/SUM(B2:B10) |
| Points below 90% | none | 3.5 |
| Service credit, this month | £0 | £14,000 |
| Service credit, the quarter | £0 | £38,000 |
Scope.
AVERAGE,AVERAGEA,AVERAGEIFandAVERAGEIFSin Excel for Microsoft 365, 2021, 2019 and Excel for Mac.AVERAGEIFSneeds Excel 2007 or later; the weighting behaviour is identical in Google Sheets.Throughout, column B is visits, column C is visits that started on time, column D is the branch percentage, and the data runs from row 2 to row 10.
01Nine Branches and One Headline Number
The month is nine rows. Visits in B, on-time visits in C, and a percentage in D that reads =IFERROR(C2/B2,"").
Every percentage in column D is correct. Ashbourne really did start 1,207 of its 1,420 visits on time, and Brindle Park really did start all 96 of its own.
Three of the nine branches are small enough to vanish. Brindle Park opened in May and did 96 visits, Dunhollow covers one village and did 48, Fallowfield did 74. Together that is 218 visits out of 6,498.
Hestham opened on the 29th and did none at all, so IFERROR left empty text in its percentage cell. Section 6 is about what that one cell does to a month.
Nine Branches, Eight Equal Votes
Marchwood Care's monthly service report, in A2:F14. Column B is visits, column C is visits that started on time, and column D is `=IFERROR(C2/B2,"")` — each branch's own percentage, correct to the decimal. Brindle Park, Dunhollow and Fallowfield did 218 visits between them and hold the three highest percentages; Ashbourne, Cawthorne, Eastgate, Garrowby and Ivelet did 6,280 and none of them cleared 88%. Hestham opened on the 29th and has empty text where its percentage would be, so AVERAGE divides by eight rather than nine. The last three rows are the same month read three ways: the dashboard's mean of the column, the contract's ratio of the totals, and what the gap between them costs.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
02AVERAGE Weights Rows, Not Visits
=AVERAGE(D2:D10) returns 91.1%, and it is doing exactly what it promises. It adds the numbers it can see and divides by how many there were: eight, because the ninth cell holds text.
So every branch in column D is worth 12.5% of the headline, whatever it did that month. Dunhollow's 48 visits carry the weight of Eastgate's 1,400.
| Branch | Visits | On-time % | Weight in =AVERAGE(D2:D10) | Share of visits |
|---|---|---|---|---|
| Ashbourne | 1,420 | 85.0% | 12.5% | 21.9% |
| Eastgate | 1,400 | 87.0% | 12.5% | 21.5% |
| Brindle Park | 96 | 100.0% | 12.5% | 1.5% |
| Dunhollow | 48 | 100.0% | 12.5% | 0.7% |
| Hestham | 0 | — | none | 0.0% |
The two right-hand columns are the whole article. A mean over rows weights rows; the contract weights visits.
Scenario: In your own branch sheet, put =AVERAGE(D2:D10) and =SUM(C2:C10)/SUM(B2:B10) in two cells side by side and subtract one from the other. Anything but nought means the headline is weighted by row count.
03The Three Smallest Branches Carried 37.5% of It
Brindle Park, Dunhollow and Fallowfield did 218 visits between them, which is 3.4% of the month. In =AVERAGE(D2:D10) they are three of eight numbers, so they carry 37.5% of the answer.
They are also the three highest percentages on the sheet, and that is not luck. =COUNTIFS(B2:B10,"<100",D2:D10,">0.95") returns 3: every branch under a hundred visits scored above 95%.
A round of two visits a day can clear both. A round of fifty cannot have a clear day, because one van in traffic is already a late visit.
Delete those three rows and the same wrong formula returns 86.0%, half a point from the truth. The formula did not improve; the rows got more alike.
Scenario: Sort your own branch table by volume with =SORT(A2:F10,2,-1), average the top five percentages, then average the bottom five. A gap of more than a point means the headline is reporting your smallest branches.
04Divide the Totals, Do Not Average the Ratios
The contract defines on-time service as on-time visits divided by visits, across the contract. That sentence is already a formula: =SUM(C2:C10)/SUM(B2:B10), which returns 86.5%.
It does not use column D at all. The percentage column exists so a manager can read one branch; the headline comes from the two columns of counts beside it.
A ratio of totals is not a mean of ratios, and only one of them is the number you promised. 5,622 visits out of 6,498 started on time, and 86.5% is the only arithmetic that says so.
If you would rather name the parts, =LET(v,SUM(B2:B10),o,SUM(C2:C10),o/v) returns the same figure and says what it is doing.
Scenario: Put =SUM(C2:C10)/SUM(B2:B10) in the cell directly above your dashboard figure, formatted to one decimal. Leave both on the sheet for a month and watch which one the next review quotes.
05When All You Have Is the Percentages
Sometimes the counts are gone. A supplier sends percentages, or last year's file kept column D and nothing else. Then weight them yourself, using whatever volumes you do have.
=SUMPRODUCT(B2:B10,D2:D10)/SUM(B2:B10) returns the same 86.5%. It multiplies each percentage by its visits, adds the products, and divides by total visits.
The numerator deserves a check of its own. =SUMPRODUCT(B2:B10,D2:D10) returns 5,622, which is exactly =SUM(C2:C10). If those two disagree, a percentage somewhere was typed rather than derived.
One cell breaks it.
SUMPRODUCTreturns#VALUE!the momentD9holds empty text instead of nothing at all.Leave that cell genuinely empty, or guard the column:
=SUMPRODUCT(B2:B10,IF(ISNUMBER(D2:D10),D2:D10,0))/SUM(B2:B10). A branch with no visits weighs nothing either way.
06Blanks, Zeros and Empty Text Are Three Different Things
AVERAGE skips empty cells and skips text. It does not skip zero. Those two sentences decide your headline, and nothing on the face of the sheet tells you which case you are in.
Hestham did no visits in the month. Depending on what sits in D9, the same =AVERAGE(D2:D10) reports that month three ways.
What is in D9 | =AVERAGE(D2:D10) | =COUNT(D2:D10) |
|---|---|---|
| Nothing at all | 91.1% | 8 |
=IFERROR(C9/B9,"") | 91.1% | 8 |
The text n/a | 91.1% | 8 |
0, typed in to be safe | 81.0% | 9 |
Ten points of reported service hang on whether somebody filled a gap with a nought. A branch that did nothing did not fail: it has no percentage, and a zero claims that it does.
Scenario: Put =COUNT(D2:D10), =COUNTA(D2:D10) and =ROWS(D2:D10) in three cells. Here they read 8, 9 and 9, so one cell holds something AVERAGE will not count. Any gap between the first two is text hiding in a number column.
07AVERAGEA Is Not a Safer AVERAGE
AVERAGEA looks like the thorough version of the same function. It is the opposite: it counts the cells AVERAGE was right to skip, and it counts them as zero.
=AVERAGEA(D2:D10) returns 81.0% on this sheet. The empty text in D9 becomes a nought, the divisor becomes nine, and a branch that did no work drags the group down five points.
Logical values are worse. AVERAGEA reads TRUE as 1, and 1 in a percentage column is 100%, so a stray tick invents a perfect branch.
| Cell contents | AVERAGE | AVERAGEA |
|---|---|---|
| Empty cell | ignored | ignored |
Empty text from IFERROR | ignored | counts as 0 |
| Any other text | ignored | counts as 0 |
TRUE | ignored | counts as 1 |
0 | counts as 0 | counts as 0 |
Text that looks like a percentage is a third case. =VALUE(D9) turns the text 85.0% into 0.85, which is the repair; until you run it, AVERAGE is silently averaging fewer rows than the sheet shows.
08AVERAGEIF and AVERAGEIFS Weight Rows Too
Filtering is not weighting. =AVERAGEIFS(D2:D10,B2:B10,">=500") drops the three small branches and returns 86.0%: closer to the truth by accident, and still a mean of ratios.
The conditional form of a ratio of totals is a pair of SUMIFS over the counts. =SUMIFS(C2:C10,B2:B10,">=500")/SUMIFS(B2:B10,B2:B10,">=500") returns 86.1% for the same five branches.
SUMIF does the same job on one condition, and on Microsoft 365 =AVERAGE(FILTER(D2:D10,B2:B10>=500)) is the explicit version of the mistake — which is useful, because you can see what it is averaging.
Leave the division by zero alone. When no row matches, both
AVERAGEIFSand theSUMIFSpair return#DIV/0!.That error is the honest answer to a month with no visits in it. Wrapping it in
=IFERROR(...,0)turns "we did not trade" into "we failed everything", and a dashboard cannot tell the difference afterwards.
Scenario: Take any filtered average on your own dashboard and rebuild it as two SUMIFS over the underlying counts. Put the pair beside the original, then change one branch's volume by a factor of ten and watch only one of them move.
09Growth Rates Have the Same Shape
A month up 20% and a month down 20% average to nothing at all. Take £100 to £120 and £120 to £96, and the fact of the two months is a 4% fall.
Percentages of different bases do not average, any more than percentages of different denominators do. Chain them instead: =(96/100)^(1/2)-1 returns −2.0%, the monthly rate that actually compounds to −4%.
The general form. With growth factors in
E2:E13,=PRODUCT(E2:E13)^(1/COUNT(E2:E13))-1is the average monthly rate, and=GEOMEAN(E2:E13)-1says it in one function.=POWER(96/100,1/2)-1is the same arithmetic for two months.A factor is 1.2 for a 20% rise and 0.8 for a 20% fall. Average them and you get 1.0; multiply them and you get 0.96, which is what the bank account did.
10The Unweighted Mean Is Not Wrong, It Is Unlabelled
There is a question the mean of column D answers exactly: how did the average branch do, counting each branch once? For a per-branch decision — staffing, a manager's review, where to send help — that is the right question.
It is also the right question when the denominators match. Nine branches of equal size make the two formulas agree, and =MAX(B2:B10)/SMALL(B2:B10,2) tells you in one cell how far from that you are.
What fails is a headline that answers one question and is read as the other. Labelling the cell "mean of branch rates, unweighted" costs five words and ends the argument before it starts.
Scenario: Find the headline percentage on your own dashboard and write beside it which of the two it is. If you cannot tell from the formula in two seconds, nor can the person who quotes it in a meeting. Then put the other one next to it.
Try it in the grid11Five Checks Before the Dashboard Goes Out
- Both numbers, side by side.
=ABS(AVERAGE(D2:D10)-SUM(C2:C10)/SUM(B2:B10))returns 0.046 here. Anything above half a point is a weighting problem, not a rounding one. - Count what AVERAGE counted.
=COUNT(D2:D10)returns 8 where=ROWS(D2:D10)returns 9. One row is not in the headline, and the sheet does not say which. - Size the rows.
=MAX(B2:B10)/SMALL(B2:B10,2)returns 29.6. A mean over rows that differ thirty-fold is a mean of the wrong thing. - Foot the percentage column.
=SUMPRODUCT(B2:B10,D2:D10)should equal=SUM(C2:C10), which is 5,622. If it does not, a percentage was typed rather than derived. - Re-derive one branch.
=C6/B6against theD6already on the sheet. A single mismatch means column D is history rather than arithmetic.
12Ten Traps
- A mean of ratios.
=AVERAGE(D2:D10)on a percentage column answers a question nobody asked, and never errors while doing it. - A zero for "no data". An empty cell is skipped; a nought is a failing score. Here that is the difference between 91.1% and 81.0%.
- Empty text from
IFERROR.AVERAGEignores it,COUNTAcounts it, andSUMPRODUCTrefuses the whole column with#VALUE!. AVERAGEAas a safer AVERAGE. It counts text as 0 andTRUEas 1, which in a percentage column is a branch at 100%.- Filtering instead of weighting.
AVERAGEIFSnarrows the rows and still weights the ones left over equally. - Averaging an average. A quarter built from three monthly means is the same trap one level up, and February is lighter than March.
- Averaging growth rates. Up 20% then down 20% reports as flat and is a 4% fall. Use
GEOMEANon the factors. - Hidden and filtered rows.
AVERAGEcounts rows a filter is hiding.=SUBTOTAL(101,D2:D10)and=AGGREGATE(1,5,D2:D10)do not. - Averaging rounded figures. A column rounded to one decimal, averaged, drifts from the same column at full precision. Round the headline, not the inputs.
=IFERROR(AVERAGE(D2:D10),0). A broken month is reported as a zero month, and the dashboard shows the worst figure the contract has ever seen.
13Five Exercises
- Reproduce it. Build the nine rows, put
=AVERAGE(D2:D10)and=SUM(C2:C10)/SUM(B2:B10)side by side, and confirm 91.1% against 86.5%. - Move one branch. Change Dunhollow from 48 visits to 1,480, keeping it at 100%, and watch which of the two cells moves.
- Break it with a zero. Type 0 into
D9and read the headline again. Then delete it and read it a third time. - Trip
SUMPRODUCT. Put=IFERROR(C9/B9,"")back intoD9and run the weighted formula. Then guard it withISNUMBER. - Chain the growth. Put 1.2 and 0.8 in two cells, average them, multiply them, and work out which one matches £100 becoming £96.
What to Take Away
Nothing in this dashboard was broken. AVERAGE returned the mean of the numbers it was given, column D was right to the last decimal, and the headline landed above the threshold in a month when the service did not.
What nobody had written down is that a mean over rows weights rows. Nine branches of wildly different size make eight equal votes out of 6,498 visits, and the smallest rounds vote first.
Two cells say it before the pack goes out: on-time visits over visits, and a count of the column against the rows in it. One catches the weighting, the other catches the cell that quietly left the calculation.
Two cells, against £38,000 of service credits on a quarter that was reported as a pass.