Microsoft Excel Help: Calculate A Trendline Forecast

Problem: You have historical sales data by month, as shown in Fig. 391. You want to predict future sales by month. How do you start and what pitfalls do you need to watch out for?

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.

Note: Click any of the images shown below to see a larger view.

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()


Fig. 391
Fig. 392
Fig. 393
Fig. 394
Fig. 395
Fig. 396
Fig. 397
Fig. 398

References and Additional Resources

If you're looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel. This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, and even how to play games like Craps in Excel. If you can't find the solution to your problem, leave a note in our Comments section and we'll try to help point you in the right direction.

Other Resources:

Microsoft Excel Official Site,

Bill Jelen, Microsoft Excel 2010 In Depth, Available from