HomeGoogle DocsSpreadsheetHow to Use the TREND Function in Google Sheets

How to Use the TREND Function in Google Sheets

Published on

The TREND function in Google Sheets is the best tool in simplistic forecasting. It returns values along a linear trend using the least squares method. With the help of the TREND function, you can predict demands.

The TREND is an array formula. So it returns an array result (result in a range) without using the function ARRAYFORMULA. I mean no need to wrap the TREND formula in Google Sheets with the ARRAYFORMULA function.

Syntax:

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

Here is one example to Google Sheets TREND function, then we can go to the function arguments.

The below example shows the details of sales over a period of 9 months from January to September and the trend.

The formula is in cell C2. See the chart for the straight trendline along the sales.

linear trend in Google Sheets

In the above TREND formula (see the 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 – It is the range containing (already known) dependent (y) values (sales value in the range B2: B10), used to curve fit an ideal linear trend.

known_data_x – It’s the values of the independent variable(s) (the month in the range A2: A10) corresponding with known_data_y.

I’ll explain to you the rest of the function arguments with the below example.

Trend Function in Google Sheets to Analyze Sales Over a Time to Predict Future Demand

In the above example, I have the sales data from January to September. I want to forecast the upcoming month’s sales. See how to do that in Google Sheets with the TREND function.

I mean I want to forecast the sales for the months from October to December. See that months trend below in the range B11: B13.

Trend to Analyze Sales Over a Time Period in Google Sheets

Here I’ve used the full function syntax. If you want to know more details about the Syntax, refer to this Google Sheets help article.

The TREND function works well on horizontal as well as vertical data range. Unlike some other function, there is no need to use the TRANSPOSE with this function.

See the above same data below but aligned horizontally. Here also the TREND formula works flawlessly without any additional data formatting.

TREND function in horizontal dataset

That’s all about the TREND function in Google Sheets. Enjoy!

Similar:

  1. How to Use the FORECAST Function in Google Sheets.
  2. How to Use the GROWTH Function in Google Sheets.
  3. Dynamic Data Ranges in Growth, Trend, and Forecast in Google Sheets.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

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