Category : Lotus and other Spreadsheets
Archive   : BEST_FIT.ZIP
Filename : BEST_FIT.TXT

10. BEST_FIT (BEST_FIT.WQ1)
==========================

BEST_FIT is a macro library that calculates and plots the best
fit (trend) line for a regression analysis. A regression
analysis is used to study the correlation between two variables
to determine if a relationship exists. BEST_FIT will plot your
trend line and independent and dependent variables on an
XY graph.

REGRESSION TERMINOLOGY

Ind. Variable The data that affects the dependent variable.
(Ex. Number of sales advertisements mailed.)

Dep. Variable The data that is affected by the independent
variable. (Ex. Sales generated by the
advertisement.)

Constant The Y intercept of the regression. This the
point where the line meets the Y axis.

Std Err / Y Est
The estimated deviation of the regression line.

R Squared Measures the validity of the model. Ranges from
0 to 1 with 1 being optimal. It is used as an
index to compare
different models with the same number of
independent variables.

Deg. of Freedom
The parameter that determines the shape of the
curve.

X Coefficients The coefficient of the independent variable. It
is the slope of the best fit line.

Std Err of Coef
Gives an error estimate of the
coefficients.


THE FORMULA

The formula Quattro Pro uses to calculate the best fit line is
_ _
y' = y + b(x - x)

x = The independent variables
_
x = The average of the independent variables

y = The dependent variables

y'= The Y axis values for the best fit line
_
y = The average of the dependent variables

b = The slope of the best fit line, computed using

_
E(x - x)y
B = ÄÄÄÄÄÄÄÄÄ
_
2
(x - x)


TO USE BEST_FIT

1. Retrieve the spreadsheet BEST_FIT.WQ1 which contains the
best fit
macro.

2. Open the spreadsheet containing the data for analysis.

3. This macro requires a blank area of your spreadsheet to
display the regression data. This area must be at least
7 columns wide and as many rows as there are points in the
analysis. The upper left cell of this block must be named
R_OUTPUT. Use /Edit | Names | Create to name this cell.
Be sure no data appears in this area as it may be erased.

4. Press [Alt-A] to execute the macro.

5. You will be prompted for the range of your independent and
dependent variables.

6. Quattro Pro will now perform the regression analysis.

================================= END ================================