Microsoft Office Tutorials and References

In Depth Information

**Syntax**

Figure 14.16
,
later in this chapter, shows a visual map of the statistics be-

ing returned.

The accuracy of the line calculated by LINEST depends on the degree of

scatter in the data. The more linear the data, the more accurate the LINEST

model. LINEST uses the method of least squares for determining the best fit

for the data.

The line- and curve-fitting functions LINEST and LOGEST can calculate the

best straight line or exponential curve that fits the data. However, you have

to decide which of the two results best fits the data. You can calculate

TREND(known_y's,known_x's) for a straight line or GROWTH(known_y's,

known_x's) for an exponential curve. These functions, without the known_x's

argument, return an array of y values predicted along that line or curve at

your actual data points. You can then compare the predicted values with the

actual values. You might want to chart them both for a visual comparison.

In regression analysis, Microsoft Excel calculates for each point the

squared difference between the y value estimated for that point and its actu-

al y value. The sum of these squared differences is called the residual sum

of squares. Microsoft Excel then calculates the sum of the squared differ-

ences between the actual y values and the average of the y values, which is

called the total sum of squares (that is regression sum of squares + resid-

ual sum of squares). The smaller the residual sum of squares compared with

the total sum of squares, the larger the value of the coefficient of determ-

ination, r-squared, which is an indicator of how well the equation resulting

from the regression analysis explains the relationship among the variables.