What Is FORECAST.LINEAR Function in Google Sheets?
The FORECAST.LINEAR function in Google Sheets is used to predict a future value along a straight line based on existing data. It applies linear regression to determine the relationship between a set of known values and then uses that relationship to estimate a value for a new input. It helps in identifying trends and making forecasts when the data follows a linear pattern. The function requires three arguments: the x-value to predict, a range of known y-values (dependent variable), and a range of known x-values (independent variable).
For example, suppose we have the number of products sold over the past three months: January (100), February (150), and March (200). Le t us try to predict the sales in April, which would be the fourth month. Use the following formula =FORECAST.LINEAR(4, B1:B3, A1:A3). Here, A1 to A3 contain the months and contain the sales numbers.
The function will return an estimated sales number for the fourth month based on the trend in the previous data.
Syntax
Let us look at the syntax of the function.
=FORECAST.LINEAR(x, data_y, data_x)
Arguments:
- x – The data point (independent variable) for which you want to predict a corresponding value.
- data_y – The dependent variable (known y-values). This is what you’re trying to predict.
- data_x – The independent variable (known x-values). This is the basis for prediction.
How To Use FORECAST.LINEAR Function in Google Sheets?
The FORECAST.LINEAR function is used to predict a future value based on existing data that follows a linear trend. For example, it can help estimate future sales based on past monthly performance. It can also predict temperature based on historical data. We use this function in business forecasting, trend analysis, and projecting values wherever there is a linear relationship between the variables.
There are two ways to use this function:
- Enter FORECAST.LINEAR in Google Sheets manually
- Through the Google menu bar
Enter FORECAST.LINEAR in Google Sheets Manually
Let’s walk through an example to understand how to use this function.
Step 1: Go to an empty sheet and enter the data. In column A, we enter the known values, here it is the months in numbers. In column B, enter the temperature values. These are the average temperatures of a city logged month wise.
Step 2: Click on the cell where we must get the forecasted result. Here, type in the following formula based on its syntax:
=FORECAST.LINEAR(7, B2:B7, A2:A7)
For this, first enter the = sign, followed by the name of the function. Open the parentheses and enter the arguments in the order specified in the syntax above. Close the parentheses.
Here, 7 is the x-value you want to predict for (the 7th month), B2:B7 is the range of known y-values of temperature, and A2:A7 is the range of known month values.
Step 3: Press Enter. The result is the predicted y-value for the given x-value based on the linear trend in the existing data.
Using the Menu Bar
- Select the cell where you want the result to appear.
- Go to Insert → Function → Statistical → FORECAST.LINEAR.
- Fill in the inputs for the arguments: x-value to predict, known y-values, and known x-values.
- Press Enter to see the forecasted result.
Examples
Let’s explore a few examples to see how FORECAST.LINEAR in Google Sheets applies in everyday situations. These examples demonstrate its practical use in making predictions based on past data trends.
Example #1 – Predict Sales for the Next Month Based on the Past Year’s Data
In this example, consider a business that tracks its monthly sales for five months. We have entered the sales and month details in a sheet, as shown below. They want to predict sales for June using the linear forecast function.
Step 1: Enter the data where in column A, the months as numbers are in the form of number. 1 for January, 2 for February, and so on. In column B, input the sales values in dollars.
Step 2: Click on cell B7 and enter the following formula:
=FORECAST.LINEAR(A7, B2:B6, A2:A6)
Step 3: Press Enter. Google Sheets calculates the predicted sales for June, which is $30900.
The result shows a steady upward trend in sales, helping the business plan inventory more effectively.
Example #2 – Forecast Future Weather Patterns Using Historical Temperature Data
In this forecast linear in google sheets example, let us consider a climate researcher who wants to estimate the average temperature for July based on the rising trend of temperatures from March to June. The trend recorded from March to June were entered in a sheet. Assuming the trend continues linearly, they want to forecast the temperature for July.
Step 1: Open a Google Sheet and enter the data. In column A, input the months as numbers: 1 for April, 2 for May, and 3 for June. In column B, input the corresponding average temperatures.
Step 2: Click on cell an empty cell and enter the formula:
=FORECAST.LINEAR(A8, B2:B7, A2:A7)
Step 3: Press Enter. Google Sheets calculates the forecasted average temperature for July (month 4), which is 24°C.
The result indicates a consistent warming trend, helping the researcher can predict the seasonal temperature planning.
Example #3 – Estimate Future Expenses Based on Past Spending Patterns
An person tracks their monthly utility expenses to budget for the upcoming month. They recorded expenses for the month of January, February, and March. Observing a consistent rise, they want to estimate the expected utility cost for April.
Step 1: Open a Sheet and enter the data, as shown below.
Step 2: Click on cell C2 (or any empty cell) and enter the formula:
=FORECAST.LINEAR(4, B2:B4, A2:A4)
Step 3: Press Enter. Google Sheets will calculate the forecasted expense for April (month 4), which is $150.
The result helps them plan their monthly budget more accurately by projecting expenses based on previous trends.
Important Things to Note
- If one has the sales data for the past few months, they can use FORECAST.LINEAR in Google Sheets to predict future sales based on the existing trend.
- The length of the known_x’s array should be the same as that of the known_y’s. Also, the variance of the known_x’s must not be zero.
- If either of the columns of x or y is empty or one has more data points than the other, FORECAST.LINEAR return the #N/A error value.
- The function can handle zero and negative values if the overall trend is linear.
Frequently Asked Questions (FAQs)
Let us look at some errors which can occur when we use the function in Google Sheets.
• N/A error – This happens if the supplied known_x’s and the known_y’s arrays are of different lengths.
• #DIV/0! error – It is seen if the variance of the known_x’s is equal to zero.
• #VALUE! error – It occurs when the future value is not numeric.
Google Sheets trend vs forecast comparison is as follows. Both the functions predict future values based on existing data. However, FORECAST.LINEAR returns a single predicted value for a specific x-value. TREND however returns multiple predictions and is often used to fill a series. TREND is better for generating full trend lines, while FORECAST.LINEAR is ideal for quick, single-point forecasts
If the relationship between x and y is not linear, the function might produce inaccurate results. An important characteristic of the function is that it assumes a constant rate of change. Hence, visually or statistically, the data should follow a roughly straight-line trend before applying the function.
Download Template
This article must help understand FORECAST.LINEAR Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is FORECAST.LINEAR Function in Google Sheets. We learn the how to use it to predict a future value along a straight line with examples. You can learn more from the following articles. –
Leave a Reply