STATS_LINEAR – Multiple linear regression with hierarchical steps
What it does
Multiple linear regression (OLS) with hierarchical steps separated by | and interactions a*b.
Predictors enter in the written order; a constant or linearly dependent predictor does not enter (as in SPSS; with lambda = 0) and is named in an "Excluded: …" line.
lambda > 0 gives ridge regression; an empty lambda takes the value from Settings (0 by default).
centerInteractions TRUE builds a*b from mean-centered values, so the B of a is its effect at the mean of b; empty — from Settings (FALSE by default).
Syntax
=STATS_LINEAR(columns, yName, steps, lambda, centerInteractions)Parameters
columns — required
Cell range with data, e.g., A1:D100 or A:D.
Usually the first row should contain column names.
You can also add labels as the first row and column names as the second row.
Labels should use formats like "male:1,female:2" or "low:1:3,mid:3:5,high:5:7".
Examples:
A1:D100— use 4 columnsA1:C200— use 3 columnsA:F— use 6 columnsA1:D100 where row 1 contains labels and row 2 contains column names— use labels from the sheet
If every non-empty cell of the first row holds at least two label rules ("low:1,high:2"), it is the labels row, and the next row holds the column names; an empty cell there is a column without labels.
One rule in a cell ("yes:1") is read as a name, so a header such as "T:1", "Ratio 1:2" or "Note:" stays the names row. Give such a column a second rule, or pass one label in the labels parameter, where one rule is valid.
If the first row contains ordinary text, it is treated as the column names row as before.
A number, a date or a checkbox in the names row is a name: 2025, 2025-01-01, TRUE.
Column names must be unique: two identical headers are an error "Duplicate column name: x".
Every column with values needs a name: an empty header cell over values is an error "The name of column 2 is empty: a column with values needs a name", even when colFilter does not select that column. A column with neither a name nor values (the empty right edge of a range such as A:Z) is dropped without an error.
Column names that start with "#" are reserved/unsupported in filters, because "#" is used for index-based colFilter rules (for example #1, #2-#5).
What counts as a number (the same in every function):
- A column is numeric when it holds more numbers than text; with as many texts as numbers, or more, it is a text column.
- Every cell is typed by itself: a number written as text ('10', '007') is the number, a checkbox is 1 (checked) or 0 (unchecked). The texts 'TRUE'/'FALSE' stay text.
- A cell that does not match the type of its own column (text in a numeric column, a number in a text column) is missing for calculations and is counted in missing.
- An empty cell is a missing answer; the sign "." means "not applicable" — it is not a missing answer, not a category and not counted in missing.
- A text column with value labels (a labels row above the names) is analysed by the codes of its labels. A text column without labels is not analysed as numbers: lists of columns skip it with a note, tests on a pair of columns and models show an error.
An empty tail of the range (rows below the data) is dropped — only blank cells make it: a row of "." at the end stays and counts as not applicable, as in the middle. A column with neither a name nor values is dropped; a named empty column stays.
yName — required
Dependent variable (response). Column name as in the header.
A Y whose values are all the same (in the rows the model uses) is an error: "Dependent variable y has no variance". For STATS_LOGISTIC, Y must hold exactly the two values 0 and 1.
An empty yName is an error: "Linear Regression needs the predicted column (yName)" (the same in STATS_LOGISTIC and STATS_BOOTSTRAP).
Examples:
"y""mood"
steps — required
Predictors: comma-separated column names; blocks separated by "|" are hierarchical steps, each step adds its block to the previous ones.
A step is read as a column filter over the columns without Y: names, a regex ("/^x/"), index rules ("#2-#4"); an interaction is written as a*b (the product of two columns). "|" inside /.../ does not split steps: "/^(x1|x2)$/" is one step. A step that selects no column is an error.
"*" in a step is every column but Y, in its place among the items: "age,sex|*" enters the controls first, then every other predictor.
Steps only add predictors: an exclusion ("-x1", "/^x/,-x3") is an error Step "-x1": exclusions are not supported in regression steps - list the predictors to add. A minus inside /.../ is a part of the pattern.
Predictors enter in the written order. A constant predictor, or one that repeats the others (a copy, their sum), does not enter, as in SPSS (tolerance < 0.0001): its coefficient cells stay empty and the line "Excluded: sum (constant or linearly dependent on other predictors, tolerance < 0.0001)" names it. The written order decides which one is left out: "x1,x2,sum" leaves out sum, "sum,x1,x2" leaves out x2.
When no predictor of a step enters, the formula shows "No predictors entered. Excluded: …".
Examples:
"x1,x2"— multiple regression"x1,x2|x3|x4*x3"— hierarchical regression with an interaction term (x4*x3)
lambda — optional, default 0
Ridge penalty λ (0 for OLS).
Empty — the lambda from Settings (0 by default); the setting applies to both STATS_LINEAR and STATS_LOGISTIC.
With lambda > 0 the model is ridge in both functions: the slopes are shrunk (not the intercept) and predictors are not checked for dependence — nothing is excluded. The linear report then gives no standard errors, t, p or VIF, the logistic one no standard errors, Wald, Sig. or odds-ratio intervals; the model F (linear) and the omnibus chi-square and AIC/BIC (logistic) are still printed but are not valid tests of a penalized model, and the report says so: the line "Calculated with lambda …" (linear) and the omnibus header (logistic).
A number of at least 0; "Infinity" or 1e309 is an error: "lambda must be a number of at least 0 (e.g. 0), got Infinity".
Should be a number of at least 0
Examples:
0— no penalty0.5— ridge
centerInteractions — optional, default FALSE
TRUE builds an interaction a*b from mean-centered values: (a − mean of a) · (b − mean of b), the means over the rows of the model. Only the product is centered: a, b and the other predictors stay as they are.
B, SE and p of a*b, R Square and F do not change. The B of a becomes its effect at the mean of b (without centering — its effect where b is 0, which may be outside the scale), and the VIF of the product drops.
A step with an interaction says so under its coefficients: "Interaction terms use mean-centered values: …". A step without an interaction does not change.
Empty — the value from Settings (FALSE by default); the setting applies to STATS_LINEAR, STATS_LOGISTIC and STATS_BOOTSTRAP. TRUE, 1 or their text turn it on; FALSE, 0 or their text turn it off; anything else is an error: centerInteractions must be TRUE or FALSE (or 1, 0), got "yes".
Should be TRUE or FALSE
Examples:
TRUE— a*b from centered a and bFALSE— a*b from the raw values
Examples
- — the sample data (50 rows) all examples use
- —
=STATS_LINEAR(Data!A1:T51, "y", "x1,x2", 0) - — two steps: x1,x2, then x added
=STATS_LINEAR(Data!A1:T51, "y", "x1,x2|x", 0) - — interaction in the second step
=STATS_LINEAR(Data!A1:T51, "y", "x1,x2|x1*x2") - — the interaction from mean-centered x1 and x2
=STATS_LINEAR(Data!A1:T51, "y", "x1,x2|x1*x2", 0, TRUE)
What the result contains
A report that spills below the formula cell: the title, Model Summary (a column per step: Predictors, R, R Square, Adjusted R Square, Std. Error of the Estimate, Sample size), then for each step the ANOVA table and the Coefficients table (B, StdError, Beta, t, p, VIF). With two or more steps the Regression row of each ANOVA table has five more columns to the right: R Square Change, F Change, df1, df2, Sig. F Change (as SPSS Change Statistics) — keep the cells to the right of the block free.
An excluded predictor keeps empty cells in Coefficients and is named in the line "Excluded: …" under it.
Without residual degrees of freedom (as many coefficients as rows) StdError, t and p are empty. With ridge (lambda > 0) StdError, t, p and VIF are empty.
Very small p-values are shown as they are (for example 2.08E-24).
With centerInteractions TRUE a step with an interaction a*b has the line "Interaction terms use mean-centered values: …" under its Coefficients table, after the "Excluded: …" text when there is one.
How to read the result
Start with the ANOVA table: Sig. below 0.05 means the predictors together explain Y better than its mean alone.
Then R Square in Model Summary — how much of Y the model explains (0.02 small, 0.13 medium, 0.26 large).
Then Coefficients: p below 0.05 marks the predictors that matter; B gives the change of Y per unit, Beta compares predictors in different units.
In a report: "The model explained 25% of the variance, R² = .25, F(2, 47) = 7.83, p = .001. Study hours predicted the score, B = 0.50, β = .42, t(47) = 3.10, p = .003."
With several predictors, check VIF: above 5 the predictors overlap, and their separate B and p are unreliable; compare models by Adjusted R Square.
With several steps, read R Square Change and Sig. F Change in the ANOVA table of each step: ΔR² is what the predictors added at that step explain beyond the earlier ones, and Sig. F Change below 0.05 says the step helps. In a report: ΔR² = .06, F(1, 7) = 15.39, p = .006.
What each number means
Model summary
Predictors— PredictorsThe headings of this table: read each column as one model.
R— Multiple correlation RFrom 0 to 1. It is rarely reported: R Square, its square, is easier to read.
R Square— R²R² = 0.25 means the predictors account for a quarter of the differences in Y. A common guide (Cohen): 0.02 small, 0.13 medium, 0.26 large.
In a hierarchical model the rise of R² from one step to the next is what the added predictors explain on top of the earlier ones; the ANOVA table of each step gives it as R Square Change with its F Change test.
In a report: R² = .25, F(2, 47) = 7.83, p = .001.
Adjusted R Square— Adjusted R²The fair version of R² for comparing models with different numbers of predictors. It can fall below 0 when the model explains nothing at all.
Std. Error of the Estimate— Standard error of the estimateHow precise the predictions are: with 2.5, a prediction is typically off by about 2.5 points of Y.
Sample size— Sample sizeCompare it with your sample: every row with a gap in any of the columns is dropped.
Calculated with lambda 0— Ridge penalty used0 is the ordinary regression — the one you usually want. Ignore this line unless you set lambda yourself.
ANOVA of the model
Source— Source of variationRead each row as one part of the differences in Y.
SS— Sum of squaresThe ingredient of R² (Regression SS / Total SS) and of F; rarely reported on its own.
df— Degrees of freedom of the model and the errorReported in brackets after F: F(2, 47) — the Regression df first, then the Residual df.
MS— Mean squareF is MS Regression divided by MS Residual.
F— F of the modelRead its Sig. In a report: F(2, 47) = 7.83, p = .001.
Sig.— p of the modelBelow 0.05 — the predictors together explain Y better than its mean alone. At 0.05 or above — the model as a whole does not help significantly.
Regression— RegressionIts F and Sig. are the test of the whole model.
Residual— ResidualIts MS is the variance of the prediction errors; its square root is the Std. Error of the Estimate.
Total— TotalThe same number for every model on the same rows: only its split between Regression and Residual changes.
R Square Change— R² changeΔR² = 0.06 means the predictors added at this step explain 6% of Y on top of the earlier ones. Read it with Sig. F Change next to it.
In a report: ΔR² = .06, F(1, 7) = 15.39, p = .006.
F Change— F changeRead its Sig. F Change; F itself goes into the report with df1 and df2 next to it: F(1, 7) = 15.39.
df1— Degrees of freedom 1 of the F changeThe first number in brackets after F: F(df1, df2). You report it, you do not interpret it.
df2— Degrees of freedom 2 of the F changeThe second number in brackets after F: F(df1, df2). You report it, you do not interpret it.
Sig. F Change— p of the F changeBelow 0.05 — the predictors added at this step help significantly beyond the earlier ones; at 0.05 or above — the step adds nothing reliable, and the simpler model is enough.
Coefficients
Variable— VariableRead the numbers along this row or column for that variable.
Intercept— InterceptRarely interpreted — a predictor value of 0 is often outside the data (age 0). It is needed to compute predictions.
B— Coefficient BB = 0.5 for hours: one more hour, half a point more, all else equal. The sign gives the direction; compare predictors in different units by Beta, not by B.
StdError— Standard error of BLarge next to B — the coefficient is imprecise. It is used for the test and the interval and is rarely reported on its own. SPSS calls it Std. Error (REGRESSION) or S.E. (LOGISTIC REGRESSION).
Beta— Standardized coefficient βBeta puts predictors measured in different units on one scale: the larger |Beta|, the stronger the predictor within this model.
t— t of the coefficientBeyond about ±2 the coefficient usually becomes significant; read p next to it.
p— p of the coefficientBelow 0.05 — this predictor adds to the prediction with the other predictors held fixed.
In a report: "Study hours predicted the score, B = 0.50, β = .42, t(47) = 3.10, p = .003."
VIF— Variance inflation factor1 — no overlap with the other predictors. Above 5 (some say 10) the predictors overlap so much that their separate B are unreliable: drop one of them or set lambda above 0.
Interaction terms use mean-centered values: the B of a variable in an interaction is its effect at the mean of the other.— Centered interactionsThe step has an interaction a*b and centerInteractions is TRUE: the product is built from mean-centered values, (a − mean of a) · (b − mean of b). a, b and the other predictors stay raw.
So the B of a is its effect at the mean of b, not where b is 0. B, SE and p of a*b and the fit of the model are the same as without centering. After an "Excluded: …" text the line goes into the same cell.
How it is computed
Rows with an empty cell in Y or in any predictor of any step are left out, so every step is fitted on the same rows.
Predictors enter in the written order; a constant predictor or one that is a linear combination of those before it (tolerance below 0.0001) does not enter, as SPSS forced entry; with lambda above 0 nothing is excluded.
Ordinary least squares on centered data: B = (XᵀX)⁻¹Xᵀy, the intercept ȳ − Σ Bⱼx̄ⱼ. With lambda above 0 — ridge, (XᵀX + λI)⁻¹Xᵀy, the intercept not penalized; then no standard errors, t, p or VIF.
R² = 1 − SSE/SST; the ANOVA table tests the whole model with F = MS Regression / MS Residual (with lambda above 0 the F and its Sig. are printed by the same formula but are not a valid test of the ridge model); each coefficient is tested by t = B / SE with n − p − 1 degrees of freedom, two-sided.
Beta = B · sₓ / s_y; VIF = 1 / (1 − R²ⱼ) of each predictor on the others — as SPSS REGRESSION.
An interaction a*b is the product of its parts, built by the function (a column named a*b in the data is not read). With centerInteractions TRUE it is (a − ā)(b − b̄), the means over the rows of the model; a, b and the other predictors stay raw. B, SE and p of a*b, R Square, F and R Square Change do not change; the B of a becomes its effect at the mean of b (raw: at b = 0); the B, SE and p of a and b are those of the model with a and b centered too — only the intercept differs.
Errors
A column that does not exist: "Column nope does not exist".
A Y whose values are all the same: "Dependent variable y has no variance".
No predictor of a step enters: "No predictors entered. Excluded: c (constant or linearly dependent on other predictors, tolerance < 0.0001)".
A text predictor without value labels: "… needs numeric columns, text columns: …".
lambda below 0 or infinite: "lambda must be a number of at least 0 (e.g. 0), got …".
centerInteractions not TRUE or FALSE: "centerInteractions must be TRUE or FALSE (or 1, 0), got \"yes\"".
About STATS_LINEAR
STATS_LINEAR fits a multiple linear regression by ordinary least squares: it predicts one column (Y) from several others. Blocks of predictors separated by "|" are hierarchical steps, each adding its block to the previous ones, and a*b adds an interaction. Predictors enter in the order you write them; there is no automatic selection. The report recalculates by itself when new responses arrive from Google Forms.
Each step has its Model Summary (R, R², adjusted R², standard error of the estimate, sample size), an ANOVA table that tests the whole model, and Coefficients: B, standard error, Beta (the standardized coefficient), t, p and VIF, which warns when predictors overlap. With two or more steps, the R² change and its F test are added, as in SPSS Change Statistics. Beta and VIF follow the definitions of SPSS REGRESSION.
A row with an empty cell in Y or in any predictor is left out of every step, so all steps use the same rows. A constant predictor, or one that repeats others, does not enter, as in SPSS, and is named in an "Excluded" line. lambda above 0 gives ridge regression; then standard errors, t, p and VIF are not reported. Every number of the report is explained on this page in plain words.