Descriptive statistics and regression in Excel

In depth

Sample or population: STDEV.S and STDEV.P

Excel has two standard deviations. STDEV.S assumes the data are a sample and divides the sum of squared deviations by $n - 1$; STDEV.P treats them as the whole population and divides by $n$. VAR.S and VAR.P return the corresponding variance, the square of the standard deviation. Microsoft’s rule: if the data represent the entire population, use STDEV.P.

For Lantern’s 231 orders the difference is small: STDEV.S gives $100.90 and STDEV.P $100.68. For the 13 breakfast events it is larger, $326.60 against $313.79, because dividing by 12 instead of 13 matters more. In business analysis the data are nearly always a sample of a process that goes on (this spring’s orders stand for orders in general), so STDEV.S is the usual choice; use STDEV.P when the rows are the whole group and you don’t generalise beyond it, such as the salaries of a shop’s eight staff.

Quartiles and percentiles: .INC and .EXC

QUARTILE.INC and PERCENTILE.INC place the p-th percentile at position $1 + (n - 1)p$ in the sorted values, interpolating between neighbours; B01 and B02 use this method. QUARTILE.EXC and PERCENTILE.EXC use position $(n + 1)p$. For the 231 orders, Q1 is at position 58.5 by .INC, between $15.30 and $15.50, giving $15.40; by .EXC it is at position 58, exactly $15.30. The 90th percentile is $68.00 by .INC and $76.80 by .EXC.

The .EXC versions exclude the ends: QUARTILE.EXC returns #NUM! for quart 0 or 4, and PERCENTILE.EXC returns #NUM! when k is 0 or 1, or so close to them that it can’t interpolate. QUARTILE.INC with quart 0, 2 and 4 returns the minimum, the median and the maximum. Neither method is wrong; state which one you used, and expect other software to give slightly different quartiles on small data. Excel’s box and whisker chart offers a similar choice, “inclusive median” or “exclusive median”, which Microsoft defines by whether the median is included in the calculation when the number of values is odd.

MODE.SNGL returns the most frequent value and #N/A if no value repeats. That makes it useful for counts and prices, which repeat, and close to useless for measurements such as weights or times, where almost every value is different.

The Analysis ToolPak

The Analysis ToolPak is an Excel add-in that runs a set of statistical tools from one dialog. On Windows, load it once with File > Options > Add-ins, choose Excel Add-ins in the Manage box, select Go, tick Analysis ToolPak and select OK (Microsoft’s page gives separate steps for a Mac). The tools then appear under Data > Data Analysis. Each tool works on one worksheet at a time. The Descriptive Statistics and Regression reports are values, not formulas: when the data change, run the tool again. Excel for the web can’t run the Regression tool.

Descriptive Statistics asks for the input range, whether the data are grouped in columns or rows, whether the first row holds labels, where to put the output, and which parts to include: Summary statistics, Confidence Level for Mean, and the kth largest and smallest values. Microsoft’s page says only that the tool “generates a report of univariate statistics”; the lines below are those the tool prints (as Cameron’s and BetterSolutions’ guides to it list them), with the values for the 231 order totals and the worksheet formula that reproduces each one:

Three lines need explaining. Standard Error is the standard error of the mean, $s \div \sqrt{n}$: how much the mean would vary from one sample of 231 orders to the next. Kurtosis is Excel’s KURT, which is excess kurtosis: about 0 for normally distributed data, positive when the tails are heavier. Its value of 199.5 says one order sits extraordinarily far out. Confidence Level(95.0%) is not a probability but a margin, the same as =CONFIDENCE.T(0.05,STDEV.S(J2:J232),COUNT(J2:J232)): the mean plus or minus 13.08 gives a 95% confidence interval for the mean, which B04 explains.

Skewness is Excel’s SKEW, the adjusted Fisher–Pearson coefficient that B02’s widgets use. It cubes each value’s standardised distance from the mean (kurtosis uses fourth powers), so it is even more sensitive to one extreme value than the standard deviation: 13.66 with the office order, 2.29 without it.

Frequency tables: FREQUENCY and COUNTIFS

FREQUENCY(data_array, bins_array) returns one more count than there are bins; the last counts values above the highest bin. It ignores blank cells and text. In Excel for Microsoft 365, Excel 2021 and Excel 2024 you type it in the top cell and it spills; in Excel 2019 and earlier you select the whole output range first, type the formula and press Ctrl+Shift+Enter (on Windows).

The same counts come from one COUNTIFS per bin, with the lower and upper edges in two columns: =COUNTIFS($J$2:$J$232,">"&K2,$J$2:$J$232,"<="&L2), filled down. The criteria ">" and "<=" reproduce FREQUENCY’s rule (above the lower edge, up to the upper edge); writing ">=" and "<" instead would move every value that sits on an edge into the next bin. The Histogram chart and the ToolPak’s Histogram tool use the same rule, with an underflow bin that includes its edge.

Correlation

CORREL(array1, array2) returns Pearson’s correlation coefficient, between −1 and +1: near +1 when the points lie close to a rising straight line, near −1 for a falling one, near 0 when there is no straight-line pattern. It skips text and blanks, returns #N/A if the two ranges hold different numbers of points, and gives the same answer whichever range comes first. PEARSON is the same calculation.

B01’s warning applies in full: two variables can be correlated without one causing the other, and a confounding variable can drive both. Here a causal story is plausible, because Ana’s quote sheet charges per guest. Hours and pastries change invoices too; in these events they are almost unrelated to the number of guests, so they show up as scatter around the line rather than in its slope. Measuring their effects takes a multiple regression, which the project reads.

The least-squares line

Excel’s linear trendline, SLOPE, INTERCEPT and the ToolPak all fit the least-squares line $\hat{y} = b_0 + b_1 x$: the line that makes the sum of the squared residuals (actual minus predicted y) as small as possible. Its slope is $b_1 = \sum (x - \bar{x})(y - \bar{y}) \div \sum (x - \bar{x})^2$ and its intercept $b_0 = \bar{y} - b_1 \bar{x}$, so the line passes through the point of means: 82.03 guests and $774.93.

Watch the argument order. SLOPE, INTERCEPT, RSQ and LINEST take known_y’s first and known_x’s second; FORECAST.LINEAR takes the new x first: =FORECAST.LINEAR(x,known_y's,known_x's). FORECAST.LINEAR replaced FORECAST in Excel 2016; FORECAST still works for compatibility. Swapping y and x gives the regression of guests on invoice, a different line.

LINEST returns the whole fit as an array. =LINEST(C2:C76,B2:B76,TRUE,TRUE) gives five rows and two columns: the slope and intercept; their standard errors; R² and the standard error of the estimate; the F statistic and the residual degrees of freedom; and the regression and residual sums of squares. In Excel for Microsoft 365, Excel 2021 and Excel 2024 it spills from one cell; in Excel 2019 and earlier it must be entered as an array formula with Ctrl+Shift+Enter. A trendline label shows only five digits of each coefficient, so calculate predictions from SLOPE and INTERCEPT, not from numbers copied off the chart.

To add the trendline, select the scatter chart, open Chart Elements and tick Trendline, then in the Format Trendline pane choose Linear and tick Display Equation on chart and Display R-squared value on chart.

Reading the ToolPak’s Regression output

Data > Data Analysis > Regression asks for the Input Y Range (the invoices, C1:C76) and the Input X Range (the guests, B1:B76), with Labels ticked because row 1 holds headings. Its Summary Output has three blocks. The Explore section reproduces it; here it is for the 75 one-day events.

• R Square = regression SS ÷ total SS $= 3{,}629{,}738 \div 4{,}153{,}275 \approx 0.874$. The total SS measures how far invoices vary around their mean; 87.4% of that variation lines up with the number of guests, and 12.6% is left in the residuals. With one X it equals $r^2$.
• Standard Error $= \sqrt{\text{residual SS} \div (n - 2)} = \sqrt{7{,}171.73} \approx 84.69$ dollars. A typical invoice was about $85 away from the line: a more useful measure of a prediction’s accuracy than R².
• ANOVA: each MS is SS ÷ df, and $F = 3{,}629{,}738 \div 7{,}171.73 \approx 506.1$. Significance F is the p-value of the test that every slope is 0; with one X it equals the slope’s P-value. 1.47E-34 is Excel’s notation for $1.47 \times 10^{-34}$: a slope this far from 0 would essentially never arise by chance if guests had no linear relation to invoices.
• Coefficients: the intercept 227.88 and the slope 6.6692. t Stat = coefficient ÷ its standard error ($6.6692 \div 0.2964 \approx 22.5$). Lower 95% and Upper 95% are the coefficient ± $t_{0.025,\,73} \times$ standard error, with $t_{0.025,\,73} \approx 1.993$: the slope’s 95% confidence interval runs from $6.08 to $7.26 per guest.

What the line says, and what it doesn’t

• The slope: across these events, one more guest went with $6.67 more on the invoice, on average (95% interval $6.08 to $7.26). That is close to the $6.50 a guest on Ana’s quote sheet, though the line knows nothing about her price list: it only summarises what her invoices did.
• The intercept, $227.88, is the predicted invoice for 0 guests. No event had fewer than 35 guests, so this is an extrapolation: it anchors the line and is not Ana’s setup charge.
• Predictions are safest inside the data’s range, 35 to 207 guests: for 100 guests, =FORECAST.LINEAR(100,C2:C76,B2:B76) gives $894.80, and actual invoices at that size have scattered by roughly the standard error, $85, either side.
• R² is not predictive accuracy. 0.874 doesn’t mean predictions are 87.4% accurate or right 87.4% of the time; it says how much of the past variation the line accounts for. The standard error tells you the size of a typical miss, and only new events show how well the line predicts.
• Correlation is not causation, and the regression describes how Ana has priced in the past, not what clients would pay. Checking the line’s assumptions with residual plots, and handling several X variables, belong to B06 Regression Analysis; the p-values and intervals rest on assumptions B04 explains.

Why the two-day conference is set aside

EV-001, Meridian Partners’ conference on 12 March, ran for two days with 263 guests and was invoiced $4,200. Ana quotes a two-day event day by day, so its invoice isn’t on the same footing as the others, and its 263 guests lie beyond the largest one-day event (207). Fitted to all 76 events, the line changes a great deal: slope $10.15 a guest, intercept −$36.43, R² 0.746 and standard error $232.54, against $6.67, $227.88, 0.874 and $84.69 without it.

One point did that because it is extreme in both directions: far beyond the other events in guests, and far above the line in dollars. Points far from the mean of x have high leverage and can tilt a least-squares line on their own, a diagnostic B06 develops. Setting the conference aside is not hiding data: the analysis says it covers one-day events, and the conference is reported separately. Excluding a point only because it spoils the fit would be.