news@ncsuvx.ncsu.edu (USENET News System) (08/03/89)
Several people have complained about the lack of documentation with
opus. I thought I included the file with the arc but if not, I
apologize. The following is the first half of the documentation. The
second half will follow in the next post. Let me also say that a newer,
better
version of opus will be posted soon, and the documents will be a
continuation of these. The newer version handles changes (movements of
cells as one would expect, whereas the current version is
idiosyncratic..
From: liebo@csclea.ncsu.edu (Dr. Stan Liebowitz)
Path: csclea!liebo
***Just a note: the author states that a new version,
2.3 will be available in the middle of July. This
version will allow reading and writing lotus worksheets
(wks and wk1), the moving of graphics text with the
mouse, far better use of memory allowing larger sheets,
and other enhancements. In order to get the lotus
read/write capability, however, you need to register
with him.***
Opus v. 2.00
--------------
Spreadsheet and Charting program
for the Atari ST
by Doug Harrison
>>> Introduction
Opus is a GEM spreadsheet and charting program, which I think
you'll find powerful, fast, and easy to use. In order to keep this
documentation to a reasonable length, I'll focus primarily on
description of features, rather than provide a full tutorial
(which probably isn't needed). Some of you may be familiar with
the original version of Opus, which appeared in the Sept. 1988
ST-Log. If you fall into this category, great! You can skip over
the majority of the spreadsheet description, but please be sure to
read the section on charts and the files INSTALL.DOC and
CHANGES.DOC.
>>> Shareware
Opus versions 2.00 and above are shareware. Opus version 2.00 is
the result of about 8-9 months of additional work and includes
many new features above and beyond the original; the charting
facility is completely new, and the spreadsheet is much faster in
all operations (for a description of most of the changes and
additions, please see the file CHANGES.DOC). I plan on supporting
Opus with at least one or two upgrades, and I will be available to
answer questions through the mail and on Compuserve and GEnie. If
you find Opus useful and wish to support my efforts, you can send
a check for $15 (or whatever you feel the program is worth!) to:
Doug Harrison
P.O. Box 66236
Baton Rouge, LA 70806-6236
For quick response to questions, my Compuserve ID is 72277,2315
and my GEnie mailbox address is D.S.HARRISON. There also is (or
soon will be) an Opus topic in Category 2 of the GEnie message
base.
>>> Distribution Policy
You may freely distribute Opus (and please do), as long as all
files and notices are included. It may be included as part of
"shareware disks" provided by various groups, as long as no fee
beyond media costs is charged.
>>> Running Opus
IF YOU READ NOTHING ELSE, PLEASE, PLEASE READ INSTALL.DOC!!!! Read
it NOW, before you try to run Opus! It really and truly DOES
contain ESSENTIAL information, OK?! (forgive my tone, I'm just
speaking as I need to be spoken to...)
Here's a list of the files Opus tries to load when it's executed,
in order:
1. OPUSMONO.RSC or OPUSCOLR.RSC (depending on monitor in use)
2. PRINTER.INF (printer control codes)
3. OPUS.CNF (configuration file, explained later)
4. screen fonts, provided GDOS is present
5. OPUS.WID (printer font widths file)
Opus doesn't need to access the disk it was loaded from once it
has booted. However, when printing charts on a floppy-based
system, the disk containing the GDOS files must be present in the
boot drive. Please see the section "Printing Charts" for details.
///////////////////////////////////////////////
///// Worksheets /////
///////////////////////////////////////////////
Now that you've got Opus up and running, you should have a blank
worksheet on your screen. Let's first take a look at "The Panel",
the area directly underneath the menu bar.
>>> The Panel
The Panel contains the following items, from left to right:
1. the active cell or cell cursor location
2. an icon area, containing the "cell deletion" icon and
the "data entry" icon (the X and check-mark icons,
respectively)
3. the edit area, in which you enter data, consisting of two
50 column lines.
The panel also may contain up to three additional icons, and these
indicate various states. First, the "Circ" icon may appear should
you enter a formula that contains or makes a circular cell
reference (this icon may show up as "???" if Opus can't determine
circularity- more on this later). Second, if the cell cursor is
moved into a hidden cell, the "H" icon will appear. Finally, the
"P" icon appears when the cell cursor is in a protected cell. A
click on the "Circ" icon will present you with an alert box
indicating the cell containing the circular reference, while
clicking on the "H" or "P" icons will unhide or unprotect the
active cell.
If you want to delete the currently active cell, click on the
cell-deletion icon. Similarly, to complete data entry into a cell,
click on the data entry icon or press RETURN, ENTER, or any of the
cursor keys.
>>> Data Entry
Opus supports the three traditional spreadsheet cell classes:
numbers, labels, and formulas. Unlike other spreadsheets which
require you to precede a label with the apostrophe or formulas
with plus or equal signs, Opus uses the function keys F8, F9, and
F10 to change cell class. You may also perform this action from
the Edit menu. Look under the Edit menu now, and note many items
have keyboard equivalents. All menu items with corresponding
keyboard commands have the commands listed to their right. If
you're unsure of the meaning of some of the symbols, please look
under the Help menu item and click on the Keyboard entry. Here,
you will also find all the ways you can move about the
spreadsheet. For a list of the various mouse functions, please
refer to the Help item Mouse.
>>> Cell Format
Opus offers many options for cell format, and they are found under
the Edit menu item. All but the number formatting options are
found in the same dialog, and both of these dialogs reflect the
state of the active cell when they are first displayed. To display
this dialog, click on Justification under the Edit menu. The
Justification button will be selected, indicating that this
parameter is the one that will be changed. In order to alter the
text style, for example, you must select that button as well. Now,
consider the "extent" buttons, which allow you to specify whether
the format changes should affect only one cell, a range, or the
entire worksheet. For example, to change the justification of a
range without altering other formatting within the range, make
sure that the Justification button is the only one selected when
you click on OK or press RETURN. Please note that the sparse
matrix design requires any cell containing data or a format
differing from the global format to *exist*, which means that it
consumes memory. This makes it impossible to format an arbitrarily
large range. However, you may certainly change the default cell
format through the "Global" option, affecting all existing cells
and all cells entered thereafter.
>>> Number Format
Now, pull down the Edit menu and click on Number. This dialog
functions just like the previous one; to select an attribute to be
changed, you must click on its title. For example, to turn off
commas, click on the line "Suppress Commas?", click on the box to
its right (checking it), and then press OK or hit RETURN.
Likewise, if you wish to change the precision, click on the
"Precision" title. The "Format Strings" option allows you to
specify a string that will be output on all numeric or formula
cells possessing that attribute. For example, you could specify a
format string "Part #", and any cell having that as part of its
number format would be display as "Part #xxx", where xxx is its
value. You can use the reverse apostrophe "`" (the key to the left
of Backspace) to specify the value position within the format
string; the default is to append the value to the end of the
string. In order to make a new format string, click on the "Edit
box," and then type in the string. When you're done, click on
"Add." The string will appear in the scrollable list. To make the
string part of a cell's format, click on the string within the
scrollable list and also on the "Format Strings" title before
exiting the dialog.
The "Global Upper and Lower Limits" fields contain the values
above and below which the cells will display as scientific
notation. For example, if the global lower limit is 1E-3, any cell
containing a value whose magnitude is less than 0.001 (but not
equal to zero) will be displayed in scientific notation.
Note: Many Opus dialogs use a worksheet range as a parameter. If a
range was selected before the dialog was invoked, that range will
appear in the "Begin" and "End" fields in the dialog. In the case
of the format dialogs, the "Range" button will also be
preselected. If a range was not selected, however, the extent of
the format dialogs will be set to "Cell," meaning that the active
cell alone will be affected (provided you don't override this). In
the case of the Number format dialog, the "Use All" button will be
preselected as well, so that you needn't click on all the
attribute titles you want to alter.
>>> Block Commands
A block or range is simply a rectangular group of one or more
cells. You can select a block by dragging the mouse or clicking on
the Start and End Block items under the Block menu. If you're
using the mouse, the worksheet will scroll if you move off the
working area. When a block is selected, several worksheet
functions become available. The Show First Cell and Show Last Cell
items under the Marks menu change to Show Block Start and Show
Block End. Under the Block menu, Copy, Move, and Delete Block
become available. Before discussing the items under the Block
menu, let's take a look at the difference between relative and
absolute cell references.
>>> Cell References
Cell references within formulas are divided into relative and
absolute types. A relative cell reference is written as A1, while
absolute cell references are written as $A1, A$1, or $A$1. The
dollar sign indicates whether the row or column part of the
reference is to be considered absolute, meaning that it is "cast
in stone" and will never be altered by any worksheet function,
such as Move Block, Replicate Cell, or Insert Row. To clarify,
consider the worksheet below:
A B
1 1 A1+1
2 B1+1 A2+1
If you selected the range A1:B2 and then chose to move it to cell
A3, the resultant worksheet would appear as follows:
A B
1
2
3 1 A3+1
4 B3+1 A4+1
As you can see, the relationships between the formulas have
remained intact. Were those cell references absolute, they would
not have been altered by the move.
Note that several worksheet functions give you the option to
consider *all* cell references as absolute, and these include Copy
and Move block, Insert and Delete row/column, and Replicate Cell.
>>> Copy and Move Block
These functions, along with Delete Block, are only available when
a block is selected. So, to copy or move a block, first select
one, and then move the cell cursor to the cell you wish to be the
upper-left corner of the destination block. When you choose one of
these, you will first be asked to confirm your selection, as there
is no "Undo" function. Then, you will be asked whether to treat
all cell references as absolute, including the relative ones.
Generally, you'll want to choose "Relative," so that only the true
absolute references will be considered absolute. And in any event,
a relative reference will only be adjusted provided it points to a
cell contained within the selected range, which consequently also
changes position. As a final point, be aware that cells *outside*
the source range containing formulas with cell references pointing
within the source range will *not* be altered, even if the range
is moved. For example, suppose cell F1 references cell A1, and you
move the range A1:B2 to cell A3. F1 will continue to reference
cell A1.
>>> Delete Block
Permanently deletes a block.
>>> Insert and Delete Row/Column
These functions allow you to specify whole or partial row and
column insertion and deletion. As an illustration of "partial," a
partial row is defined as follows. First, if no block is selected,
the partial row extends from the active cell to the rightmost cell
in that row. If a block is selected, then the partial row extends
from the upper-left cell in the block to the rightmost cell in
that row and within the block. For the sake of the operation, a
"virtual block" is defined as consisting of the partial row plus
all partial rows below it. A partial column is defined in an
analogous manner. This allows a rather flexible definition of the
extent of these operations, so that, for example, it's easy to
insert a row at row #18, confined to columns F and G, rather than
extending over the entire worksheet space (columns A..IU). Without
the "partial" option, you would have to select the range F18:G998
and then move it to cell F19.
Note: These operations are really block moves, and the description
of the handling of cell references for block moves applies here as
well.
>>> Data Fill
This function fills a range with numbers, beginning at some
initial value to which some increment value is added for each
cell. You specify the direction of the data fill as "Right" or
"Down."
>>> Replicate Cell
This function copies a cell throughout a range. If the cell
contains a formula, then relative cell references are adjusted,
provided you haven't click on the "Absolute" button in the dialog.
Note that it doesn't matter whether cell references point to cells
within the selected range, as it does for block copying and
moving.
>>> Sort
This function uses the ShellSort algorithm to sort a range on a
row or column basis. For example, consider the range A1:C10, which
is to be sorted by row. For "Key Cell," I specify cell B1, meaning
cells in column B are compared. Then rows 1-10 are sorted based on
the values in column B, for columns A-C. You may also specify
ascending (low to high) vs descending (high to low) as the "sense"
of the sort. The precedence of cells is as follows, from lowest to
highest:
1. Empty cells
2. Values (numeric or formula cells)
3. Labels.
Note that relative cell references in formulas aren't affected by
the sorting operation (in other words, any cell reference is
treated as if it's absolute).
>>> Auto Cursor
To illustrate this feature, select a range and position the cursor
anywhere within it, except the last cell. Now press RETURN, and
observe, the cursor advances to the next cell. You may specify the
direction for the auto cursor through the Options menu, and you
may also turn it off. This feature comes in really handy when you
have a lot of numbers to enter, as you can first select a range
and then enter the data solely through the numeric keypad. The
worksheet also scrolls properly if the range extends beyond the
displayed portion.
And that about does it for the block operations. Let's forge ahead
and consider formulas and functions, but first a few words about
recalculation modes.
>>> Recalculation
Opus provides natural order recalculation, meaning that during
evaluation of a formula, any cell references are calculated before
their values are used. This ensures that formulas containing
references to other cells are calculated based upon up to date
values.
Opus also provides an automatic recalculation feature, meaning
that if you change the value of a cell that is referenced by some
formula, that formula will be recalculated automatically. This is
a little different from other spreadsheets that provide a similar
function, in that Opus doesn't recalculate the entire spreadsheet,
but rather, just those cells that are affected by the change. This
method (also called minimal recalculation) can be much faster for
large worksheets and literally makes this a usable feature. How
does Opus know what cells are affected? Briefly, each cell that is
referenced by a formula has a "dependent cell list," and when you
change the value of such a cell, Opus can traverse this list and
recalculate all the other cells that depend on the cell's value.
Note that having a dependent cell list requires a cell to exist,
meaning it consumes memory. Be aware that range references within
formulas cause an entry to be placed in the dependent cell list of
every cell within the range, and the cells will be created as
necessary. This precludes the use of arbitrarily large ranges in
formulas, lest you're fond of "out of memory" errors.
Finally, note that you may turn off natural order and automatic
recalculation through the "Options" menu. With both turned off,
recalculation proceeds in a row by row fashion, which can be
useful in some applications (the supplied amortization spreadsheet
depends on it). Now, it may rarely be necessary to turn off these
features, so that Opus may complete the recalculation; you will be
informed of this with a friendly alert box. For example, consider
the worksheet below:
A B C...........IT IU
1 B1+1 C1+1 D1+1.......IU1+1 A2+1
2 B2+1 C2+1 D2+1.......IU1+1 A3+1
.
.
100 B100+1 C100+1 D100+1.....IU100+1 1
In order to calculate cell A1 using natural order, the value of
cell B1 must be known. But to calculate B1, the value of C1 must
be known. And so on, for 25,500 cells. Essentially, this involves
a recursive algorithm, that is, a function that calls itself. If
this function was allowed to call itself 25,500 times without
returning in the meantime, the machine would surely crash. But
don't worry, this can't happen in Opus 2.00; at worst, you will be
informed of this state with an alert, "Stack is nearing
overflow...", and you should then turn off natural order and/or
automatic recalculation. In general, you'll never see this alert,
as it requires a fairly large and somewhat contrived spreadsheet
before it appears. This worksheet also confounds determination of
circularity for similar reasons; it most definitely will cause the
"???" icon to appear in the Panel.
>>> Global Recalculation
When you select "Recalculate" from the "Options" menu or press F1,
Opus recalculates the entire spreadsheet, and this is called a
global recalculation. Natural order will be used, provided it
hasn't been turned off. However, the state of the automatic
recalculation mode isn't relevant here, since we're recalculating
the entire worksheet.
>>> Formulas
Formulas in Opus consist of valid arithmetic expressions, composed
of the following:
- numbers, followed by an optional percent sign
- cell references
- arithmetic operators +, -, *, and /
- logical operators =, <>, <, <=, >, >=
- unary minus, "-" (i.e. negation operator)
- functions and their parameter lists, but without the silly
'@' sign required by other spreadsheets
- expressions in parentheses.
Numbers have 17 decimal digits of precision, and the range is
+/- 1E-37 ... +/- 1E37 or thereabouts.
At present, Opus doesn't support strings in formulas or string
functions; a future update likely will. The maximum formula length
is 100 characters. Opus follows the usual rules of operator
precedence, summarized below, from highest to lowest:
- unary minus
- expressions in parentheses
- *, /
- +, -
- =, <>, <, <=, >, >=
Operators with equal precedence are evaluated left to right, so
that 6/3/4 is interpreted as (6/3)/4, rather than 6/(3/4).
>>> Functions
The heart of any spreadsheet lies in its list of functions; Opus
provides some 59 functions, which I will divide into the following
categories: Arithmetic, Power, Trig, Logical, Statistics,
Regression, Probability, Financial, Lookup, Cell, Range, and
Miscellaneous. Please note that range references are written as
two cell references separated by the colon, as in "A1:F5".
>>> Arithmetic
- DIV, MOD (numerator, denominator)
These are division and modulus functions that satisfy
the equation below (for both real and integer values):
x = DIV(x,y)*y+MOD(x,y)
- ABS (expr)
Returns the absolute value of expr.
>>> Power
- LN (expr)..............natural logarithm (base e)
- LOG (expr).............log base 10
- EXP (expr).............e raised to a power
- POW (expr1,expr2)......expr1 raised to expr2 power
- SQR (expr).............square
- SQRT (expr)............square root
>>> Trig
All the trig functions (except RAD) expect angles to be
specified in radians, and the inverse trig functions return a
radian value.
- SIN, COS, TAN (angle)........sine, cosine, tangent
- ASIN, ACOS, ATAN (expr)......inverse functions
- SINH, COSH, TANH (angle).....hyperbolic sin, cos, tan
- ASINH, ACOSH, ATANH (expr)...inverse functions
- RAD (angle in degrees).......returns angle in radians
- DEG (angle in radians).......returns angle in degrees
- PI().........................returns pi
>>> Logical
Operators: =, <>, <, <=, >, >=
IF (condition, action, alternate action)
The three IF parameters may be any valid expression,
including nested IFs. IF returns the value of "action"
when condition evaluates to non-zero, and it returns
"alternate action" when condition evaluates to zero.
AND (expr1, expr2, expr3, ...)
Returns 1 if all parameters are non-zero, and 0 if any
evaluate to zero.
OR (expr1, expr2, expr3, ...)
Returns 1 if any parameter is non-zero, and 0 only if
all evaluate to zero.
NOT (expr)
Returns 1 if expr evaluates to zero, and 0 if expr
evaluates to non-zero.
Both AND and OR require at least two parameters.
>>> Statistical
- SUM (range)
Computes sum of all values within range. Empty cells and
labels are considered to have value 0.
- PROD (range)
Computes product of all values within range. Empty cells
and labels are considered to equal 1. Should the range
contain no values, PROD returns 0.
- MEAN (range)
Computes the mean or average of all values within range.
Empty cells and labels aren't considered.
- VAR (range)
Computes the sample variance for range, ignoring empty
cells and labels.
- SDEV (range)
Computes the sample standard deviation for range,
ignoring empty cells and labels.
- SERR (range)
Computes the sample standard deviation of the mean (the
standard error) for range, again ignoring empty cells
and labels.
- MAX, MIN (range)
Return the maximum and minimum values within the range.
- COUNT (range)
Returns the number of cells containing values within
range.
>>> Regression
Note that these three functions all require the "Type"
parameter, which is explained under REGR.
- REGR (type, y-range, x-range)
Performs linear regression via the least squares method
on any of the following models:
Type Equation Model
0 y = Mx+B Linear
1 y = Be^(Mx) Exponential
2 y = B+M*ln(x) Logarithmic
3 y = Bx^M Power
Please note that Opus performs the proper transformation
on the data for you, so you aren't required to have a
separate column for e^x to use the exponential model,
for example. REGR returns the value "M" in the cell
containing the formula and the value "B" in the cell
immediately to its right. "B" will overwrite this cell,
so make sure you store no data there!
- PREDV (type, y-range, x-range, x-expr)
This function calculates the value "y" based on the
regression parameters M and B for the value "x-expr". Be
aware it is more time and memory efficient to simply
plug the number into the proper equation, provided of
course you have already determined M and B through REGR.
- CORR (type, y-range, x-range)
CORR rounds out the regression functions. It calculates
the correlation coefficient, which is a measure of
goodness of fit.
The regression functions allow empty cells within their
ranges. X and y values are associated by their cell locations
within their respective ranges, not by the number of empty
cells that may separate them. For example, consider the range
A1:A5 below:
A B
1 1
2 empty
3 empty
4 2
5 3
For the sake of the regression calculation, value 1 is cell
A1, value 2 is cell A2, and so on. It is perfectly legal to
have an x-value without a corresponding y-value; that x-value
will simply be discarded from the calculation. However, the
reverse is not true; should you specify a y-value without a
corresponding x-value, any of these functions will return an
error. I chose Opus to work in this manner because in my
work, I'm often collecting sets of data from several trials
of the same experiment, where the known values are the same
from trial to trial. Occasionally, during a trial one of the
data points gets botched, and it should be discarded from the
calculation. By simply deleting the cell which would
otherwise contain that y-value, I can use the same template
for analyzing all the data sets. (I wish my Hewlett-Packard
calculator worked this way; instead, it forces me to delete
any x value without a corresponding y value, altering the
relationships between other data sets which may be in its
memory)
Finally, all the regression functions allow ranges spanning
more than one column or row. Values are associated on a row
by row basis.
>>> Probability
RAND (lower bound, upper bound)
Returns a random number between the given lower and
upper bounds. The difference between these two numbers
must be less than 16,277,216, since this is the range of
the ST's random number generator.
COMB, PERM (x, y)
Combinations and permutations of x objects taken y at a
time.
>>> Financial
These functions operate somewhat differently from those in
the original version of Opus. First, all parameters are
required. Second, I've adopted a convention taken by my HP-
27S calculator and also, at least, by the Microsoft
spreadsheet Excel. This involves breaking down money values
into cash inflows, which are expressed as positive numbers,
and cash outflows, expressed as negative numbers. For
example, the PMT function will normally return a negative
value, as you are "paying money." I found this method
confusing at first, but I grew to prefer it, and since I
(occasionally) like to support standards...
The "type" argument appears in all the financial functions,
and if equal to 1, it indicates payments occur at the ends of
periods (ordinary annuities). If type equals 0, payments are
assumed to occur at the beginning of periods (annuities due).
Note that for some combinations of arguments, namely those
implying a simple or compound interest calculation, the type
field will be meaningless; nevertheless, it is required as a
place-holder.
The number of compounding periods should be whatever is most
appropriate; for example, 365*n for daily compounding over n
years. The rate arguments refer to the interest rate per
compounding period, so for monthly compounding over 1 year at
12% interest, rate would equal 12%/12, or 1%, while # periods
would equal 12.
For working, real world examples of the financial functions,
I refer you to the files "FINCALC.OPS" and "AMORTIZE.OPS."
- PV (rate, # periods, payment, future value, type)
Computes present value.
- FV (rate, # periods, payment, present value, type)
Computes future value.
- NPER (rate, payment, present value, future value, type)
Calculates number of periods.
- PMT (rate, # periods, present value, future value, type)
Calculates payment.
- RATE (#periods, payment, present value, future value, type,
guess)
Computes interest rate. RATE uses an iterative method
(Newton's) to find rate; it requires a "guess value" to
seed the process, and you should try using a value less
than what you expect RATE to return. (Good starting
points for guess are values like 0.01, 0.001, etc.) RATE
returns an error if it can't find a reasonably accurate
solution within 20 iterations.
The financial functions are based on the following equation,
and RATE successfully returns if it finds an interest rate
that satisfies the equation to an accuracy of 1E-8. (Equation
from Hewlett Packard 27S calculator manual)
0 = PV+(1+(i%*S)/100)*PMT*USPV+FV*SPPV,
where
S = payment mode (0 for end mode, 1 for begin mode. Note
this is exactly opposite to what the functions
expect as an argument; the value 1 seemed more
logical than 0 to indicate a date later in the
period.)
i% = periodic interest rate
n = number of compounding periods
SPPV = (1+i%/100)^-n
USPV = (1-(1+i%/100)^-n)/(i%/100)
>>> Lookup
- INDEX (row index, column index, range)
INDEX returns the value of a cell within range, located
by row and column index, which must be numbers greater
than or equal to one. These indices represent offsets
into the range, such that the upper-left corner of the
range has 1,1 for its indices. For example,
INDEX(1,1,A1:B5) returns the value of A1, while
INDEX(3,2,A1:B5) returns the value of B3 (row 3 and
column 2 within the range A1:B5).
- VLOOKUP (value, column index, range)
VLOOKUP searches in the first column of the indicated
range for the greatest value less than or equal to the
value you supplied, and it returns the value in the same
row as this cell, with the column determined by "column
index" as an offset into the range. Like INDEX above, a
value of 1 indicates the first column within the range,
2 the second, and so on. The function is a "vertical
lookup," as it searches in the vertical direction. An
error occurs if no match is found or the index
references a column outside the block.
-------------------------------------------------------
Stan Liebowitz Department of Econ/Business
North Carolina State University Raleigh, NC 27607
try liebo@csclea.ncsu.edu it seems to work, more than I can say for