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.
You can find the Home Price Estimator.xlsx file with the other examples on the companion
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