# Category : Lotus and other Spreadsheets

Archive : AD36.ZIP

Filename : EXAMPLE.FOR

--------------------------------------------------------------------------------

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

------------------------------------------------

by

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:

A B C D E

1

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

following:

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

names.

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:

c:

cd c:\123\data

a:autodoc

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

pages.

------------------------------------------------------------------------------

Finally this is a simple but powerful interpretative program. But..it 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.

------------------------------------------------------------------------------

Updates

-------

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.)

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

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

But one thing that puzzles me is the “mtswslnkmcjklsdlsbdmMICROSOFT” string. There is an article about it here. It is definitely worth a read: http://www.os2museum.com/wp/mtswslnk/