See all Microsoft Excel tips
Gotcha: When you select two cells for the LINEST function, they must be side by side. If you try to select two cells that are one above the other, you will just get two copies of the slope.
Alternate Strategy: A different method is to use the INDEX function to pluck a specific answer from the array.
=INDEX(LINEST(C2:C35),1,1) will return the first element from the array, as shown in Fig. 398. This is the slope.
=INDEX(LINEST(C2:C35),1,2) will return the second element from the array. This is the y-intercept.
Summary: The LINEST
function will automate the process of performing a least-squares method to fit a line to a series of actual sales. Because the function returns multiple values, you have to use care when entering. Either enter it in multiple cells with the Ctrl+Shift+Enter key combination or use the INDEX function to extract values.
Functions Discussed: =LINEST(); =INDEX()
See all Microsoft Excel tips