How to Use Forecast and Trend in excel

Mar 17, 2020 • edited Mar 18, 2020

If you have historical time-based data, you can use it to create a forecast. When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.


What Is a Forecast?

A forecast predicts something for the future. For example, the expectations of the future numbers in terms of company growth can be called a forecast. The forecast function, in short, is used to calculate the straight lines that are based on known X and Y values. The values of Y can be calculated for the values of X inside or outside the range of the X values.

What Is a Trend?

A trend is something that represents a particular status of something currently happening. For instance, there is a trend for the youth of today’s generation to be rather glued to their smartphones. OK, maybe not all of them, but it is definitely a trend!


When you add a trendline to an Excel chart, Excel can display the equation in a chart (see below). You can use this equation to calculate future sales. The FORECAST and TREND function give the exact same result.

Trendline in Excel

Explanation: Excel uses the method of least squares to find a line that best fits the points. The R-squared value equals 0.9295, which is a good fit. The closer to 1, the better the line fits the data.

  1. Use the equation to calculate future sales.

Equation

  1. Use the FORECAST function to calculate future sales.

Forecast Function

Note: when we drag the FORECAST function down, the absolute references ($B$2:$B$11 and $A$2:$A$11) stay the same, while the relative reference (A12) changes to A13 and A14.

  1. If you prefer to use an array formula, use the TREND function to calculate future sales.

Trend Function

Note: first, select the range E12:E14. Next, type =TREND(B2:B11,A2:A11,A12:A14). Finish by pressing CTRL + SHIFT + ENTER. The formula bar indicates that this is an array formula by enclosing it in curly braces {}. To delete this array formula, select the range E12:E14 and press Delete.

#Tutorial#How To#Functions

How to Remove Spaces in Excel

How to use Index Match in Excel