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

Output of file : BEST_FIT.TXT contained in archive : BEST_FIT.ZIP


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.


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

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

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


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
(x - x)


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

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 ================================