How to Use the FORECAST Function in Google Sheets

The FORECAST or FORECAST.LINEAR function in Google Sheets helps users predict a future value based on historical data.

To find multiple future values, you can use the TREND function. While TREND can also predict a single future value, it is generally more efficient for predicting multiple future values.

FORECAST is often simpler to use for predicting a single future value. However, it can predict multiple future values when used with ARRAYFORMULA.

The TREND and FORECAST functions use the Least Squares method to determine the linear regression line, ensuring accurate predictions. As a result, the output of both functions is identical.

Linear regression is a statistical technique used to understand how a dependent variable (commonly represented as y) is related to one or more independent variables (represented as x).

Example of the FORECAST function and corresponding sales prediction chart

FORECAST Function: Syntax and Example Formulas

Syntax:

FORECAST(x, data_y, data_x)

Note: There is also a new function called FORECAST.LINEAR, which has the same syntax as FORECAST. Google Sheets introduced this new function to ensure compatibility with other spreadsheet applications that use it in their newer versions.

FORECAST.LINEAR(x, data_y, data_x)

Whenever I refer to FORECAST, you can assume it’s the same as FORECAST.LINEAR.

Arguments:

You can easily understand the arguments of the FORECAST function with the help of a sample dataset. Let’s consider the following sample data in the range A1:B:

AB
MonthSales
1/1/20186
1/2/20187.5
1/3/20188
1/4/20189
1/5/20188.2
1/6/201811.5
1/7/201813.45
1/8/201816
1/9/201817.05
1/10/2018[Value to be forecasted]
  • x: The value on the x-axis to forecast. In this example, we want to forecast the value for October, so x is A11.
  • data_y: The y-axis values from the historical data, which is the range B2:B10.
  • data_x: The x-axis values from the historical data, which is the range A2:A10.

The formula to find the expected y-value for October will be:

=FORECAST(A11, B2:B10, A2:A10) // returns 17.71

Array Formula Usage

To forecast future y-values for October, November, and December, you can use the FORECAST function as an array formula:

=ARRAYFORMULA(FORECAST(A11:A13, $B$2:B10, $A$2:A10))

Assume A11, A12, and A13 contain 1/10/2018, 1/11/2018, and 1/12/2018, respectively.

However, I recommend using the TREND function as follows:

=TREND(B2:B10, A2:A10, A11:A13)

Syntax:

TREND(known_data_y, [known_data_x], [new_data_x], [b])
FORECAST Array Formula example with TREND function alternative

Real-life Example of the FORECAST Function in Google Sheets

In the following example, we use the FORECAST function with time-based (historical) data, such as past sales over a period, to predict future sales.

The sample data is the same as before: the first column contains month start dates from January to December, and the second column contains sales data from January to September. In this case, we labeled the third column as ‘Predicted.’

In cell C11, insert the following FORECAST formula and drag it down to C13:

=FORECAST(A11, $B$2:$B$10, $A$2:$A$10)
Column chart displaying sales forecast in Google Sheets

Let’s create a column chart using this data.

To create the chart, select A1:C13 and click Insert > Chart. In the Chart Editor sidebar, choose ‘Column’ as the chart type.

The blue horizontal bars represent historical sales data, while the red bars represent the forecasted data.

That concludes the explanation of the FORECAST function in Google Sheets.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.