In this tutorial, I will discuss 4 suitable methods to find the x-intercept in Excel.
Download Practice Workbook
Download the following workbook for your practice from the following link.
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.
4 Methods to Find x-Intercept in Excel
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.
- 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.
- Use the following formula in Cell C18 to find the x-intercept of this Covid-Mask data.
- 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 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.
- First off, 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).
3. Using LINEST Function to Find x-Intercept
- Here is the formula to use the LINEST function to get the x-intercept.
- Put this formula in Cell C19 and press ENTER.
- And you will get an intercept in Cell C20 (and a slope in Cell C19).
4. Using Data Analysis ToolPak
The fourth option is using the Data Analysis ToolPak of Excel. To know more details about this tool, click on the following article: How to Use Data Analysis ToolPak in Excel.
- 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.
- 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.
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. And for more Excel-related articles, visit our blog. Thank you!