TREND Function

Basic Description

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.

Syntax: TREND( known_y’s, [known_x’s], [new_x’s], [const] )

Where the arguments are as follows :

known_y’s An array known y-values
[known_x’s] An array 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] An array of numerical values representing a set of new x-values, for which you want to calculate the corresponding new y-values.

This is an optional argument which, if omitted, is set to be equal to the [known_x’s] array.

[const] An optional logical argument that specifes whether the constant ‘b’, in the straight-line 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 equation becomes y = mx

If more than one new y-value 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.

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.


Trend Function 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 exactly 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.


Trend Function Errors

The most common errors from the Excel Trend function are listed in the table below :

#REF! Occurs if the [known_x’s] array has a different length to the known_y’s array.
#VALUE! Occurs if any of the values in the supplied known_y’s, [known_x’s] or [new_x’s] arrays are non-numeric.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment