liebo@csclea.ncsu.edu (Dr. Stan Liebowitz) (08/03/89)
- HLOOKUP (value, row index, range)
This function is exactly analogous to VLOOKUP, except
that it performs a horizontal lookup. That is, it
searches the first row of the indicated range, looking
for a value less than or equal to the value you
supplied, and it returns the value in the same column as
this cell but in the row indicated by "row index."
Again, the row index is an offset into the range, such
that row index = 1 indicates the first row of the range.
>>> Cell
- ISERR (cell ref)
Returns 1 if the cell referenced has an error status and
0 otherwise.
- ROW, COLUMN (cell ref)
Return the row and column number of the argument.
>>> Range
- ROWS, COLUMNS (range)
Return the number of rows or columns in the range
supplied.
>>> Miscellaneous
- FAC (expr)
Computes the factorial of expr, which must be an integer
such that 0 <= expr <= 33.
- ROUND, TRUNC (expr, place)
These functions take an expression and either round or
truncate it to the value specified by "place." Place may
be positive, negative or zero, and I think an example
will clarify the differences. Consider the number
126.556, supplied to ROUND and TRUNC, for the following
values of place:
expr = 126.556
place ROUND TRUNC
2 126.56 126.55
1 126.6 126.5
0 127 126
-1 130 120
-2 100 100
-3 0 0
As you can see, you can round or truncate within the
whole number portion of the expression, as well as
within the fractional portion.
>>> Cell Status
Every living cell has an associated status which tells Opus
whether it's full, empty, or for some reason generated an error.
Error-trapping in formula evaluation is extensive, and I think,
complete. For example, if a formula caused a division by zero, the
cell will display a "DivBy0" error message, and any cell that
referenced it will also assume this status upon recalculation.
There are several other error messages, and they are used
depending on the nature of the offense. The syntax error is a
special case; should you enter a formula that's semantically
incorrect, Opus will "keep you in that cell" and (usually) place
the cursor near the source of the error in the edit area.
Users of the original version will be glad to hear that Opus will
no longer crash upon floating point overflows; this was an
additional benefit of putting Personal Pascal on the shelf and
rewriting Opus in Mark Williams C. Now, floating point overflows
will display as "a very large number." There remains only one
loose end in the error-checking; Mark Williams C is supposed to
set a certain variable when the library function "pow" overflows,
and it doesn't. Thus, the spreadsheet function POW and several
others, including the financial functions, will generate "a very
large number" upon pow overflow, and the cell status won't reflect
the error condition. Hopefully, MW Co. will correct this in a
future compiler update.
>>> Named Cell and Range References
Opus v. 2.00 and above support named cell and range references, so
that you may give a cell or range a friendly English-language name
for use within formulas. These "aliases" may be up to 10
characters long; they consist of alphanumeric characters and the
underscore and may not begin with a numeral. Also, you aren't
allowed to create a name that duplicates a valid cell reference or
a function name. Opus supports as many as 100 of these aliases,
and you create them through the Define Name dialog, which is found
under the Options menu. To use this feature, enter the name you
wish to define in the "Name:" line and also the cell or range
reference (using dollar signs to indicate absoluteness) in the
"Refers To:" line. Then, add it to the list by clicking "Add." The
name will appear in the scrollable list (which is always sorted),
and it will be available for use in functions. To delete a name,
click on it and then click "Delete." It will be removed, and all
occurrences of that name within the worksheet will be replaced by
the underlying explicit cell or range reference. Note that when
you click on one of the names in the list, Opus places that name
and its value in the two editable fields below the list. As long
as you don't alter the name itself, you may then modify the
"Refers To:" line and click on "Add" to lock in the modification.
When deleting or altering names, there may be a slight delay as
Opus performs some housekeeping (converting names to explicit
references, validating what in effect may be completely new
formulas, updating dependency lists, etc.). Bear in mind these
named references behave exactly like explicit ones, following the
same rules governing adjustment for worksheet operations such as
copy, move, insert row, and others previously described. You may
notice when copying, moving, or replicating formulas that Opus
substitutes the explicit reference for the named reference. This
happens when a named reference needs to be adjusted; there just
isn't any good way to make a single name point to more than one
cell or range! However, this substitution will not be applied to
completely absolute named cells or ranges, since these are never
adjusted.
>>> Files
You may save and load whole worksheets and blocks; block files
consist solely of data, while worksheet files contain the data,
all the user settings, and the four charts. A block may be merged
into the current worksheet, while loading a file causes the
current worksheet to be erased.
File operations are much faster in Opus 2.00, largely due to use
of a custom 10 sector buffering scheme. In other words, Opus
always reads or writes 10 sectors worth of data when it accesses
the disk, unless of course there simply isn't enough data to fill
10 sectors. The speed increase is truly remarkable, and to take
full advantage of it on floppies, be sure to use a "twisted" disk
formatted for 10 sectors/track.
Please note that Opus 2.00 files are not compatible with the
original; too many fundamental changes were made. However, I will
maintain compatibility with this version in future updates.
>>> Printing
You control worksheet printing through the Print dialog, found
under the File menu. You may enter two title lines, and these will
appear on the first page only. Opus supports one-line headers and
footers; you may easily include such things as page number, file
name, date, and time, and any part of a header and footer may be
left, right, or center justified. To see how to accomplish this,
please refer to the Help menu item Print/Save As Text. The only
really new features here are the margin and lines/page fields;
margins are specified as number of characters or lines from the
respective side of the page. For example, a right margin of 5 will
leave a 5 character margin on the right side of the page. The
other Print dialog options should be reasonably self-explanatory.
In order for features such as condensed print and draft vs. final
to work, Opus needs to know the proper control codes for your
printer. Since these are everything but standardized, I've
included a program, PRINTDEF.PRG, so that you may customize Opus
for your printer. Please see the file INSTALL.DOC for details on
PRINTDEF.
To wrap up this subject, you may also save a file as text, which
really means print a standard ASCII file to disk. This file will
include titles, headers, footers, etc., but it will not contain
any text special effects (naturally).
>>> Freeze Titles
To freeze a row and/or column, position the cell-cursor in the
*last* row or column on the screen you wish to freeze. For
example, suppose you have rows 5 through 20 on the screen, and you
want to freeze rows 5-15. Place the cell cursor in row 15, and
choose Freeze Titles from the Options menu. Select Row and press
OK. Now, you may scroll downwards, while the frozen rows remain on
screen; you are also prevented from advancing the cursor above row
16. This can be very useful when you have labels over several
lines that you'd like to keep in view at all times. Although
freezing affects the screen appearance, it has no effect on
printing.
>>> Defaults
Opus 2.00 allows you to save your favorite settings in a
configuration file, OPUS.CNF, which is loaded at boot-time. This
file contains default path information, worksheet options, and
chart options. You may change the defaults and save a new
configuration file through the Defaults item under the File menu.
To change paths, click on the path lines and select a new one with
the item selector. Besides the paths, OPUS.CNF contains the
following items:
- global column width
- global cell format
- auto-cursor status and direction
- natural order and automatic recalculation statuses
- show formulas status
- grid on/off
- small font (for monochrome)
- printer:
- show row/column titles
- condensed print status
- show formulas status
- header
- footer
- draft vs. final
- top, bottom, left, and right margins
- lines/page
- charts:
- all user-controlled options (except selected ranges),
taken from current active chart
Note: The Temporary Files path refers to the path where the
temporary metafile and worksheet files will be saved during chart
printing. More to come...
///////////////////////////////////////////////
///// Charts /////
///////////////////////////////////////////////
NOTE: If GDOS isn't installed, you won't be able to chart. If you
aren't sure how to create a proper ASSIGN.SYS or OPUS.WID file,
please see INSTALL.DOC for instructions (it's really pretty easy,
as I've written two short programs to facilitate the process).
>>> The Basics
The charting facility is implemented as a mode separate from the
worksheet. To create a chart, you first select ranges from the
worksheet for the chart data sets, and then select To Chart from
the worksheet Chart menu. This takes you to a new screen and menu
bar, from which you may customize the chart's appearance and print
it. Opus displays the chart in a window whose maximum size is 8
inches wide by 5 inches tall, and the screen display is a very
close approximation of the final printer output. Finally, to get
back to the worksheet, select To Opus from the chart File menu.
Note that you can easily flip back and forth by pressing the
control-C key.
In the following sections, I'll describe some of the charting
conventions. Since I feel the charting features are mostly
intuitively obvious, afterwards I will simply run through the menu
items and explain some of the subtler points, leaving the rest to
experimentation. However, it is probably a good idea to read the
section on saving metafiles and printing charts, which are found
under the "File" heading.
>>> Axis Naming Conventions
All chart types except Pie are essentially plots of the equation
y = f(x), where y is a variable that depends on the value of x. X
is typically associated with the horizontal axis and y with the
vertical, but since Opus allows rotation of the axes, this
distinction can become blurred. Since x is more properly referred
to as the "independent variable" and y as the "dependent
variable," I call the axes "Dependent Axis" and "Independent
Axis."
>>> Data Series
A data series is simply a set of values associated with an axis
variable. The Independent Axis may have only one associated data
series, but you may specify up to six Dependent Axis series,
identified by the letters A-F. To select a range as a data series,
first select a worksheet range, drop down the worksheet Chart
menu, and click on the desired item under the greyed-out "Data
Ranges" label. A small dialog appears, containing the range
definition. Press OK to complete the process, and the chosen menu
item will be checked to indicate it's in use. To delete a series,
click on the appropriate menu item and click on the Clear button
within the select-range dialog.
In order to produce a Pie chart, you must first select a range for
the Independent/Pie series (this one does double-duty). To produce
any of the other chart types, you must select at least one
dependent variable range. The dependent and independent variables
are associated exactly as for the worksheet regression functions.
That is, ranges may extend over more than one column; they are
associated on a row by row basis. Also, it is perfectly legal to
have an independent value without a corresponding dependent value,
but the reverse is not true.
Finally, you may specify labels for the independent range for any
chart type except Pie; if the first cell within the independent
range is a label, all the rest must also be labels. The labels
have implicit values of 0, 1, 2, ...
>>> Plotting Standard Error Bars and Regression Lines
These may be automatically plotted, providing you've created the
formulas in the worksheet. Simply associate the ranges with the
appropriate data series. Note that for regression, you must select
the slope and y-intercept cells as a range, and the slope must be
the first cell in the range.
>>> The Screen Display
The size of the chart as displayed on the screen is very nearly
the same as the final printed size, and the relationships between
objects are practically identical between the screen and printer.
When you alter the chart size, you will find that the window
containing it expands or shrinks, and it is centered at all times.
The window size corresponds to the chart area, with a 0.1 inch
margin around all sides. It also reflects the "frame" that will
contain the chart when it's imported as a metafile into another
program.
Since redrawing the screen can take a couple of seconds, I thought
it preferable to redraw the screen only when requested. Thus, you
may make many changes affecting a chart's appearance, and when
done, press 'D' to force a redraw. About the only time the chart
is drawn without the user's request is when he flips over from the
worksheet, changes the chart size, or selects a new chart with the
chart switcher.
>>> Markers
The objects indicating plotted points are called markers. For line
and scatter charts, the markers are as follows: data series A:
circle, B: square, C: up triangle, D: cross, E: down triangle, F:
plus. Bar, pie, and area markers are differentiated on the basis
of fill pattern. Be aware that in color, the circle marker may
appear as a plus sign at the standard size of 0.08 inches due to
the limited resolution, but it will print fine (details on
altering the size follow).
>>> The Chart Switcher
Opus supports four charts per worksheet, and you select them
through the Chart Switcher dialog. This dialog is accessible from
both the worksheet Chart menu and the chart File menu. To restore
a chart to the default settings and clear all range definitions
for that chart, click on the Restore Defaults button.
>>> Measuring
Many dialogs allow you to alter the sizes of objects; Opus uses
inches for all measurements.
>>> The File Menu
A. Save Metafile...
Opus is capable of saving two types of metafiles, a
standard one (.GEM) suitable for programs that read
metafiles, like Publisher ST, and a special one (.GEO)
intended for later printing by OutChart. Opus will ask
you to specify the format before allowing you to choose
a file name; choose "Standard" if you intend to load the
metafile from another (unrelated) application and
".GEO" if you plan on printing the file from OutChart.
Note: OutChart doesn't like standard metafiles at all;
please feed it .GEO files, unless you enjoy "Corrupted
Metafile" error messages (read on for explanation).
Although programs like Publisher ST allow scaling of
imported metafiles, for best results you should keep the
relative horizontal and vertical dimensions the same.
You may notice that the square marker types vary in size
somewhat in a metafile imported into Publisher, even
though the chart is set to the right size. This is
simply a consequence of round-off error in calculating
the size of the box on the screen (although the box is a
square by metafile standards, some round-off error
occurs depending on where the box is to be located on
the screen or page). As you would expect, this effect
decreases in significance as the resolution of the
output device increases; that is, it's most noticeable
on the color monitor and hardly noticeable on a 150 DPI
printer. Before printing a chart, Opus saves a temporary
metafile which it later interprets; Opus isn't afflicted
with similar round-off errors because it uses some
special metafile commands to ensure uniform sizes for
markers. OutChart metafiles contain these special
commands as well. Unfortunately, other programs wouldn't
know how to interpret these special commands, so "Save
Metafile" can also save a standard one that should be
compatible with any program that claims to load
metafiles.
B. Print...
Prints the chart. An option for a final formfeed is
present, since some printer drivers execute a formfeed
when Opus closes the printer workstation, but others
don't. For details on sizing the chart and placing it on
the page, please refer to Chart Dimensions under "The
Edit Menu." See the file OUTCHART.DOC as well for an
alternative to printing from within Opus.
To conserve memory, Opus loads the printer driver and
fonts at print-time. The process is as follows:
1. Opus saves a metafile and the worksheet, in the
path specified for Temporary files
2. Opus releases the worksheet memory and screen
font memory to the system,
3. Opus loads the printer driver and fonts,
4. Opus reads in the metafile and prints the chart,
5. Opus releases the printer driver and font
memory,
6. Opus reloads the screen fonts,
7. Opus reloads the worksheet,
8. Opus deletes the temporary metafile and
worksheet file.
For this to work, several conditions must be satisfied.
First, you must have your GDOS disk in the boot drive or
have the files set up on a hard drive. Second, you must
specify a drive with enough free space to store the
temporary metafile and worksheet files (metafiles
typically range 2-10K; worksheet files vary considerably
more). Third, you must have enough free RAM in the
system to load the printer driver and fonts. FONTWID.PRG
provides this information, when you create your OPUS.WID
file. When you first load Opus, before entering any
data, check Statistics under the worksheet Options menu
and make sure the System Memory is greater than or equal
to the memory required to print, as given by FONTWID.
Although printing is a rather disk-intensive process, it
really is pretty fast on a hard drive system. For best
results on any system, set the Temporary Files path to a
RAM disk, but make sure you have enough free RAM to
print!
Finally, I should note that the ST's memory allocation
system is flawed. Under some essentially unpredictable
conditions, after printing you may find yourself with
less worksheet RAM than before. This is due to memory
fragmentation and is generally not a problem, unless
your worksheet is already pushing the free RAM limits.
In this case, you may find yourself with too little
memory to reload the worksheet. To protect yourself from
this, always save your worksheet before printing a
chart. Fortunately, the fragmentation seems to occur
only with the first printout; I've printed more than 10
charts in a single session with no problems beyond
losing 30K of worksheet space due to the fragmentation.
C. Load/Save Chart
Presently not implemented, since four charts are saved
with the worksheet. If there's enough demand, I may add
these in a future update.
D. Chart Switcher...
Allows you to select any of the four charts. To simply
switch from one chart to another without calling this
dialog, press the appropriate number key.
>>> The Edit Menu
A. Axes
Allows you to fine-tune the axis appearance as needed.
To switch from editing one axis to the other, click on
the shaded title bar at the top of the dialog. I believe
all the options are pretty self-explanatory, except "the
axis crosses other axis at tic mark#" option. Opus
always tries to determine a "nice" place for the axes to
cross one another. You may override the default choice
by clicking on the number field. This will highlight it
and enable the up and down arrows beside it. You may
then increase or decrease the value by clicking on the
arrows. Note that tic# 0 refers to the lowest tic on the
vertical axis and the leftmost tic on the horizontal
one.
B. Chart Dimensions
You may create charts of any size up to 8 inches wide by
5 inches tall. You may specify the placement of the
chart on the page through the left and top margin
values. Some printer drivers support only an 8x10 inch
area, such as my Migraph Deskjet drivers. Thus, to
horizontally center a chart 7 inches wide and 5 inches
tall, I specify 0.5 inches as my left margin (left
margin = (8-7)/2). To vertically center the same chart,
I specify 2.5 inches as the top margin (top margin =
(10-5)/2). Some printer drivers may allow printing to
the entire 8.5 inch width of a page (or 11 inch height),
so you may have to experiment a bit to determine the
proper offsets. Remember, when you create a chart AxB
inches in size, these numbers include a 0.1 inch margin
around the entire chart area.
C. Legend
A legend allows you to give a label to each of the data
series on multiple-series charts. The legend may appear
to the right of the chart or below it, and the "legend
labels" are preceded by the marker associated with the
series. To change the size of these legend markers,
enter a new value into the Chart Dimensions dialog under
the "Marker Size" field. For bar, pie, and area charts,
the marker is a small filled box, and the size of these
markers is twice the size entered under Marker Size (I
did it this way so I could easily flip between scatter
and bar charts and not worry about getting huge chart
markers or tiny legend markers).
The Edit button allows you to create or modify the
legend labels. You will note 12 lines in this dialog;
the last six are for Pie charts, where each slice may
have a legend entry. When you select a range for legend
text from the worksheet, the labels are copied into the
legend text array and are from that point separate from
the worksheet.
D. Pie Appearance
You may have pie slice values, percentages, and legend
labels appear by the slices or within the legend, in
various combinations. If you choose "By Slices" for some
of these and have the standard legend appear to the
right of the Pie, the slice labels may overlap the
legend (sorry, but this is the only case where the chart
doesn't get out of the way properly. Maybe next
revision). To correct for this, you may enter a larger
value for the Legend distance into the Chart Dimensions
dialog.
E. Scaling
Opus generally selects a nice numeric range for your
data. To override the defaults, invoke the scaling
dialog and click on the entry you wish to change (you
may not edit it otherwise). It will be highlighted,
indicating user settings are in effect.
F. Titles
Opus provides two-line titles for each axis and the
chart itself. Independent axis titles are shown as I1:
and I2:, while dependent axis titles are shown as D1:
and D2:. Just as with legends, you may copy a worksheet
range into the titles array. You may then edit or create
new entries through the Titles dialog. To change font or
special effects, click on the entry you wish to change,
and the font selector will appear. To turn off titles,
uncheck the box at the upper right of the title lines.
G. Values by Markers
If this is in effect, Opus displays the underlying value
of a marker in an appropriate place for scatter, line,
bar, and stacked bar charts. Use Pie Appearance to
perform this action for Pie charts.
>>> The Options Menu
The items under this menu all act as on/off switches.
A. Character Spacing
When on, Opus uses the information from OPUS.WID to
determine the width of text on the printer. With this
value, it's possible to scale the text string so that it
occupies the same relative width on the screen as it
does on the printer, allowing a much better screen
approximation of the final output. This can be very
important, since many screen fonts are much wider than
their printer counterparts; it's especially important
when you want to display text centered within a box, as
for a chart legend, because the box dimensions are
calculated based on the size of the string.
Note that when turned on, characters may overlap. This
can be especially bad for the vertical axis title on a
color system, but remember, the true extent of the text
is shown (even though it may not be readable!).
Although this feature has no effect upon printing, it
should be left on when saving a standard metafile. That
way, object dimensions are calculated based upon printer
font widths. When you import such a metafile into
Publisher ST, the strings may appear to exceed the
boundaries of a legend box, for example, since Publisher
doesn't perform the "width scaling" on metafile text
that it does on regular text. Fear not, the metafile
will print correctly! If you had saved the metafile
without character spacing, it would look perfect on the
screen in Publisher ST, but the legend box would print
too large, leaving a very noticeable gap at the right
border of the box. This is a true tradeoff, and it's a
consequence of the NON-device independence of GDOS fonts
(I want display Postscript!)
B. Regression
This causes regression lines to be displayed for scatter
charts, provided you have defined ranges for the
regression values. Although you may perform regression
on non-linear models, at present Opus only plots the
linear model properly (others may be supported in future
releases).
C. Rotate Axes
Makes the current horizontal axis vertical and the
current vertical axis horizontal.
D. Show Border
Draws a full box around all charts except Pie.
E. Standard Error Bars
This causes the error bars to be displayed, provided you
have defined ranges for the error values. Available for
scatter, line, and bar charts.
>>> The Types Menu
A. Area
Plots an area chart. All dependent values should be
either positive or negative, with no mixing (or, if
anyone can tell me a meaningful way to plot an area
chart with both positive and negative values, I'll try
to implement it). Maximum number of independent axis
values (categories): 48.
B. Bar
Plots a bar chart. Use Bar Spacing to vary width of bars
and distance of bars from tics. Maximum number of
categories: 48.
C. Line
Essentially a "connect the dots" scatter plot. No limit
to number of points.
D. Pie
Plots a pie chart with up to 12 slices. You may explode
any number of slices by clicking on them. Both the Chart
titles and horizontal axis title are printed, if
defined.
E. Scatter
Scatter plot. No formal limit to number of data points.
F. Stacked Bar
The cautions described for area charts apply here as
well.
>>> The Special Menu
Currently, Fill Pattern is the only item available, but object
oriented drawing features may appear here in a future release. To
change the fill pattern for a series, select the series number
from the Fill Pattern dialog (#1-6 = ranges A-F, while #7-12 refer
to the remaining pie slices). Then click on the new pattern and
press OK. If you are viewing a bar, pie, or stacked bar chart,
simply double-click within a marker belonging to the series you
wish to change; the Fill Pattern dialog will appear, indicating
the chosen series. Pie slices are numbered from 1-12, counter-
clockwise.
>>> Future Plans
Although I feel this version of Opus is quite complete, there
remain several things I would like to add, such as date/time and
string functions to the worksheet. Also high on my list of
priorities is implementation of some simple object oriented
drawing features for the charting facility. Another possibility is
a curve-fitting feature, which would generate a smooth curve
through a series of points. Finally, I'm always open to ideas, so
let me know what you would like to see.
-------------------------------------------------------
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