INTERCEPT in Google Sheets: Values at Zero and Shifted Reference Origins

• Introduced: first introduction date is not established by the reviewed official history; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; these examples use the web worksheet workflow.
• Checked: October 14, 2026; official INTERCEPT reference. Documentation and independent models, with no native Sheets execution or wider client certification.

=INTERCEPT(data_y,data_x)

The dependent Y range comes first and the independent X range second. Both are required. Google explicitly says text encountered in the arguments is ignored, but its compact reference does not specify a complete missing-data or native-error matrix.

For ordinary complete numeric pairs, this guide checks the least-squares identity intercept=mean_y-slope*mean_x. The coefficient calculations are independently modeled; no native Sheets results or extreme-value stability guarantees are claimed.

A reference to the same numbers in a different row order is harmless only when both columns are rearranged together. Keep observation identifiers when preparing the paired data.

Enter X 1,2,3,4 in A2:A5 and Y 3,5,4,8 in B2:B5:

=INTERCEPT(B2:B5,A2:A5)

Expected result: 1.5. The two means are 2.5 and 5. The SLOPE model gives 1.4, so the intercept is 5-1.4*2.5=1.5.

There is no observed X=0 row in this table. The intercept is therefore an extrapolated point of the fitted line, even though the function can calculate it. A meaningful mathematical coefficient does not by itself validate a real-world prediction at that coordinate.

The line is Y=1.5+1.4*X. In C2 enter:

=INTERCEPT($B$2:$B$5,$A$2:$A$5)+SLOPE($B$2:$B$5,$A$2:$A$5)*A2

Fill through C5. Expected fitted values: 2.9,4.3,5.7,7.1. In D2 enter =B2-C2 and fill down. Expected residuals: 0.1,0.7,-1.7,0.9.

Their sum is 0, while their squared total is 4.2. Positive and negative errors can cancel in a sum; that cancellation does not make the fit exact.

In E2 enter =A2-2.5 and fill through E5. The transformed X values are -1.5,-0.5,0.5,1.5.

=INTERCEPT(B2:B5,E2:E5)

Expected result: 5. The transformed X mean is zero and the slope remains 1.4. The fitted observations have not changed; only the meaning of the origin has changed. X-prime=0 now corresponds to original X=2.5.

This explains why intercepts from models using different time origins or measurement offsets cannot be compared as though they used the same coordinate system.

Transform original X to 11,12,13,14 while leaving Y unchanged. The fitted slope remains 1.4, but the intercept becomes -12.5.

At transformed X=11 the fitted value is -12.5+1.4*11=2.9, matching the original fit at X=1. The negative intercept reflects a distant changed origin, not a new negative observation in the data.

If a target X is also transformed, apply the same rule to it. Shifting only the observed X values changes the meaning of any unshifted target.

Add 20 to every original Y. Expected intercept becomes 21.5 and slope remains 1.4. Multiply original Y by two instead; expected intercept becomes 3 and slope becomes 2.8.

For the exact line Y 4,7,10,13 at X 1,2,3,4, the expected intercept is 1 and slope is 3. This simple control can help reveal reversed argument ranges.

FORECAST evaluates a fitted line at a specified X. Its independent expectation at X=0 agrees with this intercept, but a forecast function does not establish that extrapolation is appropriate or supply uncertainty limits automatically.

Constant X makes a unique slope/intercept fit unavailable in the mathematical model. Google’s dedicated reference does not name that native error, nor every empty-array, mixed-type or mismatched-range outcome. Preserve those uncertainties rather than treating another spreadsheet’s error table as Google evidence.

• Google INTERCEPT reference.
• Editorial testing standards.

Examples use fictional data, English function names and comma separators. Expected outputs are independent mathematical calculations or explicitly documented rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.