What Is FORECAST In Google Sheets?
The FORECAST in Google Sheets helps users predict a future value based on existing values. It does the future value predictions using linear regression and users can use it to calculate future sales and consumer requirements. The Google Sheets FORECAST function predicts future y-values based on a set of known x-y values to determine the relationship between the known x and y values.
For example, we have the dataset below with x and y values. We will calculate the y value for a new x value, not in the list, using the FORECAST() function.
Select cell E1, enter the formula =FORECAST(D2,B2:B14,A2:A14) and press “Enter”, as shown below.
The output is 96.12, rounded to 2 decimal points. The x value 20 falls in-between the values 19 and 21, in cells A11 and A12. And therefore, the forecasted value is a value derived between the corresponding values 85 and 104, from the cells B11 and B12.
Key Takeaways
- The FORECAST in Google Sheets forecasts or predicts a future value or multiple values using the existing data based on the linear regression concept. We can mathematically calculate the same using the formula “y = a + bx”.
- The function has three mandatory arguments as inputs that are the x, data_x and data_y. Then, we will forecast the new y-value for x, given the known x- and y-values which are nothing but the independent and the dependent values, respectively.
- Ensure to organize the dataset so that the cell range of the x and the y values are same. Because if it is not, then we will be unable to derive the forecasted output and the formula will throw an error.
- The x argument value must always be a numeric value. Also, the data_x must never be a 0.
- Users can use the functionto predict monthly and yearly future sales figures and inventory requirements.
Syntax
The syntax of the FORECAST Formula in Google Sheets is,
The three mandatory arguments of the FORECAST Google Sheets Formula are,
- x: It is a numeric x-value considered to forecast or predict the y-value using the FORECAST().
- data_y: It is the dependent data range, the y values.
- data_x: It is the known independent data range, the x values.
How To Use FORECAST Function In Google Sheets?
We can use the FORECAST in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon
Step 1: Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow – click the “Statistical” option right arrow – select the “FORECAST” function, as shown below.
Step 2: The “FORECAST” formula appears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the formula in the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type =FORECAST( in the cell, as shown below. [Alternatively, type =F or =For and double-click the FORECAST from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some FORECAST in Google Sheets examples to derive one or multiple future values, and to forecast the sales and expenses.
Example #1 – FORECAST One Future Value
Consider the below table that consists of known x- and y-values. We will FORECAST One Future Value, here 35, using the existing independent values.
The steps to FORECAST One Future Value, 35, using the FORECAST function are,
Step 1: Select cell B13 and enter the formula =FORECAST(35,B2:B11), as shown below.
[Note: We can enter the first argument x, as a direct value or a cell reference.]
Step 2: Now, enter the x-value cell range in the formula and close the brackets. The complete formula is =FORECAST(35,B2:B11,A2:A11), as shown below.
Step 3: Press “Enter”, to view the forecasted output.
Example #2 – FORECAST Multiple Future Values
In the given dataset, we have the data of customers visiting a store in the first week of July 2024. Based on this data we will FORECAST Multiple Future Values, here, for the next immediate week.
The steps to FORECAST Multiple Future Values for the 2nd week of July 2024 are,
Step 1: Select cell C9 and enter the formula=FORECAST(A9,$B$2:$B$8,$A$2:$A$8), as shown below.
[Note: We have entered the cell range in the formula with the $ sign indicating that it is an absolute reference, i.e., the input cell ranges are fixed.]
Step 2: Press “Enter” and drag the formula from cell C9 to C15 using the fill handle, to get the following results.
Therefore, we FORECAST Multiple Future Values for the 2nd week of July 2024, using the 1st week of July 2024 data.
Example #3 – Use FORECAST to Calculate Projected Sales
The company data given below consolidates sales data on the 15th of every month. The below table shows the sales data for 2023, and now, we need use FORECAST to Calculate Projected Sales for the first six months of 2024.
The steps to Calculate Projected Sales for the first six months of 2024 are,
Step 1: Select cell C14 and enter the formula =FORECAST(A14,$B$2:$B$13,$A$2:$A$13), as shown below.
Step 2: Drag the formula from cell C14 to C19 using the fill handle, to get the following results.
Example #4 – Use FORECAST to Calculate Projected Expenses
We will use FORECAST to Calculate Projected Expenses for the given dataset, that consists of year 2019 to 2024 income. We have the expenses from 2019 to 2023 and we must calculate the forecasted expense of the year 2024.
The steps to use the FORECAST to Calculate Projected Expenses are,
Step 1: Select cell C7 and enter the formula =FORECAST(B7,C2:C6,B2:B6), as shown below.
Step 2: Press “Enter”, to get the forecasted expense for the year 2024, as shown below.
Important Things To Note
- The FORECAST() and the FORECAST.LINEAR() helps us achieve the same forecast results.
- For a non-numeric argument x, the FORECAST() output will be the #VALUE! error.
- If the arguments data_y or data_x is blank or empty in the dataset or the array sizes of the two data ranges are different in the formula, then we will get the #N/A error.
- If the data_x is zero, we will get the #DIV/0! error.
Frequently Asked Questions (FAQs)
The FORECAST in Google Sheets may not work for the following reasons:
a. We have provided a non-numeric x.
b. One of the supplied array ranges, data_x or data_y, is empty.
c. The supplied array ranges as the second and third arguments in the FORECAST() are of different lengths.
We often forget in which category a function falls, here, the “FORECAST” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “FORECAST” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the FORECAST in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “FORECAST” function, as shown below.
We can use FORECAST function in Google Sheets to predict future sales figures, inventory requirements, exam scores, expenditures, and measurements.
The mathematical equation to represent the FORECAST Excel function is:
y = a + bx
where,
y : The new y-value we need to determine as the forecast for the x-value.
a = ymean – bxmean
b = ∑(x – xmean)(y – ymean) / ∑(x – xmean)2
xmean and ymean : The mean values of the known x and y-value ranges.
Download Template
This article must be helpful to understand the FORECAST in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is FORECAST In Google Sheets. We learn its syntax and how to use it to predict a future value with detailed examples. You can learn more from the following articles. –
Leave a Reply