INTERCEPT function - The function returns the point at which the line will intersect the y-axis by using the existing x and y values ​​in Excel

The following article introduces you to the INTERCEPT function - one of the functions in the statistical function group is very popular in Excel.

INTERCEPT function - The function returns the point at which the line will intersect the y-axis by using the existing x and y values ​​in Excel Picture 1

Description: The function returns the point at which the line will intersect the y-axis by using existing x and y values. Use the function when you want to determine the value of a dependent variable when the independent variable is 0.

 Syntax: INTERCEPT (known_y's, known_x's)

Inside:

- known_y's : A set of dependent data.

- known_x's : A set of independent data.

Attention:

- The value of the argument must be a number, name, array or reference containing numbers.

- If the argument is a reference array containing text values ​​or logic -> these values ​​are ignored, but the value 0 is still counted.

- If known_y's , known_x's have a different number of data points or contain no data points -> the function returns the # N / A error value .

- The intersection equation of the regression line is:

[a = overline y - boverline x]

Inside:

[b = frac {{sum {left ({x - overline x} right) left ({y - overline y} right)}}} {{sum {{{left ({x - overline x} right)} ^ 2 }}}}]

And x and y are the models AVERAGE (known_x's) and AVERAGE (known_y's).

- The underlying algorithm used in INTERCEPT and SLOPE is different from the underlying algorithm used in the LINEST function .

- In case the function returns multiple values ​​-> # DIV / 0 error message

For example:

Find the point where a line will intersect the y-axis with the data in the data table below:

INTERCEPT function - The function returns the point at which the line will intersect the y-axis by using the existing x and y values ​​in Excel Picture 2

- In the cell to calculate enter the formula : = INTERCEPT (C6: C10, D6: D10)

INTERCEPT function - The function returns the point at which the line will intersect the y-axis by using the existing x and y values ​​in Excel Picture 3

- Press Enter -> toss the degree of the data point that a line intersects the y-axis:

INTERCEPT function - The function returns the point at which the line will intersect the y-axis by using the existing x and y values ​​in Excel Picture 4

- Where the number of data points of the two arrays x, y is different -> the function returns the # N / A error value

INTERCEPT function - The function returns the point at which the line will intersect the y-axis by using the existing x and y values ​​in Excel Picture 5

Here, the array y consists of 5 elements, but the array x has only 3 elements -> the number of elements is different.

- Case 1 of 2 empty array -> function returns error value # N / A

INTERCEPT function - The function returns the point at which the line will intersect the y-axis by using the existing x and y values ​​in Excel Picture 6

Above are instructions and some specific examples when using INTERCEPT   function in Excel.

Good luck!