TREND function is a built-in function in excel which comes under the category of Statistical function to calculate the linear trend line of known y’s and know x’s. TREND function uses the “Least square method to find the line of best fit and to find new y-value for the given x-values. This function is used to predict the future based date where it will be very useful in accounting and financial reporting.
Syntax:= TREND(known_y’s, [known_x’s], [new_x’s], [const])
The TREND function syntax has the following arguments:
- Known_y’s (required argument) – This is the set of y-values we already know in the relationship y = mx + b.
- Known_x’s (optional argument) – This is a set of x-values. If we provide the argument, it should be of the same length as the set of known_y’s. If omitted, the set of [known_x’s] takes on the value {1, 2, 3, …}.
- New_x’s (optional argument) – This provides one or more arrays of 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 (optional argument) – This specifies whether to force the constant b to equal 0. If const is TRUE or omitted, b is calculated normally. If false, b is set equal to 0 (zero) and the m-values are adjusted so that y = mx.
Example: Let’s look at some Excel TREND function examples and explore how to use the TREND function as a worksheet function in Microsoft Excel:
Example 1:
Suppose we wish to build a forecast or extrapolate the future revenue of a company. The data set given is shown below:
To calculate future sales, we will use the TREND function. The formula to use will be:
Syntax: =TREND(B2:B10,A2:A10,A11:A13)
Result: 141665.9195
150386.7031
159398.1795
Here the Trend function with the next month sales has been shown below.
This will return an array of values for y, as the x values to be calculated for are an array of x’s. So, it must be entered as an array formula, by highlighting the cells which need to be filled, input your function into the first cell of the range, and press CTRL-SHIFT-Enter
Example 2:
Let’s see another example with graphical chart format using multiple values.
The below example shows months wise sales data where there are ups and downs in sales value if we produce this format we cannot the view the sales data easily, Hence we can show the sales data in using trend function in chart format so that I will be given the appropriate result.
In the sales data A1 represents Month, B1 represents actual sales and C1 represent Trend values.
We are going to using the trend function here to get the result, select the column C2 to C17 and use the trend function as per below.
After selection uses the Trend function by selecting C2: C17and then we have to use CTRL+SHIFT ENTER to get the entire trend value which is shown below.
we get the line chart graph as given below where it shows very clearly sales data month wise
Note:
- #REF! error – Occurs if the known_x’s array and the new_x’s array are of different lengths.
- #VALUE! error – Occurs when either:
- Non-numeric values provided in the known_y’s, [known_x’s] or [new_x’s]
- The [const] argument provided is not a logical value.
