Google Sheets FORECAST Function (+ Examples)

Financial forecasting is as popular as ever, but the tools have certainly improved. If you’ve ever had to spend time on linear regressions, you’ll be happy to meet the Google Sheets FORECAST function. The function will take your historical data and return the predicted future value, so you can use it to analyze trends in your financial data. If your data shows exponential growth, you may want to consider using the GROWTH formula instead.

In this article, you will learn about the FORECAST function and some similar functions available in Google Sheets. You will also learn how to use the formula to extrapolate future sales and expenses values based on historical data.

Google Sheets FORECAST Function

The FORECAST function can be used to calculate future y-values, based on known x- and y-values. Linear regression is used to find the best fit for existing data and calculate future values based on this relationship. You can get the same results using TREND, which is an array formula.

You may have noticed that there is a FORECAST.LINEAR formula in Google Sheets. Using this will get you the same results as using FORECAST or TREND, as they all use the least squares method to find the best fit.

FORECAST Formula Syntax

The syntax for the FORECAST function is the following:

=FORECAST(x, data_y, data_x)

• x represents the value we want to forecast (y) on the x-axis.
• data_y represents the array of existing values for the dependent variable.
• data_x represents the array of existing values for the independent variable.

There are some important points to keep in mind when using the FORECAST formula:

• x values must be numerical or you’ll get a “#VALUE!” error.
• data_y and data_x must have the same dimensions or you’ll get an “#N/A” error.

Use FORECAST to Calculate Projected Values

First, you’ll see a practical example of FORECAST to calculate the projected value for sales in a given year based on data from previous years. Second, you’ll learn how to apply this formula for projected expenses for a trimester based on the previous three.

Since FORECAST is not an array formula, this will require an extra step.

Use FORECAST to Calculate Projected Sales

In this example, FORECAST is used to predict the value for 2022, based on the known sales values for the years 2016 to 2021. Each column should contain data corresponding to the year value and the sales.

1. 1. In Google Sheets, click on the cell where you want the projected sales value to appear.

1. 2. Type in the FORECAST formula as shown below.

1. 3. Select the value of x for which you want the predicted value. Add a comma to move to the following argument.

1. 4. Select the cells containing data_y, the known values for the dependent variable. Then include all the cell values from sales. Add a comma to move to the last argument in the formula.

1. 5. Select the cells containing data_x, the known values for the independent variable. Here, cell values correspond to the year. Close the parenthesis and press “Enter”.

1. 6. That’s it. The projected value for your chosen year should appear as shown below.

Use FORECAST to Calculate Projected Expenses

Now, you’ll learn how to use FORECAST and ARRAYFORMULA to get the projected expenses for the next three months, based on the known values for the last nine months.

1. 1. Open the historical data you want to use in Google Sheets.

1. 2. Select the cells where you want the projected expenses to appear.

1. 3. Type in the ARRAYFORMULA, as shown below.

1. 4. After the parenthesis, type FORECAST and open a new parenthesis.

1. 5. Instead of selecting just one cell for the value of x, select the array. Add a comma to move to the second argument of the formula.

1. 6. Select the cells containing data_y, the known values for the dependent variable. Add a comma to move to the last argument in the formula.

1. 7. Select the cells containing data_x, the known values for the independent variable. Here, select the month values of the first three trimesters.

1. 8. Remember to close both parentheses before pressing “Enter”.

1. 9. That’s it. The projected expenses for the next three months are shown below.

Conclusion

The FORECAST formula in Google Sheets can be used to predict future values based on historical data. As with any prediction of the future, it shouldn’t be taken too literally, as it assumes that the relationship between the variables will remain unaffected in the future. However, if you have historical data that shows a linear trend and you don’t foresee big changes in the near future, the FORECAST formula can be very useful.

After reading this article, you know about the FORECAST function and how to use it to calculate projected yearly sales and monthly expenses. You’ve also learned how to use the ARRAYFORMULA to obtain multiple predicted values from FORECAST.

To learn more about financial formulas and forecasting, check out these related articles:

• How To Use Goal Seek in Google Sheets
• How to Use GOOGLEFINANCE Function in Google Sheets