TREND Function in Excel

Trend Function in Excel

Trend function in Excel is a Statistical Function that computes the linear trend line based on the given linear set of data. It calculates the predictive values of Y for given array values of X and uses the least square method based on the given two data series. The Trend function in Excel returns numbers in a linear trend matching known data points that is the existing data on which the trend in excel predicts the values of Y dependent on values of X needs to be linear data.

What is the least square method?

It is a technique used in the regression analysis that finds the line of best fit (is a line through a scatter graph of data points that foremost indicates the relationship between those points) for a given dataset, which helps to visualize the relationship between the data points.

Syntax

Below is the TREND Formula in Excel.

TREND Formula in Excel

Arguments

For the given linear equation, y = m*x + c

Known_y’s: It is a required argument that represents the set of y-values that we already have as existing data in a dataset that follows the relationship y = mx + c.

Known_x’s: It is an optional argument that represents a set of x-values that should be of the equal length as the set of known_y’s. If this argument is omitted, the set of known_x’s takes the value (1, 2, 3 … so on).

New_x’s: It is also an optional argument. These are the numeric values that represent the new_x’s value. If the new_x’s argument is omitted, it is set to be equal to the known_x’s.

Const: It is an optional argument that specifies whether the constant value c to equal 0. If const is TRUE or omitted, c is calculated normally. If false, c is taken as 0 (zero), and the values of m are adjusted so that y = mx.

How to Use TREND Function in Excel?

TREND function in excel is very simple and easy to use. Let us understand the working of the TREND function by some examples.

You can download this TREND Function Excel Template here – TREND Function Excel Template

Example #1

In this example, suppose we have data for test scores with their GPA, now using this given data, we need to make the prediction for the GPA. We have the existing data in column A and B, the existing values of GPA corresponding to scores is the known values of Y, and the existing values of the score are the known values of X. We have given with some values for X values as a score, and we need to predict the Y values that is the GPA based on the existing values.

Existing Values:

Trend Function in Excel - Example 1

Given Values and Values of Y to be predicted:

Trend Function in Excel - Example 1-1

In order to predict the values of the GPA for given test scores in cell D2, D3, and D4, we will use the TREND function in excel.

The TREND formula in excel will take the existing values of known X and Y, and we will pass the new values of X to calculate the values of Y in cell E2, E3, and E4.

The TREND formula in excel will be:

=TREND($A$2:$A$16,$B$2:$B$16,D2)

We fixed the range for known values of X and Y and passed the new value of X as a reference value. Applying the same TREND formula in excel to other cells, we have

TREND Example 1-2

Output:

TREND Example 1-3

So, using the TREND function in excel above, we predicted the three values of Y for the given new test scores.

Example #2 – Predicting the Sales Growth

So in this example, we have existing sales data of a company for the year 2017 that increases linearly from Jan 2017 to Dec 2017. We need to figure out the sales for the given upcoming months. That is, we need to predict the sales values based on the predictive values for the last one year data.

The existing data contains the dates in column A and the sales revenue in column B. We need to calculate the estimated sales value for the next 5 months. Historical Data is given below:

Example 2

In order to predict the sales for the given upcoming months in the next year, we will use the TREND function in excel since the sales value is increasing linearly, the given known values of Y is the sales revenue, and the known values of X are the end dates of the month, the new values of X are the dates for next 3 months that is 01/31/2018, 02/28/2018 and 03/31/2018, and we need to compute the estimated sales values based on historical data given in range A1:B13.

The TREND formula in excel will take the existing values of known X and Y, and we will pass the new values of X to calculate the values of Y in cell E2, E3, and E4.

The TREND formula in excel will be:

=TREND($B$2:$B$13,$A$2:$A$13,D2)

We fixed the range for known values of X and Y and passed the new value of X as a reference value. Applying the same TREND formula in excel to other cells we have,

Example 2-1

Output:

Example 2-2

So, using the TREND Function above, we have predicted the estimated sales values for the given upcoming months in cell D2, D3, and D4.

Things to Remember

  1. The existing historic data that contains the known values of X and Y needs to be linear data that, for the given values of X, the value of Y should fit the linear curve y=m*x + c. Otherwise, the output or the predicted values may be inaccurate.
  2. The TREND function in excel generates #VALUE! Error when the given known values of X or Y is non-numeric, or the value of new X is non-numeric and also when the const argument is not a Boolean value (that is TRUE or FALSE)
  3. The TREND function in excel generates #REF! Error known values of X and Y are of different lengths.

TREND Function in Excel Video

Recommended Articles

This has been a guide to TREND Function in Excel. Here we discuss the TREND Formula in excel and how to use the TREND function in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *