The Excel TREND Function

Home » Excel-Built-In-Functions » Excel-Trend-Function

Search this site:

Custom Search

Equation for a Straight Line

The Excel Trend Function finds the linear trend by calculating the line of best fit for the equation

y = mx + b

This is the simple equation for a straight line, where,

- x is the independent variable
- y is the dependent variable
- m is the slope (gradient) of the line
- b is a constant which is the value of y when x = 0

The Excel TREND function calculates the trend line through a given set of y-values and (optionally), a given set of x-values. The function then extends the linear trendline to calculate additional y-values for a further supplied set of new x-values.

The format of the function is :

TREND( Known_y's, [Known_x's], New_x's, [Const] )

Where the arguments are as follows :

Known_y's	-	A set of known y-values
[Known_x's]	-	A set of known x-values. This is an optional argument which, if provided, should have 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	-	A set of new x-values, for which the function calculates corresponding new y-values
[Const]	-	An optional logical argument that determines how the constant 'b' is treated in the straight-line equation y = mx + b If Const is TRUE (or if this argument is omitted) the constant b is treated normally; If Const is FALSE the constant b is set to 0 in the equation y = mx + b

As the Trend function returns an array of values, it must be entered as an Array Formula.

Array Formulas:

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel Array Formulas page for more details.

Example

The spreadsheet below shows a simple example of the Excel Trend Function being used to extend a series of x- and y-values that lie on the straight line y = 2x + 10. These are stored in cells A2 - B5 and are also shown in the graph.

The Trend function uses the least squares method to find the straight line that has the best fit for the provided known x- and y- values. In this simple example, the line of best fit is the straight line y = 2x + 10.

Once Excel has calculated the straight line equation, it can use this to calculate the new y-values for the provided new x values.

Example of use of the Excel Trend Function

In this example, the values of the New_x's are stored in cells A8 - A10 and the Excel Trend function has been used, in cells B8 - B10, to find the corresponding new y values. The equation for this, as shown in the formula bar, is :

=TREND( B2:B5, A2:A5, A8:A10 )

It is seen that the Trend function in the formula bar is encased in curly braces { }. This indicates that the function has been input as an Array Formula.

Note that, although the points in the example fit along the straight line y = 2x + 10, this is not essential. The Excel Trend function will find the line of best fit for any set of values provided to it.

Return to the Excel Built-In Functions page

Information Functions

Date & Time Functions

Lookup & Reference Functions

Math Functions

Statistical Functions

Database Functions