Category : Lotus and other Spreadsheets
Archive   : AD36.ZIP
Filename : EXAMPLE.FOR

Output of file : EXAMPLE.FOR contained in archive : AD36.ZIP

AutoDoc v3.60
Listing of d:\example.wk1, File Date:07/10/88 11:28:22 Page 1
Cell Range |A |B |C |D |E |F |G |H
A1..C1 Example
A3 Expenditures
A4..F4 ;; Jan Feb Mar April Total

apples[t] = $apples[t] * apples[t]
= <<2.4 to 18.9>>, for t = Jan to Total, at B5..F5

A5..F5 apples 2.400000 3.900000 5.600000 7.500000 18.9000000

oranges[t] = $oranges[t] * oranges[t]
= <<5.2 to 33.35>>, for t = Jan to Total, at B6..F6

A6..F6 oranges 5.200000 8.400000 13.50000 6.400000 33.3500000
B7..F7 -------- -------- -------- -------- ---------

Total[t] = SUM( apples[t]..oranges[t] )
= <<7.6 to 52.25>>, for t = Jan to Total, at B8..F8

A8..F8 Total 7.600000 12.30000 19.10000 13.90000 52.2500000
A11 Quantities
B12..F12 Jan Feb Mar April Total

apples[t] = SUM( apples[t-4]..apples[t-1] )
= <<14.>>, for t = Total, at F13

A13..F13 apples 2 3 4 5 14.0000000

oranges[t] = SUM( oranges[t-4]..oranges[t-1] )
= <<23.>>, for t = Total, at F14

A14..F14 oranges 4 6 9 4 23.0000000
B15..F15 -------- -------- -------- -------- ---------

Total[t] = SUM( apples[t]..oranges[t] )
= <<6. to 37.>>, for t = Jan to Total, at B16..F16

A16..F16 Total 6.000000 9.000000 13.00000 9.000000 37.0000000
A18 Prices
B19..F19 Jan Feb Mar April Total

$apples[t] = @AVG( $apples[t-4]..$apples[t-1] )
= <<1.35>>, for t = Total, at F20

A20..F20 $apples 1.200000 1.300000 1.400000 1.500000 1.3500000

$oranges[t] = @AVG( $oranges[t-4]..$oranges[t-1] )
= <<1.45>>, for t = Total, at F21

A21..F21 $oranges 1.300000 1.400000 1.500000 1.600000 1.4500000

AutoDoc v3.60
Listing of d:\example.wk1, File Date:07/10/88 11:28:22 Page 2
Range Names And Definitions In d:\example.wk1

AutoDoc v3.60
Listing of d:\example.wk1, File Date:07/10/88 11:28:22 Page 3
Index Of Row Names[1..15] (And Which Rows Referenced Them) In d:\example.wk1

apples:5| 8
apples:13| 5 13 16
oranges:14| 6 14
$apples:20| 5 20
$oranges:21| 6 21
AutoDoc v3.60
Listing of d:\example.wk1, File Date:07/10/88 11:28:22 Page 4

Automated Documentation Of Spreadsheet Equations


Copyright (c) 1988, John D. Pearson

Have you ever wondered why spreadsheets have to be so @DA.. @DIFF.. to read.
Commercial "cell" listings help but so far they do not translate the structure
of the spreadsheet back into english. This program tries a different tack.

It assumes that your spreadsheet follows a convention that rows correspond to
variables and columns to time periods like so:

2 ;; Jan Feb Mar Apr
3 apples 1 2 0 3
4 oranges 22 44 -10 100
5 ----- ------ ------ ------ ------
6 Total +B3+B4 +C3+C4 +D3+D4 +E3+E4

The example shows a very simple spreadsheet. Apart from the data it has one
repeated formula in the cell range A6..E6. Commercial cell listing programs
repeat this formula in every cell. AutoDoc treats this as an equation.

To AutoDoc an equation is simply a cell formula that is repeated for two or more
cells in a row. In the example above the equation in range A6..E6 defines the
"total" as the sum of the "apples" and "oranges" for each of the periods "Jan"
to "Apr".

AutoDoc recovers the equations from the spreadsheet and displays the equation in
the context of the text and data as follows.

Cell Range |A |B |C |D |E
A2..E2 ;; Jan Feb Mar Apr
A3..E3 apples 1 2 0 3
A4..E4 oranges 22 44 -10 100
A5..E5 ----- ----- ----- ----- -----

Total[t] = apples[t] + oranges[t]
= <<23..103>>, for t = Jan to Apr in B6 to E6

A6 Total 23 46 -10 103

Now this may appear to be trivial (and it is), however take a more complicated
situation with many equations with forward and backward references and this same
idea makes a complex spreadsheet very readable.

AutoDoc generates an ASCII output file like the above which contains the

The first column shows the range of cells in the
rows that contain entries since only one page width
of text/data will be displayed.

The remaining columns contain the entries as they
might appear on the screen but left justified in
each column width.

All equations found are summarized as above with
[t] referring to the current cell "time period" and
<<23..103>> provides the first and last values
computed using the equation. The cell range shows
where they are in the spreadsheet row.

You may customize the names by adding a column and rows(s) of sensible
abbreviations then add markers ;; as above to show which row contains
the current column headers and which column contains sensible variable

If you do not then AutoDoc will construct variable names from the
leading text in each row, but the column headers will be the usual
1-2-3 headers. A..IV. If nothing is provided then surrogate row
names are invented Row:236 etc. Even this can be helpful.

AutoDoc appends a listing of the ranges found and a simple listing of
the references to each of the variable names or rows.

Absolute row and column references are flagged as in 1-2-3 using the $
sign. An absolute column reference will be [$D] while an absolute row
reference will appear as row:232:$[$D]. If range names are defined
then AutoDoc uses these in the equation listing.

Operation And Screen Output

To use AutoDoc you will need this program and an IBM compatible running with PC
or MS-DOS, then type:

AUTODOC [input [output]]

at the DOS prompt, with [ ] meaning optional. (Do not type the [] symbols).
The INPUT and OUTPUT files may be fully qualified drive\path\name specifications
with .WK1 default on input and .FOR on output.

It takes no other parameters and if neither INPUT nor OUTPUT are given then it
is entirely menu driven. A configuration file is automatically written to the
default drive for each use and is read for the default options next time.

For example if you have a file test.wk1 on c:\123\data and your AUTODOC.EXE file
is on a floppy in the A drive: then type:

cd c:\123\data

-- and you will be prompted for everything else by the menus including the
format options available for the formula file output. The help key F1 and the
file choice keys F9 and F10 provide a range of available choices for each option
in most cases.

After providing the file to be listed and approving the output format, AUTODOC
will read the file once to identify all usable row and column names. Next it
re-reads the file and generates the equation listing on an auxiliary file with
extension .FOR. At the same time a video MAP is displayed showing the first 55
columns of the spreadsheet. You may also list this map as one of the options.

The MAP display flags in color and highlights changes in row structure along
each row. This may show a formula that has been miscopied along a row. Each
break in the row is independently listed to the formula file and should be
examined. Most users are so entranced with the marching screen display that
they do not notice the errors. The MAP list reports the changes by
capitalizing the cell type symbols.

Limitations of this program:
Comments on the current version of AutoDoc (and some remedies):

AutoDoc ONLY thrives on spreadsheets that follow the variable/time convention
described above. On all others it will generate more paper than insight since
every cell may becomes a fresh equation.

It is designed to fully process rows 1..1001 of Lotus 1-2-3 version 2 files.
Further rows 1002.. are processed without any ;; names but otherwise everything
else continues. First time users with an arbitrary spreadsheet are advised to
turn on "variable names" and "row numbers" as configuration options.

It does not automatically check and correct for duplicate variable names, macro
text or range names used out of context. Be warned that absolute row or column
references can be very misleading. If duplicate names are used then key
references will appear to be the same unless the option "add row numbers to
names" is used.

The popular LIST.COM program by Vernon Buerg and available on most bulletin
boards, is an excellent way to peruse the output file and selectively print

Finally this is a simple but powerful interpretative program. is your
responsibility to review the output carefully and to base any conclusions
derived from the results solely upon your own judgement. If in doubt check a
1-2-3 cell listing. While every effort has been made to ensure that the program
is bug-free on a PC/XT and a Compaq 386 under DOS 3, there are almost
certainly situations and PC configurations that AutoDoc has not encountered and
in which it may not function correctly.

AutoDoc is copyrighted by the author and distributed for comment. You are
encouraged to try it and circulate it with this file attached. If
you find it useful then for an updated version of this program with
documentation write to:

John D. Pearson,
2007 Franklin Avenue,
McLean Va. 22101.

and enclose $15. Suggestions for improvement are welcome.

This file supercedes version 2.1 called AD21.ARC (version 3.5 7/1/88.)

  3 Responses to “Category : Lotus and other Spreadsheets
Archive   : AD36.ZIP
Filename : EXAMPLE.FOR

  1. Very nice! Thank you for this wonderful archive. I wonder why I found it only now. Long live the BBS file archives!

  2. This is so awesome! 😀 I’d be cool if you could download an entire archive of this at once, though.

  3. But one thing that puzzles me is the “mtswslnkmcjklsdlsbdmMICROSOFT” string. There is an article about it here. It is definitely worth a read: