In this tutorial, I will discuss 4 suitable methods to find the x-intercept in Excel.
What Is x-Intercept?
If a straight line intersects X-axis at (2,0), the x-intercept will be 2. So, you can say that the x-intercept is the x-ordinate of a point on a straight line, where the line intersects the X-axis.
How to Find X Intercept for Linear Data in Excel
We will consider a linear equation (y = 3x – 7). To be specific, it is a straight-line equation. Here, we will find a value of X when y = 0.
First, choose cell C12 >> apply the following formula >> hit Enter.
=INTERCEPT(B6:B10,C6:C10)Now, plot a Scatter with Smooth Lines and Markers chart to get a better understanding.
- The INTERCEPT function finds the point at which a line intersects the y-axis. It uses known x-values and known y-values.
- Note that, the INTERCEPT function considers the intersection at the y-axis which means it calculates the y-intercept.
- So, when you calculate the x-intercept with the INTERCEPT function, your y-values will be INTERCEPT’s x values and your x-values will be INTERCEPT’s y-values.
How to Find x-Intercept in Excel: 5 Suitable Methods
Let’s consider the following data consists of monthly Covid cases (set as Y-values) and monthly use of masks (set as X-values).
We have to know which percentage of people are still using masks when the number of covid cases becomes zero. Excel has many functions and features to find the x-intercept. Of them, we will see 4 in the next sections.
1. Using INTERCEPT Function to Find x-Intercept
If you just need to know the x-intercept, then using the INTERCEPT function of Excel is the fastest method.
🔀 Steps:
- Use the following formula in Cell C18 to find the x-intercept of this Covid-Mask data.
=INTERCEPT(C5:C16,D5:D16)
- Here, your x-values=C5:C16 ⇒ y-values in the formula.
- And your y-values=D5:D16 ⇒ x-values in the formula.
- So, we get the x-intercept = 90.16% (after applying the percentage format of Excel).
🔎 Interpretation of x-Intercept:
- x-Intercept means the y value is set to zero. In our case, the y-value is covid cases.
- So, this result means: when the use of masks is 90.16%, the covid cases will be zero.
- Or, when the covid cases tend to zero, the use of masks among people is still 90.16%.
2. Using FORECAST.LINEAR Function to Find X-Intercept
Here, we will use FORECAST.LINEAR to find X Intercept for the above dataset.
🔀 Steps:
- First, choose cell D19 >> apply the following formula >> hit OK.
=FORECAST.LINEAR(D18,C5:C16,D5:D16)
When calculating X Intercept using the FORECAST.LINEAR function, we must switch between the known_ys and know_xs values. So, use People Using Masks (X) as known_ys and Covid Cases (Y) as know_xs.
3. Using the Trendline Equation Feature of Excel Graph
You can also find the x-intercept by using the Trendline Equation feature of the Excel graph created from the dataset you have. Follow the steps below to do this.
🔀 Steps:
- First, go to the Insert tab and click on the Scatter icon and select the first suggested icon.
- A blank graph canvas will appear.
- Now, click on the Chart Area canvas and then right-click on your mouse. Then click on Select Data… option.
- From the Select Data Source window, press the Add button.
- The Edit Series pop-up will appear.
- From this window, give a suitable name for the series.
- Then choose X values. Note that, like the previous method, Excel’s X values are equivalent to our Y values here.
- Similarly, choose the Y values.
- Then, press OK.
- Now we will be back to the Select Data Source window again.
- Notice that the Calculate x-Intercept (the name we gave) series appears and is selected automatically.
- Finally, press the OK button.
- You are done with making the graph, a couple more steps to get the result now.
- Click on the Chart Elements button (a + sign you see in the image below).
- Then unmark the Gridlines for a better view.
- After that, select the Trendline option and click on the angle icon (〉) just beside it.
- Select More Options.
- A window named Format Trendline will appear.
- From this window, click the 3rd icon (when hovering your mouse, you will see its name is Trendline Options).
- Then select the Linear radio button.
- Lastly, mark the ‘Display Equation on the chart’ checkbox.
- Get back to the graph.
- You will see that an equation appears in the chart area.
- The equation is:
- So, finally, we get the desired result, our x-intercept is 0.9016 (90.16%, when formatted as a percentage).
Read More: How to Set Intercept Trendline in Excel
4. Using LINEST Function to Find x-Intercept
The LINEST function is another option to find the x-intercept in Excel. This function is a combination of SLOPE and INTERCEPT functions. It returns slope and intercepts as well with a single click.
🔀 Steps:
- Here is the formula to use the LINEST function to get the x-intercept.
=LINEST(C5:C16,D5:D16)- Put this formula in Cell C19 and press ENTER.
- And you will get an intercept in Cell C20 (and a slope in Cell C19).
Read More: How to Find Intercept of Two Lines in Excel
5. Using Data Analysis ToolPak
The fourth option is using the Data Analysis ToolPak of Excel.
🔀 Steps:
- To enable the Data Analysis ToolPak, press ALT+F+T and get to the Excel Options window.
- Then go to the Add-ins section and select Excel Add-ins from the Manage drop-down.
- Then click on the Go button.
- A new window will pop up named Add-ins.
- Mark the Analysis ToolPak checkbox and press OK.
- Now you will have this feature on your Excel ribbon.
- Go to the Data tab and click on Data Analysis from the Analysis group.
- From the Data Analysis options, select Regression and press OK.
- The Regression window will pop up.
- Select Input Y Range first, remember that you have to select your X-values here.
- Similarly, select Input X Range.
- Finally, press OK.
- You will be back to the Regression window again.
- Select where to place the Output Range (in our case, the location is Cell F4).
- Then press OK.
- The regression summary will be generated by ToolPak.
- From this report, you will get the x-intercept along with other statistics.
- In the following image, we have highlighted the x-intercept.
Read More: How to Find Y Intercept in Excel
Quick Notes
- You must remember that all through the article, we have considered the X values of our data as the known_ys (and vice versa for Y values) of the Excel function or graph tool or the Data Analysis tool.
- Use the INTERCEPT and Trendline Equation methods if you just need to know the x-intercept quickly.
Download Practice Workbook
Download the following workbook for your practice from the following link.
Concluding Words
So that’s it for today. I hope you have enjoyed this article and can easily find the x-intercept now. If you face any problems, then leave us a comment. Thank you!
<< Go Back to Excel INTERCEPT Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hey, these 4 methods to determine an x-intercept for a data set are wrong. You have to determine the x incept for a given line by solving the equation of a line for x and setting y=0. None of these methods provide and even close to accurate value for a data sets linear regression line’s x-intercept. READER BEWARE.
Hello Dr Timber
I went through this article and did not find any technical issues. In my view, you should reconsider what you have said.
This article is about finding an X intercept. To demonstrate this point, this context considers a practical dataset where the percentage of people still using masks is calculated when the number of Covid Cases becomes Zero. I think you expected the calculation to find X Intercept for Y = 0. That’s what is calculated in those methods. When the writer introduced us to the dataset, it was explicitly mentioned.
Anyway, I am introducing another method that may overcome your confusion. I have calculated the percentage of people who still use masks when the number of Covid Cases becomes Zero. I am using FORECAST.LINEAR Function to predict the percentage. And I get the same result shown in this article.
Excel Formula:
The data considered in this article is not Linear. That’s why you get confused. However, these methods must be able to find an X intercept for any Linear data.
Stay safe. And good luck!
Regards
Lutfor Rahman Shimanto