Excel's Statistical Functions: MEDIAN, Percentiles, RANK and the Average Nine of the Twelve Are Below

Twelve reps, one quarter, one number at the bottom of the revenue column: 263,791.67 average per rep.

Read the column. Nine of those twelve reps closed less than that. Two of them closed less than a third of it. The average is a real number, correctly calculated from real data, and as a description of that sales team it is close to useless — because one renewal worth 1,240,000 is standing in the middle of the column holding it up.

This is not an argument against AVERAGE. It is an argument for knowing which summary you are asking for. Excel ships every statistic you need to describe a column honestly — the middle, the spread, the shape, the ranking, the outliers — and most of them are one function call. The work is not learning them. The work is knowing which one the sentence you are about to write actually needs.

What you need. Everything here works in Excel 2010 and later, in Microsoft 365, on Mac, on the web and in Google Sheets, with two exceptions flagged where they come up: MODE.MULT needs a dynamic-array Excel to spill neatly, and MEDIAN+FILTER is a Microsoft 365 combination. Nothing needs the Analysis ToolPak.

1) The Average, and the Nine People Below It

🎯 Scenario: The quarter is closed. Twelve reps, revenue, cycle time and a satisfaction score, exactly as the CRM exported it.

Start with the two one-liners that disagree:

A gap of over 100,000 between two summaries of the same twelve numbers. And a third formula explains it in one shot:

=COUNTIF(D2:D13,">"&AVERAGE(D2:D13))   →   3

Three reps are above the average. Nine are below it. That is not a broken average — it is a perfectly working one, applied to a column that is skewed: most values clustered low, one very large value pulling the mean towards itself.

The mechanism is worth stating plainly, because it is the whole reason the rest of this article exists. AVERAGE adds everything and divides. Every value gets a vote weighted by its size, so a single number that is eight times the median gets eight times the say. MEDIAN sorts and takes the middle. Every value gets one vote regardless of size, so the whale counts for exactly as much as Sofia Rossi's 74,000: one row.

That difference has a name — the mean is sensitive to outliers, the median is robust to them — and it decides which one you want:

• Use the mean when you need the total to reconcile. Mean × count = total, always. Budgets, capacity, cost per unit, anything you will multiply back up.
• Use the median when you are describing a typical member of the group. "What does a rep close in a quarter" is a median question, and answering it with 263,791.67 tells nine people they underperformed a number that one deal invented.

The honest version of the headline is both: median 158,000, mean 263,792, one renewal accounting for 39% of the quarter. That sentence is three functions long and nobody can misread it.

2) MEDIAN, and the Middle That Is Not a Row

MEDIAN sorts the values and takes the middle one. With an odd count that is a real value from a real row. With an even count — and twelve is even — there is no middle row, so Excel averages the two either side of the gap.

Sorted, the revenue column runs:

74,000  96,500  96,500  121,500  133,000  [158,000  158,000]  182,000  203,000  288,000  415,000  1,240,000

Positions six and seven are both 158,000, so the median is 158,000 exactly — and it happens to be a value two reps actually closed. Do the same on Cycle Days and the arithmetic shows:

18  19  21  22  25  [27  29]  34  38  41  47  96
=MEDIAN(E2:E13)   →   28

Nobody had a 28-day cycle. 28 is the average of 27 and 29, and it is still the right answer to "what does a typical deal take" — but if you are going to hand somebody a row that matches, take the count into account first. Two useful details while you are here:

• MEDIAN ignores text, logicals and empty cells, exactly as AVERAGE does. It does not ignore zeros.
• It takes up to 255 arguments, so =MEDIAN(D2:D13, G2:G13) medians two ranges together, and =MEDIAN(25, raw, 250) is the old clamp trick: the middle of floor, value, cap.

3) MODE.SNGL, MODE.MULT, and the Tie You Cannot See

The third summary is the most common value. On a continuous column like revenue it is nearly useless — no two reps close the same amount except by coincidence — but on a scored column it is the only one that means anything.

The CSAT column holds 8, 7, 9, 6, 8, 0, 9, 7, n/a, 8, (blank), 7.

=MODE.SNGL(F2:F13)   →   8

That answer is true and misleading. There are three 8s and three 7s. MODE.SNGL cannot report a tie, so it returns whichever tied value appears first in the range — Ana Ruiz's 8 sits in F2, Tom Nowak's 7 in F3, so 8 wins on position, not on frequency. Sort the sheet differently and the "most common score" changes to 7 without a single value being edited.

MODE.MULT is the honest one. It returns an array of every value tied for most common, which spills down two cells in a modern Excel:

=MODE.MULT(F2:F13)   →   8
                         7

In an older Excel, select two cells first and commit with Ctrl + Shift + Enter. And if there is no repeated value at all, both return #N/A — which is a correct answer to "what is the most common revenue figure", however unhelpful it looks.

4) What Counts as a Number: COUNT, COUNTA, COUNTBLANK

The CSAT column has twelve rows and three kinds of nothing in it. Four counting functions disagree about it, and every one of them is right:

Ten numbers, one text cell holding n/a, one empty cell. COUNTA counts the n/a; COUNT does not; COUNTBLANK finds the one truly empty cell and ignores the text.

The trap in that table is COUNTA, because a formula returning an empty string is not an empty cell. A column of =IF(A2="","",A2) looks blank and counts as full: COUNTA sees a formula result and counts it, COUNTBLANK sees "" and — this is the inconsistency worth memorising — counts it as blank anyway. If your denominator has to be right, count the thing you actually mean: =COUNT() for numbers, or =COUNTIF(F2:F13,"<>") for cells with something real in them.

5) AVERAGE Skips the Blank and Adds the Zero

Now the denominator matters. Same column, three defensible answers:

AVERAGE ignores blanks and text, so 69 ÷ 10 = 6.9. Divide by every row instead and two customers who never answered the survey drag the score down to 5.75. Exclude Luis Marin's zero and it climbs to 7.67.

None of those is a bug. They answer three different questions, and the only wrong move is picking one without deciding which question you are answering. The one that needs a decision from a human is the zero:

• A real 0 is a score. Somebody rated the service zero out of ten. It belongs in the average, and dropping it is flattering the number.
• A 0 that means "no responses" is a missing value wearing a number's clothes, and it belongs nowhere near AVERAGE — it is a blank that a form filled in for you.

You cannot tell those apart from the sheet, which is the point. Ask whoever built the export, and when the answer is "no responses", the fix is upstream: make the export write an empty cell, not a zero. Until it does, AVERAGEIF(range,"<>0") is a patch, and one that will silently discard the genuine zeros too.

6) LARGE and SMALL: The Top Three Without Sorting Anything

MAX and MIN give you the ends. LARGE and SMALL give you the kth from either end, which is what you need for a top-three that survives the list being re-sorted:

LARGE(range,1) is MAX and SMALL(range,1) is MIN; the point is everything after 1. Two things they do that a sort cannot:

They stack into one cell. =SUM(LARGE(D2:D13,{1,2,3})) → 1,943,000, the top three deals in a single formula, no helper column. Against a total of 3,165,500 that is 61.4% of the quarter closed by three people — one number, and it belongs in the summary far more than the mean does.

They pair with INDEX/MATCH to name the row. =INDEX(A2:A13, MATCH(LARGE(D2:D13,2), D2:D13, 0)) → Priya Raman. Watch the tie though: ask for the 6th largest and MATCH finds 158,000 and returns Hana Lindqvist both times, because MATCH stops at the first hit. Ranking with ties is the next section's problem, and this is why it has one.

Both functions ignore text and blanks, and both return #NUM! if k is zero, negative, or larger than the count of numbers in the range — so =LARGE(D2:D13,15) is an error, not a blank.

7) RANK.EQ, RANK.AVG, and the Place That Disappears

RANK.EQ gives each value its position in the range, largest first by default:

=RANK.EQ(D2, $D$2:$D$13)     third argument omitted → descending
=RANK.EQ(E2, $E$2:$E$13, 1)  1 → ascending, which is what you want for cycle days

Filled down the revenue column, that gives:

Nobody is 7th, and nobody is 11th. RANK.EQ hands tied rows the same rank and then skips as many places as it consumed — two reps at 6th means the next one is 8th. That is standard competition ranking and it is correct; it just means a ranked column of twelve rows will not contain the numbers 1 to 12, and a VLOOKUP for "the rep ranked 7th" comes back #N/A.

RANK.AVG splits the difference instead: two rows tied for 6th and 7th place both get 6.5. Use it when the ranks feed arithmetic — averages of ranks, percentile calculations, anything where the missing 7 would bias the result. Use RANK.EQ when a human is reading it, because "6.5th" is not a sentence anybody says out loud.

To break ties deterministically, add a tiny tiebreaker instead of arguing about it — deals closed, for instance:

=RANK.EQ(D2,$D$2:$D$13) + COUNTIFS($D$2:$D$13, D2, $C$2:$C$13, ">"&C2)

Hana Lindqvist closed 17 deals against Karl Vogt's 13 on the same revenue, so nobody is ahead of her on the tiebreak and she keeps 6; Karl has one rep ahead of him and moves to 7. Tom Nowak and Grace Bell separate the same way at 96,500, on 9 deals against 12. Ties broken, 1 to 12 complete, and the rule is written down in the formula where anybody can read it.

RANK without a suffix still works and is identical to RANK.EQ; it is kept for compatibility with pre-2010 files. New formulas should say which one they mean.

8) Ranking Inside a Region, With COUNTIFS

RANK.EQ ranks against one flat range. It has no idea regions exist, and there is no RANKIFS. The standard replacement is a COUNTIFS that counts how many rows in the same group beat this one, plus one:

=COUNTIFS($B$2:$B$13, B2, $D$2:$D$13, ">"&D2) + 1

Read it as a sentence: how many rows share my region and have more revenue than me — that many people are ahead, so I am one place behind them.

The group size for the denominator is the same idea with one condition: =COUNTIFS($B$2:$B$13, B2), or plain COUNTIF. This pattern handles ties the RANK.EQ way — equal values get equal ranks and the next place is skipped — and it extends to as many grouping columns as you like by adding pairs of arguments. It is the one formula in this article worth committing to memory, because "rank within category" is a request that arrives roughly monthly and there is still no function for it.

9) Percentiles and Quartiles, and the Two That Disagree

A percentile is the value below which a given share of the data sits. The 25th percentile of Cycle Days is the number a quarter of the deals came in under.

Excel has two families, and they do not agree:

Those are not rounding differences. 46.4 and 81.3 are the same 90th percentile of the same twelve numbers, and the gap is the interpolation rule:

• .INC (inclusive) places the n values at positions 0, 1/(n−1), … 1. The 0th and 100th percentiles are the minimum and maximum, so every p from 0 to 1 returns something.
• .EXC (exclusive) places them at 1/(n+1), … n/(n+1), treating your rows as a sample from a larger population that extends past both ends. It refuses any p outside 1/(n+1) to n/(n+1) — with twelve rows that is 0.077 to 0.923, which is why the 95th is #NUM! and not a number.

That #NUM! is the single most useful thing in this section. It is Excel saying twelve rows do not contain a 95th percentile, and it is right. .INC will answer the same question with 69.05 and no warning at all.

Which to use: .INC when the rows are the whole population — these twelve are the team, all of it, no inference intended. .EXC when the rows are a sample and you mean to say something about deals in general. PERCENTILE and QUARTILE with no suffix are the old names and behave as .INC.

QUARTILE is PERCENTILE with a quarter-sized dial, which is easier to type and harder to mistype:

Going the other way — where does this value sit rather than what value sits here — is PERCENTRANK.INC. =PERCENTRANK.INC(E2:E13, 29) → 0.545, so a 29-day cycle is slower than 54.5% of the quarter's deals.

10) The IQR Rule: Naming an Outlier Instead of Arguing About One

Everyone can see that the 96-day deal is unusual. The problem with "can see" is that it is not a rule, and a number that is unusual in a report is a number somebody will want removed from it. Quartiles give you the rule.

The interquartile range is the middle half of the data: Q3 − Q1.

Q1  =QUARTILE.INC(E2:E13,1)         →  21.75
Q3  =QUARTILE.INC(E2:E13,3)         →  38.75
IQR =QUARTILE.INC(E2:E13,3)-QUARTILE.INC(E2:E13,1)   →  17

The conventional fences sit one and a half IQRs beyond each quartile:

Upper fence  =Q3 + 1.5*IQR   →  64.25
Lower fence  =Q1 - 1.5*IQR   →  -3.75

🎯 Scenario: The regional director wants the 96-day deal taken out of the cycle-time average, and you want the decision to be a rule rather than a favour.

One flag column and it is no longer an opinion:

=IF(OR(E2>$H$2, E2<$H$3), "outlier", "")

96 clears the upper fence of 64.25; 47 does not; nothing goes below the lower fence, which is negative and therefore unreachable — a normal result for a column that cannot go below zero. One outlier, named by a formula that was written before anybody looked at the answer.

Why fences built from quartiles rather than from the mean and standard deviation: the quartiles barely move when you add an extreme value, while the mean and the standard deviation are both dragged towards it. Here the outlier inflates the very mean you would test it against — AVERAGE(E2:E13)+2*STDEV.S(E2:E13) is 77.48, a threshold the 96 helped set. The IQR rule does not have that problem, which is why box plots are drawn from it.

11) STDEV.S, STDEV.P, and the Sales Target of Minus 58,000

The mean says where the column sits. The standard deviation says how spread out it is — roughly, the average distance from the mean, in the same units as the data.

.S when your rows are a sample of something bigger you want to generalise about; .P when your rows are the entire population you care about. The n − 1 in the sample version corrects for the fact that a sample's own mean is closer to the sample than the true mean would be, so dividing by n understates the spread. With twelve rows the difference is 4%; with thousands it is nothing; with five rows it is large enough to matter. If you cannot decide, ask whether the answer is about these twelve reps (.P) or reps in general (.S).

Now look at what it says here. Mean 263,792, standard deviation 321,721, so the usual "mean ± one standard deviation" band is:

263,792 − 321,721  =  −57,929
263,792 + 321,721  =  585,513

A range of minus 58,000 to 586,000 for a quarter's revenue. Revenue cannot be negative, and no rep on this list is anywhere near 586,000 except the one who broke the column in the first place.

That is not a broken function. It is the standard deviation doing exactly its job and telling you the shape is wrong for the summary: mean and standard deviation describe a roughly symmetric spread, and this column is not one. When the band goes impossible, that is the signal to switch to quartiles — median 158,000, middle half between 115,250 and 224,250 — which describes the same twelve reps without claiming anybody sold minus 58,000.

The one place the standard deviation earns its keep on data like this is the coefficient of variation, =STDEV.S(D2:D13)/AVERAGE(D2:D13) → 1.22. Spread larger than the average is a portable red flag: any column with a CV over about 1 is a column whose mean should not be quoted alone.

12) TRIMMEAN, and the Trim That Trims Nothing

TRIMMEAN cuts a percentage off both ends of the sorted data and averages what is left — the gymnastics-judging approach.

=TRIMMEAN(D2:D13, 0.2)   →   185,150

20% of twelve rows is 2.4 values. Excel rounds that down to the nearest multiple of 2 so it can remove the same count from each end: two values go, one from the top (1,240,000) and one from the bottom (74,000), and the remaining ten average to 185,150. Against a mean of 263,792 and a median of 158,000, that is a defensible middle — the whale's influence is gone, and ten real rows still contribute.

The rounding is the part that bites:

TRIMMEAN(range, 0.1) on twelve rows returns the plain average, silently, because 1.2 rounds down to zero values trimmed. A trimmed mean that trims nothing looks exactly like a trimmed mean that worked. On small ranges, check the count you expect to lose before trusting the number: =2*INT(COUNT(range)*percent/2) tells you how many rows will actually go.

Three ways to handle the whale, side by side, and the honest thing is that all three are legitimate:

What is not legitimate is picking whichever came out best and printing it without saying which one it is.

13) Conditional Statistics, and the MEDIANIFS That Does Not Exist

Excel gives you a conditional version of some statistics and not others:

The ones that exist are straightforward, and the region breakdown shows why they matter here:

=AVERAGEIFS($D$2:$D$13, $B$2:$B$13, "AMER")   →   404,000
=AVERAGEIFS($E$2:$E$13, $B$2:$B$13, "APAC")   →   19.67

AMER has the highest mean revenue of any region and the lowest median but one. It is the whole article in one row: the same renewal that broke the company average breaks the regional one, and until you put the median beside it, AMER looks like the region to copy.

For the statistics with no IFS form, wrap the condition in an array instead. On Microsoft 365, FILTER is the readable way:

=MEDIAN(FILTER($D$2:$D$13, $B$2:$B$13="AMER"))         →   139,750
=PERCENTILE.INC(FILTER($E$2:$E$13, $B$2:$B$13="EMEA"), 0.9)   →   36.4
=STDEV.S(FILTER($D$2:$D$13, $B$2:$B$13="EMEA"))        →   44,007.95

Anywhere else, the classic form does the same job by turning the unmatched rows into FALSE, which MEDIAN then ignores:

=MEDIAN(IF($B$2:$B$13="AMER", $D$2:$D$13))

In Excel 2019 and earlier that needs Ctrl + Shift + Enter; in Microsoft 365 it just works. Either way the rule holds: any function that takes a range takes a filtered array, which quietly gives you a conditional version of every statistic Excel has, including the four it never shipped.

14) Which Number to Print

The functions are the easy half. Choosing is the half that gets read:

And two rules that survive contact with an audience:

1. When the mean and the median disagree, print both. The gap is information — it is the shape of the distribution, in one subtraction. Hiding it is a choice about what the reader is allowed to know.
2. Say what you excluded, in the sheet. A trimmed mean, an AVERAGEIF with a threshold, an outlier flag — all of them are fine, all of them are defensible, and none of them survives being discovered later by somebody who was not told.

Practice

1. The two summaries. Put =AVERAGE(D2:D13) and =MEDIAN(D2:D13) side by side, then write =COUNTIF(D2:D13,">"&AVERAGE(D2:D13)). Say in one sentence what those three numbers together tell a sales director.
2. The denominator. Produce 6.9, 5.75 and 7.67 from the CSAT column with three different formulas, and write down the question each one answers.
3. The vanishing rank. Fill RANK.EQ down the revenue column and try to look up "the rep ranked 7th". Then do it with RANK.AVG and explain why 6.5 appears twice.
4. Rank inside the region. Write the COUNTIFS rank for every rep and check Karl Vogt comes out 2nd of 4 in AMER while ranking 6th overall.
5. The 95th percentile. Ask PERCENTILE.EXC for 0.95 of the cycle days, then PERCENTILE.INC. Explain the #NUM! to somebody who wants the number anyway.
6. The fences. Build Q1, Q3, IQR and both fences in four cells, then flag every outlier in the cycle column with one IF. Say what happens to the flag if Dan Osei's 96 becomes 60.
7. The trim that did nothing. Compare TRIMMEAN(D2:D13,0.1) with AVERAGE(D2:D13). Then find the smallest trim percentage that actually removes a value from a twelve-row range.
8. The regional lie. Build the mean and median revenue per region with AVERAGEIFS and MEDIAN(FILTER(…)), and say which region you would send a new rep to.

Summary

AVERAGE is not wrong. It is a specific summary — the one where every value votes in proportion to its size — and on a column with one 1,240,000 in it, that is a summary of the renewal rather than of the team. MEDIAN gives every row one vote, which is why it says 158,000 while the mean says 263,792, and why nine of twelve reps recognise one of those numbers and not the other.

The rest of the toolkit exists to describe the same column from other angles. COUNT, COUNTA and COUNTBLANK settle what the denominator is before you divide by it, and settle it differently for a blank cell, a text cell and a zero somebody's form typed. LARGE and SMALL pull the kth value from either end without sorting anything, and SUM(LARGE(range,{1,2,3})) puts "three people closed 61% of the quarter" into one cell. RANK.EQ skips a place after every tie — nobody is 7th, and that is correct — while RANK.AVG splits it for arithmetic and COUNTIFS ranks inside a region, which is the formula to remember because there is no RANKIFS.

Percentiles come in two flavours that genuinely disagree, and the #NUM! from PERCENTILE.EXC is a feature: twelve rows do not contain a 95th percentile, and .INC will hand you one anyway. Quartiles turn into the 1.5 × IQR fences, which name an outlier before anyone has looked at the answer — and they do it from statistics the outlier cannot inflate, unlike the mean and standard deviation it drags along with it.

Which leaves the judgement, which no function makes for you. When one standard deviation below the mean is minus 57,929, the column is telling you the mean was the wrong summary. Print the median beside it, say what you trimmed, and let the gap between the two numbers do the work it is there to do.