What Is TREND Function in Google Sheets?
The TREND function in Google Sheets is a forecasting tool that predicts future trends based on supplied historical values. It uses the least squares method to predict future values, fit a linear trend to data, and calculate parameters for the future trend. It is useful for making informed decisions about future values. In finance, it can be used to forecast financial metrics, like revenue, profit, and expenses, with historical data.
In the example below, you can observe that we have calculated the trends for January to April using the following Google Sheets TREND formula:
=TREND(B2:B5, A2:A5)
Key Takeaways
- The TREND function in Google Sheets predicts future values based on historical data by performing linear regression to calculate a trend line.
- It uses that line to forecast future trends.
- The syntax of the TREND function is as follows:
- =TREND(data_y, data_x, [new_xs], [const])
- data_y: Known dependent values.
- data_x: Known independent variable values.
- new_xs (optional): These are the new independent variable values for which we predict the dependent variable values.
- const (optional): This is a logical value (TRUE or FALSE).
- It helps forecast trends in business, sales, etc. which rely on data analysis.
- It can handle both simple linear regression and multiple regression (multiple independent variables).
Syntax
Before we proceed with the TREND function in Google Sheets, let us first study its syntax.
The syntax for the TREND function is:
=TREND(known_data_y, [known_data_x], [new_data_x], [b])
- known_data_y: The array or range of (y) values. They are dependent variables.
- known_data_x: The values corresponding with known_data_y which are independent variable(s)
- new_data_x: An optional array of new x-values for which you want to predict y-values.
- b: An optional Boolean parameter that determines whether to include the y-intercept of the trendline in the calculation. It calculates b if TRUE or makes it 0 if FALSE
How to Use TREND Function in Google Sheets?
The TREND formula Google Sheets is commonly used in trend analysis and forecasting.
Here, we will see how to use the TREND function with two sets of data: known Y’s and known X’s with stepwise explanation. You have some sales data, as shown below. The sales figures will be the known Y’s, and the months are your known X’s. We use TREND to calculate the relationship between these two variables. Let us use this data to predict the sales for months 7 and 8.
Step 1: Select the cell where you wish to make the prediction. Here, we choose B9.
Step 2: Enter the following formula: =TREND(B2:B7, A2:A7, A9:A10)
- B2:B7 is the known Sales (dependent variables).
- A2:A7 is the months (independent variables).
- A9:A10 are the independent variables sales for July and August.
Step 3: Press Enter, and the predicted value for the next two month will appear.
Remember, both ranges must be the same size otherwise it will lead to errors.
Using the Google Menu bar
- First, go to the cell where you wish to enter the TREND formula.
- In the Google menu bar, click on “Insert” – “Function” – “ARRAY” – “TREND.”
- Enter the required arguments. Close the bracket and press the “Enter” key.
Examples
The TREND function in Google Sheets calculates a linear trend based on historical data. It is very useful for predicting future values. Let us look at some interesting examples below.
Example #1
Below is an example of how to use the TREND function in Google Sheets to predict expenses based on the data supplied. Here, we have a student’s monthly expenses for the last few months and wish to forecast their expenses for the next three months based on this trend.
Step 1: Enter the data as shown in the table below.
Step 2: We use the TREND function to predict the expenses for months 7,8,9. Let us use the following formula in cell B10.
=TREND(B2:B7, A2:A7, A10:A12)
- B2:B7 is the Actual Expenses.
- A2:A7 are the month numbers.
- A10:A12 are the month numbers for which we will predict the expenses.
Step 3: After entering the formula, TREND in Google Sheets calculates the predicted expenses for the next three months based on the linear trend.
Example #2 – Forecast sales for the upcoming months
We have the sales details of a store with the dates in Column A and sales amount in $ in Column B.
Step 1: To predict sales for September through December, use the following formula in cell B11 to return the forecasted sales for those months.
=TREND(B1:B8, A1:A8, A10:A13)
Step 2: Press Enter. You can see the predicted sales for the remaining months of the year.
0
Example #3 – TREND Function with Multiple Variables
There are times when a single variable isn’t enough to predict trends. You might need to use multiple variables for accurate predictions or forecasts. Let us look at one such example, where we use TREND with multiple variables.
We are trying to predict sales based on the month and the money spent on promotions. Here, we have two sets of independent variables, the months and marketing campaign amounts, and one dependent variable, the sales.
Let us put these details in a table.
Step 1: Suppose we want to calculate the trend of sales based on both the month and the amount spent on marketing campaigns run that month.
Use the formula as shown below.
In column D2, enter the following:
Use the formula: =TREND(C2:C7,A2:B7).
Step 2: Press Enter, and your sales trends will appear.
In this formula, {A2:B7} represents the two sets of known X’s. By including both variables, TREND calculates accurately considering multiple factors.
It is a complex approach, with special care being taken to check the relationships between the variables.
The TREND function uses historical data to establish a linear relationship between Marketing Campaigns, Months, and Sales.
Important Things to Note
- Not just months, you could use days, weeks, or years as dependent variables. But the data should be in a consistent order and accurate interval.
- The TREND function assumes the relationship between the independent and dependent variables is linear and can be helpful in making simple forecasts.
- Always, the dependent variable is a single column of data.
- In the case of multiple variables, you’ll be performing multivariate linear regression, where the dependent variable is affected by more than one independent variable.
Frequently Asked Questions (FAQs)
The TREND function is straightforward if you pay attention to the relationship between the dependent and independent variables. However, you may get the following errors:
#REF! Error: This error occurs when the known Y’s and known X’s ranges are not the same in size. Always ensure that both ranges are of equal length.
#VALUE! Error: It occurs when your data contains non-numeric values. Sometimes we make the mistake of giving data such as months as text. Always ensure that your data is numeric.
If your predictions seem off, check whether your data is linear. The TREND function is designed for linear relationships.
1. The TREND function in Google Sheets allows you to perform multivariate linear regression to forecast values based on more than one independent variable.
2. It is very useful when multiple factors influence a dependent variable (e.g., sales).
3. The TREND function can be used to extend the trendline into the future, which helps forecast future values.
4. The function can be combined with AVERAGE to calculate the average predicted value and with the IF function to create conditional forecasts.
“Trend” and “forecast” are similar concepts with a slight difference. Let us look at the difference between the same.
• Trends represent current or past periods. For example, we review sales numbers from recent years to determine the cash flow trend, which helps us understand our business’s growth and current performance.
• As mentioned in the article’s beginning, the syntax of the TREND function is
=TREND(data_y, data_x, [new_xs], [const])
• Forecast is about to the future. For example, by analysing the historical data, you can project future changes and predict where the trends seem to be going.
• Syntax: =FORECAST(x, data_y, data_x)
x: The data point for which you want to predict a value
data_y: The known dependent values
data_x: The known independent values
Download Template
This article must help understand TREND Function in Google Sheets with itsformula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is TREND Function in Google Sheets. We learn its syntax & how to use TREND Function in Google Sheets with examples and working template. You can learn more from the following articles. –
Leave a Reply