RSQ, FORECAST.LINEAR, the trendline, the ToolPak block

Two more functions

Module 13.3 gave the line two functions. Two more finish the spreadsheet toolkit. RSQ is the square of r:

=RSQ(C2:C7, B2:B7)                  → 0.9972

$R^2=r^2=0.9986^2=0.9972$: the share of the variation in sales that the line accounts for — 99.7 % here. It has no sign, runs from 0 to 1, and for one predictor is exactly CORREL squared; Lesson 13.5 reads the same number off R. FORECAST.LINEAR plugs a value of x into the line:

=FORECAST.LINEAR(6, C2:C7, B2:B7)   → 34.00

The argument order is different from SLOPE: the x value first, then known_y, then known_x. At the mean ad spend of 6 it returns the mean sales of 34 — the (x̄, ȳ) check from Lesson 13.3. Older sheets have FORECAST with no suffix and the same arguments; the .LINEAR name arrived in Excel 2016 and Sheets has both.

Worked example — the trendline

The scatter from Lesson 13.1, right-clicked: Add trendline → Linear, with Display equation on chart and Display R-squared value on chart ticked (in Sheets, the chart editor's Customize → Series → Trendline, then Label → Use equation). The chart now shows the line and, in its corner, an equation such as $y=3.4524x+13.286$ and $R^2=0.9972$.

It is the same line — but the coefficients are rounded for display, to however many digits the chart chose, and the rounding is not under your control. A forecast built by typing the displayed numbers into a cell drifts from FORECAST.LINEAR by the rounding error. Use the trendline to see the fit; use SLOPE and INTERCEPT to compute with it.

The Analysis ToolPak block

Excel's Data → Data Analysis → Regression (Windows and Mac; not in Sheets) takes the Y range and the X range and writes a three-part block. Where each number from this module lives:

• Regression Statistics — Multiple R is r, 0.9986; R Square is $R^2$, 0.9972; Adjusted R Square and Standard Error are deferred to AFM 113; Observations is 6.
• ANOVA — the F test; AFM 113.
• Coefficients — two rows. Intercept is $b_0$, 13.2857; X Variable 1 is $b_1$, 3.4524 — the slope, named after the column position because the ToolPak does not read your header unless you tick Labels. The columns to its right (Standard Error, t Stat, P-value, the confidence bounds) are the same columns R prints in Lesson 13.5, and the same ones AFM 113 interprets.

One more ToolPak habit: it wants the X range in contiguous columns. For one predictor that is one column, and it never matters until the second predictor arrives.

Type both, then read the forecast

CRISP-DM: RSQ and the block are modelling → assess model; FORECAST.LINEAR is the model in use.