How to Find the x-Intercept in Excel – 5 Methods

 

What Is the x-Intercept?

Representation of x-intercept in Excel graph

The x-intercept is the x-ordinate of a point on a straight line, where the line intersects the X-axis. If a straight line intersects the X-axis at (2,0), the x-intercept will be 2.


How to Find the X Intercept for Linear Data in Excel

Consider a linear equation (y = 3x – 7). To find the value of X when y = 0:

  • Select C12 >> use the following formula >> press Enter.
=INTERCEPT(B6:B10,C6:C10)
  • Plot a Scatter with Smooth Lines and Markers chart.

Finding X Intercept for Linear Data

Important Notes
  • The INTERCEPT function finds the point at which a line intersects the y-axis. It uses known x-values and known y-values.

INTERCEPT function syntax

  • The INTERCEPT function considers the intersection at the y-axis, which means it calculates the y-intercept.
  • When you calculate the x-intercept with the INTERCEPT function, your y-values will be INTERCEPT x values and your x-values will be INTERCEPT y-values.

How to Find the x-Intercept in Excel – 5 Methods

The dataset showcases monthly Covid cases (set as Y-values) and monthly use of masks (set as X-values).

You want to know what is the percentage of people using masks when the number of covid cases becomes zero.


Method 1 – Using the INTERCEPT Function to Find the x-Intercept

Use the INTERCEPT function.

 Steps:

  • Use the following formula in C18 to find the x-intercept.
=INTERCEPT(C5:C16,D5:D16)

Using INTERCEPT Function to Find x-Intercept

Your x-values=C5:C16y-values in the formula.

Your y-values=D5:D16x-values in the formula.

The x-intercept = 90.16% (after applying the percentage format).

 Interpretation of the x-Intercept:

  • The y value (covid cases) is set to zero.
  • When the use of masks is 90.16%, covid cases will be zero.
  • Or, when the covid cases tend to zero, the use of masks is still 90.16%.

Method 2 – Using the FORECAST.LINEAR Function to Find the X-Intercept

Steps:

  • Select D19 >> enter the following formula >> click OK.
=FORECAST.LINEAR(D18,C5:C16,D5:D16)

Use of FORECAST.LINEAR Function to find X Intercept

Notes

When calculating X Intercept using the FORECAST.LINEAR function, you must switch between the known_ys and know_xs values: People Using Masks (X) as known_ys and Covid Cases (Y) as know_xs.


Method 3 – Using the Trendline Equation Feature of Excel Graphs

Steps:

  • Go to the Insert tab and click Scatter.
  • Select the first icon.

approaching to create a blank graph background in Excel

  • A blank graph canvas will be displayed.
  • Click the Chart Area canvas and right-click.
  • Click Select Data… option.

  • In the Select Data Source window, click Add.

  • In Edit Series, name the series (Calculate x-Intercept).

selecting X and Y values of the series

  • Choose the X values. (Excel’s X values are equivalent to Y values here.)

select y values from x coordinates in Excel to get the x intercept

  • Choose the Y values.
  • Click OK.

 

  • The Calculate x-Intercept series is displayed in the Select Data Source window and automatically selected.
  • Click OK.

 

  • Click the Chart Elements button (the + sign in the image below).
  • Uncheck Gridlines.
  • Select Trendline and click the angle icon ().
  • Select More Options.

  • In Format Trendline, click the 3rd icon (Trendline Options).
  • Select Linear.
  • Check ‘Display Equation on the chart’.

  • Go back to the graph.
  • An equation is displayed in the chart area:
y=-0.0001x+0.9016
  • This is the output: the x-intercept is 0.9016 (90.16%, when formatted as a percentage).

Using the Trendline Equation Feature of Excel Graph to find x-intercept

Read More: How to Set Intercept Trendline in Excel


Method 4 – Using the LINEST Function to Find the x-Intercept

Use the LINEST function in a combination of the SLOPE and the INTERCEPT functions.

Steps:

  • Use the formula in C19:
=LINEST(C5:C16,D5:D16)
  • Press ENTER.

Using LINEST Function to Find x-Intercept

  • The intercept is displayed in C20 (and a slope in C19).

Read More: How to Find Intercept of Two Lines in Excel


Method 5 – Using the Data Analysis ToolPak

 Steps:

  • To enable the Data Analysis ToolPak, press ALT+F+T and go to the Excel Options window.
  • Select Add-ins and choose Excel Add-ins in Manage.
  • Click Go.

Going to Excel add-ins in Excel Options window

  • In Add-ins, check Analysis ToolPak.
  • Click OK.

Enabling Analysis ToolPak to get x-intercept and other data

  • The feature is displayed on the ribbon.
  • Go to the Data tab and click Data Analysis in Analysis.

  • In Data Analysis, select Regression and click OK.

  • In the Regression window, Enter the Input Y Range (remember you have to select your X-values here).
  • Enter the Input X Range.
  • Click OK.

  • In the Regression window, select a location to place the Output Range (here, F4).
  • Click OK.

Using Regression method of Analysis ToolPak to find x-intercept

  • The regression summary will be generated by ToolPak. You will see the x-intercept and other statistic data.

Read More: How to Find Y Intercept in Excel


Download Practice Workbook

Download the following workbook.


<< Go Back to Excel INTERCEPT Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

2 Comments
  1. 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.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Aug 10, 2023 at 12:41 PM

      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:

      =FORECAST.LINEAR(D18,C5:C16,D5:D16)

      Finding X Intercept with Article Dataset

      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.

      X Intercept with Linear Data

      Stay safe. And good luck!

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo