Open In App

How to Find the Y-intercept of a Line of a Graph in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

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. 

Dataset

 

Step 2: In cell D13, type = INTERCEPT() to calculate the intercept of the given data. 

Using-intercept()

 

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. 

Selecting-range

 

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. 

Selecting-all-x-values

 

Step 5: Your intercept() function is completed. Press Enter

Pressing-enter

 

Step 6: The intercept comes out to be 4.22 for the given data set. 

Intercept-found

 

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. 

Dataset

 

Step 2: Select the range of y values, D5:D11, to make a chart corresponding to it.  

Selecting-data

 

Step 3:  Click on the Insert tab. Under the Charts section, select the line chart

Selecting-chart

 

Step 4: A-line chart appears corresponding to the given data. 

Chart-appears

 

Step 5: Select the obtained line. Right-click on it. 

Selecting-line

 

Step 6: A drop-down appears. Click on the Add Trendline…

Adding-trendline

 

Step 7: Format Trendline dialogue box appears on the right-most side of the window. 

Formatting-trendline

 

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. 

Displaying-equation-on-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.  

Equation-appears

 


Last Updated : 02 Jun, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads