Equation for a Straight Line
The Excel Trend Function finds the linear trend by using the least squares method to calculate the line of best fit for a supplied set of y and x values.
If there is a single range of xvalues, the calculated line satisfies the simple straight line equation:
where,
If there are multiple ranges of xvalues, the line of best fit satisfies the following equation:
where,
The Excel TREND function calculates the linear trend line through a given set of yvalues and (optionally), a given set of xvalues.
The function then extends the linear trendline to calculate additional yvalues for a further supplied set of new xvalues.
The syntax of the function is:
Where the function arguments are as follows:
known_y's    An array known yvalues. 
[known_x's]    One or more arrays of known xvalues. 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]    An optional argument, providing one or more arrays of numeric values representing a set of new xvalues, for which you want to calculate the corresponding new yvalues. Each array of [new_x's] should correspond to an array of [known_x's]. If the [new_x's] argument is omitted, it is set to be equal to the [known_x's]. 
[const]    An optional logical argument that specifes whether the constant 'b', in the straightline equation y = mx + b, should be forced to be equal to zero. 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 and the straight line equation becomes y = mx. 
The Trend Function as an Array Formula:
If more than one new yvalue is to be calculated by the Excel Trend function, the new values will be returned as an array. Therefore, the function must be entered as an Array Formula (see the examples below).
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 CTRLSHIFTEnter.
See the Excel Array Formulas page for further details.In the spreadsheet below, the Excel Trend Function is used to extend a series of x and yvalues that lie on the straight line y = 2x + 10. The known x and yvalues are stored in cells A2B5 of the spreadsheet, and are also shown in the spreadsheet graph.
Note that, it is not essential that the supplied points fit exactly along the straight line y = 2x + 10, (although they do in this example). The Excel Trend function will find the line of best fit for any set of values provided to it.
The Trend function uses the least squares method to find the line of best fit and then uses this to calculate the new yvalues for the provided new xvalues.
In this example, the values of the [new_x's] are stored in cells A8A10 and the Excel Trend function has been used, in cells B8B10, to find the corresponding new yvalues. As shown in the formula bar, the formula is:
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.
In the above spreadsheet on the right, the Excel Trend Function is used to extend a series of yvalues that correspond to three sets of xvalues.
The known xvalues are stored in cells A2C6 and the known yvalues are stored in cells D2D6 of the spreadsheet.
The set of new xvalues is stored in cells A9C11 and the Excel Trend function, which has been entered into cells D9D11, is used to find the corresponding new yvalues.
As shown in the formula bar, the formula for the Trend function is:
Again, it can be seen that the Trend function in the formula bar is encased in curly braces { }, showing that it has been input as an Array Formula.
For further examples of the Excel Trend function, see the Microsoft Office website.
If you get an error from the Excel Trend function, this is likely to be one of the following:
#REF!    Occurs if the [known_x's] array has a different length to the known_y's array. 
#VALUE!    Occurs if either:
