Hit the equal sign key to tell Excel you are about to enter a function. You can select up to 5 rows (10 cells) and get even more statistics, but we usually only need the first six.

Since it is an array function, select 6 cells (2 columns, 3 rows). The images below and the following text summarize the mechanics of using LINEST in Excel. (As an aside, in physics we would rarely force the y-intercept to be zero in the fit even if we expect it to be zero because if the y-intercept is not zero, it may reveal a systematic error in our experiment.) Notice that the slope of the fit will be equal to 1/k and we expect the y-intercept to be zero. Therefore, s is the dependent variable and should be plotted on the y-axis. That makes F the independent value and it should be plotted on the x-axis.

Let’s assume that since you control the force used, there is no error in this quantity. Because linear regression aims to minimize the total squared error in the vertical direction, it assumes that all of the error is in the y-variable. Hooke’s law states the F=-ks (let’s ignore the negative sign since it only tells us that the direction of F is opposite the direction of s). You systematically varied the force exerted on the spring (F) and measured the amount the spring stretched (s). Let’s say you did an experiment to measure the spring constant of a spring.

The LINEST function performs linear regression calculations and is an array function, which means that it returns more than one value. To find these statistics, use the LINEST function instead. The equation for the fit can be displayed but the standard error of the slope and y-intercept are not give. In Excel, you can apply a line-of-best fit to any scatterplot.

Excel linear regression intercept 0