How to Use the TREND Function in Google Sheets

The TREND function in Google Sheets is an excellent tool for simple forecasting. It returns values along a linear trend using the least squares method. With the TREND function, you can predict future data points, such as demands, based on existing trends.

As a side note, the TREND function is an array formula that can return multiple results in a range without the need to use the ARRAYFORMULA function.

TREND Function: Syntax and Basic Example

Syntax:

TREND(known_data_y, [known_data_x], [new_data_x], [b])

Let me explain the arguments using formula examples.

Below is an example of the TREND function in Google Sheets. It calculates a trend line based on sales data over 9 months (from January to September) using the formula:

=TREND(B2:B10, A2:A10)
Linear Trend Line in Google Sheets

The accompanying chart shows a straight trendline, which illustrates the linear relationship of sales over time.

This trendline is created using the least squares method, as applied by the TREND function with the formula =TREND(B2:B10, A2:A10).

The method fits a straight line to your existing data points in B2:B10 (sales data) and A2:A10 (corresponding months). It minimizes the sum of the squared differences between the actual data points and the values on the trend line, providing the best fit for the given data.

To make predictions, you need to include new x-values (new_data_x) in the formula. In the above TREND formula (see cell C2 in the image), I’ve used only part of the arguments. What are they?

TREND(known_data_y, [known_data_x])

Here are the details:

  • known_data_y: This range, B2:B10, contains the known dependent (y) values (sales data) used to fit an ideal linear trend.
  • known_data_x: These are the values of the independent variable (the months in the range A2:A10) that correspond to the known_data_y.

I’ll explain the remaining function arguments with the example below.

Analyzing Sales Over Time and Predicting Future Demand with the TREND Function

In the above TREND function example, I have sales data from January to September, and I want to forecast sales for the upcoming months. Let’s see how to do that in Google Sheets using the TREND function.

I want to predict sales for the months of October through December. The following formula in cell B11 will return the forecasted sales for those months:

=TREND(B2:B10, A2:A10, A11:A13)
Trend Analysis of Sales Over Time in Google Sheets

Where:

  • known_data_y: B2:B10 (the sales data for January to September)
  • known_data_x: A2:A10 (the corresponding months)
  • new_data_x: A11:A13 (the months for which we want to forecast sales)

In this example, I haven’t specified the last argument, which is b, so the default value is assumed, i.e., TRUE.

What does this mean?

When b is omitted or set to TRUE, the formula calculates the trend line with a y-intercept that best fits the data. If b is set to FALSE, the trend line is forced to start from the point where the x-axis and y-axis meet (the origin).

Here’s a table illustrating the effect of specifying b in the TREND function:

MonthActual SalesForecasted Sales (b=TRUE)Forecasted Sales (b omitted)
1/1/201811
1/2/201822
1/3/201833
1/4/201844
1/5/201855
1/6/201866
1/7/201877
1/8/201888
1/9/201899
1/10/201855.25110.20
1/11/201855.28121.44
1/12/201855.32132.32

Resources

Here are some related tutorials for Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

1 COMMENT

  1. This was really helpful!

    Can you please also share when it’s wise to use this formula vs Growth formula?

    I tried using the Growth formula but, it did not work in some cases because certain values were at 0.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.