You can use the function to find the length of the seasonal pattern. Enter the value 49 into cell C13, select the range A1:C17 and insert a scatter plot with straight lines and markers.ģ. The default value of 1 indicates seasonality is detected automatically.Ģ. The fourth argument indicates the length of the seasonal pattern. The FORECAST.ETS function below predicts a future value using Exponential Triple Smoothing. The FORECAST.ETS function in Excel 2016 or later is a great function which can detect a seasonal pattern.ġ. This equation predicts the same future values. Note: when you add a trendline to an Excel chart, Excel can display the equation in a chart. Enter the value 89 into cell C11, select the range A1:C14 and insert a scatter plot with straight lines and markers. Repeat step 3 afterwards.Explanation: when we drag the FORECAST.LINEAR 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.Ģ.

In this case change the cell reference type of the 'known x' and 'known y' values as described in step 2, using F4. Note: If you receive a #VALUE! error, check if the 'known x' and/or 'known y' values were auto-filled incorrectly - you may have used relative references when setting up the formula. Select the first value of the new trendline and copy the function by using Excel's Auto Fill feature, dragging the fill handle in the cell's lower right corner to the right until the cell of the last value is selected as well. This way the selection will stay exactly the same when this formula is copied and will not be adjusted according to the new position. Note: By pressing F4 the cell reference type of the selected cells will change to absolute cell references. Select the first x value (e.g., 'B1') and press ENTER. Select all 'known x' values and press F4 (e.g., '$B$1:$D$1'). To see the correct character for your region refer to the Excel tooltip appearing while you enter the formula).

(Note that the character expected by Excel as an arguments separator depends on your Windows regional settings. Select all 'known y' values and press F4 (e.g., '$B$3:$D$3').Įnter Excel's arguments separator, e.g., ',' (comma). Type ' =TREND(' or use the Insert Function ( fx) menu in Excel. Open the internal datasheet and add a new series, e.g., 'Trendline 1'.Ĭalculate the first value of the trendline using the TREND function: To create a trendline in a line chart, add a new series to the line chart, then calculate its values to form a trendline, e.g., by using the TREND function of Excel:

Trendline excel equation