What's the difference between INTERCEPT and SLOPE?

INTERCEPT returns the y-value where the regression line crosses the y-axis (at x=0). SLOPE returns the line's steepness (how much y changes per unit of x). Together they define the complete line: y = SLOPE × x + INTERCEPT. You need both to make predictions.

Why does INTERCEPT return a value that doesn't exist in my data?

INTERCEPT calculates where the best-fit line would cross the y-axis mathematically, not where your actual data is. The intercept is often a theoretical point; its value depends on the trend's slope and position, not the observed data range.

Can INTERCEPT work with only two data points?

Yes, but it's meaningless. With two points, any regression line passes perfectly through both, so there's no trend to analyze—you're just defining a line through two points. Use at least 3–4 points for INTERCEPT to reveal a meaningful pattern.

When should I use INTERCEPT instead of just taking my first value?

Use INTERCEPT for forecasting and trend analysis (e.g., 'when will stock run out?'). Use your actual first value if you want the real, observed starting point. INTERCEPT is a mathematical extrapolation; it doesn't describe what you actually have—it describes what the trend implies you'd have at x=0.

INTERCEPT function

INTERCEPT finds where a linear regression line crosses the y-axis, revealing the trend's mathematical starting point.

=INTERCEPT(known_ys, known_xs)

Generate a INTERCEPT formula

Describe what you need. The generator will reach for INTERCEPT where INTERCEPT is the right tool, and tell you when it is not.

Arguments

How INTERCEPT reads its arguments

Argument	Required	Description
known_ys	Required	An array of dependent variable values (y-coordinates); must contain only numbers and match the length of known_xs.
known_xs	Required	An array of independent variable values (x-coordinates); must contain only numbers and match the length of known_ys.

Returns

A decimal number representing the y-intercept value of the best-fit line.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Predict when tomatoes will run out based on daily usage

Day	Tomatoes (kg)	Olive Oil (L)	Basil (bunches)	Mozzarella (kg)
1	60	20	100	30
2	54	19.5	95	29.5
3	48	19	88	29
4	42	18.5	80	28.5
5	36	18	75	28
6	30	17.5	68	27.5

=INTERCEPT(B2:B7, A2:A7)

Result: 66

INTERCEPT calculates the y-intercept for tomatoes consumption (column B vs column A). The result of 66 kg represents where the best-fit line crosses the y-axis—in other words, if you extended the consumption trend backward to day zero, you'd have started with 66 kg. The steady decline of 6 kg per day means stock runs out around day 11. This helps predict when to reorder.

2. Establish baseline olive oil usage to optimize supplier orders

Day	Tomatoes (kg)	Olive Oil (L)	Basil (bunches)	Mozzarella (kg)
1	60	20	100	30
2	54	19.5	95	29.5
3	48	19	88	29
4	42	18.5	80	28.5
5	36	18	75	28
6	30	17.5	68	27.5

=INTERCEPT(C2:C7, A2:A7)

Result: 20.5

Applying INTERCEPT to olive oil (column C) returns 20.5 L—the mathematical starting point of the consumption trend. With consumption declining at 0.5 L per day, the intercept shows that day zero would have 20.5 L on hand. This reveals your steady-state consumption rate and helps set par levels for inventory. Comparing this to actual starting stock shows whether you're overstocking or running lean.

3. Compare ingredient depletion rates to prioritize deliveries

Day	Tomatoes (kg)	Olive Oil (L)	Basil (bunches)	Mozzarella (kg)
1	60	20	100	30
2	54	19.5	95	29.5
3	48	19	88	29
4	42	18.5	80	28.5
5	36	18	75	28
6	30	17.5	68	27.5

=INTERCEPT(E2:E7, A2:A7)

Result: 30.5

For mozzarella (column E), INTERCEPT returns 30.5 kg. Comparing intercepts across all ingredients (tomatoes: 66, olive oil: 20.5, mozzarella: 30.5) reveals which ingredients deplete fastest relative to their initial stock. Tomatoes drop 6 kg daily while mozzarella drops only 0.5 kg daily. This insight helps allocate delivery schedules and supplier agreements—high-turnover items like tomatoes need more frequent orders or larger quantities.

Common errors

Which INTERCEPT error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	One or both arrays contain text, dates, or other non-numeric values. INTERCEPT requires pure numbers to calculate regression coefficients.	Verify that known_ys and known_xs reference only numeric columns. If data includes headers, exclude row 1 from your range. If a column has mixed types (some numbers, some text), clean it or select a different column.
#DIV/0!	The two arrays have different lengths, or one array contains fewer than two cells. Regression math requires at least two paired (x, y) points.	Ensure known_ys and known_xs have identical lengths and span at least two rows (e.g., A2:A7 paired with B2:B7). Check for mismatched range references like A2:A7 and B2:B8.
#NUM!	All values in known_xs are identical. When the x-values don't vary, the slope is undefined and the regression line cannot be calculated.	Verify that known_xs contains different values across its range. If all x values are the same (e.g., all cells = 5), pick a different column for the independent variable or review your data source.

Tips and when to use something else

INTERCEPT assumes a linear relationship between your variables. Always plot your data as a scatter chart first to confirm the pattern is roughly straight, not curved or randomly scattered.
Outliers and errors heavily skew the intercept. Clean your dataset by investigating unexpected values before running INTERCEPT—one bad data point can shift the intercept significantly.
INTERCEPT and SLOPE work as a pair: INTERCEPT gives the y-intercept (b), SLOPE gives the steepness (m). Together they form the regression equation y = m*x + b, which you can use for forecasting beyond your data range.
If your data shows exponential growth or decay (like bacterial cultures or radioactive decay), use LOGEST instead of INTERCEPT; if it's polynomial or power-law, use LINEST instead.

Related functions

SLOPESLOPE calculates the slope of a linear regression line between two data sets, showing the rate of change between variables.LINESTLINEST returns regression statistics for fitting a straight line through your data, including slope, intercept, and optional correlation metrics.FORECAST.LINEARFORECAST.LINEAR returns a predicted y-value by fitting a straight line through known x/y data points and extrapolating to a new x.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.

Frequently asked questions

What's the difference between INTERCEPT and SLOPE?: INTERCEPT returns the y-value where the regression line crosses the y-axis (at x=0). SLOPE returns the line's steepness (how much y changes per unit of x). Together they define the complete line: y = SLOPE × x + INTERCEPT. You need both to make predictions.
Why does INTERCEPT return a value that doesn't exist in my data?: INTERCEPT calculates where the best-fit line would cross the y-axis mathematically, not where your actual data is. The intercept is often a theoretical point; its value depends on the trend's slope and position, not the observed data range.
Can INTERCEPT work with only two data points?: Yes, but it's meaningless. With two points, any regression line passes perfectly through both, so there's no trend to analyze—you're just defining a line through two points. Use at least 3–4 points for INTERCEPT to reveal a meaningful pattern.
When should I use INTERCEPT instead of just taking my first value?: Use INTERCEPT for forecasting and trend analysis (e.g., 'when will stock run out?'). Use your actual first value if you want the real, observed starting point. INTERCEPT is a mathematical extrapolation; it doesn't describe what you actually have—it describes what the trend implies you'd have at x=0.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17