Microsoft Office Tutorials and References

In Depth Information

**Calculating linear regression**

Although you can use real estate listing prices to arrive at an estimated price, it is

better to use actual sale prices, if you can get them. And of course, this is only one tool

and it only works with five variables; there are always many other variables to consider.

No guarantees.

You can find the Home Price Estimator.xlsx file with the other examples on the companion

website.

The TREND function

LINEST returns a mathematical description of the straight line that best its known data.

TREND finds points that lie along that line and that fall into the unknown category. You can

use the numbers returned by TREND to plot a trendline—a straight line that helps make

sense of actual data. You can also use TREND to
extrapolate
, or make intelligent guesses

about, future data based on the tendencies exhibited by known data. (Be careful. Although

you can use TREND to plot the straight line that best its the known data, TREND can’t

tell you whether that line is a good predictor of the future. Validation statistics returned

by LINEST can help you make that assessment.) The TREND function takes the form

=TREND
(known_y’s, known_x’s, new_x’s, const
).

The first two arguments represent the known values of your dependent and independent

variables. As in LINEST, the
known_y’s
argument can be a single column, single row, or

rectangular range. The
known_x’s
argument also follows the pattern described for LINEST. The

third and fourth arguments are optional. If you omit
new_x’s
, the TREND function considers

new_x’s
to be identical to
known_x’s
. If you include
const
, the value of that argument must

be TRUE or FALSE (or
1
or
0
). If
const
is TRUE, TREND forces
b
to be
0
.

To calculate the trendline data points that best it your known data, simply omit the third

and fourth arguments from this function. The results array will be the same size as the

known_x’s
range. In Figure 17-4, we used TREND to find the value of each point on the

regression line that describes the data set from the example in Figure 17-3. To create these

values, we selected the range C2:C19 and entered
=TREND(B2:B19, A2:A19)
as an array

formula using Ctrl+Shift+Enter.

To extrapolate from existing data, you must supply a range for
new_x’s
. You can supply

as many or as few cells for
new_x’s
as you want. The result array will be the same size as

the
new_x’s
range. In Figure 17-5, we used TREND to calculate demand for the 19th, 20th,

and 21st months. To arrive at these values, we typed
19
through
21
in A21:A23, selected

C21:C23, and entered
=TREND(B2:B19, A2:A19, A21:A23)
as an array formula by

pressing Ctrl+Shift+Enter.