The FORECAST function in Google Sheets helps users to find a future value based on existing values. These existing values are normally called historical data. Does the FORECAST formula limit to find future value only, not future values?
To find future values you can use the function TREND, which is an array function. FORECAST is a statistical function that only returns one single value. But with the help of an ARRAYFORMULA, you can use the FORECAST function similar to the TREND function.
Both the functions TREND and FORECAST follow the Least Squares method, so the result would be identical.
FORECAST Function in Google Sheets – Syntax and Formula Examples
Syntax:
FORECAST(x, data_y, data_x)
You can very easily understand the FORECAST function arguments if you refer to the above image.
Arguments in the FORECAST Function in Google Sheets:
x – It is the value on the x-axis to forecast. In the above example, we have the value up to the month of September.
The x-axis value that we want to forecast is for the month of October, November, and December.
In normal use, you can select the cell A11 as x. In an array, you can extend the selection up to A13 like A11: A13.
data_y – It’s the dependent data representation in an array. In our example, it’s the y-axis value in the range B2: B11.
data_x – It’s the independent data representation in an array. It’s the range A2: A11 in our example.
See the below screenshot to understand how to use the FORECAST function in Google Sheets in Normal (non-array) and as well as in Array uses.
Also, I have included the TREND function for a comparison of prediction.
The TREND function and the FORECAST in array return the same result.
Purpose of the FORECAST Function in Google Sheets
As its name denotes, you can use Google Sheets FORECAST function in time-based (historical) data, like past sales over a period, to predict/forecast the future sales.
In the above example, just move the formula in E11 to C11 to plot a column chart with historical as well as predicted sales data as below.
That’s all about the FORECAST Function in Google Sheets. Hope you have enjoyed the stay.
Related Reading:
Hello, I’m looking for sequence prediction. How can I use this for that?