STATS_LOGISTIC – Binary logistic regression in Google Sheets™

What it does

Binary logistic regression with optional ridge regularization (lambda >= 0).

Y must hold exactly the two values 0 and 1 (TRUE/FALSE, "0"/"1" written as text and text with value labels coding 0 and 1 are accepted). Recode another Y first.

Supports multiple model steps via | separators and interactions a*b.

Each step fits a model using selected predictors and returns Meta, Model Summary, Omnibus Tests, and Variables in the Equation sections.

Meta includes convergence status and the actual number of performed iterations.

The fit runs on centered and scaled predictors and B, SE and intervals are transformed back, so a predictor with a large origin (a date as a number) gives correct estimates.

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.

If lambda > 0, the ridge penalty on the slopes stabilizes coefficients under separation or collinearity. An empty lambda takes the value from Settings (0 by default).

Results returned as a flattened 2D report: section titles + table rows.

Syntax

=STATS_LOGISTIC(columns, yName, steps, lambda, maxIter, tol, 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 columns
A1:C200 — use 3 columns
A:F — use 6 columns
A1: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 penalty
0.5 — ridge

maxIter — optional, default 100

Max iterations for the logistic fit: a whole number of at least 1. Meta shows how many were performed and whether the model converged.

Should be a whole number of at least 1

Examples:

50

tol — optional, default 0.000001

Convergence tolerance (logistic): a number above 0.

0, "Infinity" or 1e309 is an error: "tol must be a number above 0 (e.g. 0.000001), got 1e309".

Should be a number of at least 0

Examples:

1e-6

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 b
FALSE — a*b from the raw values

Examples

— the sample data (50 rows) all examples use
— two steps; pass is coded 0 and 1 =STATS_LOGISTIC(Data!A1:T51, "pass", "x1,x2|x", 0, 50, 1e-8)
— =STATS_LOGISTIC(Data!A1:T51, "pass", "x1|x2", 0)
— Error example: y is not coded 0 and 1 =STATS_LOGISTIC(Data!A1:T51, "y", "x1")

What the result contains

Returns a labeled 2D report that spills below the formula cell.

For each step includes Meta, Model Summary, Omnibus Tests of Model Coefficients, and Variables in the Equation (B, StdError, Wald, Sig., Exp(B), CI95 Lower, CI95 Upper).

CI95 uses the exact normal quantile 1.95996, as SPSS.

With centerInteractions TRUE a step with an interaction a*b has the line "Interaction terms use mean-centered values: …" under its Variables in the Equation table, after the "Excluded: …" text when there is one.

How to read the result

Start with the omnibus test: Sig. below 0.05 means the predictors together predict the outcome better than chance.

Check Meta: converged should be TRUE, and n should be close to the size of your sample.

See how well the model fits: Nagelkerke R Square (the one most often reported) and Accuracy — compare accuracy with the share of the more common outcome.

Then go through Variables in the Equation: Sig. below 0.05 marks the predictors that matter, and Exp(B) says how much — above 1 raises the odds of Y = 1, below 1 lowers them.

With several steps, compare the steps: a lower AIC and a higher Nagelkerke R² show that the added predictors help. The omnibus test of a step tests the whole model, not only what the step adds.

In a report: "The model was significant, χ²(2) = 12.40, p = .002, Nagelkerke R² = .31, and classified 76% of cases correctly. Study hours raised the odds of passing (OR = 1.82, 95% CI 1.12–2.95, p = .015)."

What each number means

Model summary

-2 Log Likelihood — −2 Log Likelihood (deviance)

On its own it says little. It compares models on the same rows: its drop from the intercept-only model is the chi-square of the omnibus test, and its drop from one step to the next shows what the added predictors bring.

AIC — Akaike information criterion

For choosing between models of the same Y on the same rows: the lower AIC, the better the balance of fit and simplicity; a difference under about 2 is negligible, the value itself means nothing. Compare only converged models without lambda: with ridge or without convergence AIC is not the usual criterion.

BIC — Bayesian information criterion

Like AIC, but it punishes extra predictors harder as the sample grows, so it prefers simpler models. Lower is better; compare only converged models without lambda on the same rows.

McFadden R Square — McFadden pseudo R²

Not a share of explained variance like R² of a linear regression, and it runs lower: McFadden himself called values of 0.2–0.4 an excellent fit.

Cox & Snell R Square — Cox & Snell pseudo R²

Mostly reported next to Nagelkerke R², because SPSS prints both; Nagelkerke is the easier one to read.

Nagelkerke R Square — Nagelkerke pseudo R²

The pseudo R² reported most often: 0 — the model adds nothing, 1 — a perfect fit. It is built from the likelihood, not from variance: do not read 0.30 as "30% of the variance explained".

In a report: "Nagelkerke R² = .30".

Accuracy — Classification accuracy

Easy to explain: 0.75 means the model gets 3 cases of 4 right. Compare it with the share of the more common outcome: if 70% of the rows are 1, always guessing 1 already gives 0.70.

It is the Overall Percentage of the SPSS Classification Table at the cut-off 0.5; the table itself (right and wrong answers for 0 and for 1) is not reported.

Omnibus test of the model

Chi-square — Likelihood-ratio chi-square

The larger, the more the predictors together improve the prediction. Read its Sig.; in a report: χ²(2) = 4.08, p = .130.

df — Degrees of freedom of the model

Written in brackets after the chi-square in a report: χ²(2) = 4.08, p = .130.

Sig. — p of the model

Below 0.05 — the model as a whole predicts better than guessing from the overall share of 1s. At 0.05 or above — the predictors together do not help significantly.

This is the first number to look at in a logistic regression.

Coefficients

Variable — Variable

Read the numbers along this row or column for that variable.

Intercept — Intercept

Rarely interpreted — a predictor value of 0 is often outside the data (age 0). It is needed to compute predictions.

B — Coefficient B

Positive — the event becomes more likely as the predictor grows; negative — less likely. The size is on a logarithmic scale (log-odds) and hard to read: look at Exp(B) instead.

StdError — Standard error of B

Large 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).

Wald — Wald statistic

Tests whether B differs from 0. It follows the chi-square distribution with 1 df, so above about 3.84 means p below 0.05. Read Sig. next to it.

df — Degrees of freedom of the Wald test

Always 1 here; SPSS prints the same column, so the table can be copied as it is.

Sig. — p of the coefficient

Below 0.05 — this predictor matters with the other predictors held fixed.

The Wald test is cautious with large coefficients, so with one predictor it can disagree with the omnibus test (for example Sig. 0.08 here and 0.04 in the omnibus test): for the model as a whole, trust the omnibus test.

Exp(B) — Odds ratio

The number to report. 1.65 means every extra unit multiplies the odds of the event by 1.65 (+65%); 0.80 means the odds fall by 20%; 1 means no effect.

Odds are not a probability: odds of 1 are a 50% chance, odds of 3 a 75% chance.

In a report: "Each additional hour of study raised the odds of passing (OR = 1.65, 95% CI 0.95–2.86, p = .077)."

CI95 Lower — Lower bound of the odds ratio interval

Together with the upper bound: a range of plausible values for the true odds ratio. If the whole range is above 1 (or below 1), the predictor raises (or lowers) the odds; if it contains 1, the effect may be nil.

CI95 Upper — Upper bound of the odds ratio interval

Together with the lower bound: a range of plausible values for the true odds ratio. A very wide interval (0.0001 to 285) means the data say almost nothing about this coefficient.

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.

The model log(p / (1 − p)) = B₀ + B₁x₁ + … is fitted by maximum likelihood with Newton–Raphson (IRLS), on centered and scaled predictors, and B is transformed back. It stops when every step of B is below tol, or after maxIter iterations.

Standard errors from the inverse Fisher information; Wald = (B / SE)² with a two-sided normal p; Exp(B) and its 95% interval e^(B ± 1.959964 · SE), as SPSS.

The omnibus test is the likelihood-ratio chi-square against the intercept-only model — the Model row of the SPSS table; the Step and Block rows (what a step adds) are not reported. The pseudo R² are McFadden, Cox & Snell and Nagelkerke; accuracy reads p ≥ 0.5 as 1 (the Overall Percentage of the SPSS Classification Table).

With lambda above 0 the slopes are penalized by (λ/2) · Σ B² (ridge); the report then has no standard errors, Wald tests or odds-ratio intervals, and the omnibus chi-square and AIC/BIC, still printed, are not valid tests of a penalized model.

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, the deviance and the pseudo R² 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

Y not coded 0 and 1: "Logistic regression needs Y coded 0 and 1, got: 0, 1, 6" (the values found).

No finite estimate exists: "Logistic regression failed: estimates are not finite numbers".

No predictor of a step enters: "No predictors entered. Excluded: …".

A column that does not exist: "Column nope does not exist".

centerInteractions not TRUE or FALSE: "centerInteractions must be TRUE or FALSE (or 1, 0), got \"yes\"".

lambda, maxIter or tol out of range (including "Infinity" or 1e309): one error naming the option, for example "tol must be a number above 0 (e.g. 0.000001), got 1e309".

About STATS_LOGISTIC

STATS_LOGISTIC predicts a yes/no outcome, a Y column coded 0 and 1 such as passed or failed, from one or more predictors. TRUE/FALSE is accepted; recode any other Y first. Blocks separated by "|" are hierarchical steps, a*b adds an interaction, and predictors enter in the order you write them. A row with an empty cell in Y or in any predictor is left out of every step. The report recalculates by itself when new responses arrive from Google Forms.

For each step, Variables in the Equation gives B, its standard error, the Wald test and p, and Exp(B), the odds ratio, with its 95% interval as in SPSS. The omnibus test compares the model with the intercept-only model. Model Summary adds the McFadden, Cox & Snell and Nagelkerke pseudo R² and the accuracy, the share of cases predicted correctly (the Overall Percentage of the SPSS Classification Table). Meta shows whether the model converged.

Under separation or collinearity, lambda above 0 adds a ridge penalty that stabilizes the coefficients; the report then has no standard errors, Wald tests or odds-ratio intervals. Every number of the report is explained on this page in plain words.