Microsoft Office Tutorials and References
In Depth Information
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.