Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 93 ยท Statistics and Math

Statistics for Analysts: Averages, Spread and Rank

Describe data in Excel: AVERAGE, MEDIAN, STDEV.S vs STDEV.P, VAR, PERCENTILE, QUARTILE, RANK.EQ, LARGE, SMALL and z-scores, with runnable stand-ins.

A single column of numbers tells you very little until you summarize it. Where is the middle? How far do the values scatter around it? Where does one value stand compared with the rest? Excel answers these questions with a family of statistical functions, and picking the right one matters: the mean, the median and the standard deviation can all point in different directions when the data has an outlier or comes from a sample.

You already know the basics from SUM, AVERAGE, COUNT, MIN and MAX. This lesson goes further: measures of spread, percentiles and quartiles, rank, the k-th largest or smallest value, and standardized scores. Excel cannot run here, so the TryIts use a JavaScript model of the functions (a stand-in that follows the documented formulas) and pandas, whose describe() produces the same numbers.

Describe a column in three ways

Center AVERAGE, MEDIAN, MODE.
Spread STDEV, VAR, MIN to MAX.
Position RANK, PERCENTILE, LARGE.
1 / 2

The center: mean, median and mode

AVERAGE(range) adds the numbers and divides by how many there are. Text, logical values and blanks inside a range are ignored. MEDIAN(range) is the middle value of the sorted list (the mean of the two middle values when the count is even). MODE.SNGL(range) returns the most frequent value, and #N/A when no value repeats.

The mean is pulled by extreme values; the median is not. For salaries, house prices and response times, the median is usually the more honest summary. If the two differ a lot, you have skewed data and should look at the distribution before quoting a single figure.

The spread: variance and standard deviation

Variance measures the average squared distance from the mean, and the standard deviation is its square root, which brings it back to the units of your data. Excel has two versions of each, and choosing between them is the most common statistics mistake in spreadsheets:

Function Use it when Divides by
VAR.S, STDEV.S the values are a sample from a larger group n - 1
VAR.P, STDEV.P the values are the whole population n

Ten exam scores from one class are a population if you only care about that class, and a sample if you want to say something about all classes. With a sample, dividing by n - 1 corrects the tendency of a small sample to underestimate the real spread. The older names STDEV and VAR still work and mean the sample versions; prefer the dotted names because they say what they do.

Handy neighbors: MIN, MAX, MAX - MIN for the range, and AVEDEV for the average absolute deviation.

Position: rank, large and small

  • LARGE(range, k) is the k-th largest value, SMALL(range, k) the k-th smallest. LARGE(A2:A20, 1) equals MAX.
  • RANK.EQ(number, range, [order]) gives the position of a value in the list. Order 0 (the default) ranks the largest as 1; any non-zero order ranks the smallest as 1. Ties get the same rank, so the next rank is skipped. RANK.AVG gives tied values the average of their ranks instead.
  • To get the name that goes with the third-highest score, use LARGE inside a lookup, for example =INDEX(A2:A20, MATCH(LARGE(B2:B20, 3), B2:B20, 0)). See INDEX and MATCH. Ties would return the first match, so break ties with a helper column first.

Percentiles and quartiles

The p-th percentile is the value below which p percent of the data falls. PERCENTILE.INC(range, p) takes p as a fraction from 0 to 1, so 0.9 is the 90th percentile. QUARTILE.INC(range, q) is the same with q set to 0 to 4 (0 is the minimum, 2 is the median, 4 is the maximum).

The .INC ("inclusive") versions place the p-th percentile at position p * (n - 1) in the sorted list, counting from zero, and interpolate between neighbors when that position is not a whole number. The .EXC ("exclusive") versions use position p * (n + 1) counting from one, and return #NUM! when p is so close to 0 or 1 that no such position exists. Use .INC unless a method you were told to follow specifies otherwise. pandas, by default, matches .INC.

A common use is the interquartile range, QUARTILE.INC(r, 3) - QUARTILE.INC(r, 1), and the "1.5 times IQR" rule for flagging outliers.

Z-scores: how unusual is a value?

A z-score says how many standard deviations a value sits from the mean: (x - mean) / stdev. Excel has STANDARDIZE(x, mean, standard_dev) for it. A z-score near 0 is typical; beyond about 2 or -2 is unusual. Which standard deviation you pass (.S or .P) changes the answer, so be consistent.

Run it: statistics in JavaScript

The stand-in implements each function from its definition, so you can see the sample versus population difference and the two percentile methods. The scores include a tie (70 appears twice) and one outlier.

JS A statistics toolkit on ten scores

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In a worksheet, each of those lines is one short formula, for example =STDEV.S(B2:B11) or =RANK.EQ(B2, $B$2:$B$11). The absolute reference in the rank formula keeps the range fixed when you copy it down; see cell references.

Which function ignores what?

All of these functions skip text and blank cells inside a range reference, but a number typed directly as an argument is used, and text that looks like a number typed as an argument is converted. Cells holding numbers stored as text are silently skipped, which is why a statistics function sometimes seems to "miss" values: see number-as-text problems. Compare the count in the status bar (select the range) with what you expect.

There are ...A variants (AVERAGEA, STDEVA, MAXA) that treat text as 0 and TRUE as 1. They are rarely what you want.

For conditional statistics, such as the average of one region only, combine them with conditional aggregates (AVERAGEIFS) or, in modern Excel, with FILTER: =MEDIAN(FILTER(B2:B100, A2:A100 = "North")).

The same numbers in pandas

pandas computes the whole summary at once with describe(). Its defaults match Excel's .S and .INC functions, which makes it a good way to cross-check a sheet.

Python pandas describe() and rank

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare the two outputs: the quartiles, the sample standard deviation and the 90th percentile agree with the JavaScript stand-in.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You have the scores of every student in your class and want to describe only that class. Which function fits best?

    The data is the whole population of interest, so STDEV.P, which divides by n, is correct. STDEV.S is for samples drawn from a larger group.
  2. 2. What does RANK.EQ return for two values that are tied for first place when ranking descending?

    RANK.EQ gives tied values the same rank and skips the following ranks. RANK.AVG would return 1.5 for both.
  3. 3. Why can the median be a better summary than the mean for salaries?

    A few very high salaries raise the mean a lot, while the median stays near the typical value.
  4. 4. What does =PERCENTILE.INC(A2:A11, 0.5) equal?

    The 50th percentile is the median. With the inclusive method it is the middle value, or the mean of the two middle values when the count is even.
  5. 5. A z-score of -2.1 means the value is:

    A z-score counts standard deviations from the mean, and the minus sign means below it.

Summary

  • AVERAGE, MEDIAN and MODE.SNGL describe the center; the median resists outliers.
  • Use STDEV.S and VAR.S for samples (divide by n - 1) and STDEV.P and VAR.P for a whole population.
  • LARGE, SMALL, RANK.EQ and RANK.AVG give position; RANK.EQ skips ranks after ties.
  • PERCENTILE.INC and QUARTILE.INC interpolate; the .EXC versions error near the extremes.
  • STANDARDIZE gives z-scores; pandas describe() is a quick way to check your sheet.

Next: Financial Functions: PMT, NPV, IRR and More.