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