STATS_PARTIAL – Partial correlation in Google Sheets™

What it does

Partial correlation: the Pearson correlation of two variables with the controls held constant (SPSS PARTIAL CORR). Two variables give one correlation, three or more a matrix, as STATS_PEARSON.

A constant control (or two controls that repeat each other) gives empty Correlation and Significance cells — on purpose: such a control carries no information and almost always means the wrong column was picked. Check the controls when the cells are empty.

df = n − 2 − k for k controls; the confidence interval is Fisher’s with 1/√(n − 3 − k). SPSS prints no interval here; the other correlations of ALS Stats do, so this one does too.

Missing values: each correlation uses the rows complete in its two variables and in every control. Two variables give one sample; in a matrix the pairs can rest on different rows.

Without controls the result is exactly the Pearson correlation. Zero-order correlations are not printed — use STATS_PEARSON for them.

Syntax

=STATS_PARTIAL(columns, colFilter, controls, ciLevel, alternative)

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.

colFilter — optional, default "*"

Comma-separated column selector.

Use names ("age,score"), negative rules ("-id,-note"), a regex ("/^q/"), or index rules ("#N", "#A-#B", "-#N", "-#A-#B").

Case-sensitive; flags are not supported.

A name that does not exist is an error: "Column nope does not exist" (excluding it, "-nope", is ignored). Empty — all columns ("*").

Examples:

"age,score" — Names only (include only "age" and "score")
"-id,-group" — Exclude some (include all except "id" and "group")
"/^q/" — Regex only (all starts with "q")
"#1,#3-#5" — Index include rules (first, and range 3..5)
"-#1,-#3-#4" — Index exclude rules (drop first and 3..4)
"age,#2-#4,-#3" — Mixed name/index rules with deterministic order
"age,/^q/,-q5" — Mixed (include age, all starts with "q" except "q5")
"/^x1{1,2}$/" — Regex with a comma inside: x1 and x11

Choose which columns from columns to use - a comma-separated string of rules.

⚙️ Four rule types (you can mix them):

By name - exact header text (case-sensitive)
- "age,score,total"
Negative rules - exclude by name (prefix with -)
- "-id,-notes" (include all except id and notes)
Regex - a regular expression between slashes /.../
- "/^q/" (all headers starting with q), "/(score|age)/", "/^x1{1,2}$/" (commas inside /.../ do not split the filter)
- ❗— case-sensitive
- ❗— flags are not supported.
Index rules - position-based rules with #
- "#1" (first selected column), "#2-#5" (range), "-#1", "-#2-#4"

"*" alone selects every column; it cannot be combined with other rules ("*,-id" is an error) — write "-id" instead.

Rules are applied in the written order, and the selected columns keep that order ("/^q/,-q5,age" puts age last).

Index rules count the columns of the range: "#1" is its first column.

A name that does not exist is an error: "Column nope does not exist" — also in colName, yName, steps, rowCol, colCol and colNameForSplit. Excluding a name that does not exist ("-nope") is ignored.

A filter that selects no column — a regex without a match ("/^Q/" where the columns are q1, q2), index rules past the end, exclusions that remove every column — is an error that names the filter and the columns of the range: colFilter "/^Q/" selects no columns (columns: q1, q2, t). Empty and "*" select every column.

In STATS_ANALYZE, a regex with brackets or | needs the new syntax: PEARSON('/(score|age)/').

A colFilter saved in Settings applies to every formula without its own filter: on a range without those columns the formula shows the error above.

# prefix is reserved for index syntax and is not treated as a column-name filter token.

colFilter applies to all expressions in query. It does not create separate groups per expression.

controls — optional

Columns held constant in a partial correlation, with the syntax of colFilter: "age", "age,income", "/^ctrl/".

A control is never taken as a variable, even when colFilter matches it: "*" with controls "age" correlates every other column, holding age constant.

Empty — no controls: the result is the Pearson correlation.

Examples:

"age" — one control
"age,income" — two controls

Each control takes one degree of freedom: df = n − 2 − k for k controls, so k controls need at least k + 3 complete rows; with fewer, Correlation, Significance and df are empty.

A constant control, or two controls that repeat each other (one a multiple of the other), leave Correlation and Significance empty on purpose: such a control carries no information and almost always means the wrong column was picked; a silent answer would hide that nothing was controlled for.

A row with a gap in any control is left out; in a matrix each pair also needs its two variables filled, so the pairs can rest on different rows (SPSS PARTIAL CORR by default uses one common sample).

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.9
0.95
0.8

alternative — optional, default "two-sided"

Alternative hypothesis for two-sample/one-sample tests and correlations: "two-sided", "greater" or "less"; 1 and -1 (number or text) mean greater and less.

Case and spaces around do not matter ("Greater", " less "). Another value is an error: alternative must be two-sided, greater or less (or 1, -1), got "grater". Empty — the default from Settings.

A one-sided hypothesis gives a one-sided interval: [lower, ∞) for greater, (−∞, upper] for less (for correlations [lower, 1] and [−1, upper]).

Allowed values: "two-sided", "greater", "less"

Examples:

"two-sided"
"greater"
"less"

Examples

— the sample data (50 rows) all examples use
— x1 and x2 with age held constant =STATS_PARTIAL(Data!A1:T51, "x1,x2", "age")
— a matrix, two controls =STATS_PARTIAL(Data!A1:T51, "x,x1,x2", "age,score")

What the result contains

Two variables — a report that spills below the formula cell:

the title "Partial Correlation controlling for age (two-sided) for a,b",
Correlations: Correlation, Significance, df,
Confidence interval: Level, Lower, Upper.

Three variables or more — the correlation matrix with significance stars and the "Correlation data" table (Pair, df, r, p, interval), as STATS_PEARSON.

Fewer than k + 3 complete rows for k controls: Correlation, Significance and df are empty.

How to read the result

Compare the partial correlation with the ordinary one (STATS_PEARSON of the same columns): a big drop means the controls explain part of the relation.

Then Significance (p): below 0.05 — the relation holds with the controls held constant.

Empty Correlation and Significance mean a constant control, two controls that repeat each other, or too few complete rows (fewer than the number of controls + 3) — check the controls and the missing answers.

In a report: "Controlling for age, study hours and the score were still related, r(47) = .36, p = .011."

What each number means

Correlations

Correlation — Partial correlation

Answers "do the two still go together when the controls are the same?". If it is much weaker than the ordinary correlation, the controls explain part of the relation.

In a report: r(47) = .09, p = .540, controlling for age.

Significance — Significance of the partial correlation

Below 0.05 — the relation holds even with the controls held constant.

df — Degrees of freedom of the partial correlation

Written in brackets after r: r(46) = .14. Every control costs one degree of freedom.

Confidence interval

Level — Confidence level: 0.95 means that in 95 samples of 100 an interval built this way would contain the true value.
Lower — Lower bound for the partial r: Together with the other bound: a range of plausible values for the true coefficient. If the interval contains 0, the relation may be nil.
Upper — Upper bound for the partial r: Together with the other bound: a range of plausible values for the true coefficient. If the interval contains 0, the relation may be nil.

Significance: *<0.05,<0.01,*<0.001 — Significance stars: What the stars in the matrix mean: * p < 0.05, p < 0.01, * p < 0.001. Text columns left out of the matrix are named after it.

Details of every pair

Pair — Pair of columns: Each row of this table is one cell of the matrix above, with its details. For a correlation the order of the two columns does not matter.
df — Degrees of freedom of the partial correlation: Written in brackets after r: r(46) = .14. Every control costs one degree of freedom.
r — Partial correlation r: If it is much weaker than the ordinary correlation of the pair, the controls explain part of the relation. In a report: r(46) = .14, p = .358, controlling for age and score.
p — p of the partial correlation: Below 0.05 — the relation holds even with the controls held constant.
CI 0.95 Lower — Lower bound for the partial r: Together with the other bound: a range of plausible values for the true coefficient. If the interval contains 0, the relation may be nil.
CI 0.95 Upper — Upper bound for the partial r: Together with the other bound: a range of plausible values for the true coefficient. If the interval contains 0, the relation may be nil.

How it is computed

Rows with an empty cell in either variable or in any control are left out. In a matrix, each pair is computed on the rows complete for that pair and all controls, so the cells can rest on different rows — unlike SPSS PARTIAL CORR, whose default (MISSING=LISTWISE) uses one common sample for all variables.

Both variables are regressed on the controls (least squares); the partial r is the correlation of the two residuals — the same number as SPSS PARTIAL CORR.

The test: t = r · √(df / (1 − r²)) with df = n − 2 − k, k — the number of controls; two-sided or one-sided by alternative.

The confidence interval: the Fisher z transform with n − 3 − k.

Errors

One cell instead of the report:

fewer than two variables left after the controls are removed: "Partial Correlation requires at least 2 columns, got: a";
a text column without value labels among the variables or the controls: "Partial Correlation needs numeric columns, text columns: t (add value labels or recode to numbers)";
a column name that does not exist: "Column nope does not exist"; a misspelled alternative or ciLevel outside 0…1 — as in the other correlations.

About STATS_PARTIAL

STATS_PARTIAL measures the relation between two variables with other variables held constant — for example, study hours and the score, controlling for age. The partial r is the same number as SPSS PARTIAL CORR. Two variables give one correlation; three or more give a matrix with significance stars, as in STATS_PEARSON. The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.

Name the controls in the controls parameter, for example "age" or "age,income". Each control takes one degree of freedom: df = n − 2 − k for k controls. Next to the correlation and its significance (p) you get a confidence interval, which SPSS does not print here. Rows with an empty cell in either variable or in any control are left out. Compare the result with the ordinary correlation from STATS_PEARSON: a big drop means the controls explain part of the relation. Empty Correlation and Significance cells mean a constant control, two controls that repeat each other, or too few complete rows.

Every number of the report is explained on this page in plain words, together with the line to write in your report, for example: Controlling for age, study hours and the score were still related, r(47) = .36, p = .011.