3.7 Describing data honestly in Excel

What you'll be able to do

Summarise a set of data so the reader is informed instead of misled. The right centre for the shape, the spread stated honestly, correlation reported without a cause story, and no digits pretending to a precision nobody has.

Before you start

Open the pivot sales workbook. Its amounts column leans to one side, like most money data.

The work

1. Same either way: work out the three centres side by side: =AVERAGE(range), =MEDIAN(range) and =MODE.SNGL(range). When the mean and the median roughly agree, the data is balanced and the mean serves. The mean often sits well above the median, as it does in almost all income, sales and waiting-time data. A long tail of large values is pulling it up, and then the median is the honest headline. The mean of a lopsided column answers "the total divided by the count", not "what is typical".
2. Same either way: spread comes next, in pairs. =STDEV.S(range) is the standard deviation, which is a kind of average distance from the mean. =QUARTILE.INC(range,1) and =QUARTILE.INC(range,3) mark off the middle half of the data, and the gap between them is called the interquartile range. Extreme values pull the standard deviation the same way they pull the mean, and the interquartile range ignores them. Report the standard deviation with a mean, and the interquartile range with a median.
3. Same either way: see the shape before you trust any summary. Select the column and press Alt+F1 for a quick chart, or use Insert → Statistic Chart → Histogram on the ribbon. Two humps mean two populations mixed together, and then no single-number summary describes anybody.
4. Same either way: percentiles say where one value stands. =PERCENTRANK.INC(range, x) gives the fraction of the data below x. =PERCENTILE.INC(range, 0.9) gives the value at the 90th percentile. Fee cutoffs, waiting-time targets and mark standings are all percentile sentences.
5. Same either way: relationships come from =CORREL(rangeX, rangeY), which scores how two columns move together from −1 to 1. A scatter chart (Insert → Scatter) with a trend line shows the same thing. Then say the warning aloud, every time: things moving together is not one causing the other. Ice-cream sales and drownings move together because of summer. Marketing spend and sales may move together because of the festival season. A correlation earns a question, never an action on its own.
6. Same either way: round for the reader at the end. Use =ROUND(x, 0) in the cells you present, or set fewer decimal places with Ctrl+1. A mean of 4,238.2871 claims six figures of knowledge that nobody has. False precision reads as care and is the opposite. Two or three significant figures is what a decision needs.

Try it

For the amounts column, work out the mean and the median, note the gap, and say which one you would report. Then the standard deviation and the interquartile range, the histogram, the 90th percentile, and the correlation of amount with any second column of numbers. Report that last one in a sentence with no causing verb in it. Round everything for a reader.

Check yourself