STATS_BOOTSTRAP – Bootstrap Confidence Intervals for Regression
What it does
Bootstrap confidence intervals for regression via resampling.
Supports pairs and residual schemes and hierarchical steps separated by |.
Returns a structured report with a Summary block plus per-step Meta and Bootstrap Estimates sections.
Each step estimates the slope of the predictor it adds. Predictors enter in the written order; a constant or linearly dependent predictor does not enter, as in STATS_LINEAR.
Interval bounds are percentiles at position (B + 1)·α, as SPSS BOOTSTRAP. The numbers change from run to run: resampling is random.
Syntax
=STATS_BOOTSTRAP(columns, yName, steps, B, method, ciLevel, 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)
B — optional, default 2000
Bootstrap resamples count: a whole number of at least 1.
Should be a whole number of at least 1
Examples:
200015001000
Resamples whose model cannot be fitted are dropped; when fewer than B remain, Meta shows them as "8 of 50", and with fewer than half of B the interval is left empty.
Another value is an error: "B must be a whole number of at least 1 (e.g. 2000), got 20.5".
method — optional, default "pairs"
Bootstrap scheme: "pairs" resamples whole rows, "residual" resamples the residuals of the fitted model.
Allowed values: "pairs", "residual"
Examples:
"pairs""residual"
Allowed: "pairs" or "residual" (case does not matter); anything else is an error: method must be pairs or residual, got "resid".
ciLevel — optional, default 0.95
Confidence level for intervals in tests/bootstraps: a number strictly between 0 and 1 (0.95, not 95). A number written as text ("0.9") is accepted.
Another value is an error: ciLevel must be between 0 and 1 (e.g. 0.95), got 95. Empty — the level from Settings.
Should be a number between 0 and 1
Examples:
0.90.950.8
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_BOOTSTRAP(Data!A1:T51, "y", "x1,x2", 2000, "pairs", 0.95) - —
=STATS_BOOTSTRAP(Data!A1:T51, "y", "x1,x2|x",1500,"residual") - —
=STATS_BOOTSTRAP(Data!A1:T51, "y", "x1,x2", 1800, "pairs") - —
=STATS_BOOTSTRAP(Data!A1:T51, "y", "x1,x2|x")
What the result contains
Returns a labeled report (2D array) that spills below the formula cell.
Includes Summary, then for each step: Meta and Bootstrap Estimates.
Step sections report Predictor added, Predictors in model, Slope, Lower, Upper, CI Width, and Estimate within CI.
When the predictor of a step is excluded, its estimates are empty and the step says "Excluded: …". When fewer than B resamples could be fitted, Meta shows "8 of 50"; with fewer than half of B the interval is empty.
With centerInteractions TRUE a step with an interaction a*b has the line "Interaction terms use mean-centered values: …" under its Bootstrap Estimates table.
How to read the result
Look at Lower and Upper: an interval that does not contain 0 means the predictor has an effect, without assuming normal errors.
Slope is the size of the effect, as B of STATS_LINEAR; CI Width — how precisely it is known.
Check B in Meta: "8 of 50" means only 8 finite slopes — fewer than half, so there is no interval at all; at least half but fewer than B, the interval rests on fewer resamples.
In a report: "The bootstrap 95% CI of the slope of x2 (2000 resamples, pairs) was [−0.43, 0.63]."
What each number means
Summary
Method— Resampling methodpairs is the safer default: it does not assume the model is right. residual assumes the errors are alike for all rows and gives narrower intervals when that holds.
B— Number of resamplesMore resamples — a steadier interval; 1000–2000 is common. The interval changes slightly on every recalculation: the resamples are random.
n— Number of valuesHow many observations the result rests on. Compare it with the size of your sample: empty cells are left out.
CI level— Confidence level95% means that in 95 samples of 100 an interval built this way would contain the true slope.
Interval type— Interval typeThe simplest bootstrap interval: no normal distribution is assumed.
Target— TargetThe column whose change the slope describes.
Meta
Predictor added— Predictor addedRead the slope as the effect of this predictor with the others of the model held fixed.
Predictors in model— Predictors in modelThe predictors held fixed while the slope is estimated.
Method— Resampling methodpairs is the safer default: it does not assume the model is right. residual assumes the errors are alike for all rows and gives narrower intervals when that holds.
B— Number of resamplesMore resamples — a steadier interval; 1000–2000 is common. The interval changes slightly on every recalculation: the resamples are random.
n— Rows in the modelIf n is much smaller than your sample, many rows have a gap in one of the columns, and each such row is dropped from every step.
CI level— Confidence level95% means that in 95 samples of 100 an interval built this way would contain the true slope.
Bootstrap estimates
Statistic— StatisticThe name of each number in the row: Slope, Lower, Upper, CI Width. This report has no p.
Value— ValueRead each row: the name on the left, the number here.
Slope— SlopeThe same number as B of STATS_LINEAR for this predictor; the bootstrap adds an interval that does not rely on normal errors.
Lower— Lower bound of the slopeTogether with the upper bound: a range of plausible values for the true slope. If the interval contains 0, the predictor may have no effect. It changes slightly on every recalculation: the resamples are random.
Upper— Upper bound of the slopeTogether with the lower bound: a range of plausible values for the true slope. If the interval contains 0, the predictor may have no effect.
CI Width— Width of the intervalThe narrower, the more precisely the slope is known.
Estimate within CI— Estimate within the intervalNormally TRUE. FALSE means the slope of the data lies outside the percentile interval of the resamples — a sign of bias or instability of the bootstrap, worth a second look.
How it is computed
Rows with an empty cell in Y or in any predictor are left out (listwise); a constant or linearly dependent predictor does not enter, as STATS_LINEAR.
The slope is the least-squares B of the last predictor of each step, with the other predictors of the model held fixed.
pairs: B times, n rows are drawn with replacement and the slope is fitted again; residual: the fitted values plus residuals drawn with replacement. A resample without a finite slope is drawn again (not copied), up to max(20 × B, 100) attempts.
The interval: the (1 − level)/2 and (1 + level)/2 percentiles of the resampled slopes (SPSS HAVERAGE); no interval when fewer than half of the resamples are usable. The resamples are random, so the bounds change slightly on every recalculation.
With centerInteractions TRUE an interaction a*b is (a − ā)(b − b̄), centered once on the original sample (the resamples take the centered column); the slope of a*b does not change, the slope of a becomes its effect at the mean of b.
Errors
A Y whose values are all the same: "Dependent variable y has no variance".
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\"".
B, method or ciLevel out of range: one error naming the option, for example "method must be pairs or residual, got \"resid\"".
About STATS_BOOTSTRAP
STATS_BOOTSTRAP gives a confidence interval for a regression slope by resampling: it fits the model again on many resampled versions of the data (B = 2000 by default) and takes the interval from the spread of the slopes. Name the dependent variable (yName) and the predictors (steps). Blocks separated by | are hierarchical steps, each adding its predictors to the previous ones; x4*x3 adds an interaction, the product of two columns.
Two schemes are available: pairs, the default, resamples whole rows; residual resamples the residuals of the fitted model. For each step the report shows the predictor added, its slope, the lower and upper bounds, the width of the interval and whether the estimate lies within it. The bounds are percentiles of the resampled slopes, as in SPSS BOOTSTRAP. Rows with an empty cell in Y or in any predictor are left out (listwise).
Resampling is random, so the bounds change slightly on every recalculation, including when new responses arrive from Google Forms. Resamples whose model cannot be fitted are dropped; when fewer than half of B are usable, the interval is left empty. Every number of the report is explained on this page in plain words.