How To Plot X Intercept In Excel

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More than...Less

This article describes the formula syntax and usage of the INTERCEPT role in Microsoft Excel.

Clarification

Calculates the indicate at which a line will intersect the y-axis past using existing x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values. Utilize the INTERCEPT function when yous desire to determine the value of the dependent variable when the independent variable is 0 (zero). For example, you can use the INTERCEPT function to predict a metal'south electrical resistance at 0°C when your data points were taken at room temperature and higher.

Syntax

INTERCEPT(known_y'southward, known_x'due south)

The INTERCEPT part syntax has the following arguments:

Known_y's Required. The dependent prepare of observations or data.
Known_x's Required. The independent ready of observations or data.

Remarks

The arguments should be either numbers or names, arrays, or references that incorporate numbers.
If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value naught are included.
If known_y's and known_x's incorporate a different number of data points or incorporate no data points, INTERCEPT returns the #Northward/A error value.
The equation for the intercept of the regression line, a, is:

where the slope, b, is calculated as:

and where x and y are the sample means Boilerplate(known_x's) and Boilerplate(known_y's).
The underlying algorithm used in the INTERCEPT and SLOPE functions is dissimilar than the underlying algorithm used in the LINEST part. The difference between these algorithms can atomic number 82 to different results when data is undetermined and collinear. For example, if the data points of the known_y's statement are 0 and the data points of the known_x's argument are 1:
- INTERCEPT and Slope return a #DIV/0! mistake. The INTERCEPT and Gradient algorithm is designed to look for one and only 1 answer, and in this case there tin can be more than than one answer.
- LINEST returns a value of 0. The LINEST algorithm is designed to render reasonable results for collinear information, and in this example at to the lowest degree one reply tin exist found.

Example

Copy the instance data in the following tabular array, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the cavalcade widths to see all the data.

Known y	Known 10
2	6
3	v
9	11
1	7
8	five
Formula	Description	Result
=INTERCEPT(A2:A6, B2:B6)	Point at which a line will intersect the y-axis past using the x-values and y-values to a higher place	0.0483871