Microsoft Excel: Calculate A Trendline Forecast

Article by Mr Excel (11,387 pts ) , published Jul 1, 2008

Problem: You have historical sales data by month, as shown in Fig. 391. You want to predict future sales by month.

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
                                                                                                                                                                                                                                                                                                        

Images

Fig. 391Fig. 392Fig. 393Fig. 394Fig. 395Fig. 396Fig. 397Fig. 398
 
Subscribe to Home Office
RSS
Get free weekly updates, directly to your inbox.