Dec 232017
Do non-linear regression with 1-2-3 V2.0.
File CURVEFIT.ZIP from The Programmer’s Corner in
Category Lotus and other Spreadsheets
Do non-linear regression with 1-2-3 V2.0.
File Name File Size Zip Size Zip Type
CURVEFIT.DAT 210 99 deflated
CURVEFIT.DOC 6150 2362 deflated
CURVEFIT.WK1 145585 16765 deflated

Download File CURVEFIT.ZIP Here

Contents of the CURVEFIT.DOC file

General Least Squares Curve Fitting Program
Version 1.00

Written by Michael Lindell
200-L Rotunda Circle
Newport News, VA 23602
(804) 865-4508 (Day)
(804) 874-4927 (Home)

This program was written using Lotus 1-2-3 Release 2. It uses the
least squares method to calculate the best curve (function) passing through
a given set of data. Data can be entered interactively or can be read from
a data file. Currently, a maximum of 100 data pairs can be entered, and can
be fit with the following functions : linear, logarithmic, exponential,
general power, and polynomial up to 7th order. Familiarity with Lotus is
assumed, although for the most part the program is menu driven.

Listed below are the various functions which are available along with
any restrictions on their use.

Function Equation Restrictions
------------------ -------------------- -------------------------------

1. Linear Y(X)=a+b*X None
2. Logarithmic Y(X)=a+b*Ln(X) All X > 0
3. Exponential Y(X)=a*Exp(b*X) All Y > 0
4. General Power Y(X)=a*X^b All X,Y > 0
5. Polynomial Y(X)=a+b*X+c*X^2+... Maximum order=7, and the order
must be less than the number of
data pairs.

In the above equations, X is the independent variable, Y is the dependent
variable, and the coefficients a, b, c, etc., are determined by the program
such that the error between the data and the model is minimized in the least
squares sense. Violation of the restrictions for a given function will result
in an error condition from Lotus.

Explanation of Menu Items

Main Menu - The main menu has the following items :

Help Data Edit Curve Read Write Quit

Help : Gives a brief information screen about the program.

Data : Puts the program in the data entry mode. Select this item to start
a new session. Any data currently displayed will be erased. Once
data entry is completed, press Alt-M to redisplay the main menu.

Edit : Select this item to edit data that has already been entered (or has
been read from a data file).

Curve : After data has been entered, select this to choose the type of
curve you want fit to the data. Once the curve has been chosen
(and the order of the polynomial chosen if a polynomial curve was
selected) the program displays a wait message while it computes
the equation coefficients. The program then returns the chosen
equation with the calculated coefficients. It also calculates
the predicted Y value for each X value and the percent difference
between the predicted Y value and the actual Y value. A secondary
menu is now displayed which is described later.

Read : Read in data from an external data file. Any data currently
displayed will be erased. The data file may have been created by
this program or created with a text editor. It can contain data in
any format (e.g., 1.234, 2.3E+06) but cannot contain any text or
blank lines. For a sample data file see "curvefit.dat".

Write : Writes the current X,Y data to an external data file.

Quit : Quit the program and return to DOS.

Secondary Menu - The secondary menu, displayed after the "curve" option of
the main menu has been selected, has the following items :

Graph Browse Refit Exit

Graph : Displays a graph of the actual data and the chosen curve fit to
the data.

Browse : Exits the secondary menu in order to page through the results.
This simply gives you control of the cursor so you can move down
and see the data that is off the current screen to see how well
the model predicted the actual data. Press Alt-G to redisplay
the secondary menu.

Refit : Returns to the curve selection screen so you can choose a different
curve to fit to the data.

Main : Exits the secondary menu and returns to the main menu.

Miscellaneous Comments

The program does not have any cell protection feature in effect, so
anything entered in an area where it does not belong may result in an error
condition and the program may have to be reloaded.

In the data editing stage, there is no problem with adding more data
pairs. However, problems may arise if you try to delete data pairs. An
error condition will result if the Lotus commands Range Erase and/or Move
are used. These commands can produce unrecoverable errors in the worksheet.
It is suggested that if such operations are required on the data that they be
performed on a stored data file, then the file can be read back into the
program. A memory-resident text editor is very useful for this since you
would not have to exit Lotus to edit the data (or, from the data input or
edit mode you could temporarily exit Lotus via the System command).

A sample data file is contained in "curvefit.dat". This file can be read
into the program through the "Read" option of the main menu. You may want to
fit several of the curves to this data to get a feel for how the program
operates. (Incidently, the data was generated using the equation
Y(X) = 2.0 * Exp(0.5*X) + random noise; so the exponential function should
produce a decent fit).

This program will not work in Lotus 1A since some of the macros invoked
by the program are not defined in that version.

If you have any comments or suggestions for improving the program,
please contact me.

 December 23, 2017  Add comments

Leave a Reply