A Y-intercept is a point on the line when x is equal to zero. It is more generally used in the slope-intercept form of the line. Let’s learn, how to find the Y-intercept of a line of a graph in excel. Among many different forms of a line, the most commonly used line equation is the slope-intercept form. The graph of the linear equation y = mx+c where m is the slope and c is the intercept. The slope m represents the angle made by the line with the X-axis. It is defined as m = tanθ, where θ = Angle made by line with X-axis. The intercept c is the y coordinate of the line when x is equal to zero.
Note: From the point of view of data analysis, we consider intercept as the starting point of our data because most of the real time variables represented on X-axis are always greater than 0.
Trend Line
It is the best fit line for a given data set. Whenever we plot the data points on the graph, there are very less chances of getting a perfect linear line. To resolve, this issue we use linear regression. In this method, we draw the best fit line for the given set of data. This linear regression helps predict the future values of the dependent variables. The best fit line is also called as a trend line.
Finding Y-intercept using Excel
Excel functions and graphs are awesome tools to find the slope and intercept of the given set of data points. It’s possible that the data points might not show a linear relationship always, so it uses the trend line or the best fit line to tell the slope and intercept of the given data set. Now, the significance of the trend line is more understandable. There are two methods by which we can find the Y-intercept of the given data points in excel:
1. Using = INTERCEPT() Function
One of the best methods to calculate the intercept is to use the =INTERCEPT() function in excel. The function returns the intercept of the best fit line of the given data set.
Syntax = INTERCEPT(y-values, x-values)
Step 1: Given a data set, of x and y values. Calculating the intercept of the given data.
Step 2: In cell D13, type = INTERCEPT() to calculate the intercept of the given data.
Step 3: In the first parameter of the intercept() function, select the range D4:D10, these are the known y values of the data set.
Step 4: In the second parameter of the intercept() function, select the range C4:C10, these are the known x values of the data set.
Step 5: Your intercept() function is completed. Press Enter.
Step 6: The intercept comes out to be 4.22 for the given data set.
2. Using Line Graph
A line graph representing the given data can also be used to find the intercept of the given data set. Excel provides functionality to display the equation of the best fit line of the chart. The equation of the line used here is y = mx+c, where c is, the intercept of the line. Following are the steps:
Step 1: Given a data set, of x and y values. Calculating the intercept of the given data.
Step 2: Select the range of y values, D5:D11, to make a chart corresponding to it.
Step 3: Click on the Insert tab. Under the Charts section, select the line chart.
Step 4: A-line chart appears corresponding to the given data.
Step 5: Select the obtained line. Right-click on it.
Step 6: A drop-down appears. Click on the Add Trendline…
Step 7: Format Trendline dialogue box appears on the right-most side of the window.
Step 8: Scroll down and Check the box Display Equation on Chart. With this, the best fit line and the equation of the best fit line appear inside the chart.
Step 9: A dotted blue line appears inside the chart. We can also see the equation of line i.e. y = 1.2821x + 4.2286 where, slope(m) = 1.281 and intercept(c) = 4.2286.