How to Find the Y-Intercept of a Line of a Graph in Excel on a Mac
By Richard Gaughan
Linear relationships abound. Ten customers in your store will buy roughly twice as much as five customers. A service call five miles away will take about five times as much gasoline as a call one mile away. Staying open for three night-time hours will cost about three times as much in electricity as staying open for one night-time hour. All of those relationships can be described with linear equations. One of the most common and convenient forms of a linear equation is y = m*x +b, where m is the slope of the line and b is the y-intercept. If you've got data, but aren't sure how to put it in that linear form, Excel for the Mac can help.
Open a new spreadsheet and type "X" into cell A1 and "Y" into cell A2.
Enter your x values into column A starting in cell A2. Enter the corresponding y values into column B, starting in cell B2. Remember that each x value will have a corresponding y value matched to just that value.
Select all the x-values, from cell A2 down the column to the end of the x values you've just entered in. Choose "Insert," "Name" and then "Define" from the menu bar. The dialogue box will come up with "X", which you had entered above the data. Accept that name by clicking "OK," which will let you refer to all your x values using just the name "X".
Repeat Step 3 for the y values in column B, except the B column dialogue box will offer you the name "Y," which you should accept.
Now "X" and "Y" refer to the data you've entered for x and y values.
Click in cell D2 and type "=Intercept(Y,X)" without quotes. The value Excel computes will be the y-intercept.
Select the cells containing the x and y values.
Select the Charts toolbar, select the "Scatter" option and then select the "Marked Scatter" option. A graph will appear with the x and y values shown as points.
On the toolbar for Excel for Mac, select "Layout 9" from the "Quick Chart Layouts" option. This will draw a "best fit" line through the data points. It will also write an equation on top of the graph.
Extract the y-intercept value from the equation displayed on the graph. The equation will be in the form "y = m*x +b" where m is a number corresponding to the slope and b is a number corresponding to the y-intercept.
Remember, you type an equals sign as the first character in a cell where you want to enter a formula.
You can also have Excel compute the slope analytically as well; just type "=slope(Y,X)" in whichever cell you'd like to have display the slope of the line that best fits your data.
First published in 1998, Richard Gaughan has contributed to publications such as "Photonics Spectra," "The Scientist" and other magazines. He is the author of "Accidental Genius: The World's Greatest By-Chance Discoveries." Gaughan holds a Bachelor of Science in physics from the University of Chicago.