Descriptive Statistics: Averages, Ranking, Percentiles and Variability

The problem

A column of numbers says nothing until it is summarized. A manager does not want to read 200 customer totals or 15 tender prices: he wants to know what is typical, what is exceptional, and how much the figures move around. Excel's statistical category contains dozens of functions, from correlation to chi-square tests, but a small group of them answers most everyday questions and needs no statistical theory to use well.

The difficulty is not in the syntax, which is almost always a single range. It is in choosing the right measure, because each one answers a slightly different question, and the wrong one can tell a convincing but false story.

What is typical: four kinds of average

On the Averages sheet, B2:B16 holds the net price per pack achieved in 15 hospital tenders. Most prices cluster around 40, two tenders went badly wrong in opposite directions, and several tenders landed exactly on the same reference price.

AVERAGE, the arithmetic mean, adds the values and divides by their number. It is the measure everybody knows, and it is sensitive to every single value, including the extreme ones.

MEDIAN returns the middle value once the data are sorted; with an even number of values, the average of the two middle ones. It ignores how extreme the extremes are, so one exceptional tender barely moves it. When the mean and the median are far apart, the distribution is skewed, and the median is usually the better description of a "typical" case.

MODE.SNGL returns the most frequent value, or #N/A if no value repeats. In price data it often reveals something useful: a price that many tenders converge on, such as a reference price.

TRIMMEAN is a compromise between the mean and the median. It removes a percentage of the values from the two tails before averaging:

=TRIMMEAN(B2:B16,I6)

With 20% in I6, twenty percent of 15 values is 3, and Excel rounds the number of excluded values down to an even number, so it removes the lowest and the highest value and averages the remaining 13. TRIMMEAN is useful whenever the extremes are suspected to be anomalies rather than information, which is often the case with negotiated prices.

The geometric mean, for growth rates

There is a fourth average that matters whenever the values are rates of change. Suppose a price rose by 10% one year and fell by 10% the next. The arithmetic average of the two changes is 0%, but the price is not back where it started: 100 became 110, then 99. The real average change is about -0.5% per year.

The geometric mean gets this right. It works on growth factors (1 plus the rate), not on the rates themselves. With the annual price changes in E2:E6 and their growth factors =1+E2 in F2:F6:

=GEOMEAN(F2:F6)-1

returns the average annual change that, compounded over the five years, gives exactly the actual cumulative change. It is the same logic as a compound annual growth rate, and it is always lower than or equal to the arithmetic average of the rates. Using AVERAGE on growth rates is one of the most common, and most flattering, mistakes in commercial reporting.

Where a value stands: extremes, ranks and percentiles

The Ranking sheet lists 200 customers with their annual net sales in B2:B201.

MIN and MAX return the smallest and largest values. LARGE and SMALL generalize them: =LARGE($B$2:$B$201,3) returns the third largest value, so a small table with k from 1 to 5 in one column and LARGE in the next produces a top-five list without sorting anything.

RANK.EQ returns the position of a value in the list:

=RANK.EQ(E2,$B$2:$B$201)

where E2 holds the sales of the customer we are looking at. By default the ranking is descending, so the largest value is number 1; a third argument of 1 reverses it. Tied values share the same rank, and the next rank is skipped. RANK.AVG gives tied values the average of the positions they occupy, which some statistical uses require.

PERCENTILE.INC answers a different question: below which value does a given share of the data fall?

=PERCENTILE.INC($B$2:$B$201,E6)

with 90% in E6, returns the value that separates the bottom 90% of customers from the top 10%. The 50th percentile is the median. PERCENTRANK.INC does the reverse: given a value, it returns the share of the data below it, so =PERCENTRANK.INC($B$2:$B$201,E2) tells us that a customer sits, for example, at the 87th percentile. Unlike RANK.EQ, it works even for a value that is not in the list, which is handy for testing "what if" figures.

A practical case: how concentrated are sales?

Percentiles make it easy to measure concentration: the share of total sales generated by the best 20% of customers. With 20% in E9, the cut-off is the 80th percentile, and SUMIFS adds up everything above it:

=SUMIFS($B$2:$B$201,$B$2:$B$201,">"&PERCENTILE.INC($B$2:$B$201,1-E9))/SUM($B$2:$B$201)

Without PERCENTILE.INC, the same figure requires sorting the customers, computing a running percentage and finding the 40th row by hand. A common rule of thumb, the Pareto principle, expects a result around 80%. It is a heuristic, not a law: in specialty care, where a few hospitals account for most of the volume, the figure can be much higher, and knowing it changes how a sales force should be deployed.

How much the values move: variability

Two products can sell the same average number of units per month and behave completely differently. The Variability sheet holds 24 months of units for two products with similar averages, one steady and one erratic.

The standard deviation measures how far, on average, the values lie from their mean. Mathematically it is the square root of the average squared distance from the mean, but the formula is never needed in Excel:

=STDEV.S(B2:B25)

STDEV.S treats the data as a sample, which is the usual case: 24 months are a sample of how the product behaves. STDEV.P treats them as the entire population and gives a slightly smaller result; with more than a few dozen values the difference is negligible.

On its own, a standard deviation is hard to interpret. Knowing that monthly sales vary by 450 units means little unless we know whether the product sells 1,000 or 100,000 units a month. Dividing the standard deviation by the mean gives the coefficient of variation, a pure ratio that can be compared across products, markets and years:

=STDEV.S(B2:B25)/AVERAGE(B2:B25)

A coefficient of 10% describes a stable product; 40% describes one that is hard to forecast, to supply and to manage. The sheet also shows a tiny example that makes the idea tangible: the series 4, 5, 6 and 1, 5, 9 have the same mean of 5, but standard deviations of 1 and 4, and the second is four times as variable.

AVEDEV is a simpler alternative: the average of the absolute distances from the mean. It is easier to explain to a non-technical audience, gives the same ranking of variability in most practical cases, and is less dominated by single extreme months.

New names, old functions

Excel 2010 renamed several statistical functions to make their behavior explicit: STDEV became STDEV.S, PERCENTILE became PERCENTILE.INC, RANK became RANK.EQ, MODE became MODE.SNGL. The old names still work and give the same results, for compatibility with older files, but the new ones should be preferred in new work.

Common mistakes

• Using AVERAGE on skewed data. A few very large customers or a single extreme tender make the mean unrepresentative; check the median.
• Averaging growth rates arithmetically. Use the geometric mean of the growth factors.
• Reading MODE.SNGL on continuous data. If no value repeats exactly, it returns #N/A, and if a value repeats by chance, the result means nothing.
• Comparing standard deviations of series with different sizes. Use the coefficient of variation.
• Forgetting that ranks are descending by default. In RANK.EQ, 1 is the largest value unless the third argument says otherwise.
• Using >= instead of > with a percentile cut-off, or vice versa, without thinking. With many identical values, the choice changes how many customers fall in the top group.

For further reading

• TRIMMEAN function - Microsoft Support
• PERCENTILE.INC function - Microsoft Support
• STDEV.S function - Microsoft Support