Chapter 12: Correlation and Simple Regression

12.1 How Much Does Waiting Cost?

Rosa, the Metro regional manager, has heard it from customers for months: the lines are too long. She wants money for a second espresso machine at two of her stores, and Marcus Bell has asked her to show what longer waits actually cost Ridgeline. Rosa brings the question to you. "I know waiting annoys people. I need a number. If we cut the wait by a minute, how much happier are customers?"

Earlier chapters compared groups: promo days against other days, trained staff against untrained staff, one region against another. This question is different. Wait time isn't a set of groups. It's a number that can be 0.5 minutes, 4.4 minutes or 12 minutes. Satisfaction is a number too. To answer Rosa, you need a way to describe how one numeric variable changes as another one changes, and a way to tell whether that pattern is real or just noise. That's what correlation and regression do.

You'll work with the Survey Table in the Ridgeline workbook. Each row is one customer's survey response. The two columns that matter are wait_minutes (minutes from joining the line to getting the order) and satisfaction (a rating of the visit from 1, very dissatisfied, to 10, very satisfied).

12.2 Look First: The Scatterplot

Before you compute anything, look at the data. When both variables are numeric, the right chart is a scatterplot.

By convention, the variable you think might explain or predict the other goes on the horizontal axis. Here that's wait_minutes. The variable you want to understand, satisfaction, goes on the vertical axis. You'll see the x variable called the explanatory or predictor variable, and the y variable called the response or outcome.

12.2.1 Build it in Excel

1. Open the Survey sheet. In the Survey Table, wait_minutes is column H and satisfaction is column I. Excel puts the left column of a selection on the x-axis, which is what you want.
2. Point at the top edge of the wait_minutes header until the pointer turns into a small black down arrow, and click. That selects the column's data. Hold Shift and click the satisfaction header the same way.
3. Choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter (the first option, dots only).
4. Click the chart, then the Chart Elements button (the + beside it). Check Axis Titles, and type "Wait (minutes)" for the horizontal axis and "Satisfaction (1–10)" for the vertical one. Give the chart a title such as "Longer waits, lower satisfaction."
5. In Chart Elements, point at Trendline, click the arrow beside it, and choose More Options. In the Format Trendline pane, keep Linear, then check Display Equation on chart and Display R-squared value on chart.
6. Drag the equation label to a clear spot. Right-click the vertical axis, choose Format Axis, and set Minimum to 0 and Maximum to 11 so the line has room.

12.2.2 Read it

Describe a scatterplot in three words: direction, form and strength.

• Direction. The dots drift down from left to right. Customers who waited longer tended to rate their visit lower. That's a negative (or inverse) relationship. If the dots rose from left to right, it would be positive.
• Form. The drift looks like a straight line, not a curve. No bend at short waits, no leveling off at long ones.
• Strength. The dots don't sit on the line. At a 4-minute wait you can find customers who gave a 5 and customers who gave a 10. The pattern is clear, but there's plenty of scatter around it. That's a moderate relationship.

The dots line up in horizontal bands because satisfaction is recorded in whole numbers. That's normal for a rating. Also check for outliers, dots far from the rest. None stand out: waits run from 0.5 to 12 minutes, and ratings from 4 to 10. The rest of this chapter explains the line and the two numbers Excel put on your chart.

12.3 Correlation: One Number for a Linear Relationship

A scatterplot shows a relationship. A correlation coefficient summarizes it in one number.

How to read r:

• r = +1: every dot sits exactly on a rising straight line.
• r = −1: every dot sits exactly on a falling straight line.
• r = 0: no straight-line pattern at all.
• In between, the closer to ±1, the more tightly the dots hug a line. As a rough guide for business data, a size around 0.1 is weak, around 0.3 is modest, around 0.5 is moderate, and 0.7 or more is strong.

In Excel, CORREL takes the two columns in either order:

=CORREL(Survey[wait_minutes],Survey[satisfaction])

The answer is r = −0.626. That's a moderate-to-strong negative relationship, and it's what your eye saw in the scatterplot.

12.3.1 How correlation works

Why does this number measure a linear relationship? The idea uses z-scores, which you met in Chapter 4. Describing Data. A z-score says how many standard deviations a value sits above or below its mean.

Convert each customer's wait and satisfaction to z-scores, and multiply the two together. Think about what the sign of that product means:

• A customer with a long wait (positive z) and a low rating (negative z) gives a negative product.
• A customer with a short wait (negative z) and a high rating (positive z) also gives a negative product.
• A customer with a long wait and a high rating, or a short wait and a low rating, gives a positive product.

If most customers fall in the first two groups, the products are mostly negative, and so is their sum. The correlation is the sum of these products divided by n − 1, which makes it a kind of average:

• In words: r is the average product of the two variables' z-scores.
• In symbols: r = Σ(z_x × z_y) / (n − 1)
• In Excel: =CORREL(x_range, y_range) does all of it at once.

Because z-scores have no units, r has no units either. You'd get the same −0.626 if wait were measured in seconds, or satisfaction on a 100-point scale.

12.3.2 A worked example you can build

Try it on the first six survey responses, so every step fits on screen. Copy their wait_minutes and satisfaction values into A2:B7 of a new sheet, with headers in row 1.

Response	Wait (x)	Satisfaction (y)	zx	zy	zx × zy
R0001	4.6	8	0.497	0.000	0.000
R0002	3.2	9	−0.431	0.791	−0.340
R0003	1.2	9	−1.755	0.791	−1.388
R0004	4.3	6	0.298	−1.581	−0.471
R0005	5.6	7	1.159	−0.791	−0.916
R0006	4.2	9	0.232	0.791	0.183
Sum					−2.932

These six customers waited 3.85 minutes on average (SD 1.51) and rated their visits 8.00 on average (SD 1.26). Build the table with these formulas:

• z_x in C2: =STANDARDIZE(A2,AVERAGE($A$2:$A$7),STDEV.S($A$2:$A$7)), filled down
• z_y in D2: =STANDARDIZE(B2,AVERAGE($B$2:$B$7),STDEV.S($B$2:$B$7)), filled down
• the product in E2: =C2*D2, filled down
• r: =SUM(E2:E7)/(COUNT(A2:A7)-1), which gives −2.932 / 5 = −0.586
• the check: =CORREL(A2:A7,B2:B7), which also gives −0.586

Five of the six products are negative or zero. Only R0006, who waited a bit longer than average and still gave a 9, pushes the other way. Six customers give −0.59. All 600 give −0.63. The small sample is close, but not the same, which is exactly the sampling variability you'd expect from Chapter 6. Sampling and the Central Limit Theorem.

12.3.3 What correlation can't do

Correlation is useful and easy to misuse. Keep three limits in mind.

It only measures straight-line relationships. Suppose satisfaction rose with wait up to a point and then fell. That curved pattern could be strong and still produce an r near 0, because the rising half and the falling half cancel out. A correlation near 0 means "no linear relationship," not "no relationship." That's why you always look at the scatterplot first.

It's sensitive to outliers. One extreme point far from the rest can pull r toward ±1 or toward 0. With 600 customers and no extreme points, that isn't a problem here. With 12 stores, it easily could be.

It says nothing about cause. You'll come back to this at the end of the chapter.

12.3.4 A correlation matrix

When you have several numeric columns, a correlation matrix shows every pair at once. Here it is for the survey's numeric columns:

	age	visits_per_week	wait_minutes	satisfaction	spend
age	1	0.008	−0.040	0.030	0.155
visits_per_week	0.008	1	0.021	−0.014	0.105
wait_minutes	−0.040	0.021	1	−0.626	0.026
satisfaction	0.030	−0.014	−0.626	1	−0.042
spend	0.155	0.105	0.026	−0.042	1

Each number appears twice, because the correlation of age with spend is the same as the correlation of spend with age. The 1s on the diagonal are each variable with itself. Most correlations are close to 0. The one that stands out is wait with satisfaction. Age with spend (0.155) is statistically significant (p < 0.001): older customers spend a little more. It's weak, though. Squaring it gives about 0.024, so age accounts for only about 2% of the variation in what customers spend. A manager shouldn't plan anything around it.

You can build the matrix with CORREL one cell at a time, or with the Analysis ToolPak's Data > Data Analysis > Correlation tool. The ToolPak needs the columns side by side with no text columns in between, and in the Survey Table loyalty_member and order_type sit among the numeric columns. Copy the five numeric columns next to each other onto a new sheet, and give the ToolPak that range with Labels in First Row checked.

12.4 The Regression Line

Correlation tells Rosa that longer waits go with lower ratings. It doesn't answer her question: how much lower per minute? For that you need the equation of a line.

Excel's trendline already showed the line: ŷ = 9.33 − 0.365 × wait. Before interpreting it, you need to know where it came from. Many lines could be drawn through a cloud of dots. Why this one?

12.4.1 Residuals

For any line you draw, each customer has an actual rating and a predicted rating on the line. The gap between them is that customer's residual.

Take customer R0004, who waited 4.3 minutes and gave a 6. The line predicts 9.33 − 0.365 × 4.3 = 7.76. The residual is 6 − 7.76 = −1.76. This customer was about 1.8 points less satisfied than the line expects for a 4.3-minute wait. Customer R0006 waited 4.2 minutes and gave a 9, a residual of +1.20.

A good line has small residuals overall. But you can't just add them up, because the positive and negative ones cancel. Any line through the middle of the cloud has residuals that sum to roughly zero.

12.4.2 Least squares

The standard fix is to square each residual (making them all positive, and making big misses count more than small ones) and add the squares. The best line is the one with the smallest total.

You can watch least squares work with helper columns. This is the best way to see that the line isn't magic.

1. On a new sheet, type b0 in A1 and 9 in B1, and b1 in A2 and -0.3 in B2. Select B1, and in the Name Box (left of the formula bar) type b0 and press Enter. Name B2 b1 the same way.
2. Go to the Survey Table. In the first empty column to the right of would_recommend, type the header predicted. In its first cell, enter =b0+b1*[@wait_minutes]. The Table fills the formula down all 600 rows.
3. Add another column, residual, with =[@satisfaction]-[@predicted].
4. Back on your new sheet, type SSE in A4 and =SUMSQ(Survey[residual]) in B4. SSE stands for the sum of squared errors, the total you want to make small.
5. Now change b0 and b1 and watch SSE.

Here's what you'll find for a few lines:

Line (b0, b1)	Sum of squared residuals
Flat line at the mean: 7.70, 0	1,123.6
10, −0.5	747.0
9, −0.3	698.5
9.6, −0.4	696.2
9.33, −0.365	683.7

No other pair of numbers gets below 683.7. That's what "least squares" means: Excel's trendline is the line with the smallest possible sum of squared residuals.

When you're done, delete the predicted and residual columns from the Survey Table (right-click a cell in the column, Delete > Table Columns). R for Excel reads the whole Table, and later chapters assume it has its original 11 columns.

You don't need trial and error in practice, because least squares has a formula. The slope is the correlation, rescaled from z-score units back into the units of y and x:

• In words: slope = correlation × (SD of y ÷ SD of x), and the line passes through the point (mean of x, mean of y).
• In symbols: b₁ = r × (s_y / s_x), and b₀ = ȳ − b₁x̄
• With Ridgeline's numbers: b₁ = −0.626 × (1.370 / 2.347) = −0.365, and b₀ = 7.702 − (−0.365 × 4.448) = 9.33

12.4.3 Interpreting the slope and intercept

The fitted line is predicted satisfaction = 9.33 − 0.365 × wait minutes.

The slope, −0.365, is the number Rosa asked for. Each additional minute of waiting is associated with a rating about 0.37 points lower, on average. Five extra minutes go with a rating about 1.8 points lower. Cutting a 6-minute wait to 4 minutes goes with a rating about 0.73 points higher. Notice the words "associated with" and "on average." The slope describes how average satisfaction differs across customers with different waits. It doesn't promise that any one customer will react that way.

The intercept, 9.33, is the predicted rating for a customer who waits zero minutes. That's at least close to the data, since the shortest wait in the survey is 0.5 minutes, so here it's a sensible ceiling: even an instant order averages around 9.3, not 10. Be careful with intercepts in general, though. Often x = 0 is far outside the data or makes no sense, such as a store with zero seats. Then the intercept is just where the line happens to cross the axis, and you shouldn't interpret it.

12.4.4 The Excel functions

Each piece of the line has its own Excel function. They all take the y range first, then the x range:

What	Formula	Result
Slope	=SLOPE(Survey[satisfaction],Survey[wait_minutes])	−0.365
Intercept	=INTERCEPT(Survey[satisfaction],Survey[wait_minutes])	9.33
R squared	=RSQ(Survey[satisfaction],Survey[wait_minutes])	0.392
Standard error of the regression	=STEYX(Survey[satisfaction],Survey[wait_minutes])	1.07
Predicted y at x = 4	=FORECAST.LINEAR(4,Survey[satisfaction],Survey[wait_minutes])	7.87

STEYX gives the standard error of the regression, 1.07. It's the typical size of a residual: a rough answer to "when I use the line to predict a customer's rating, how far off am I usually?" Here, about one point either way.

12.5 R Squared: How Much the Line Explains

Satisfaction varies a lot from customer to customer. How much of that variation does wait time account for?

Go back to the least squares table. The flat line at the mean, which ignores wait time completely, had a sum of squared residuals of 1,123.6. That's the total variation in satisfaction. The best line using wait time brought it down to 683.7. So wait time removed 1,123.6 − 683.7 = 439.9 of the 1,123.6, or about 39%.

• In words: R² = 1 − (sum of squared residuals ÷ total sum of squares)
• With Ridgeline's numbers: R² = 1 − 683.7 / 1,123.6 = 0.392, and also (−0.626)² = 0.392
• In Excel: =RSQ(Survey[satisfaction],Survey[wait_minutes])

For Rosa: differences in wait time account for about 39% of the differences in how customers rate their visit. The other 61% comes from everything else: the drink, the barista, the customer's mood, and things no survey records. For one variable predicting a human rating, 39% is a lot. It tells Ridgeline that wait time is one of the biggest single drivers of satisfaction in this data. It also tells you not to expect the line to predict any one customer's rating closely.

There's no universal "good" R². A model of human opinions is doing well at 0.3; an engineering model might need 0.99. Judge R² against the decision you need to make.

12.6 Is the Slope Real? Testing It

The survey is a sample of 600 customers. A different 600 would give a slightly different slope. Could the true slope for all Ridgeline customers be zero, and −0.365 just a sampling accident? This is a hypothesis test like the ones in Chapter 8. Hypothesis Testing:

• Null hypothesis: the population slope is 0. Wait time has no linear relationship with satisfaction.
• Alternative hypothesis: the population slope isn't 0.

The slope has a standard error, which measures how much it would vary from sample to sample. Here it's 0.0186. The test statistic is the familiar "estimate divided by its standard error":

• t = −0.3651 / 0.0186 = −19.6, with n − 2 = 598 degrees of freedom.

A t of −19.6 means the slope sits nearly 20 standard errors below zero. The p-value is far below 0.001. The relationship is not a sampling accident.

The confidence interval is more useful to a manager. It's built the same way as the intervals in Chapter 7. Confidence Intervals: estimate ± t* × standard error, with t* = T.INV.2T(0.05,598) ≈ 1.964:

• −0.365 ± 1.964 × 0.0186 = [−0.40, −0.33]

So each extra minute of waiting costs somewhere between 0.33 and 0.40 rating points, with 95% confidence. That's the range Rosa should quote.

12.6.1 What the regression assumes

The test and intervals rest on four assumptions. Each one has a check, and most of the checks use residuals.

1. Linearity. The average of y changes in a straight line with x. Check: the residuals show no curve when plotted against x.
2. Independence. Each observation's residual is unrelated to the others. Check: think about how the data were collected. Survey responses from 600 different customers are reasonably independent. Daily sales from the same store on consecutive days may not be.
3. Constant spread. The residuals are about as spread out at every value of x. Check: the residual plot doesn't fan out or narrow.
4. Roughly normal residuals. Check: a histogram of residuals looks roughly bell-shaped. With large samples this matters least, thanks to the central limit theorem.

A residual plot puts x (or the predicted value) on the horizontal axis and the residuals on the vertical axis. If the model fits, it looks like a shapeless horizontal band centered on zero.

For the wait model, it does. The residuals range from −3.1 to +3.4, with a standard deviation of 1.07. Their average stays close to zero across short, medium and long waits, and their spread stays about the same (an SD near 1.0 to 1.1 in every range of waits). You'll see faint diagonal stripes, which come from satisfaction being a whole number. That's expected with ratings. A histogram of the residuals is close to symmetric and bell-shaped. The assumptions look reasonable.

12.7 LINEST: The Whole Regression in One Formula

SLOPE, INTERCEPT and the rest each give one number. LINEST gives the whole set, including the standard errors you need for the test. On a new sheet, click a cell with empty space below and to the right of it, and enter:

=LINEST(Survey[satisfaction],Survey[wait_minutes],TRUE,TRUE)

The third argument, TRUE, tells Excel to fit an intercept. The fourth, TRUE, asks for the extra statistics. The result spills into a grid of 5 rows and 2 columns. LINEST doesn't label it, so you need to know what each cell is:

	Column 1	Column 2
Row 1	slope: −0.3651	intercept: 9.3259
Row 2	standard error of the slope: 0.0186	standard error of the intercept: 0.0936
Row 3	R²: 0.3915	standard error of the regression: 1.0692
Row 4	F: 384.78	residual degrees of freedom: 598
Row 5	regression sum of squares: 439.9	residual sum of squares: 683.7

Type labels beside the grid so you remember. To get the slope's t statistic, divide the slope by its standard error: if the grid starts in E2, that's =E2/E3, which gives −19.6.

Notice that row 5 shows the same split you found with helper columns: 439.9 explained by the line plus 683.7 left over equals the total of 1,123.6.

12.8 Predictions, and How Uncertain They Are

Rosa wants to use the line to predict. There are two quite different questions she could be asking, and they have different answers.

1. What's the average satisfaction of all customers who wait 4 minutes? This is about the mean of many customers. The line estimates it well, because averages are stable.
2. What will this next customer, who waits 4 minutes, rate us? This is about one person. Even if you knew the true line exactly, individuals scatter around it by about a point either way.

Both questions have the same point estimate: 9.33 − 0.365 × 4 = 7.87. Their intervals differ a lot. The first gets a confidence interval for the mean response. The second gets a prediction interval.

Wait	Predicted rating	95% CI for the mean rating	95% prediction interval for one customer
2 minutes	8.60	[8.47, 8.72]	[6.49, 10.70]
4 minutes	7.87	[7.78, 7.95]	[5.76, 9.97]
8 minutes	6.40	[6.25, 6.56]	[4.30, 8.51]

Read the 4-minute row in plain language: "Customers who wait 4 minutes average a rating between about 7.8 and 8.0. Any single one of them will probably rate us somewhere between about 5.8 and 10." The mean is pinned down to within a tenth of a point. The individual is uncertain by about two points either way.

Also notice that the confidence interval is narrowest at 4 minutes, near the average wait of 4.4, and wider at 2 and 8. The line is best known near the middle of the data and less certain toward the ends, like a seesaw that wobbles more at its tips.

You can build both intervals in Excel. Put the wait you want to predict at in a cell named x0. The pieces are the standard error of the regression (STEYX), the sample size, and how far x0 is from the mean wait:

• Half-width of the CI for the mean: =T.INV.2T(0.05,598)*STEYX(Survey[satisfaction],Survey[wait_minutes])*SQRT(1/600+(x0-AVERAGE(Survey[wait_minutes]))^2/DEVSQ(Survey[wait_minutes]))
• Half-width of the prediction interval: the same formula with 1+ added inside the square root: SQRT(1+1/600+…)

That 1+ is the whole difference between the two: it adds the scatter of one customer around the line. At x0 = 4 the half-widths come out to about 0.09 and 2.10.

12.8.1 Extrapolation

Rosa asks one more question: "One of our Metro stores had a 15-minute line last Saturday. What does the model say about those customers?"

The formula will happily answer: 9.33 − 0.365 × 15 = 3.85, with a prediction interval of [1.7, 6.0]. Don't trust it. The survey's waits run from 0.5 to 12 minutes. Nobody in the data waited 15 minutes, so the data can't tell you whether the line keeps falling at the same rate, falls faster as tempers fray, or levels off because the customers who'd be most upset already left the line.

Push the line far enough and it gives impossible answers: at a 26-minute wait it predicts a rating below zero, on a scale that starts at 1. The honest answer to Rosa: "Our data only cover waits up to 12 minutes. At 12 minutes, the model already predicts about a 5. Beyond that, we don't know, but there's no reason to think it gets better."

12.9 Correlation Is Not Causation

The regression says customers who wait longer rate their visits lower. Does waiting cause lower ratings? It's plausible, and the size of the effect is believable. But this survey is an observational study, not a randomized experiment, so the data alone can't prove it. You met the difference in Chapter 9. Comparing Two Groups, along with the reason it matters: a confounding variable that affects both x and y can create or exaggerate a relationship.

Here's one candidate. Metro stores have the longest waits (5.8 minutes on average) and the lowest satisfaction (7.0). Mountain stores have the shortest waits (3.1 minutes) and the highest satisfaction (8.4). Maybe some of the "wait effect" is really a "Metro effect": crowded city stores, rushed commuters, noisier rooms. If so, cutting waits in Metro might help less than the slope suggests. You can't sort that out with one explanatory variable. Chapter 13. Multiple Regression lets you hold region constant and see how much of the wait effect remains. (Spoiler: most of it.)

Two more relationships in Ridgeline's data show why you have to think beyond the numbers.

12.9.1 Significant but nearly useless: temperature and transactions

In the DailySales Table, a row is one store on one day. Is hotter weather bad for business? =CORREL(DailySales[high_temp_f],DailySales[transactions]) gives r = −0.06, and the test of the slope gives p < 0.001. The slope is −0.30 transactions per degree Fahrenheit.

So the relationship is "statistically significant," and almost worthless for prediction. R² is 0.0036: temperature accounts for less than half of one percent of the day-to-day variation in transactions. The p-value is small only because there are 4,380 rows. This is statistical versus practical significance from Chapter 8. Hypothesis Testing, in regression form.

There's a deeper problem. Temperature is tangled up with season and place. Ridgeline's busiest months are the cold ones (about 388 transactions a day in January versus 311 in July), and its Mountain stores are both colder and quieter than the others. A one-variable regression can't separate the weather from the calendar or the store. In Chapter 13. Multiple Regression you'll see the temperature slope change a lot once those are held constant.

12.9.2 Modest and plausible: tenure and staff ratings

In the Staff Table, does experience go with better customer ratings? Regressing customer_rating (1 to 5) on tenure_months gives r = 0.33, R² = 0.11, and p < 0.001:

predicted rating = 4.18 + 0.0053 × tenure months

Each extra year of tenure goes with a rating about 0.06 points higher. But trained staff have more tenure on average (26.5 months versus 14.1), as you saw in Chapter 9. Comparing Two Groups. Is this a tenure effect or a training effect? One predictor can't say. The next chapter answers that too.

12.10 Describe the Data to AI

Now do the analysis the AI-first way and use Excel to check it. Open R for Excel in JOLT. In 1. Data, choose Add a table… and pick Survey. The app will describe its columns: names, types and ranges. Your job is to tell the AI what they mean and what you want.

Type this data description into the 2. Ask your AI box:

Each row of Survey is one customer's survey response. wait_minutes is minutes from joining the line to getting the order. satisfaction is the customer's rating of the visit from 1 (very dissatisfied) to 10 (very satisfied).
Question: How much lower is satisfaction for each extra minute of waiting, and is that relationship real? We also want predicted satisfaction for waits of 2, 4 and 8 minutes.
I'm in an intro business statistics course and have learned scatterplots, correlation, simple linear regression, R squared, testing a slope, and the difference between confidence and prediction intervals.
Please fit a simple linear regression of satisfaction on wait_minutes, test the correlation, and give 95% prediction intervals at waits of 2, 4 and 8 minutes. Send the key results to Excel with excel_out(), and explain how to read them.

Click Copy prompt, paste it into your AI assistant, and send it.

12.11 Read the Script

A script for this usually looks like the one below. Yours may use different names or a different order. What matters is that it does the same things.

# Fit the regression line: satisfaction explained by wait time
model <- lm(satisfaction ~ wait_minutes, data = Survey)
excel_out(model, "Regression: satisfaction on wait_minutes")

# Test the correlation between the two columns
r_test <- cor.test(Survey$wait_minutes, Survey$satisfaction)
excel_out(r_test, "Correlation test")

# Predict for three wait times, with 95% prediction intervals
new_waits <- data.frame(wait_minutes = c(2, 4, 8))
pred <- predict(model, newdata = new_waits, interval = "prediction")
excel_out(cbind(new_waits, pred), "Predictions with 95% prediction intervals")

# Residual plot: residuals against wait time
plot(Survey$wait_minutes, resid(model), xlab = "Wait (minutes)", ylab = "Residual")
abline(h = 0)

Read it in four parts:

• The model. lm stands for linear model. The part in parentheses, satisfaction ~ wait_minutes, reads "satisfaction explained by wait_minutes." The variable on the left of the ~ is y; the one on the right is x. If the AI wrote them the other way round, it fitted the wrong line, the same mistake as swapping ranges in SLOPE. data = Survey says which data frame to use.
• The correlation test. cor.test computes r and tests whether it differs from zero. Here the order of the two columns doesn't matter.
• The predictions. The script makes a tiny table of three waits, 2, 4 and 8, and asks predict for the predicted rating at each one. The words interval = "prediction" are the important part. They ask for prediction intervals for individual customers. If the AI wrote interval = "confidence", you'd get the much narrower intervals for the mean rating. Neither is wrong, but you need to know which one you have. Check this line every time.
• The plot. plot draws residuals against wait, and abline(h = 0) adds a horizontal line at zero to judge them against.

Each excel_out() call becomes an output card that you can send to the workbook.

12.12 Run It in R for Excel

Paste the AI's reply into 3. Run R code. The app keeps just the code. Click Run. If R reports an error, click Copy a prompt to fix it and paste that prompt back to the AI.

When it runs, the Results area shows an output card for each excel_out() call and the residual plot. On the regression card, click New sheet. Do the same for the other two cards. Click Insert into Excel under the plot to bring it into the workbook.

R for Excel lays out a regression the same way Excel's Analysis ToolPak does. The new sheet has:

• SUMMARY OUTPUT and Regression Statistics: Multiple R (0.626, the size of r), R Square (0.392), Adjusted R Square, Standard Error (1.069, the same as STEYX) and Observations (600).
• ANOVA: the total sum of squares (1,123.6) split into Regression (439.9, df 1) and Residual (683.7, df 598), each SS divided by its df (MS), F = 384.8, and Significance F, the p-value of the overall test.
• Coefficients: rows for Intercept and wait_minutes, with the coefficient, Standard Error, t Stat, P-value, and Lower 95% / Upper 95%. These are the numbers from your LINEST grid and the slope test.

Adjusted R Square (0.390) matters in Chapter 13. Multiple Regression. With one predictor it's almost identical to R Square.

12.13 Interpret and Verify

12.13.1 Two tools, one answer

Run the same regression with the Analysis ToolPak and compare. (If Data Analysis isn't on the Data tab, turn it on with File > Options > Add-ins > Manage Excel Add-ins > Go, and check Analysis ToolPak. Excel for the web doesn't have the full ToolPak, so do this step in the desktop app.)

1. Choose Data > Data Analysis > Regression > OK.
2. Input Y Range: select the satisfaction column including its header, $I$1:$I$601 on the Survey sheet.
3. Input X Range: select the wait_minutes column including its header, $H$1:$H$601.
4. Check Labels, because both ranges include headers. Leave Confidence Level at 95%.
5. Choose New Worksheet Ply and name it ToolPak regression.
6. Under Residuals, check Residuals and Residual Plots. Click OK.

Put the two sheets side by side (View > New Window, then View > Arrange All > Vertical) and compare them cell for cell. R Square, Standard Error, the ANOVA table, the coefficients, their standard errors, t Stats, P-values and 95% limits should all match. Then check the key numbers a third way against your LINEST grid and CORREL. If any number disagrees, look first for swapped x and y, or a range that missed some rows.

Check the predictions sheet too. At 4 minutes it should show a fit of 7.87 and a prediction interval from about 5.76 to 9.97, which matches the FORECAST.LINEAR result and the half-width of about 2.10 you built with formulas.

12.13.2 Write the conclusion

Statistics output isn't an answer until it's a sentence a manager can use. Here's a version for Rosa and Marcus:

Longer waits go with clearly lower satisfaction. In our survey of 600 customers, each additional minute of waiting was associated with a satisfaction rating about 0.37 points lower on our 10-point scale (95% confidence interval: 0.33 to 0.40 points). Differences in wait time account for about 39% of the variation in ratings, which makes wait one of the strongest single drivers we've measured. Customers who wait 2 minutes average about 8.6; those who wait 8 minutes average about 6.4. A Metro store that cut its average wait from about 6 minutes to 4 could expect ratings around 0.7 points higher, if the relationship is causal. This is survey data, not an experiment, and Metro stores differ from others in more than wait time, so the next step is to check whether the effect holds once region is taken into account.

It leads with the finding, gives the slope in the reader's units with its uncertainty, puts R² in plain words, predicts only inside the data's range, and states the main limitation. You'll practice this structure in Chapter 14. Communicating Results: Capstone.

12.14 Summary

Start any analysis of two numeric variables with a scatterplot, and describe its direction, form and strength. The correlation coefficient r, the average product of z-scores, sums up a linear relationship in one number from −1 to +1. It misses curves, can be swayed by outliers, and says nothing about cause. For Ridgeline, wait time and satisfaction have r = −0.626.

Least squares regression fits the line with the smallest sum of squared residuals. Its slope answers Rosa: each extra minute of waiting goes with a rating about 0.37 points lower (95% CI 0.33 to 0.40), and R² = 0.39 says wait accounts for about 39% of the variation in ratings. A confidence interval describes the average response at a given wait; a prediction interval, much wider, describes one customer. Don't predict outside the data's range, check residual plots for curves and uneven spread, and verify lm in R for Excel against LINEST and the ToolPak. Finally, remember that a significant slope from observational data shows association, not cause, as temperature's tiny but "significant" link to transactions shows.