INTERCEPT

Fits a straight line to data using linear regression and returns its intercept on the y-axis.

Syntax:

INTERCEPT(yvalues, xvalues)

yvalues and xvalues are single row or column ranges specifying points in a set of data. yvalues and xvalues must be the same size.

INTERCEPT fits a straight line through these data points, using the linear regression method (least squares). It then returns the y value where that straight line crosses the y-axis.

The equation of a straight line may be given as y = a + bx. The linear regression method calculates:

$b=\frac{\sum (x_i-\overline{x})(y_i-\overline{y})}{\sum (x_i-\overline{x}{)}^2}$

and

$a=\overline{y}-b\overline{x}$

a is the y-axis intercept returned by this function.

Example:

INTERCEPT(B2:B6, A2:A6)

where the x values in A2:A6 are 1, 2, 3, 4, 5 and the y values in B2:B6 are 2, 4, 7, 8, 10 returns 0.2. The equation of the straight line found is very nearly y = 2x (and would be if B4 contained 6) - thus the straight line very nearly (but not quite) goes through the origin.

Using the INTERCEPT Function:

The syntax for the INTERCEPT function is:

INTERCEPT(known_y's, known_x's)

• known_y's: This is the range of your dependent variable (the data you are trying to predict). In our table, this would be the Sale Price column.
• known_x's: This is the range of your independent variable (the data that influences the prediction). In our table, this would be the Square Footage column.

If the data from the table above is in cells A1:C5, the formula would look like this:

INTERCEPT(C1:C5, B1:B5)

Interpretation of the Result:

Let's say the result of this calculation is approximately $108,306.

This means that, based on the linear relationship found in your data, a house with zero square footage would be predicted to have a base value of $108,306.

Meaning:

While a house with 0 square feet is impossible, the intercept value of $108,306 is a very useful piece of information. It represents the value contributed by factors other than square footage. This could include:

• The value of the land itself.
• The desirability of the neighborhood.
• Proximity to schools, parks, or city centers.
• Average property values in the area.

In essence, the intercept provides a baseline or starting point for the linear regression model. The overall predictive equation would be:

• Predicted Price = (Slope * Square Footage) + Intercept

Where the slope tells you how much the price increases for each additional square foot, and the intercept is the base price before any square footage is considered.