01 / 14Nine Branches and One Headline Number

AveragesWeighted AveragesReporting

Average of Averages in Excel: Why Your Percentage Is Wrong

The Board Signed Off 91.1% On Time and the Client Billed £38,000 of Service Credits, Because the Dashboard Averaged the Branch Averages

20 Oct 202613 min read

UsesAVERAGESUMPRODUCTCOUNT, COUNTASUMIFS

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.

MeasureOn the dashboardOn the contract
Headline on-time91.1%86.5%
Formula=AVERAGE(D2:D10)=SUM(C2:C10)/SUM(B2:B10)
Points below 90%none3.5
Service credit, this month£0£14,000
Service credit, the quarter£0£38,000

Scope. AVERAGE, AVERAGEA, AVERAGEIF and AVERAGEIFS in Excel for Microsoft 365, 2021, 2019 and Excel for Mac. AVERAGEIFS needs 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.

ABCDEF
1
Branch
Visits
On time
On-time %
Share of visits
What happened
2
Ashbourne
1420
1207
85.0%
21.9%
The biggest round in the group, and the branch the headline should mostly be about. It did 21.9% of the month's visits and carries 12.5% of the dashboard figure.
3
Brindle Park
96
96
100.0%
1.5%
Opened in May, two visits a day, and every one of them on time. A real 100%, and worth exactly as much to =AVERAGE(D2:D10) as Ashbourne's 1,420 visits.
4
Cawthorne
1180
1003
85.0%
18.2%
The second-biggest round and the second branch at exactly 85%. Three of the four large branches sit between 85% and 88%, which is what the month actually looked like.
5
Dunhollow
48
48
100.0%
0.7%
One village, one carer, 48 visits. Seven tenths of one per cent of the work, and an eighth of the reported month.
6
Eastgate
1400
1218
87.0%
21.5%
The best of the large rounds at 87%, and still four points below what the dashboard claimed for the group as a whole.
7
Fallowfield
74
73
98.6%
1.1%
One late visit in the whole month out of 74. On a round this size a single van in traffic is the difference between 100% and 98.6%.
8
Garrowby
1300
1144
88.0%
20.0%
The strongest large branch at 88%. Even this one, the best of the four that do the work, is below the 90% the contract asks for.
9
Hestham
0
0
0.0%
Opened on the 29th and did no visits at all, so IFERROR left empty text in D9. AVERAGE skips it, AVERAGEA counts it as nought, and SUMPRODUCT refuses the column outright with #VALUE!.
10
Ivelet
980
833
85.0%
15.1%
The fourth large round, at 85% like the other two. Four branches did 5,900 of the month's 6,498 visits and none of them cleared 88%.
11
Dashboard: AVERAGE of D
91.1%
—
Eight percentages added and divided by eight. The three smallest branches are three of those eight numbers, which is why a month that ran at 86.5% was reported as a pass.
12
Contract: on time / visits
6498
5622
86.5%
100.0%
5,622 visits of 6,498 started on time. This is the figure the contract defines, it uses no percentage column at all, and it is 4.6 points below the one that went to the board.
13
Service credit at 90%
3.5 points
£14,000
The contract charges £4,000 for every point below 90%, measured across the contract. This month cost £14,000 and the quarter cost £38,000, all of it on months the dashboard passed.

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.

BranchVisitsOn-time %Weight in =AVERAGE(D2:D10)Share of visits
Ashbourne1,42085.0%12.5%21.9%
Eastgate1,40087.0%12.5%21.5%
Brindle Park96100.0%12.5%1.5%
Dunhollow48100.0%12.5%0.7%
Hestham0—none0.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.

Try it in the grid

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.

Try it in the grid

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.

Try it in the grid

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. SUMPRODUCT returns #VALUE! the moment D9 holds 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 all91.1%8
=IFERROR(C9/B9,"")91.1%8
The text n/a91.1%8
0, typed in to be safe81.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.

Try it in the grid

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 contentsAVERAGEAVERAGEA
Empty cellignoredignored
Empty text from IFERRORignoredcounts as 0
Any other textignoredcounts as 0
TRUEignoredcounts as 1
0counts as 0counts 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 AVERAGEIFS and the SUMIFS pair 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.

Try it in the grid

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))-1 is the average monthly rate, and =GEOMEAN(E2:E13)-1 says it in one function. =POWER(96/100,1/2)-1 is 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 grid

11Five Checks Before the Dashboard Goes Out

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. Re-derive one branch. =C6/B6 against the D6 already on the sheet. A single mismatch means column D is history rather than arithmetic.

12Ten Traps

  1. A mean of ratios. =AVERAGE(D2:D10) on a percentage column answers a question nobody asked, and never errors while doing it.
  2. 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%.
  3. Empty text from IFERROR. AVERAGE ignores it, COUNTA counts it, and SUMPRODUCT refuses the whole column with #VALUE!.
  4. AVERAGEA as a safer AVERAGE. It counts text as 0 and TRUE as 1, which in a percentage column is a branch at 100%.
  5. Filtering instead of weighting. AVERAGEIFS narrows the rows and still weights the ones left over equally.
  6. Averaging an average. A quarter built from three monthly means is the same trap one level up, and February is lighter than March.
  7. Averaging growth rates. Up 20% then down 20% reports as flat and is a 4% fall. Use GEOMEAN on the factors.
  8. Hidden and filtered rows. AVERAGE counts rows a filter is hiding. =SUBTOTAL(101,D2:D10) and =AGGREGATE(1,5,D2:D10) do not.
  9. Averaging rounded figures. A column rounded to one decimal, averaged, drifts from the same column at full precision. Round the headline, not the inputs.
  10. =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

  1. 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%.
  2. Move one branch. Change Dunhollow from 48 visits to 1,480, keeping it at 100%, and watch which of the two cells moves.
  3. Break it with a zero. Type 0 into D9 and read the headline again. Then delete it and read it a third time.
  4. Trip SUMPRODUCT. Put =IFERROR(C9/B9,"") back into D9 and run the weighted formula. Then guard it with ISNUMBER.
  5. 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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge