Statistical
TREND Function
Fits a leastsquares regression line to a range or array and
returns the yvalues along that line for the xvalues you
specify.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Syntax
Managers... 
Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more. 
TREND(known_y's,
known_x's, new_x's, const)

known_y's
Required. The set of values you already know in the
relationship:
y = mx + b.

known_x's
Optional. A set of values you might already know in the
relationship:
y = mx + b.

If known_x's is
omitted, it is assumed to be the array {1,2,3,...} that
is the same size as known_y's.

The array known_x's
can include one or more sets of variables. If only one
variable is used, known_y's and known_x's
can be ranges of any shape, as long as they have equal
dimensions. If more than one variable is used,
known_y's must have a height of one row or a width
of one column.

new_x's
Optional. New xvalues for which you want TREND to return
corresponding yvalues.

If you omit new_x's,
it is assumed to be the same as known_x's.

If you omit both known_x's
and new_x's, they are assumed to be the array
[1,2,3,...] that is the same size as known_y's.

The new_x's argument must
include a row or column for each independent variable,
just as known_x's does. So if knowny's
is in a single column, knownx's and
new_x's should have the same number of columns.

const
Optional. A logical value that specifies whether to force
the constant b to equal 0.

If const is TRUE or omitted, b
is calculated normally.

If const is FALSE, b is set
equal to 0 and the mvalues are adjusted so that
y = mx.
Applies To
Excel 2003 and above.
Examples
You can
download this example workbook here,
along with all other example workbooks I've completed for this
Excel help area.
Example 1:
The
formulas in row 3 find the trend for the data over seven months.
Because the const argument was omitted, the formula
used the default value of TRUE.
The formula for cell B3 is copied to the right as shown in the
figure.
In the chart, you can see that the calculated trend follows the
data very closely.
Example 2:
The formulas in row 3 find the trend in the data over
seven periods. Here, however, the const argument was included.
When you compare the values in row 3 of this example to the
values in row 3 of the previous example, you'll see a slight
difference. This is because dates aren't evenly spaced as the
seven periods are.
Even so, the values are so similar that the two charts look
virtually identical.
Example 3:
Row
3 in this example is quite different from row 3 of the preceding
examples. This is because the const argument is FALSE.
By setting const to FALSE we change the TREND equation
from y = mx + b to y = mx.
That is, we force the trend line to have a yintercept equal to
zero.
This chart makes the change in the trend easier to see. In
the previous two charts, the red line meets the y axis at about
2. But here, it touches at zero exactly.
Example 4:
This example from the STEYX
function, shows the TREND function in use. Here, we add the
standard error to each trend value to define the High boundary,
and we subtract the standard error to define the Low boundary.
You can find a more complete version of this
example of
data boundaries here.
Other ExcelUser
Information
Other Help
