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!
You should read it
- QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel
- QUARTILE.INC function - The function returns the quartile of a dataset including values 0 and 1 in Excel
- MODE.MULT function - The function returns a vertical array of the most common values in Excel
- MAX function - The function returns the largest value in a set of values in Excel
- RANK.EQ function - Function returns the rank of a number in a list of numbers, returns the highest rank when multiple values with the same rank in Excel
- RANK.AVG function - The function returns the rank of a number in a list of numbers in Excel
- FORECAST function - The function returns a value along a linear trend in Excel
- STDEV.P function - The function returns the standard deviation based on the whole in Excel
- STDEV.S function - The function returns the standard deviation based on a sample in Excel
- PERCENTRANK.INC function - The function returns the rank of a value in a dataset as a percentage including values 0 and 1 in Excel
- PERCENTRANK.EXC function - The function returns the rank of a value in a dataset as a percentage excluding values 0 and 1 in Excel
- PERCENTILE function - The function returns the kth percentile in Excel
May be interested
MODE.SNGL function - Function that returns the most frequently occurring, or the most repeated values in an array or data range in Excel
NORM.S.INV function - The function returns the inverse of the normalized distribution with an average value of 0 and a standard deviation of 1 in Excel
PERCENTRANK.EXC function - The function returns the rank of a value in a dataset as a percentage excluding values 0 and 1 in Excel
PERCENTRANK.INC function - The function returns the rank of a value in a dataset as a percentage including values 0 and 1 in Excel
PROB function - The function returns the probability that values in a range are between two limits in Excel
RANK.EQ function - Function returns the rank of a number in a list of numbers, returns the highest rank when multiple values with the same rank in Excel