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.
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.
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.
That’s all about the TREND function in Google Sheets. Enjoy!
Similar:
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.