How to Use FORECAST and FORECAST.LINEAR Functions in Google Sheets to Predict Future Values

Forecasting future metrics based on historical performance is a cornerstone of business planning, financial modelling, and capacity management. Whether anticipating quarterly revenue, project resource demands, or website traffic, linear regression provides an objective, mathematical baseline. Google Sheets offers two native functions for linear trendline projection: FORECAST and FORECAST.LINEAR. Both functions use the method of least squares to fit a straight line across existing observations and project future values accurately.

Understanding FORECAST vs FORECAST.LINEAR

In Google Sheets, FORECAST and FORECAST.LINEAR perform identical underlying mathematical calculations. FORECAST represents the classic spreadsheet formula, while FORECAST.LINEAR was introduced to ensure strict syntax interoperability with modern standards and Excel workbooks.

The mathematical equation underpinning both functions is the classic linear formula:

\[y = a + bx\]

Where:

• \(x\): The independent variable (e.g., future month, marketing spend, or user count).
• \(y\): The predicted dependent value (e.g., expected revenue or server load).
• \(b\): The slope of the best-fit regression line.
• \(a\): The y-intercept where the regression line crosses the vertical axis.

Function Syntax and Parameter Breakdown

Both functions share the exact same parameter structure:

=FORECAST(x, data_y, data_x)
=FORECAST.LINEAR(x, data_y, data_x)

• x: The target data point along the independent axis for which you wish to predict a value. This can be a future date, sequential time index, or numerical input.
• data_y: The range or array containing known dependent historical observations (e.g., historical sales figures in column B).
• data_x: The range or array containing known independent historical values corresponding to data_y (e.g., historical month numbers or dates in column A).

Step 1: Preparing Your Dataset in Google Sheets

To avoid calculation errors, structure your data in clear adjacent columns without text headings interspersed between numbers. For example, consider this 6-month sales record:

• Column A (Month Index, data_x): Cells A2:A7 containing values 1, 2, 3, 4, 5, 6.
• Column B (Monthly Revenue in £, data_y): Cells B2:B7 containing values 12500, 14200, 15800, 17100, 18900, 20400.
• Target (Month 7, x): Placed in cell A8.

Step 2: Calculating Single Future Estimates

To calculate the expected revenue for Month 7:

1. Click on cell B8.
2. Enter the following formula:

=FORECAST(A8, B$2:B$7, A$2:A$7)

Or alternatively, using the modern syntax:

=FORECAST.LINEAR(A8, B$2:B$7, A$2:A$7)

Press Enter. Google Sheets calculates the slope and intercept across the six historical periods and outputs approximately £21,980.

Step 3: Projecting Multi-Period Forecasts with ArrayFormula

If you want to project multiple future months (e.g., Months 7 through 12 in cells A8:A13) simultaneously without dragging individual formulas down, wrap the calculation in ARRAYFORMULA:

=ARRAYFORMULA(FORECAST.LINEAR(A8:A13, B2:B7, A2:A7))

Google Sheets dynamically populates cells B8:B13 with projected monthly values in a single calculation step.

Step 4: Handling Dates as the Independent Variable

Google Sheets stores dates as serial numbers, making it easy to forecast directly against calendar dates:

=FORECAST.LINEAR(DATE(2026, 12, 31), B2:B50, A2:A50)

Ensure that both the historical date range (A2:A50) and the target date are formatted as standard Date cells (via Format > Number > Date) to prevent string misinterpretation.

Handling Common Spreadsheet Errors

• #N/A Error: Triggered when the ranges for data_y and data_x do not contain the exact same number of rows or columns. Verify range boundaries (e.g., both must span Row 2 to Row 7).
• #DIV/0! Error: Occurs when the variance of data_x is zero (all independent values in the historical range are identical, making slope calculation impossible). Ensure data_x contains distinct values.
• #VALUE! Error: Appears when non-numeric text labels are included inside the numerical ranges.

Validating Predictive Accuracy in Google Sheets

While FORECAST.LINEAR generates a straight-line mathematical trajectory, real-world data often displays non-linear seasonal fluctuation. To validate whether a linear model suits your dataset, calculate the coefficient of determination (\(R^2\)) using the RSQ function:

=RSQ(B2:B7, A2:A7)

An \(R^2\) value close to 1.0 confirms strong linear correlation, indicating that your FORECAST.LINEAR estimates provide reliable planning baselines.