home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Media Share 9
/
MEDIASHARE_09.ISO
/
business
/
rebel30.zip
/
CHAP5-11.DOC
< prev
next >
Wrap
Text File
|
1993-03-10
|
180KB
|
3,753 lines
_______________________
______________________________________________/ Chapter 5 EDIT MODE
From the Input Line, your editing capabilities are limited to erasing
the last character typed with the <Back Space> key. In addition,
there is no way of modifying the contents of existing cells - short of
retyping them. Obviously, if you are working with long complicated
formulas, this could be a tedious way of correcting errors or making
changes.
To provide you with more flexibility in this area, a special set of
function key options are available, that will allow you to revise the
contents of existing cells. Specifically, you will be able to; 1) use
the arrow keys to move the input cursor; 2) delete characters; 3)
insert new characters; 4) search and replace patterns of characters;
5) 'capture' a series of characters to be re-inserted at a different
location within the same or different entries; 6) incorporate the
formulas and labels of other cells into the cell being edited; 7)
locate closing brackets; 8) display error messages; and 9) single step
through formula calculations, allowing you to check the intermediate
results of each operation as an expression is evaluated.
To enter the 'Edit Mode', move the Cell Pointer to the cell you wish to
modify and press the <F2> function key TWICE from the Root Menu. The
contents of that cell along with the Edit Mode Function Key Options
should appear just above the Column Bar. You can now use the arrow keys
to move the input cursor to any location within the entry. The <Up>
arrow will move you immediately to the beginning of the entry, while the
<Down> arrow will move you back to the end. There is also an 'insert'
and 'overlay' character mode that will allow you to insert new
characters or overwrite existing ones. The F1 (INS) option will toggle
you between them (refer to the section below). And, you can use either
the <Del> key or F2 (DEL) option to delete characters. Try entering
the Edit Mode with the Cell Pointer positioned on an empty cell and
experimenting with each of these options. Remember, you can use the
<Esc> key at any time to abort.
After you have made your changes, you can save them by pressing the
<Enter> key. If a problem is detected with an expression when exiting
the Edit Mode, an error message will appear at the top of your screen
and you will be returned to make the necessary changes. This differs
from the way in which errors are handled from the Input Line. Here, no
error messages will appear. Instead, only a series of question marks
(????) will be displayed in the cell(s) that have detected a problem and
you are allowed to continue as though nothing has happened. You can get
a general idea of what went wrong in these cases by moving the Cell
Pointer to the cell that contains the question marks and then entering
the Edit Mode. This will cause an error message to be displayed that
will hopefully help you determine what when wrong. If you are unable to
correct the problem from within the Edit Mode, use the <Esc> key to
exit. You can return at a later time to fix the problem. Be aware,
however, that when you abort the Edit Mode (after an error has been
detected), the spreadsheet will sometimes insert a single quote (') at
the beginning of the expression. If this happens, you must remove the
quote before the expression can be re-evaluated.
.........
: f2 : From the "Root Menu", press F2
: EDIT :
.........
: f2 : then press F2 again
: Edit :
_____ _____ _____ _____ _____
EDITING: |1 INS| |2 DEL| |3 RPL| |4 GO| |5 CUT| . . .
f1 - Toggles between INSERT and OVERWRITE entry modes. When
the descriptive word is displayed in capital letters
(INS), you're in the 'overwrite' mode. This means that
any character you type will be added to the end of the
entry or will overwrite an existing character, depending
on the cursor position. When the insert identifier is
displayed with lower case letters (ins), you're in the
'insert' mode. In this case, new characters are placed
between existing characters.
f2 - This key DELETES the character at the current cursor
position. It works just like the <Del> key on your
keyboard.
f3 - SEARCHES the entry for a pattern of characters and
REPLACES them with another pattern. You will be prompted
to enter both of these patterns.
f4 - MOVES the Cell Pointer to another cell location on the
worksheet - without forcing you to exit the Edit Mode.
This allows you to view the contents of other cells; and,
if you like, to use the F7 (LBL) option to incorporate
them into the current entry.
f5 - CUTS (captures) a series of characters from the text that
is currently being edited, placing them into the 'cut'
buffer. This buffer can then be re-inserted at any
location within the entry. To execute the command, press
the F5 key with the cursor positioned on the first
character of the string you wish to capture. Additional
characters will continue to be placed into this buffer
while the cursor is moved to the right with the Right
<Arrow> Key. Do not press any other key (other than the
right arrow key) until AFTER you have TERMINATED THE
'CUT' OPERATION BY PRESSING THE F5 KEY A SECOND TIME.
The buffer's contents are preserved until the process is
repeated (i.e. they are not lost when you exit the Edit
Mode). This allows you to insert the buffer into other
cells, as well. The F6 (PUT) option is used to copy the
contents of the 'cut' buffer back into the entry, at any
cursor position you like.
_____ _____ _____ _____ _____
. . . |6 PUT| |7 LBL| |8 ADR| |9 DBG| | [()]|
f6 - PUTS (inserts) the contents of the 'CUT' buffer into the
entry at the current cursor location (see the F5 'CUT'
option).
f7 - Inserts the 'formula' or 'label' of the cell that is
highlighted by the Cell Pointer into the entry - at the
current cursor location. The F4 (GO) command can be used
to move the Cell Pointer to the cell that contains
statement you wish to capture. While in the Edit Mode,
the <Home>, <End>, <Pg Up>, and <Pg Dn> Keys can also be
used to move the Cell Pointer, one cell at a time.
f8 - This option works like the F7 (LBL) command except that
it inserts the 'Cell Address' of the cell that is
highlighted by the Cell Pointer into the current entry.
f9 - This command is used to debug a 'formula' by allowing you
to evaluate it - one step at a time. After each
operation, it will pause and display the value calculated
or substituted. Press the <Enter> key to continue to the
next operation.
f0 - With the cursor placed on any square bracket [] or
parenthesis (), this option will cause the cursor to
jump to its CLOSING bracket. This command also works
with the ?:; (arithmetic-if) delimiters.
____________________________
_________________________________________/ Chapter 6 TECHNICAL INFO
This chapter is designed to provide you with a more detailed explanation
of the more important topics covered thus far. Every attempt has been
made to keep each of these sections as short and to the point as
possible. The first six contain most of the what you'll need to use
REBEL effectively. It's highly recommended, however, that you read them
all - in the order they are presented, since each section tends to build
on the information that precedes it.
Chapter Contents
Section 6:1 CELL TYPES
Section 6:2 CELL ADDRESSES
Section 6:3 OPERATORS
Section 6:4 FORMULAS
Section 6:5 ARRAYS
Section 6:6 MULTIPLE WORKSHEETS
Section 6:7 RANGE OPERATIONS
Section 6:8 DIRECT CELL ADDRESSES
Section 6:9 INDIRECT CELL ADDRESSES
Section 6:10 WHEN DO FORMULAS GET RECALCULATED?
Section 6:11 FORCING CELLS TO RECALCULATE
Section 6:12 OPTIMIZING YOUR WORKSHEET
Section 6:13 MISCELLANEOUS TOPICS
-----------------------------------------------------------------------
Section 6:1 CELL TYPES
-----------------------------------------------------------------------
A FORMULA, LABEL, or VARIABLE LABEL cell is created each time an entry
is made (limited to 255 characters) from the Input Line. The specific
type of cell created depends on whether the entry is interpreted as a
mathematical expression (a formula) or a string of characters (a label).
For the most part, this is done automatically by the spreadsheet, which
will attempt to make an educated guess based on the first character of
your entry. It is important that the correct decision is made (and in
most cases it is), since the cell 'type' governs the way in which a
cell's data can be used by other cells.
FORMULA CELLS are created whenever a mathematical expression (formula)
is entered at the Input Line. This can be anything from a simple
number to a complex set of arithmetic operations. To be interpreted
as such, it must begin with one of the following characters:
+ - . [ ( 0 1 2 3 4 5 6 7 8 9 @
There are, however, times when an otherwise valid expression may not
begin with one of the above characters (i.e. expressions that begin
with function calls). In these situations, you must FORCE the
spreadsheet to interpret your entry as a 'formula' by beginning it
with a plus sign (+). For example, the formula "sqrt(16)" should be
entered as "+sqrt(16)" to prevent it from being interpreted as a
'label'. The plus sign (+) will not affect the results.
LABEL CELLS are created whenever an entry is made that DOES NOT begin
with one of the characters that denotes a formula. Labels consist of a
string of characters that appear on the screen exactly as they are typed
from the Input Line. The spreadsheet makes no attempt to interpret
these entries in any way. If a label is longer than the column width
of the cell that it's stored, it will overlap up to to 63 adjacent empty
cells before its display is finally terminated. Just as with formulas,
there are occasions when a label will begin with a character that can
cause some confusion. For example, the string "1st of January" begins
with a '1'. If typed in as it stands, the spreadsheet will look at the
first character and think: "this is a formula", and it will create a
FORMULA CELL to store it in. This, of course, will produce an error
when the spreadsheet attempts to calculate its value! That is, what
does "1st of January" mean mathematically? It is easy to tell when this
has occurred by the question marks (?????) that are displayed by the
cell instead of the label you expected. The solution is simple - just
begin the label with a single quote ('). This will FORCE the
spreadsheet to view the entry as a label, rather than a formula. The
leading single quote will not appear when the string is displayed.
VARIABLE LABEL CELLS are created ONLY when labels are entered that
begins with a double quote ("). Unlike the single quote ('), which is
often not necessary for standard labels, THE DOUBLE QUOTE IS REQUIRED
FOR VARIABLE LABELS. Like standard labels, variable labels display
everything in the character string (just as its appears) - except for
Cell Addresses. When a Cell Address (e.g. [1,2]) is encountered, the
contents of THAT cell is displayed, rather than the Cell Address itself.
For example, if cell [1,2] contains the string "red", an entry such as
this:
"The car is [1,2].
would be displayed as:
The car is red.
UNDEFINED CELLS are automatically created by the spreadsheet when a
reference is made to an 'empty' cell. These cells are easily identified
by the long underscore (________) they display. They can be removed
either by eliminating the references to them or by making a valid entry
in their place. Normally, a reference to an UNDEFINED (empty) cell
within a formula will produce questionable results. When this occurs, a
series of question marks (?????) will appear in the display of the cell
that contains the reference. You can locate the UNDEFINED cells that
another cell is addressing with the (~) Cell Trace Command that is
described in Chapter 7.
-----------------------------------------------------------------------
Section 6:2 CELL ADDRESSES
-----------------------------------------------------------------------
A CELL ADDRESS (e.g. [row,column]) is simply a way of referencing
another cell's value within a formula. Not only do Cell Addresses allow
you to access the values of other cells, but they also cause the
formulas that use them to be recalculated when the values of the cells
they reference are changed. Cell Addresses are composed of four (4)
parts or fields (the Row, Column, Array Element, and Worksheet Level)
separated by commas and enclosed within square brackets []. Of these,
only the 'Row' and 'Column' fields that define the cell's position on
the worksheet are required.
REQUIRED________ _______________ OPTIONAL
| | | |
[Row,Column,(Array),(Level)]
| |
The ARRAY ELEMENT field __| |____ The WORKSHEET LEVEL field is
is only needed when only needed when accessing
accessing array values cells at a different level
The ARRAY ELEMENT field is optional and, if not used, will default to
element position (0). This position is reserved by a FORMULA cell to
store the value that is the result of evaluating its formula. It is the
only position assigned to a FORMULA cell when it is first created and is
generally not considered part of the array space. As a result, array
element numbers greater than (0) take on no real meaning until an array
area (that can range from 1 to 8000 elements) is assigned to the cell.
Once this is done, however, each of these element positions can be
assigned a value that in turn can be accessed through this field.
Although an array space can not be assigned to a LABEL cell, this field
can be used to address the individual characters of the label itself.
The length of the label governs the largest element number that can be
accessed. For additional information, see Section 6:5 on Arrays.
The WORKSHEET LEVEL field is also optional and defaults to the zero (0)
when left blank. A zero (0) in this field will always be replaced by
the spreadsheet with the level number (ranging between 1 and 4) of the
cell that contains the reference. This is what allows a Cell Address to
be copied to virtually any level without requiring it to be modified.
When a value greater than zero (0) is loaded into this field, it will
result is an absolute reference to a cell at that level. Keep in mind,
the Cell Addresses that uses this field may need to be modified if the
worksheet that contains them is later loaded at a different level. It's
always best to leave this field BLANK when accessing cells at the same
worksheet level (refer to Section 6:6 - Multiple Worksheets).
Examples:
[1,2] ==> Makes a reference to the 'value' in
the cell located at row 1, column 2
of the worksheet level that contains
the formula.
[1,2,0] ==> same as above.
[1,2,,4] ==> Makes an cell reference to the value
in the cell located at row 1, column 2
of worksheet level 4.
[1,2,3] ==> References the value in the 3rd array
element of the cell located at row 1,
column 2 of the worksheet level that
contains the formula.
NOTE: The Row, Column, Array, and Worksheet fields can themselves be
Cell Addresses.
-----------------------------------------------------------------------
Section 6:3 OPERATORS
-----------------------------------------------------------------------
The operators provided by this spreadsheet are patterned after those
defined in the 'C' Programming Language - with a few minor exceptions.
The precedence of each operator is listed in following table from
highest to lowest priority. Parenthesis (), of course, can be used
at any time to alter the order in which operations are performed.
UNARY OPERATORS
---------------------------------------------------------------------
Op Name Use --> Result Description
---------------------------------------------------------------------
! Logical NOT !0 --> 1 converts 0 (F) to 1 (True)
!5 --> 0 converts 5 (T) to 0 (False)
- Unary Minus -(-5) --> 5 negates a negative 5
+ Unary Plus +(-5) --> -5 has no affect
ARITHMETIC OPERATORS
---------------------------------------------------------------------
Op Name Use --> Result Description
---------------------------------------------------------------------
** Exponentiation 4**2 --> 16 4 raised to the power of 2
* Multiplication 4*2 --> 8 4 multiplied by 2
/ Division 4/2 --> 2 4 divided by 2
% Modulo 5%3 --> 2 remainder of 5 divided by 3
+ Addition 5+3 --> 8 5 added to 3
- Subtraction 5-1 --> 4 5 minus 1
NOTE: When using the modulo (%) operator, the values
of both expressions are temporarily converted to whole
numbers before the operation is performed. The result
is always a whole number.
BITWISE (SHIFT) OPERATORS
---------------------------------------------------------------------
Op Name Use --> Result Description
---------------------------------------------------------------------
<< Left Shift 2<<3 --> 16 shift the bits that
represent the number 2
3 positions to the left
>> Right Shift 16>>3 --> 2 shift the bits that
represent the number 2
3 positions to the right
NOTE: The values of both expressions are temporarily
converted to whole numbers before these operations
are performed. The result is always a whole number.
RELATIONAL OPERATORS
---------------------------------------------------------------------
Op Name Use --> Result Description
---------------------------------------------------------------------
== Equal to 4==5 --> 0 (F) 4 equals 5 (FALSE or 0)
!= Not Equal to 2!=3 --> 1 (T) 2 does not equal 3 (TRUE)
<= Less or Equal 2<=2 --> 1 (T) 2 is less than or equal
to 2 (TRUE or 1)
>= Greater or Equal 2>=4 --> 0 (F) 2 is not greater than or
equal to 4 (FALSE or 0)
< Less than 2<3 --> 1 (T) 2 is less than 3 (TRUE)
> Greater than 3>2 --> 1 (T) 3 is greater than 2 (TRUE)
BITWISE OPERATORS
---------------------------------------------------------------------
Op Name Use --> Result Description
---------------------------------------------------------------------
& Bitwise AND 1&3 --> 1 bits are set where
corresponding bits of
both numbers are the same
| Bitwise OR 1|3 --> 3 bits are set when either
number has a corresponding
bit set
NOTE: The values of both expressions are temporarily
converted to whole numbers before these operations
are performed. The result is always a whole number.
LOGICAL OPERATORS
---------------------------------------------------------------------
Op Name Use --> Result Description
---------------------------------------------------------------------
&& Logical AND (1<2)&&0 --> 0(F) if (1<2) is TRUE AND
0 is TRUE (0 is FALSE)
then the result is
TRUE (1)
|| Logical OR (1<2)||0 --> 1(T) if (1<2) is TRUE OR
0 is TRUE then the
result is TRUE (1)
ASSIGNMENT OPERATORS
---------------------------------------------------------------------
Op Name Use Description
---------------------------------------------------------------------
IMPORTANT! Assignments can only be made to ARRAY ELEMENTS or the
SPECIAL REGISTERS associated to Range Operations.
= Assignment [1,1,6] = 5 assigns the value (5)
to the sixth array
element of cell [1,1]
+= addition x += 5 same as x = x + 5
-= subtraction x -= 5 same as x = x - 5
*= multiplication x *= 5 same as x = x * 5
/= division x /= 5 same as x = x / 5
%= modulo x %= 5 (see note) same as x = x % 5
<<= shift left x <<=5 (see note) same as x = x << 5
>>= shift right x >>=5 (see note) same as x = x >> 5
&= AND (bitwise) x &= 5 (see note) same as x = x & 5
|= OR (bitwise) x |= 5 (see note) same as x = x | 5
NOTE: The Compound Assignments (% << >> & and |)
will temporarily convert both operands to whole
numbers before computing the results. The result
is always returned as a whole number.
Example:
1) [1,2,1] = 5 (legal)
2) [1,2] = 5 (illegal)
3) [1,2,1] += 5 (takes the value in [1,2,1], adds 5 to it
and places the results back into [1,2,1])
TERNARY CONDITIONAL OPERATOR
---------------------------------------------------------------------
Op Name Use --> Result Description
---------------------------------------------------------------------
?:; Arithmetic (2>1)?5:4; --> 5 IF 2 is greater than 1
if-then-else THEN(?) 5
ELSE(:) 4
____ (optional assignment to an array element)
|
| (TEST) (IF TRUE) (IF FALSE)
[r,c,a] = (Expression#1) ? Expression#2 : Expression#3 ;
| | |
|_________(REQUIRED)__________|
IF: 'Expression#1' results in a non-zero value
THEN: The expression that follows the question mark (?)
is evaluated (i.e. 'Expression#2') and whatever
follows the colon (:) is ignored.
ELSE: The expression that follows the colon (:) is
evaluated (i.e. 'Expression#3').
Example 1 ARITHMETIC IF-THEN-ELSE
IF the contents of cell [1,1] is greater than or equal to the
contents of cell [2,2]: THEN multiply the contents of cell
[3,3] by 2; ELSE multiple the contents of [3,3] by 4 and
assigns the results of array element 6 of cell [1,2].
(optional) (test) (if true) (if false)
[1,2,6] = ([1,1]>=[2,2]) ? [3,3]*2 : [3,3]*4;
or
([1,1]>=[2,2]) ? [1,2,6]=[3,3]*2 : [1,2,6]=[3,3]*4;
-----------------------------------------------------------------------
Section 6:4 'FORMULAS'
-----------------------------------------------------------------------
A 'formula' is the expression (or the set of arithmetic operations)
that is associated with a FORMULA cell when it is first created. It
is recalculated each time a change is made to one of the cells it
addresses. Since any cell can have a formula, which in turn can
reference the results of other cells, a whole progression of
calculations can be performed based on a single change.
Any given cell can have only one formula assigned to it. When
evaluated, the result is ALWAYS stored at Element position '0' of the
Cell Address ([1,2,0] or by default [1,2]), which is reserved for this
purpose. Other Element positions (i.e. those associated with the
optional ARRAY AREA) can not have 'formulas' assigned to them. This
is a subtle, but important, distinction between Element position '0'
and those that range between 1 and 8000, which is discussed in more
detail in the next section.
-----------------------------------------------------------------------
Section 6:5 ARRAYS
-----------------------------------------------------------------------
Arrays have two unique properties: 1) they allow you to store large
amounts of information with very little overhead and without taking up
large portions of your worksheet; and 2) their values can be set with
the assignment operators (=, +=, -=, etc..).
Once a FORMULA CELL has been created, an ARRAY AREA of upto 8000
values can be defined and attached to it (see Section 4:1 "Array"
for more information on how to do this). These values can then be
referenced by including the Array Element field in the Cell Address.
For example, the Cell Address "[1,2,7]" would reference the 7th array
value assigned to the cell at row 1, column 2. An important point to
remember is that element position '0' IS NOT part of the ARRAY AREA.
This position is reserved for the result of the cell's formula and is
assigned to it when the cell is first created. The ARRAY AREA, on the
other hand, is defined by the user and can only be used to store
static values or numbers. Referencing elements outside of the array
area (by using element numbers larger than the maximum size of the
array space defined for the cell) will always return a value of zero
(0).
Because formulas are not and cannot be associated with the values
stored in the ARRAY AREA, modifications to these values will not
trigger recalculations of any cell that references them; so, exercise
caution when referencing arrays that might have their values changed
(see Section 6:11 on Forced Recalculations for ways around this).
Generally speaking, arrays should be reserved for relatively static
data. If necessary, however, array values can modified from either
the Input Line (see Chapter 7 - Set Element) or programmatically with
the assignment operators (see Section 4:3 - Assignment Operators).
When using assignment operators, the Array Element field of the Cell
Address MUST be included and it MUST be greater than or equal to 1 and
less than or equal to the maximum array space defined for the cell.
You can NOT uses an assignment operator to change the value at element
position '0'. Remember, this position is reserved for the cell's
formula and is not part of the array space. This might best be
explained with an example. Let's say you enter the expression "2+2"
in cell [1,2]. In this case, the value 4 would be stored at element
position '0' and would be displayed on the screen. If you were then
to type the expression "[1,2,0]=3" at another cell location, you would
in effect be trying to tell cell [1,2] that the result of its formula
"2+2" equals 3 - not 4! If, on the other hand, you had entered
"[1,2,1]=3" it would have been perfectly legal, since an array element
simply stores a value and is not the result of a pre-assigned formula.
IMPORTANT: While an Array Area can be assigned to a cell at any time,
it is best to do it (especially for large ones) as soon as possible
after starting up the program - before the computer's memory becomes
fragmented. You should also be careful when moving a cell that contains
an array, since other cells that might reference its array elements will
not be adjusted to account for the new location.
NOTE: As previously mentioned, arrays can only be assigned to FORMULA
cells. This, however, does not mean that the Array Element field of
LABEL cells is not used. For LABELS, this field is used to address
specific characters within a label. For example, if cell [1,3]
contained the label "Hello world!", [1,3,1] would address the first
letter 'H' (or more specifically, the ASCII value for 'H' which is 72).
The assignment operators can also be used to reset the characters of any
label to any ASCII value ranging between 32 and 255. Characters with
ASCII values greater than 125 will not be saved when backing up your
worksheet.
Example 1 REFERENCING AN ARRAY ELEMENT
[1,2,7] * 10
Example 2 ASSIGNING A VALUE TO AN ARRAY ELEMENT
[1,2,7] = 25.0
Example 3 LOADING AN ARRAY AREA WITH VALUES FROM THE WORKSHEET
Load a 400 element array, defined at cell [1,50], with a range
of values that appear between cells [1,1] and [20,20] on the
worksheet (see Section 6:7 "Range Operations" for details on
how to interpret this expression).
[1,1 ^ 20,20; {[1,50,[#]]=[@]}]
NOTE: The array area for cell [1,50] must
have been defined prior to making these
assignments.
-----------------------------------------------------------------------
Section 6:6 MULTIPLE WORKSHEETS
-----------------------------------------------------------------------
Up to four (4) worksheets can be loaded and accessed at any given time.
You can move between them with the F& (Level) option described in
Section 4:6 or with the Forward Slash (/) 'Goto' Command described later
on in Chapter 7. The number between the angle brackets <>, located at
the intersection of the Row and Column Bars, indicates the worksheet
level of each window.
Formulas can be written that reference cell values from the same
worksheet by leaving the Worksheet Level field BLANK or from
worksheets loaded at different levels by including the Worksheet
Level field in the Cell Address. For example:
[1,2] ==> Addresses the cell at row 1, column 2 of
whatever level the formula that contains
this reference is loaded at (1 thru 4).
[1,2,0,3] ==> Addresses the cell at row 1, column 2,
worksheet 3
Normally, it IS NOT a good idea to include the 'Worksheet Level' field
in a Cell Address that makes a reference to a cell at the SAME level.
First it is not necessary; and second, it can cause problems if you
later copy the cell's formula to another level. When this field is left
blank or is set to zero (0), it's value will automatically be reset to
whatever level it is loaded at! This allows formulas that contain Cell
Addresses to be copied to virtually any level without modifying them.
Remember, whenever the Worksheet Level field is hardcoded (fixed), you
are making an absolute reference to a cell at a particular level. This
reference will not be adjusted when the cell is copied or moved to
another worksheet level. For example, if a formula entered at level 1
contains a hardcoded cell reference to another cell at level 1 (e.g.
[2,3,,1]), it will still reference the same cell (at level 1) - even if
the worksheet is later loaded at level 3. So, when in doubt, do not use
the 'Worksheet Level' field, since 99 percent of the time your formulas
will be referencing other cells within the same worksheet.
-----------------------------------------------------------------------
Section 6:7 RANGE OPERATIONS
-----------------------------------------------------------------------
Unlike most spreadsheets, that restrict a range to defining a block of
cells which can be used in function calls, this spreadsheet allows a
range to be used in much the same way as a Cell Address. That is, a
range (or Range Operation) IS AN EXPRESSION that returns a value - not
just a block of cells. What makes this possible, is the ability to
associate one or more formulas to a whole the range of cells. Each of
these formulas is evaluated once, except when surrounded by curly braces
{}; in which case, the expression is evaluated once for each FORMULA
cell that falls within it's range. This allows you to move though a
whole series of cells, performing a variety of operations as you go.
The following is a diagram of how Range Operations are organized:
defines formulas surrounded by
lower right curly braces {} are
corner ___ executed once for each
defines | _ cell within the range
upper left ______ |___ |
corner | | | | |
[row,col ^ row,col ; f; {f}; f;]
| | |_______|
required ____| | |____ formulas not
| surrounded by
required between _____| {} are evaluated
each formula only once
[ ] - By definition, Range Operations MUST be
enclosed within square brackets, indicating
they return a value.
^ - The (^) symbol is a required to separate the
cells which define the upper left and lower
right corners of the range of cells.
f - The (f) in the above diagram represents an
expression (formula) that is evaluated once
when it is first encountered.
{f} - Curly brackets {} act like a FOR or DO loop
that forces the Range Operation to access the
value of each FORMULA cell within the range
(beginning at the upper left corner and moving
down in a left-right direction). The formula
within the brackets is executed once as each cell
is encountered. Only one formula can be enclosed
within each set of brackets.
; - Each formula must be separated by a semicolon.
There are also seven special 'registers' that are reserved especially
for Range Operations. These registers are designed to track critical
values that may change as the expressions (associated with the Range
Operations) are evaluated. Each is represented by a special character
enclosed within square brackets (i.e. [$], [#], etc..) and is described
below.
[$] - Contains the INTERMEDIATE and FINAL results of a Range
Operation. It can act as an accumulator, whenever an
expression is surrounded by curly braces {}, storing
the value produced by the expression after it is
evaluated once for each cell within the range. By
default, this register is initialized to '0' prior to
evaluating any expression that is surrounded by curly
braces.
[@] - Whenever an expression is enclosed within curly
brackets {}, it will be evaluated once for every
FORMULA cell within the range. The [@] register acts
like a variable Cell Address that contains the VALUE
of the cell currently being accessed by the Range
Operation as it moves from one cell to the next.
[#] - This register is a COUNTER. It is initialized to one
(1) at the start of any expression that is surrounded
by curly brackets {}. Its value is incremented by
one each time the Range Operation moves to the next
FORMULA cell.
[*] - This register has no predefined function. It can
be used with assignment operations to hold the
intermediate results of any calculation.
[-] - Whenever an expression is enclosed within curly
brackets {}, it will be evaluated once for every
FORMULA cell within the range. The [-] register
contains the ROW number of the cell currently being
accessed by the Range Operation, which is the same
cell whose value is currently loaded in the [@]
register.
[|] - This register contains the COLUMN number of the cell
currently being accessed by the Range Operation.
[%] - This register contains the WORKSHEET LEVEL number of
the cell currently being accessed by the Range
Operation.
Example 1 COUNT THE CELLS WITHIN A RANGE
Count the number of FORMULA cells within a range. (Empty
cells and cells that contain Labels are skipped.)
[1,1 ^ 12,12]
NOTE: By default, a Range Operation that contains
no formulas will return the total number of FORMULA
cells found within the range. A more literal way of
doing the same thing would be:
[1,1 ^ 12,12; [$]=[#] ]
REMEMBER: The result of any expression, after
it has been evaluated (in this case: [#]) will
automatically be placed in the [$] register.
As a result, the [$]=[#] assignment really isn't
necessary and the above Range Operation could have
been shortened to look like this:
[1,1 ^ 12,12; [#] ]
Example 2 SUM ALL OF THE CELLS WITHIN A RANGE
___ accumulated results
|
[1,1 ^ 12,12; {[$]+[@]} ]
|___ value of current cell
being accessed
Example 3 SUM THE VALUES WITHIN A CELL'S ARRAY AREA
___ accumulated results
|
[2,3,1 ^ 2,3,12; {[$]+[@]} ]
| | |__ value in array element of
| | cell being referenced
|________|___ array element numbers
NOTE: Range Operations can only be performed on
arrays if the Row, Column, and Worksheet Level
are equal.
Example 4 COMPUTE AVERAGE VALUE WITHIN A RANGE
accumulated results___
|
[1,1 ^ 12,12; {[$]+[@]}; [$]/[#] ]
|
final cell count__|
REMEMBER: Only FORMULA cells are visible to a
Range Operation.
Example 5 RETURN THE LARGEST NUMBER WITHIN A RANGE
Find the largest positive number within the range bounded
by cell [10,10] in the upper left hand corner and cell
[20,20] in the lower right corner.
[10,10^20,20; {([@]>[$]) ?[@] :[$];} ]
Example 6 STANDARD DEVIATION
Compute the Standard Deviation for a range of cells
between [1,1] and [5,1].
save average ____
|
[1,1 ^ 5,1; {[$]+[@]}; [*]=[$]/[#]; {[$]+([@]-[*])**2};
sqrt(([$])/([#]-1))]
Example 7 SEARCH A RANGE FOR A VALUE
Perform a Table Search between cells [1,1] and [5,1] for
the first value greater than 4. If found, return the value
located one column to the right of it. (This is similar to
the @VLOOKUP function used by many spreadsheets).
[1,1 ^ 5,1; {[*] = ([@]>4) ?[[-],[|]+1] :[*];}; [$]=[*]]
-----------------------------------------------------------------------
Section 6:8 DIRECT CELL ADDRESSES
-----------------------------------------------------------------------
A Cell Address (i.e. [Row,Col,Element,Level]) makes a 'direct' cell
reference to the value in another cell when: NONE OF THE FIELDS USED
WITHIN THE CELL ADDRESS ARE VARIABLE. That is, when the specific cell
that is being referenced by a Cell Address (within a formula) can
never be changed as the result of an update made somewhere else in the
spreadsheet, it is termed a 'direct' cell reference. For example,
"[1,2]" makes a direct cell reference to the cell at row 1 column 2,
since it's Row or Column fields can not vary. Direct cell references
have one very important property - THEY CAN CAUSE THE FORMULAS THAT
CONTAIN THEM TO BE RECOMPUTED WHENEVER THE VALUE OF THE CELL BEING
ADDRESSED IS CHANGED. This is opposed to expressions that contain
'indirect' cell references (see Section 6:9), which are not recomputed
under similar circumstances.
-----------------------------------------------------------------------
Section 6:9 INDIRECT CELL ADDRESSES
-----------------------------------------------------------------------
Because the 'Row', 'Column', 'Array', and 'Worksheet' fields are
themselves expressions, their values can be designed to vary based on
a change made to the spreadsheet. When this condition exists, it has
the affect of changing the actual cell being addressed! This is called
'indirect' cell addressing, and it can be quite useful at times. Say,
for example, cell [1,1] contains the value 10 and cell [2,2] contains
the value 20. A Cell Address can now be written that uses the values in
both of these cells as its 'row' and 'column' number fields; thereby
referencing cell [10,20] - INDIRECTLY! In other words, the cell that
is actually being addressed depends on the contents of cells [1,1] and
[2,2]. It might look something like this:
__________________ Indirect Reference
| | to cell [10,20]
[ [1,1] , [2,2] ]
The ROW is the value | | The COLUMN is the
value in cell [1,1]________| |____ value in cell [2,2]
NOTE: In the above example, the references to cells
[1,1] and [2,2] are both 'direct' cell references.
Notice that as the values in cells [1,1] and/or [2,2] change, the
'address' of the cell being referenced by the above expression will
also change. This can be a very powerful means of accessing values
within tables, depending on the results of other calculations. There
is, however, one significant trade off you must make when using
'indirect' cell references:
A FORMULA THAT CONTAINS AN 'INDIRECT' REFERENCE TO A CELL,
WILL NOT BE RECALCULATED WHEN THE VALUE OF THAT CELL IS
CHANGED.
The only way to get around this particular drawback is to set a flag
that will FORCE the cell, containing the reference, to be recalculated
each time ANY change is made to the spreadsheet (see Section 6:11 -
Forced Recalculations).
-----------------------------------------------------------------------
Section 6:10 WHEN DO FORMULAS GET RECALCULATED ?
-----------------------------------------------------------------------
Sometimes it's difficult to tell which cells will be affected, when a
change is made to the worksheet. If you're having trouble with this,
try to remember the following rules:
A DIRECT CELL REFERENCE, within a formula, will cause THAT
formula to be recalculated each time the value of the cell
(being referenced) is changed.
ALL formulas that contain Range Operations are recomputed at
least once - each time a change is made to the worksheet
(unless the 'dRange' value is set to zero - see Section 4:8).
The (~) Cell Trace Command can also be used to locate the cells that
make 'direct' references to other cells (see Chapter 7). This is done
by placing the Cell Pointer over the cell in question and executing the
command from the Input Line. You will then be shown all of the cells
that reference it. These are the cells that will cause the formula to
be recomputed if their values change. You will be also shown the cells
that USE the value of the cell in question.
-----------------------------------------------------------------------
Section 6:11 FORCING CELLS TO RECALCULATE
-----------------------------------------------------------------------
Occasionally, you may run into a case were a cell's formula is not
recalculate when the value of one of the cells that it references is
changed. For example, if a cell makes an INDIRECT CELL REFERENCE to
another cell, its formula will NOT be recomputed when the value of
this (indirectly) referenced cell is changed. When it is absolutely
necessary that a cell's value reflect such changes, a special flag can
be set that will FORCE the cell's formula to be recalculated whenever
ANY change is made to the spreadsheet. Section 4:1 "+ReCalc" describes
how to do this by setting up and executing the following command:
"+ReCalc Cell OKAY?". This flag should be used sparingly, however, as
it defeats the "Minimal Re-Calc" capabilities of the spreadsheet. It is
also up to you to keep track of the cells that have had their 'ReCalc'
flag set.
-----------------------------------------------------------------------
Section 6:12 OPTIMIZING YOUR WORKSHEET
-----------------------------------------------------------------------
Generally speaking, there are several things you can do to optimize
your worksheet: 1) PREVENT complex formulas from being recomputed
until their results are needed by setting the "+NoCalc" flag (see
Section 4:1); 2) LIMIT the number of 'indirect' cell references -
'direct' cell references are faster; 3) KEEP the 'dRange' default
setting set to 1 (refer to Section 4:8) to reduce the number of times
a formula that contains a recursive Range Operation will be
recalculate.
If a cell contains a complicated formula that takes a long time to re-
calculate, you can speed up the spreadsheet by blocking the calculation
until you the specifically request that it be made. Section 4:1
describes how to do this by setting up and executing the following
command: "+NoCalc Cell OKAY?". It's up to you, however, to remember
when and where you have used this flag on a cell!
To minimize the number of calculations that must be performed after a
change has been made to the spreadsheet, REBEL will only re-compute
the cells that are affected by the change. This is often referred to
as "minimal recalc". There are, however, certain cases in which this
strategy can be more of a disadvantage than an advantage. Take, for
example, cell [25,25] whose formula contains a 'Range Operation' that
sums all of the cells bounded by cell [1,1] (in the upper left corner)
and cell [20,20] (in the lower left corner). The expression might look
something like this: [1,1 ^ 20,20; {[$]+[@]}]. Here's the problem.
Lets say that the value of EACH cell within this range is affected by a
change made to cell [45,45] (i.e. all cells within this range make a
'direct' reference to cell [45,45]). Now, imagine the following
scenario. Suppose you change the value in cell [45,45]. What happens?
Each of the cells within the range will be recomputed; AND EACH TIME A
CHANGE IS MADE TO A ONE OF THESE CELLS IT WILL TRIGGER THE CELL THAT
CONTAINS THE 'RANGE OPERATION' TO BE RECOMPUTED - cell [25,25]. As a
result, cell [25,25] will be needlessly recomputed 400 times (once for
each cell in the range); when, in reality, the Range Operation only
needed to be recomputed once, after the last cell in the range was
updated. This, of course, is a worst case and is easily handled with a
special setting that can limit the number of times a Range Operation can
be be recalculated as the result of a single change made to the
worksheet (see Section 4:8 - "dRange").
Another type of Range Operation that can cause problems is one that
includes itself WITHIN ITS OWN RANGE! That is, a Range Operation that
makes a circular reference to itself, causing the cell's formula to be
recalculated - indefinitely. Once again, the "dRange" setting can be
used to control this situation.
-----------------------------------------------------------------------
Section 6:13 MISCELLANEOUS TOPICS
-----------------------------------------------------------------------
ENTERING NUMBERS (from the Input Line)
Numeric values can be entered from the Input Line in several
different ways. For example:
1) Standard decimal values can be entered simply by typing
them as they appear:
0 ===> 0
25.4 ===> 25.4
40 ===> 40
040 ===> 32 (CAUTION! do not precede a
decimal value with a '0',
unless the number is zero.)
2) Octal numbers can be entered by beginning them with a zero:
040 ==(octal equivalent)==> 32
0377 ==(octal equivalent)==> 255
3) Hexadecimal numbers can be entered by preceding them with a
"0x":
0xFF ===(hex equivalent)===> 255
4) The numeric value of any ASCII character can be entered by
enclosing the character within single quotes. Note, if the
single quote is the first character in the expression, be
sure the precede it with a plus sign (+'A'). This will
prevent the expression from being interpreted as a LABEL.
'A' ==(ascii equivalent)==> 65
'B' ==(ascii equivalent)==> 66
'!' ==(ascii equivalent)==> 33
IMPORTANT! To avoid confusion, remember to NEVER being a decimal
number with a zero (unless, of course, the number is zero)!
SWITCHES
Several switches can be used when starting up REBEL:
1) The -S switch can be used to create a backup file, whenever
a worksheet is load. This file will be an exact copy of
the original worksheet, but will have a .RB0 extension.
(You will have to rename it to a .RB@ file before you can
use it.)
2) The -B switch can be used to startup REBEL in black and white
mode; otherwise, the program will be be started in the color
mode.
3) The -L: option is used to 'attach' a script library to the
current session. The -L: switch should be followed
immediately by the name of the library file (no blanks).
Also refer to Section 11:2.
REBEL -L:STDLIB
4) The -M: option is used to set the size of the Swap Area used
by the Script Manager. The -M: switch should be followed
immediately by the size (in bytes). Refer to Section 11:4.
REBEL -L:STDLIB -M:2000
5) The -X: option is used to executed a script immediately upon
entering the spreadsheet. This option is useful when it's
important to hide the spreadsheet and it's data from the user.
The option is used by following the -X: portion of the switch
immediately with the name of the script you wish to execute.
Remember, you must also use the -L: option to attach the
library that contains the script when using this option.
REBEL -L:MYLIB -X:scriptname
RESERVED and WILDCARD CHARACTERS
The tilde (~) character is reserved by REBEL as a wildcard
character. As a result, it can not be used within a label.
(Refer to the 'Search' command in the next Chapter, and the
'Load' and 'ListDir' commands described in Section 4:3 for
examples of its uses.)
________________________
_____________________________________________/ Chapter 7 SHORTCUTS
The following 'Shortcut' commands are designed to be executed from the
Input Line (below the Function Key Menu). They do not perform critical
tasks; although, you may find them useful time savers. Each of these
commands (with the exception of the <Tab> Command) must be followed by
the <Enter> key before it is executed.
NOTE! Because the backslash (\), period (.), and equal sign (=) are
used to trigger Shortcut Commands, you CANNOT begin a LABEL with one of
these characters without first preceding it with a single quote (').
Command Description
CHANGE WINDOWS <Tab> Pressing the <Tab> key at the Input Line
prompt, will cause the Cell Pointer to
jump to another window (if one exists).
SEARCH (\) A backslash (\) followed by any pattern
of characters will cause the Cell Pointer
to move to the first cell the contains an
occurrence of that pattern. If the Cell
Pointer is positioned on an 'empty' cell,
the search will begin at the top of the
worksheet. If the Cell Pointer is
positioned on an 'occupied' cell, the
search will begin from that point. The
pattern does not have to be re-entered
to make repeated searches. Simply type
a backslash (\) followed by the <Enter>
key after you have entered the pattern
the first time. The wildcard character
(~) can also be included within the
search pattern.
GOTO CELL (/) A Forward Slash (/) will produce a series
of prompts that will request the Row,
Column, and Worksheet Level to move the
Cell Pointer to. The value in the square
brackets [] of each of these prompts is
the default answer that will be used if
you press the <Enter> key with no other
input. This command can also be used with
prompts that require you to move the
Cell Pointer to define a Range or Target
cell.
ELEMENT DISPLAY (=) An Equal Sign (=) followed by an array
element number (e.g. =5) can be used to
display the value of any array position
of the cell highlighted by the Cell
Pointer. The display appears at the
Input Line and can be removed by pressing
any key. For example, "=1" will display
the value of the first array element.
Note: "=0" will always return the value
associated with the cell's formula. The
following variations of this command are
also acceptable:
=* Displays all of the values
stored in a cell's array
area - one at a time.
= The equal sign (alone), works
like the "=*' variation,
except when displaying the
contents of cells that
contain coordinates or
matrices. In these case,
coordinate pairs and matrix
rows are displayed together
(if possible).
=(5,9) Displays elements 5 thru 9
SET ELEMENT (=e<value) An Equal sign (=) followed by an array
element number (greater than 0), then a
Less Than sign (<), and finally a value
will cause that value (to the right of
the '<' sign) to be loaded at the
designated array position. For example,
"=7<25" will load the value 25 into
element position 7 of the cell that is
highlighted by the Cell Pointer. The
following variations of this command may
also be used:
=*<0 Set the values of all
elements to 0
=(5,9)<0 Set elements 5 thru 9 to 0
CELL RECALC (.) The formula of any cell can be quickly
RECALCULATED by positioning the Cell
Pointer over the cell and entering a
Dot (.) followed by the <Enter> key.
________________________
_____________________________________________/ Chapter 8 FUNCTIONS
Index to Functions
abs(x) . . . . . . 8:1:1 MATH . . . . . . absolute value
acos(x) . . . . . 8:2:5 TRIG . . . . . . arccosine
asin(x) . . . . . 8:2:6 TRIG . . . . . . arcsine
atan(x) . . . . 8:2:7 TRIG . . . . . . arctangent
atan2(x,y) . . . . 8:2:8 TRIG . . . . . . arctangent
atof . . . . 8:3:1 STRING . . . . . string to number
ceil(x) . . . . 8:1:2 MATH . . . . . . ceiling
col(x) . . . . 8:4:1 MISC . . . . . . column-offset
cos(x) . . . . 8:2:1 TRIG . . . . . . cosine
cotan(x) . . . . 8:2:2 TRIG . . . . . . cotangent
exp . . . . 8:1:3 MATH . . . . . . exponential
floor . . . . 8:1:4 MATH . . . . . . floor
frac . . . . 8:1:5 MATH . . . . . . fractional value
index . . . . 8:3:2 STRING . . . . . locate character
int . . . . 8:1:6 MATH . . . . . . integer value
log10 . . . . 8:1:7 MATH . . . . . . base 10 log
ln . . . . 8:1:8 MATH . . . . . . natural log
lvl . . . . 8:4:3 MISC . . . . . . worksheet level offset
mode . . . . 8:1:9 MATH . . . . . . change mode of angle
rnd . . . . 8:1:10 MATH . . . . . . rounds value
row . . . . 8:4:2 MISC . . . . . . row offset
sin . . . . 8:2:3 TRIG . . . . . . sine
sqrt . . . . 8:1:11 MATH . . . . . . square root
strcat . . . . 8:3:3 STRING . . . . . string concatenate
strcmp . . . . 8:3:5 STRING . . . . . string compare
strcpy . . . . 8:3:7 STRING . . . . . string copy
strlen . . . . 8:3:9 STRING . . . . . string length
strncat . . . . 8:3:4 STRING . . . . . concatenate n chars
strncmp . . . . 8:3:6 STRING . . . . . compare n chars
strncpy . . . . 8:3:8 STRING . . . . . copy n chars
tan . . . . 8:2:4 TRIG . . . . . . tangent
NOTE: Refer to Chapters 9 and 10 for COGO and MATRIX functions.
Also see file STDLIB.DOC script documentation.
IMPORTANT: All of the functions listed in this Chapter are
case sensitive (i.e. spell them just as they appear - in most
cases, with lower case letters). In addition, remember to use
a '+' sign whenever a function begins a formula. This will
insure that the cell is interpreted as a FORMULA rather than
a LABEL.
-----------------------------------------------------------------------
Section 8:1:0 MATH FUNCTIONS
-----------------------------------------------------------------------
9:1:1 abs (x)
Returns the absolute value of any expression 'x'.
8:1:2 ceil (x)
Returns the smallest integral value that is greater than or
equal to the value produced by expression 'x'.
8:1:3 exp (x)
Returns the exponential function of any expression 'x'.
8:1:4 floor (x)
Returns the largest integral value that is less than or
equal to the value produced by expression 'x'.
8:1:5 frac (x)
Returns the fraction part of 'x'.
8:1:6 int (x)
Returns the integer part of 'x'.
8:1:7 ln (x)
Returns the natural logarithm of any expression 'x'.
An error is returned for values of 'x' that are less than
or equal to 0.
8:1:8 log10 (x)
Returns the logarithm to the base 10 of any expression 'x'.
An error is returned for values of 'x' that are less than
or equal to 0.
8:1:9 mode (frommode,tomode,angle)
Changes the mode of 'angle' to another mode. The mode
settings are: 0=radians, 1=decimal degrees, 2=grads, and
3=degrees/minutes/seconds (DDD.MMSSss). The 'frommode'
parameter is setting to the current mode of the angle
and the 'tomode' parameter is set to the return mode.
Example: +mode(1,3,90.50) = 90.3000
8:1:10 rnd (x,n)
Rounds 'x' to 'n' decimal places, where 'n' can range
between 15 and -15. If 'n' is negative, it rounds to
the n-th power of 10 (e.g. round(1891,-2) = 1900).
8:1:11 sqrt (x) = square_root
Returns the square root of any expression 'x'. An error
is returned for values of 'x' that are less than 0.
-----------------------------------------------------------------------
Section 8:2:0 TRIG FUNCTIONS
-----------------------------------------------------------------------
8:2:1 cos (angle) = cos OPTIONAL FORM: cos (angle,mode)
8:2:2 cotan (angle) = cotan OPTIONAL FORM: cotan (angle,mode)
8:2:3 sin (angle) = sin OPTIONAL FORM: sin (angle,mode)
8:2:4 tan (angle) = tan OPTIONAL FORM: tan (angle,mode)
Returns the sin, cos, tan, or cotan of any 'angle' in
radians. Angles in degrees and grads can also be used by
including the optional 'mode' parameter, where 0=RADIANS
(default), 1=DEGREES, and 2=GRADS.
8:2:5 acos (cos) = angle OPTIONAL FORM: acos (cos,mode)
8:2:6 asin (sin) = angle OPTIONAL FORM: asin (sin,mode)
8:2:7 atan (tan) = angle OPTIONAL FORM: atan (tan,mode)
8:2:8 atan2 (x,y) = angle OPTIONAL FORM: atan2 (x,y,mode)
Returns the arc sin, arc cos or arc tan in radians. Angles
can also be returned in degrees and grads by including the
optional 'mode' parameter, where 0=RADIANS (default),
1=DEGREES, and 2=GRADS.
-----------------------------------------------------------------------
Section 8:3:0 STRING FUNCTIONS
-----------------------------------------------------------------------
8:3:1 atof (label) = value
Converts a character string to it numeric equivalent.
Examples: atof ("123.55") = 123.55
8:3:2 index (label,c) = pos OPTIONAL FORM: index(label,c,start)
Returns the 'position' of the first occurrence of a
character 'c' in a 'label'. The search can be optionally
started from any location by including that location in
the optional 3rd parameter.
Examples: index ("Hello World!",'o') = 5
index ([1,1],'o',6) = 8
8:3:3 strcat (cell,label) = errorcode
Appends the contents of a 'label' or a 'string' to another
'cell'. Returns '0' if successful. Note, this function
WILL NOT cause other cells to be recalculated as a result
of any change to the cell in the first parameter.
Examples: strcat([1,1],"Hello World!")
strcat([1,1],[1,3])
8:3:4 strncat (cell,string,n) = errorcode
Appends the first 'n' characters from a 'string' to the
label of to another 'cell'. Returns '0' if successful.
Note, this function WILL NOT cause other cells to be
recalculated as a result of any change to the cell in
the first parameter.
Examples: strncat([1,1],"Hello World!",5)
strcat([1,1],[1,3],5)
8:3:5 strcmp (label1,label2) = status
Compares 'label1' and 'label2', returning:
(1) if 'label1' is GREATER than 'label2'
(0) if 'label1' is EQUAL to 'label2'
(-1) if 'label1' is LESS the 'label2'
Examples: strcmp ([1,1],"Hello World!")
strcmp ([1,1],[1,3])
8:3:6 strncmp (label1,label2,n) = status
Compares the first 'n' characters of 'label1' and 'label2',
returning:
(1) if 'label1' is GREATER than 'label2'
(0) if 'label1' is EQUAL to 'label2'
(-1) if 'label1' is LESS the 'label2'
Examples: strncmp ([1,1],"Hello World!",5)
strncmp ([1,1],[1,3],5)
strlen ([1,1])
8:3:7 strcpy (cell,string) = errorcode
Copies the contents of a cell or of a 'string' to another
'cell'. Returns '0' if successful. Note, this function
WILL NOT cause other cells to be recalculated as a result
of any change to the cell in the first parameter.
Examples: strcpy ([1,1],"Hello World!")
strcpy ([1,1],[1,3])
8:3:8 strncpy (cell,string,n) = errorcode
Copies the first 'n' characters of a 'string' to another
'cell'. Returns '0' if successful. Note, this this
function WILL NOT cause other cells to be recalculated as
a result of any change to the cell in the first parameter.
Examples: strncpy ([1,1],"Hello World!",5)
strncpy ([1,1],[1,3],5)
8:3:9 strlen (label) = number_of_characters
Returns the number of characters in a label (string).
Examples: strlen ("Hello World!")
-----------------------------------------------------------------------
Section 8:4:0 MISC FUNCTIONS
-----------------------------------------------------------------------
8:4:1 col (offset) = column+offset
Adds the 'offset' value to the current COLUMN.
8:4:2 row (offset) = row+offset
Adds the 'offset' value to the current ROW.
8:4:3 lvl (offset) = level+offset
Adds the 'offset' value to the current worksheet LEVEL.
_____________________________
________________________________________/ Chapter 9 COGO
Chapter Contents
Section 9:1 ABOUT THE FUNCTIONS USED IN THIS PACKAGE
Section 9:2 ABOUT THE PARAMETERS USED BY COGO FUNCTIONS
9:2:1 Referencing other coordinates
9:2:2 Setting the angular mode (radians, degrees, grads)
9:2:3 Setting the angle type (ray, azimuth, bearing, etc..)
9:2:4 Entering angles (ray, azimuth, bearing, etc..)
9:2:5 Setting the display format
Section 9:3 FUNCTIONS THAT RETURN A SINGLE COORDINATE
9:3:1 @XY . . . . . . (Load (x,y) Rec Coordinate)
9:3:2 @NE . . . . . . (Load (north,east) Rec Coordinate)
9:3:3 @POLAR. . . . . (Load Polar Coordinate (radius,ray))
9:3:4 @Traverse . . . (Bearing/Azimuth Traverse)
9:3:5 @FieldAngle . . (Field Angle Traverse)
9:3:6 @AngAngInt . . (Angle/Angle Intersection)
9:3:7 @AngDstInt . . (Angle/Distance Intersection)
9:3:8 @DstDstInt . . (Distance/Distance Intersection)
9:3:9 @CurveRadius . (Horizontal Curve with Radius)
9:3:10 @CurveDelta . . (Horizontal Curve with Delta Angle)
Section 9:4 FUNCTIONS THAT RETURN MORE THAN ONE COORDINATE
9:4:1 @Segment . . . (Line Segment)
9:4:2 @Polygon . . . (Closed Polygon/Traverse)
Section 9:5 FUNCTIONS THAT RETURN AN ANGLE and DISTANCE
9:5:1 @Inverse . . . (Inverse Traverse)
9:5:2 @Radial . . . . (Radial Stakeout)
9:5:3 @Closure . . . (Error of Closure/Area of POLYGON)
Section 9:6 FUNCTIONS THAT RETURN MULTIPLE VALUES
9:6:1 @Triangle . . . (Triangle Solution)
Section 9:7 NULL POINTS, SEGMENTS, and POLYGONS
Section 9:8 GRAPHICS
Section 9:9 EXAMPLES
------------------------------------------------------------------------
Section 9:1 ABOUT THE FUNCTIONS USED IN THIS PACKAGE
------------------------------------------------------------------------
While this package is easy to use, it does take advantage of several of
REBEL's more advanced features. As a result, it is NOT advisable to
jump immediately into these functions without first reviewing the
preceding chapters. I highly recommend, that at a very minimum, you
become familiar with the concept of "Cell Array Areas" (discussed in
Section 6:5) before continuing.
In general, you'll find that most of the functions described in this
chapter work alike, which makes them easy to learn. There are, however,
a few important differences that distinguish these functions from the
ones you have been exposed to thus far. The most significant of these
differences is the way in which values are returned. Up to now,
functions and expressions have always returned a single value which is
displayed by the cell. Coordinate Geometry functions, on the other
hand, return (and sometimes display) multiple values, such as a
coordinate pair. The '@' symbol that precedes the name of each of these
functions is designed to help remind you of this fact. For most
spreadsheets, dealing with complex numbers such as this presents a major
problem, since they are only capable of storing a single value in each
cell. This is not a limitation for REBEL, however, which can
dynamically create and load upto 8000 values in the "array area" of a
cell. Before attempting to use these functions, there are a few
additional facts you should be aware of.
FIRST, as just mentioned, all '@' functions return multiple values,
which are automatically loaded into the cell's array area for you. It is
important to remember that before a cell can be recognized by other
functions as containing one of these multiple value solutions (such as a
coordinate pair), the cell MUST have been created with an '@' function.
In other words, if you want to enter a known or fixed coordinate pair,
you must use either the @XY, @NE, or @POLAR function to do it. You can
not create the cell yourself simply by defining and loading an array
area. Null Points, Segments, and Polygons (discussed in Section 9:7)
are the one exception to these rule.
SECOND, no '@' function can be nested within the parameter list of
another function or used within an expression. These functions must be
the lone entry in a cell's formula.
THIRD, '@' functions rarely display the value stored at element position
[0], which is normally the case with expressions and standard functions
that return single values. Instead, the '@' functions typically display
one or more of the values stored in the cell's ARRAY AREA, such as a
coordinate pair or an anlge and distance. As a result, the Column Width
must be set wide enough to display multiple values - to prevent an
overflow. If this condition (an overflow) should occur, a series of
stars (*****) will be displayed by the cell. To correct it, simply
increase the Column Width until the correct values appear (refer to the
'Width' Menu option in Section 4:6).
FOURTH, many '@' functions return valuable secondary information that
is not displayed. These values, which are also stored in the cell's
array area, are defined at the end of each function description and may
be inspected using the "=*" Element Display command discussed in Chapter
7.
SUMMARY
1) YOU CAN NOT CREATE A 'POINT', 'SEGMENT', OR 'POLYGON' CELL WITHOUT
USING AN '@' FUNCTION TO DO IT. That is, simply loading the array
area of a standard FORMULA cell with the correct values will not
work (refer to Section 9:7 for the one exception to this rule).
2) AN '@' FUNCTION CAN NOT BE NESTED (USED AS A PARAMETER) OR USED
WITHIN AN EXPRESSION - THEY MUST STAND ALONE.
3) IF THE WIDTH OF A CELL IS NOT SUFFICIENT TO DISPLAY THE RESULTS OF
A '@' FUNCTION, A SERIES OF STARS (****) WILL BE DISPLAYED,
INDICATING A DISPLAY OVERFLOW.
4) ALL FUNCTIONS SHOULD BE ENTERED EXACTLY AS THEY APPEAR IN THIS
MANUAL. FUNCTION NAMES ARE CASE SENSITIVE. For example,
"@Traverse" should NOT be entered as: "@traverse" or "@TRAVERSE".
------------------------------------------------------------------------
Section 9:2 ABOUT THE PARAMETERS USED BY COGO FUNCTIONS
------------------------------------------------------------------------
The functions used in this package contains parameters that allow them
to be customized to fit your particular needs. For example, if you
prefer to work in degrees as opposed to radians, you can do it by
setting the appropriate parameter value. Since many of the functions
require the same parameter entries, learning them is easy once you get
started. To keep from repeating myself a dozen times, however, I have
decided to explain the parameters that are common to more than one
function only once in the next five sections (Sections 9:2:1 thru
9:2:5). At first, you may feel somewhat overwhelmed by the number of
options that are available. I have tried to balance the power and
flexibility that these parameters provide with the ease-of-use of the
functions in general - without sacrificing to much in the process. I
realize there is often a fine line between these objectives (I only
hope I have succeeded in maintaining a balance). It may help to mark
the specific options you are interested in as you read through each
section, which are really the only ones you need to remember. The
remaining options can be ignored until you need them. The following
diagram provides an example of each parameter type and the section
number it is discussed in:
(Section 9:2:1) (9:2:3) (Section 9:2:5)
| | |
@Traverse ([setup],mode,type,angle,distance,fmt)
| |
(Section 9:2:2) (9:2:4)
9:2:1 REFERENCING OTHER COORDINATES
@Traverse ([SETUP],mode,type,angle,distance,fmt)
^
Whenever you see a parameter that is enclosed within Square
Brackets [], it takes on special meaning. A parameter of this
type MUST be the Cell Address ([row,col]) of a cell that
contains a POINT, SEGMENT or POLYGON (depending on the
function's requirements). If you attempt to address a cell that
does not contain the proper cell type, an error will be
returned. In the above example, '[setup]' represents the cell
that contains the coordinate from which the 'angle' and
'distance' are measured.
Note: The @XY, @NE, and @POLAR functions provide the easiest
means of loading a cell with the initial coordinate from which
other coordinates are computed. This 'initial' point is
normally known and therefore must be loaded directly before it
can be reference by other '@' functions.
9:2:2 SETTING THE ANGLE MODE (radians, degrees, grads)
@Traverse ([setup],MODE,type,angle,distance,fmt)
^
The 'mode' parameter tells the spreadsheet what unit of
measure to assume for all angles used in the function's
parameter list. Four angular modes (0-3) are available:
mode=0 Radians
mode=1 Degrees (decimal) DDD.ddd
mode=2 Grads
mode=3 Degrees (degrees,minutes,seconds) DDD.MMSSss
9:2:3 SETTING THE ANGLE TYPE
@Traverse ([setup],mode,TYPE,angle,distance,fmt)
^
The 'type' parameter tells the spreadsheet what kind of angle
(bearing, azimuth, ray, etc..) to assume for the angles that are
used or returned by the function. There are nine possible
settings. The first five (0-4) identify a group of Direction
Angles, which define the absolute direction of a line relative
to the xy-axis. The remaining four settings (5-8) identify
Field Angles that require a 'backsight' coordinate to establish
their relative direction.
Direction Angle settings (0-4):
type=0 Ray (counter-clockwise about the +x axis)
type=1 Bearing (90 degrees about the (+/-) y axis)
type=2 Azimuth No (clockwise about the +y axis)
type=3 Azimuth So (clockwise about the -y axis)
type=4 Rotation (180 deg - either direction of +x axis)
(0) (1) (2) (3) (4)
| 360 90<|>90 |>360 | | 180
---+--^- ----+---- ----+--- ----+---- ----+--+-
| 90<|>90 | 360<| | 180
Ray Bearing Azimuth Azimuth Rotation
(north) (south)
Field Angles settings (5-8):
type=5 Angle Right (right of the backsight)
type=6 Angle Left (left of backsight)
type=7 Deflection Right (right from line 180d of backsight)
type=8 Deflection Left (left from line 180d of backsight)
(point measured to)
\ (backsight)
- - - >\ 6 | 6 /
| \<- - |- - -/ 8
|7 \<- -|- - / - -
| - ->\ | / |
| 5| \ | /-- |8
| | setup | / | |
|-----|--------+-----|---|----
| | /| 5| |
| 5| / | | |8
| - - -/- |- - - |
|7 / | |
| /- - |- - - - -
| 7 / 8 | 8
- - - -/ |
/
(line - 180 degrees from backsight)
9:2:4 ENTERING ANGLES
@Traverse ([setup],mode,type,ANGLE,distance,fmt)
^
If you used one of the first three angle 'mode' settings (0
thru 2), the angle must be entered using a decimal format. For
example, mode (1) angles are entered as follows: DDD.dddd,
where the 'dddd' represents decimal degrees. Mode (3) angles,
on the other hand, require angles to be entered in degrees,
minute, and seconds (DDD.MMSSss) where: 'DDD' denotes whole
degrees; 'MM' denotes minutes; 'SS' denotes seconds; and 'ss'
denotes decimal seconds. As an example, 75 30'20.95" would be
entered: 75.302095.
Bearing angles (i.e. mode=3, type=1) are the only angles that
require a somewhat unusual entry method, since both the
quadrant and the the angle itself must be entered as a single
value. This is done by adding a special value to the bearing
angle itself to identifies its quadrant. The following diagram
illustrates these values:
(NW) +400 | +100 (NE)
------+------
(SW) +300 | +200 (SE)
Note: Since bearing angles can not be greater than 90 degrees,
adding the above values will not affect the angle itself and
can be otherwise ignored - except for identifying the quadrant.
For example, to represent the bearing N45 30'20"W , you would
enter: 445.3020 (i.e. 45.3020 + 400 ).
9:2:5 SETTING THE DISPLAY FORMAT
@Traverse ([setup],mode,type,angle,distance,FMT)
^
As mentioned earlier, the '@' functions usually return multiple
values, which generally fall into two categories:
Rectangular/Polar Coordinates and Angle/Distance pairs. The
'fmt' parameter is used to control how the spreadsheet displays
these values. Several options in each category are available.
RECTANGULAR/POLAR coordinate display formats:
fmt=0 XY Rectangular Coordinate format: "(x, y)"
fmt=1 North/East Coordinate format: "[north, east]"
fmt=2 Polar Coordinate display format: "{radius, ray}"
Notice the brackets used to enclose each coordinate type.
They'll help you distinguish between them when displayed.
Also, note that when using the Polar Coordinate display
format (fmt=2), both the entry and return mode of the 'ray'
angle is established by the function's 'mode' parameter.
Keep in mind that no matter which display format is used, the
coordinate is always stored by the spreadsheet in the same
way (i.e. as a rectangular coordinate). The x_value is stored
in element position [1] and the y_value is stored in element
position [2]. The display format does not affect the actual
values stored by the cell.
ANGLE/DISTANCE display formats:
fmt=0 General display format: TYPE(mode) ANGLE, DISTANCE
Example: "BRG(dms) 445.3020, 100.00"
Note: This format can be used to display any
angle type/mode combination.
fmt=1 Bearing/Distance format:
Example: "N45 30'20.0"W 100.00"
Note: When using this display format, the function's
'mode' and 'type' settings will be ignored and the
angle will always be displayed in degrees, minutes
and seconds.
------------------------------------------------------------------------
Section 9:3 FUNCTIONS THAT RETURN: RECTANGULAR/POLAR COORDINATES
------------------------------------------------------------------------
All of the functions, described in Sections 9:3, return coordinate
pairs. The first three of these functions (@XY, @NE, and @POLAR) are
unique in that they do not COMPUTE coordinates; rather, they are
designed to LOAD a fixed or known coordinate. Without these functions,
it would be impossible to establish the initial point from which other
coordinates are computed. Remember, you can not load a coordinate pair
into a cell's array area yourself (Section 9:7 discusses the one
exception to this case).
If an error is detected by any of these functions, a series of question
marks (?????) will appear in the cell's display. You can identify the
exact problem by moving the Cell Pointer to this cell and then entering
the Edit Mode. This will cause an error message to be displayed that
will hopefully help you correct the problem (refer back to Chapter 5).
9:3:1 @XY (X_coordinate,Y_coordinate)
This function is designed to allow you to load a fixed or known
(x,y) coordinate pair into a cell. By default, the coordinate
is also displayed using this format. Keep in mind, this
function does not 'compute' anything - it only loads a cell with
a known coordinate pair. The function is used primarily to
establish an INITIAL point from which other coordinates can be
computed.
Return Contents of Array Area:
[0]: 1 (number of coordinates pairs)
[1]: x-coordinate value of point
[2]: y-coordinate value of point
NOTE: Refer to Section 9:7 for a details on how to use this
function without a parameter list.
9:3:2 @NE (Northing,Easting)
This function is identical to the @XY routine, except that it
loads and displays a coordinate using a (North,East) or (y,x)
format. The coordinate, however, is stored internally in a
(x,y) format, allowing it to be used interchangeably with any
coordinate computed by this package.
Return Contents of Array Area:
[0]: 1 (number of coordinates pairs)
[1]: x-coordinate value of point
[2]: y-coordinate value of point
NOTE: Refer to Section 9:7 for a details on how to use this
function without a parameter list.
9:3:3 @POLAR (radius,ray)
Like @XY and @NE, the @POLAR function is used to enter a fixed
or known coordinate. The coordinate, however, must be entered
in a 'polar' format (radius,ray), where: the 'radius' is the
distance measured from the intersection of the xy-axis and the
'ray' is the angle measured counter clockwise from the positive
x-axis. The 'ray' angle MUST be entered in RADIANS when using
this function. While the coordinate is displayed in the same
polar format that was used to enter it, it is stored internally
as a rectangular coordinate - exactly as all other coordinates.
This allows it to be used interchangeably with all other
coordinates generated by this package.
Return Contents of Array Area:
[0]: 1 (number of coordinates pairs)
[1]: x-coordinate value of point
[2]: y-coordinate value of point
NOTE: Refer to Section 9:7 for a details on how to use this
function without a parameter list.
9:3:4 @Traverse ([setup],mode,type,angle,distance,fmt)
This function computes the next point, given an initial
coordinate '[setup]', an 'angle', and a horizontal 'distance'.
Any of the four available 'mode' settings (0-3) may be used,
however, the angle 'type' is limited to Direction Angles (types
0 thru 4). Refer back to Section 9:2:5 for the 'fmt' display
settings that are available.
Return Contents of Array Area:
[0]: Contains the 'ray' angle in decimal degrees of
the line INTO the point computed by this
function. This angle (mode=1, type=0) can be
used by other functions.
[1]: x-coordinate value of point
[2]: y-coordinate value of point
9:3:5 @FieldAngle ([backsight],[setup],mode,type,angle,vert,dist,fmt)
The @FieldAngle function is similar to the @Traverse function,
except that it also allows Field Angles (i.e. 'type' 5 thru 8)
to be used to compute the coordinate of the next point. As a
result, a '[backsight]' coordinate is required to establish the
direction of the line from which the field angle is measured.
When using Direction Angles (i.e. 'type' 0 thru 4), the
[backsight] coordinate is ignored. This function will except
either a slope or a horizontal distance to the next point,
depending on how the vertical angle ('vert') parameter is set.
For slope distances, the vertical angle is measured 0 upto 90
degrees above the horizontal plane or 360 down to 270 degrees
below the horizontal plane. The 'vert' parameter should be set
to '0' when entering a horizontal distance. The 'mode' setting
applies to both the 'angle' and 'vert' parameters.
Return Contents of Array Area:
[0]: Contains the 'ray' angle in decimal degrees of
the line INTO the point computed by this
function. This angle (mode=1, type=0) can be
used by other functions.
[1]: x-coordinate value
[2]: y-coordinate value
9:3:6 @AngAngInt ([point1],[point2],mode,type,angle1,angle2,fmt)
Given two coordinate pairs ([point1] and [point2]) and a
Direction Angle from each ('angle1' and 'angle2',respectively),
this function will compute the point of intersection. Any of
the four available 'mode' settings (0-3) may be used, however,
the angle 'type' is limited to Direction Angles (i.e. type 0
thru 4). Refer back to Section 9:2:5 for the 'fmt' display
settings that are available.
Limitations: This function will return an error if you attempt
to find the intersection of two parallel lines.
Return Contents of Array Area:
[0]: 1 (number of coordinates pairs)
[1]: x-coordinate value
[2]: y-coordinate value
9:3:7 @AngDstInt ([point1],[point2],mode,type,angle1,distance2,fmt)
Given two coordinate pairs ([point1] and [point2]) and a
Direction Angle measured from the first point (angle1) and a
distance measured from the second (distance2), this function
will compute the point of intersection. Any of the four
available 'mode' settings (0-3) may be used, however, the angle
'type' is limited to Direction Angles (i.e. type 0 thru 4).
Refer back to Section 9:2:5 for the 'fmt' display settings that
are available.
Note: Two solutions are possible. The second is found by
adding 180 degrees to the angle measured from [point1].
Return Contents of Array Area:
[0]: 1 (number of coordinates pairs)
[1]: x-coordinate value
[2]: y-coordinate value
9:3:8 @DstDstInt ([point1],[point2],distance1,distance2,fmt)
Given two coordinate pairs ([point1] and [point2]) and a
distance measured from each (distance1 and distance2), this
function will compute the point of intersection.
Note: Two solutions are possible. The second solution is
found by reversing the order of the coordinate and distance
parameters. That is, the computed solution is always clockwise
from point 1 to point 2.
Limitations: No solution is possible when the distance between
points 1 and 2 is greater than the sum of the two distances used
in the function.
Return Contents of Array Area:
[0]: 1 (number of coordinates pairs)
[1]: x-coordinate value
[2]: y-coordinate value
9:3:9 @CurveRadius ([PC],[PI],mode,radius,fmt)
This function returns the coordinate of the PT (Point of
Tangency - where the curve ends), given the coordinate of the
PC (Point of Curvature - where the curve begins), the PI (Point
of Intersection of the two tangents), and the 'radius' of the
curve. A curve to the right is assumed if the 'radius' is
positive. A negative 'radius' will cause the function to
compute the curve to the left. The 'mode' parameter is used to
establish the mode of the delta angle that is returned in the
array area.
Note: Even though this function returns a complete curve
solution that is loaded in the cell's array area, its primary
results (the PT) can still be referenced by other functions as
a single coordinate. The extra information is essentially
ignored.
Return Contents of the Array Area:
[0]: = 3 (number of coordinates pairs)
[1] = (x) PT
[2] = (y) PT
[3] = (x) RP
[4] = (y) RP
[5] = (x) PI
[6] = (y) PI
[7] = radius
[8] = tangent
[9] = chord
[10] = bearing of chord (DMS)
[11] = arc
[12] = delta (central angle)
[13] = degree of curve
[14] = sector area
[15] = segment area
9:3:10 @CurveDelta (PC,RP,mode,delta,fmt)
This function returns the coordinate of the PT (Point of
Tangency - where the curve ends), given the coordinate of the
PC (Point of Curvature - where the curve first begins), the RP
(Radius Point), and the 'delta' (central) angle. A curve to
the right is assumed if the 'delta' angle is positive; while a
negative 'delta' angle entry will cause the function to compute
the curve to the left. The 'mode' parameter is used to establish
the mode of the delta angle.
Note: Even though this function returns a complete curve
solution that is loaded in the cell's array space, its primary
results (the PT) can still be referenced by other functions as
a single coordinate. The extra information is essentially
ignored.
Return Contents of the Array Area:
[0]: = 3 (number of coordinates pairs)
[1] = (x) PT
[2] = (y) PT
[3] = (x) RP
[4] = (y) RP
[5] = (x) PI
[6] = (y) PI
[7] = radius
[8] = tangent
[9] = chord
[10] = bearing of chord (DMS)
[11] = arc
[12] = delta (central angle)
[13] = degree of curve
[14] = sector area
[15] = segment area
------------------------------------------------------------------------
Section 9:4 FUNCTIONS THAT RETURN: MULTIPLE COORDINATES
------------------------------------------------------------------------
The next two functions provide a means of grouping coordinates so that
they can be referenced as a single entity. Coordinates can be grouped
to form either a line segment or a polygon. For the purposes of this
package, a line 'segment' is defined as a series of two (2) or more
coordinates that form a contiguous (but not necessarily straight) line.
A 'polygon' is comprised of four (4) or more coordinates that form a
closed traverse, where the last coordinate closes on the first. The
difference between the first and last coordinate is termed the 'error
of closure'.
9:4:1 @Segment ([point],[segment],...,[point])
This function creates a special cell that contains a line
segment. Segments are used by this spreadsheet as a means of
grouping related coordinates so that they can be referenced by a
single Cell Address. These cells can be either plotted
individually or they can be used as building blocks to form
other segments or polygons. When plotted, they are treated as
separate entities and take on the color defined for the cell.
Note: There are some limitations to the colors that can be used
to plot a segment - refer to Section 9:8.
The parameter list required by this function may contain as
many references to POINT or SEGMENT cells as you like (as long
as the final coordinate count is greater than 2). An error will
be returned if you attempt to reference any other type of cell.
When referencing another SEGMENT cell, you are actually loading
all of its coordinates into the array area of the new segment.
You can do this in either forward or reverse order. To reverse
the order in which a segment's coordinates are loaded, precede
the segment's Cell Address with a minus (-) sign. This will
cause the last coordinate pair to be loaded first and so on.
Once created, cells of this type will display the word "SEGMENT"
followed by the total number of coordinate pairs it contains.
Return Contents of the Array Area:
[0] = number of (x,y) coordinate pairs
[1] = x coordinate of 1st point
[2] = y coordinate of 1st point
[3] = x coordinate of 2nd point
[4] = y coordinate of 2nd point
:
NOTE: Refer to Section 9:7 for a details on how to use this
function without a parameter list.
9:4:2 @Polygon ([point],[segment],...[point])
This function creates a special cell that contains a set of
coordinates that form a polygon. Once created, the polygon can
be either plotted (Section 9:8) or its area and the 'error of
closure' can be computed with the @Closure function (Section
9:5:3). Note: Because polygons form closed figures, they
technically do not have a beginning and end point. As a result,
POLYGON cells can not be used as building blocks to form other
polygons or line segments.
The parameter list required by this function may contain as many
references to POINT or SEGMENT cells as you like. A total of 4
or more coordinates, however, are required to form a polygon. An
error will be returned if you attempt to reference any other
type of cell. When referencing a SEGMENT cell, you are actually
loading its coordinates into the array area of the polygon. You
can do this in either forward or reverse order. To reverse the
order in which a segment's coordinates are loaded, precede the
segment's Cell Address with a minus (-) sign. This will cause
the last coordinate to be loaded first. Once the polygon is
formed, any difference between the first and last coordinate
loaded is termed the 'error-of-closure'.
Once created, cells of this type will display the word "POLYGON"
followed by the total number of coordinates that make up the
polygon.
Return Contents of the Array Area:
[0] = number of (x,y) coordinate pairs
[1] = x coordinate of 1st point
[2] = y coordinate of 1st point
[3] = x coordinate of 2nd point
[4] = y coordinate of 2nd point
:
NOTE: Refer to Section 9:7 for a details on how to use this
function without a parameter list.
------------------------------------------------------------------------
Section 9:5 FUNCTIONS THAT RETURN AN ANGLE and DISTANCE
------------------------------------------------------------------------
The next three functions return the angle and distance between two
coordinates. Two display formats are supported by setting the 'fmt'
parameter to either 0 or 1, where 0 is the General Display Format and 1
is the Bearing Display Format (refer back to Section 9:2:5). The 'mode'
and 'type' parameters are used to define the angle that will be
displayed.
9:5:1 @Inverse ([Point1],[Point2],mode,type,fmt)
This function returns the angle and distance between two
coordinates, [Point1] and [Point2]. The angle that is returned
is controlled with the 'mode' and 'type' parameters. Any of the
four available 'mode' settings (0-3) may be used, however, the
angle 'type' is limited to Direction Angles (i.e. type 0 thru
4). Refer back to Section 9:2:5 for the 'fmt' display settings
that are available.
Return Contents of the Array Area:
[0] = 1 (number of coordinate pairs)
[1] = distance
[2] = angle
9:5:2 @Radial ([BackSight],[Setup],[ForeSight],mode,type,fmt)
This function returns the field angle ('type' 5-8) measured
BETWEEN the lines formed by the [Setup] and [Backsight]
coordinates AND the [Setup] and [ForeSight] coordinates. In
addition to the angle, the distance between the [Setup] and
[ForeSight] coordinates is returned. Any of the four available
'mode' settings (0-3) may be used, however, the angle 'type' is
limited to Field Angles (i.e. type 5 thru 8). Refer back to
Section 9:2:5 for the 'fmt' display settings that are available.
Return Contents of the Array Area:
[0] = 1 (number of coordinate pairs)
[1] = distance
[2] = angle
9:5:3 @Closure ([polygon],mode,type,fmt)
This function computes the 'Error of Closure' and 'Area' of the
coordinates that form a POLYGON (refer to Section 9:4:2). The
angle and distance between the first and last coordinates is
termed the 'Error of Closure' and is displayed by the function.
The 'mode' and 'type' settings control the angle display format.
Any of the four available 'mode' settings (0-3) may be used. The
angle 'type' setting, however, is limited to Direction Angles
(0-4). Refer back to Section 9:2:5 for the 'fmt' display
settings that are available. The AREA of the polygon is stored
in element position [0], allowing it to be referenced directly
by formulas in other cells.
Return Contents of the Array Area:
[0] = AREA of polygon
[1] = DISTANCE between last and first coordinates
that form the polygon
[2] = ANGLE btw closing coordinates ('ray' angle)
[3] = Total length of the sides that form the polygon
------------------------------------------------------------------------
Section 9:6 FUNCTIONS THAT RETURN: MULTIPLE VALUE SOLUTIONS
------------------------------------------------------------------------
9:6:1 @Triangle (mode,angleA,angleB,angleC,sidea,sideb,sidec)
Given any THREE attributes of a triangle, this function will
return the full triangle solution in the cell's array area. The
following combinations of attributes are supported: ASA, SAA,
SSS, or SAS (where "SAS" means Side/Angle/Side). When using
this routine, ONLY INCLUDE THREE ATTRIBUTES (even if more are
known)! A '0' should be placed in the unknown attribute
positions. The results can be displayed using the "=*" Element
Display Command described in Chapter 7.
NOTE: The SSA option is not supported, since more than one
solution is possible.
| \
| B \
| \
| \ a
c | \
| \
| A C \
|_______________\
b
Example (SSS): @Triangle (3,0,0,0,3,4,5)
|
|__ mode of angles entered
and returned
Return Contents of the Array Area:
[0] = Area
[1] = Angle A
[2] = Angle B
[3] = Angle C
[4] = Side a
[5] = Side b
[6] = Side c
------------------------------------------------------------------------
Section 9:7 NULL POINTS, SEGMENTS, and POLYGONS
------------------------------------------------------------------------
Several of the functions discussed in this chapter can be used with an
empty (or null) parameter list. They include:
@XY()
@NE()
@POLAR()
@Segment()
@Polygon()
The 'null' form of these functions allow you to control a cell's Array
Area. That is, these functions identify a cell as containing a POINT,
SEGMENT, or POLYGON; but they do not create, load, or in anyway modify
its Array Area. Those duties are left to you to handle, which can be
very handy. Here are a few examples:
Example 1 Let's say you create a POLYGON using the standard form of
the @Polygon function (i.e. you references several other
SEGMENTS and POINTS within its parameter list). And,
let's say the coordinates the polygon is made up of are
just the ones want - you do not want any of them to
change. The problem is, when the referenced POINTS and
SEGMENTS change, so will the points used by the POLYGON.
That how spreadsheets work. How then do you save a
POLYGON? Simple, use the 'null' @Polygon() function.
Just enter this function call in the cell that contains
the polygon you want to preserve and you will break the
ties to other cells on the worksheet. Nothing can effect
these coordinates now - except you!
Example 2 Let's say you have a line SEGMENT that was formed by
referencing a number of POINTS throughout the worksheet
and that you want to copy this particular SEGMENT to
another worksheet. Will you have a problem doing this?
The answer is "yes", if it's not a 'null' SEGMENT. The
reason is that copying a SEGMENT (that references other
cells) to another worksheet would also require moving the
referenced cells! When copied, the SEGMENT will attempt
to access cells at the same relative locations on the new
worksheet as it did on the original worksheet. If cells
do not exist at these locations, an error condition will
result. To get around this, simply convert the standard
function call to a 'null' @Segment() call on the new
worksheet. Note: Copying a POINT, SEGMENT or POLYGON by
value (val) will automatically handle this situation for
you (see Section 4:2).
Example 3 Let's say you have a large polygon that you want to use
in a number of different worksheets. And, let's assume
this particular polygon is not made up of coordinates
that can found on these worksheets. Is there is easy way
to load such a polygon. Yes, you can load the polygon
into a cell that is defined by the 'null' @Polygon
function from a standard text file. Refer to the "f5
Text" option of Section 4:3 for details.
REMEMBER! WHEN USING 'NULL' FUNCTIONS, IT IS UP TO YOU TO MAKE SURE
THE CELL'S ARRAY AREA EXISTS AND THAT IT IS LOADED
CORRECTLY.
------------------------------------------------------------------------
Section 9:8 GRAPHICS
------------------------------------------------------------------------
The @Segment and @Polygon functions are used to define geometric
'shapes' that, among other things, can be plotted. When plotted, lines
are drawn between each coordinate that form these entities; while
individual coordinates (POINTS) are plotted as single points.
Function keys F2 thru F6 of the UTILITY Menu are used to display the
points, lines, and polygons created by this Coordinate Geometry Package
(see Section 4:7). You can plot as many of these objects as you like
(individually or together) by positioning the Cell Pointer over the cell
you wish to include and then pressing the F6 (Plot) function key. You
can continue to identify cells in this fashion until you press the
<Enter> key, which will initiate the display.
To provide compatibility with as many PCs as possible, REBEL uses video
BIOS calls for all of its graphics displays. While this approach
extends graphic capabilities to a wide range of IBM-PCs and
compatibles, it does not always take full advantage of your system's
video adapter. For example, only three colors can be displayed at a
time, which are grouped into two palettes (WHITE-CYAN-MAGENTA and
YELLOW-GREEN-RED). The system will attempt to use the color defined
for a cell (as long as it remains in the same color palette).
------------------------------------------------------------------------
Section 9:9 EXAMPLES
------------------------------------------------------------------------
_______________________
IMPORTANT
Before trying out any of the following examples, be sure that
the Column Width of column 3 is set to 27 (refer to 'Width'
option discussed in Section 4:6).
And, initialize cells [1,3], [2,3], and [3,3] with the
following coordinate pairs, by:
...moving the Cell Pointer to [1,3] and enter: @XY(2000,1000)
...moving the Cell Pointer to [2,3] and enter: @XY(500,500)
...moving the Cell Pointer to [3,3] and enter: @XY(1000,1000)
IMPORTANT
_______________________
Example 1) TRAVERSE North 45 degrees 15 minutes and 30 seconds East
a distance of 378 feet from the point loaded in cell [3,3].
With the Cell Pointer positioned over cell [5,3] enter:
@Traverse([3,3],3,1,145.1530,378,0)
| | | |_ XY display
| | | format (9:2:5)
| | |
mode: degrees (dms) _| | |__ bearing angle (9:2:4)
(9:2:2) |__ angle type: bearing (9:2:3)
Answer Displayed: (1268.49, 1266.08)
Example 2) Assume that, in the field, you are setup over point [3,3]
and that you are backsighting point [2,3]. Compute the
coordinate of a new point by turning an angle RIGHT of
22 degrees 30 minutes and measuring a distance of 78 feet.
With the Cell Pointer positioned over cell [6,3] enter:
mode: degrees (dms) __
|
@FieldAngle([2,3],[3,3],3,5,22.30,0,78,0)
| |
field angle right __| |_ vertical
angle
Answer: (927.94, 970.15)
Example 3) Compute the coordinate of the point that measures 750
feet from point [2,3] and 550 feet from the point [3,3].
With the Cell Pointer positioned over cell [7,3] enter:
@DstDstInt([2,3],[3,3],750,550,0)
Answer: (510.07, 1249.93)
Example 4) Assume that point [2,3] is the PC (point of curve) and
that point [1,3] is the PI (point of intersection) of a
curve that bears to the left with a radius of 1028 feet.
Compute the coordinate of the PT (point of tangency);
together with a FULL CURVE SOLUTION. With the Cell
Pointer positioned over cell [8,3] enter:
mode to be used to return delta angle _
|
@CurveRadius([2,3],[1,3],3,-1028,0)
|
radius must be negative to compute __|
a curve to the left
Answer: (934.36, 2198.08)
NOTE: Use the "=*" command at the Input Line to
review the curve solution loaded in the cell's
array area.
Example 5) Form a POLYGON with the initialized coordinates.
With the Cell Pointer positioned over cell [9,3] enter:
@Polygon([1,3],[2,3],[3,3],[1,3])
NOTE: This figure can now be plotted (see Section 9:8).
Example 6) Compute the ERROR-OF-CLOSURE of the polygon formed in the
above example. With the Cell Pointer positioned over
cell [10,3] enter:
@Closure([9,3],3,1,1)
Answer: (N90 00'00.0"E 0.00)
NOTE: The AREA of the polygon is loaded in element
position [0]. To view it, type "=0" at the Input
Line (without the quotes).
Example 7) Compute the ANGLE and DISTANCE between the coordinates
loaded in cells [2,3] and [1,3]. With the Cell Pointer
positioned over cell [11,3] enter:
@Inverse([2,3],[1,3],3,1,1)
|
bearing/distance display format _|
Answer: (N71 33'54.1"E 1581.14)
Example 8) Compute the ANGLE formed by the intersection of two lines
formed by the coordinates loaded in cell [1,3] (the
backsight), cell [2,3] (the setup or intersection), and
cell [3,3] (the foresight). With the Cell Pointer
positioned over cell [12,4] enter:
return angle right __
|
@Radial([1,3],[2,3],[3,3],3,5,0)
|
general display format__|
Answer: (RHT(dms) 26.33542, 1581.14)
_____________________________
________________________________________/ Chapter 10 MATRIX
Chapter Contents
Section 10:1 ABOUT THE MATRIX FUNCTIONS
Section 10:2 LOADING AND EDITING A MATRIX
Section 10:3 DISPLAYING MATRIX ELEMENTS
Section 10:4 REFERENCING OTHER MATRICES
Section 10:5 MATRIX FUNCTIONS THAT RETURN ANOTHER MATRIX
10:5:1 @Matrix . . . . . . . creates and loads a matrix
10:5:2 @MatrixAdd . . . . . . adds matrix A & B
10:5:3 @MatrixCrossProduct. . cross product of matrix A & B
10:5:4 @MatrixExponent . . . multiplies a matrix by itself
10:5:5 @MatrixInverse . . . . inverts a matrix
10:5:6 @MatrixProduct . . . . multiples matrix A & B
10:5:7 @MatrixScalar . . . . scalar multiplication
10:5:8 @MatrixSolution . . . returns solution set
10:5:9 @MatrixSubtract . . . subtracts matrix B from A
10:5:10 @MatrixTranspose . . . transposes matrix A
Section 10:6 MATRIX FUNCTIONS THAT RETURN A SINGLE VALUE
10:6:1 determinant . . . . . returns the determinant
10:6:2 dotproduct . . . . . . returns the dot product
Section 10:7 USING 'NULL' MATRICES
Section 10:8 USING MATRICES WITH POINTS, SEGMEMTS, AND POLYGONS
Section 10:9 EXAMPLES
------------------------------------------------------------------------
Section 10:1 ABOUT THE MATRIX FUNCTIONS
------------------------------------------------------------------------
Like the functions used in the Coordinate Geometry package, these
functions also take advantage of several of REBEL's more advanced
features. As a result, it is NOT advisable to jump immediately into
this chapter without first becoming familiar with the topics discussed
in Chapter 6. I highly recommend, that at a very minimum, you become
familiar with the concept of "Cell Array Areas" (Section 6:5) before
continuing.
Once you learn how a matrix is created and stored, you will find that
these functions are simple to use. As with the COGO functions, there
are a few important differences that distinguish them from the standard
functions that were covered in Charter 8. The most significant of these
differences is the way in which values are returned and displayed.
While standard functions (and expressions) always return a single value
that is displayed by the cell, MATRIX @functions always return multiple
values that are loaded into the cell's Array Area. These values comprise
the elements of the matrix and are not displayed by the spreadsheet.
Cells that contain matrices will display the the number of rows and
columns a matrix contains (its size) followed by the word "MATRIX".
Another important distinction is that MATRIX @functions can not be
nested within the parameter lists of other functions, since they return
multiple values. If used, they must be the only entry in a cell's
formula.
------------------------------------------------------------------------
Section 10:2 DEFINING, LOADING AND EDITING A MATRIX
------------------------------------------------------------------------
The '@Matrix' function (described in Section 10:5:1) is used to create
and load a matrix whose element values are known. All of the other
matrix functions described in this chapter create new matrices based on
an operation performed on one or more existing matrix. You can load a
matrix by either entering the values that form the matrix directly into
the parameter list of the '@Matrix' function:
@Matrix (3,4, 2,4,7,0, 1,1,9,7, 3,1,0,9)
or by setting up the @Matrix function to reference values in other
cells that can be more easily changed:
@Matrix (2,3, [5,1],[5,2],[5,3], [6,1],[6,2],[6,3])
One word of caution, if you decide to use the second method, keep in
mind that each time a value in cells [5,1] thru [6,3] is changed, it
could trigger a whole series of matrix operations to be performed.
Since you'll probably want to prevent this from happening until you
have completed all of your changes, you will need to set the +NoCalc
flag described in Section 4:1. This setting will prevent the cell that
contains the matrix (whose element values your are modifying) from
triggering other cells from recalculating - even though their values are
affected by the changes. The 'CelCalc' option listed under the UTILITY
menu (Section 4:7) can then be used to force a recalculation of the
affected cells whenever you are ready.
------------------------------------------------------------------------
Section 10:3 DISPLAYING A MATRIX
------------------------------------------------------------------------
As mentioned earlier, the spreadsheet does not display the element
values of a matrix. It is left to the you to decide whether or not to
display these values and by which method. The simplest way to display
the elements of a matrix is to use the "=" Shortcut Command. To use
this command, simply move the Cell Pointer to the cell that contains a
matrix and type an equal sign (=) at the Input Line and then press the
<Enter> key. This will cause a single row of the matrix to be displayed
each time the <Enter> key is pressed. There are a number of variations
to this command that can also be used (refer to Chapter 7 for details).
You can also display the elements of a matrix, on the screen, by
referencing the Array Area of the matrix cell from other cells. To do
this, however, it requires a knowledge of how the matrix is stored in
the Array Area. The first two array positions contain the number of
ROES and COLUMNS in the matrix. This is followed by the value of each
element beginning with the first column of the first row and moving
left-to-right to the last column in the last row. The first two
parameters determine the total number of entries that are contained in
the array.
Array
Position Contents
[1] The number of ROWS in the matrix
[2] The number of COLUMNS in the matrix
[3] Element loaded in the 1st COLUMN of the 1st ROW
[4] Element loaded in the 2nd COLUMN of the 1st ROW
:
[n] Element in the last COLUMN of the last ROW
For example, if you wanted to display the 1st element in the 1st row of
a matrix that is defined is cell [5,1], you could make the following
array reference from any empty cell: [5,1,3].
REMEMBER! THE FIRST TWO ARRAY POSITIONS CONTAIN THE NUMBER OF ROWS AND
COLUMNS IN THE MATRIX. THE ELEMENT VALUES ARE LOADED, STARTING AT ARRAY
POSITION THREE (3)!
------------------------------------------------------------------------
Section 10:5 MATRIX FUNCTIONS THAT RETURN ANOTHER MATRIX
------------------------------------------------------------------------
All of the functions described in this section will return a 'matrix'
upon completion. As a result, the 'Cell Address' of any CELL that
contains one of these function calls can be used as a parameter within
another matrix function call. Remember, a Matrix @Function 'call' can
not be nested within the parameter list of another function call, but
the 'Cell Address' of a CELL that contains a matrix CAN be used as a
parameter. So, whenever you see a function parameter surrounded by
square brackets, such as [matrix_A], it is referring to the the Cell
Address of a cell that contain a matrix (or in some cases a POINT,
SEGMENT or POLYGON).
If an error is detected by any of these functions, a series of question
marks (?????) will appear in the cell's display. You can identify the
exact problem by moving the Cell Pointer to the cell in question and
then entering the Edit Mode. This will cause an error message to be
displayed that will hopefully help you correct the problem (refer back
to Chapter 5).
________________________________________________________
10:5:1 @Matrix (rows, columns, element1, element2, ... elementn)
This function creates a matrix of the size defined by the
first two parameters. The next series of parameters are
used to load the matrix elements, beginning with the first
column of the first row and moving left-to-right to the
last column in the last row. The first two parameters
determine the total number of entries that must be made.
For example, the following function call would be used to
load this 3x4 matrix:
| 2 4 7 0 |
| 1 1 9 7 |
| 3 1 0 9 |
@Matrix (3,4, 2,4,7,0, 1,1,9,7, 3,1,0,9)
Return contents of the cell's Array Area:
[0] (not used)
[1] The number of ROWS in the matrix
[2] The number of COLUMNS in the matrix
[3] Element loaded in the 1st COLUMN of the 1st ROW
[4] Element loaded in the 2nd COLUMN of the 1st ROW
:
[n] Element in the last COLUMN of the last ROW
________________________________________________________
10:5:2 @MatrixAdd ([matrix_A],[matrix_B])
ADDS matrix A and matrix B, returning matrix C that is
loaded in the Array Area of the cell that contains this
function call. The format used to load matrix C in the
cell's Array Area is described in Section 10:5:1. Note,
the parameter '[matrix_A]' refers to the Cell Address of
the cell that contains matrix A.
Requirements:
(i) Matrix A and B must be the same size.
________________________________________________________
10:5:3 @MatrixCrossProduct ([matrix_A], [matrix_B])
Computes the CROSS PRODUCT of matrix A and B, returning
matrix C that is loaded in the Array Area of the cell that
contains this function call. The format used to load
matrix C in the cell's Array Ares is described in Section
10:5:1. Note, the parameter '[matrix_A]' refers to the
Cell Address of the cell that contains matrix A.
Requirements:
(i) Both matrix A and B must be 1X3 matrices.
________________________________________________________
10:5:4 @MatrixExponent ([matrix_A], power)
Matrix A is MULTIPLIED by itself the number of times set
by the 'power' variable, returning matrix C that is loaded
in the Array Area of the cell that contains this function
call. The format used to load matrix C in the cell's Array
Ares is described in Section 10:5:1. Note, the parameter
'[matrix_A]' refers to the Cell Address of the cell that
contains matrix A.
Requirements:
(i) Matrix A must be a square matrix.
(ii) The 'power' variable must be a whole number > 1
________________________________________________________
10:5:5 @MatrixInverse ([matrix_A])
Computes the INVERSE of matrix A, returning a matrix (C)
that is loaded in the Array Area of the cell that contains
this function call. (It should be noted that multiplying
matrix A by matrix C will produces the 'identity matrix'.)
The format used to load matrix C in the cell's Array Area
is described in Section 10:5:1. Note, the parameter
'[matrix_A]' refers to the Cell Address of the cell that
contains matrix A.
Requirements:
(i) Matrix A must be a square matrix.
________________________________________________________
10:5:6 @MatrixProduct ([matrix_A], [matrix_B])
MULTIPLIES matrix A by matrix B, returning matrix C that is
loaded in the Array Area of the cell that contains this
function call. Matrix C will have the same number of ROWS
as matrix A and the same number of COLUMNS as matrix B.
The format used to load matrix C in the cell's Array Area
is described in Section 10:5:1. Note, the parameter
'[matrix_A]' refers to the Cell Address of the cell that
contains matrix A.
Requirements:
(i) The number of columns in matrix A must equal the
number of rows in matrix B.
________________________________________________________
10:5:7 @MatrixScalar ([matrix_A], scalar)
MULTIPLIES matrix A by a single number (scalar), returning
matrix C that is loaded in the Array Area of the cell that
contains this function call. The format used to load
matrix C in the cell's Array Ares is described in Section
10:5:1. Note, the parameter '[matrix_A]' refers to the
Cell Address of the cell that contains matrix A.
________________________________________________________
10:5:8 @MatrixSolution ([matrix_A])
SOLVES a system of simultaneous equations represented by
the augmented matrix A, returning the solution set in
matrix C that is loaded in the Array Area of the cell that
contains this function. Matrix C is always returned as a
(m x 1) matrix, where m equals the number of rows in
matrix A. The format used to load matrix C in the cell's
Array Ares is described in Section 10:5:1. Note, the
parameter '[matrix_A]' refers to the Cell Address of the
cell that contains matrix A.
Requirements:
(i) Matrix A must be an augmented matrix, where
the rows of the matrix represent the coefficients
of the variables followed by the constant term.
For example:
System Augmented Matrix
x - y + z = 0 | 1 -1 1 0 |
3x + 4y + 2z = 1 | 3 4 2 1 |
2x + 7y + 13z = 2 | 2 7 13 2 |
(ii) The number of equations (rows) must equal the
number of variables. That is, matrix A must
have one more column than it does rows.
________________________________________________________
10:5:9 @MatrixSubtract ([matrix_A],[matrix_B])
SUBTRACTS matrix B from matrix A, returning matrix C that
is loaded in the Array Area of the cell that contains this
function call. The format used to load matrix C in the
cell's Array Area is described in Section 10:5:1. Note,
the parameter '[matrix_A]' refers to the Cell Address of
the cell that contains matrix A.
Requirements:
(i) Matrix A and B must be the same size.
________________________________________________________
10:5:10 @MatrixTranspose ([matrix_A])
TRANSPOSES matrix A (i.e. flips the matrix so that the
rows become the columns and the columns become the rows),
returning matrix C that is loaded in the Array Area of the
cell that contains this function call. The format used to
load matrix C in the cell's Array Ares is described in
Section 10:5:1. Note, the parameter '[matrix_A]' refers
to the Cell Address of the cell that contains matrix A.
------------------------------------------------------------------------
Section 10:6 MATRIX FUNCTIONS THAT RETURN A SINGLE VALUE
------------------------------------------------------------------------
The following two functions return single values that are displayed by
by the spreadsheet. As a result, both functions can be nested within
other expressions.
________________________________________________________
10:6:1 determinant ([matrix_A])
Returns the DETERMINANT of matrix A. Note, the parameter
'[matrix_A]' refers to the Cell Address of the cell that
contains matrix A.
Requirements:
(i) Matrix A must be a square matrix.
________________________________________________________
10:6:2 dotproduct ([matrix_A],[matrix_B])
Returns the DOT PRODUCT of matrix A and B (defined as the
sum of the products of the corresponding elements). Note,
the parameter '[matrix_A]' refers to the Cell Address of
the cell that contains matrix A.
Requirements:
(i) The number of rows and the number of columns in
matrix 'A' and matrix 'B' must be equal.
(ii) Either the number of rows or columns must equal 1,
but not both!
------------------------------------------------------------------------
Section 10:7 USING 'NULL' MATRICES
------------------------------------------------------------------------
A special variation of the '@Matrix' function allows it to be used with
an empty (or 'null') parameter list.
@Matrix()
The important thing to remember about a NULL MATRIX, is that it is
totally user defined. That is, it is left to you to create and load the
cell's Array Area with the correct element values. The @Matrix()
function will neither create or alter the Array Area of a new or
existing cell. It simply identifies a cell as containing a matrix.
This fact can be taken advantage of when it's necessary to save the
results of a matrix you have computed. Since the Array Area of an
existing matrix is already setup correctly, all that is necessary to do
to preserve its contents is to enter "@Matrix()" at the Input Line (with
the Cell Pointer positioned over the matrix cell). This will form a
NULL MATRIX, which removes the matrix operation that was used to compute
the original matrix. The element values are unaffected.
A NULL MATRIX is also useful when it comes to loading a large matrix
from an external text file (refer to loading 'Text' files in Section
4:3).
REMEMBER, WHEN USING A NULL MATRIX, IT IS UP TO YOU TO MAKE SURE THAT
AN ARRAY AREA FOR THE CELL EXISTS AND THAT IT IS LOADED PROPERLY. The
following diagram defines how the Array Area of a matrix must be setup.
Notice that the first two entries always contain the number of rows and
columns in the matrix. These values are followed by the element values
of the first row, the second row, and so on.
[1]: number of ROWS in the matrix
[2]: number of COLUMNS in the matrix
[3]: first column entry of the first row
[4]: second column entry of the first row
:
[n]: last column entry of the last row
------------------------------------------------------------------------
Section 10:8 USING MATRICES WITH POINTS, SEGMEMTS, AND POLYGONS
------------------------------------------------------------------------
One of the most powerful features of the this package is its ability to
perform repeated @MatrixProduct operations on the coordinates of POINTS,
SEGMENTS, and POLYGONS, forming a new cell (of the same type) that
contains the transformed coordinates. To do this, the spreadsheet views
each coordinate pair as an independent 1X3 matrix (the third element is
always set to 1) that can be multiplied by any 3x3 transformation
matrix. When doing this, the only rule that must be followed is that
the POINT, SEGMENT, or POLYGON cell must be the A matrix (the first
reference) in the function's parameter list. As an example, to rotate,
scale, or translate the coordinates of a polygon, the function call
should be setup as follows:
@MatrixProduct ([polygon], [transformation_matrix])
When the above operation is performed, a new cell will be created based
on the cell type of the first parameter (in this case a POLYGON) with
each of its coordinates multiplied times the transformation matrix.
------------------------------------------------------------------------
Section 10:9 EXAMPLES
------------------------------------------------------------------------
Example 1) MULTIPLY MATRIX 'A' BY MATRIX 'B'
A = | 2 1 0 | B = | 1 -1 2 |
| 0 1 1 | | 1 2 3 |
| 0 1 1 |
Load matrix A into cell [3,2] by entering the the following
formula:
@Matrix (2,3, 2,1,0, 0,1,1)
Load matrix B into cell [3,4]:
@Matrix (3,3, 1,-1,2, 1,2,3, 0,1,1)
To multiply matrix A by B, enter the following function
in cell [3,6]:
@MatrixProduct ([3,2],[3,4])
To quickly display the elements of the new matrix (C),
position the Cell Pointer over cell [3,6] and type
a lone equal sign (=) at the Input Line followed by the
<Enter> key. This will cause the first row of the new
matrix to be displayed at the Input Line. Press the
<Enter> key again to display the next row, and so on.
Example 2) DISPLAY THE ELEMENTS OF MATRIX 'C' ON THE SCREEN.
Locate an area on your worksheet large enough to display
the matrix. In this case, we'll use the range of cells
between [5,3] and [6,5].
To display matrix C (loaded in cell [3,6]), position the
Cell Pointer over cell...
[5,3] and enter: [3,6,3]
[5,4] and enter: [3,6,4]
[5,5] and enter: [3,6,5]
[6,3] and enter: [3,6,6]
[6,4] and enter: [3,6,7]
[6,5] and enter: [3,6,8]
IMPORTANT! Notice that the first element of matrix C is
stored in array position "3" ([3,6,3] - not position one.
The first two array positions of all cells that contain
matrices are reserved for the number of 'rows' and
'columns' in the matrix (refer back to Section 10:3).
IMPORTANT! The one problem with displaying a matrix as
just described is that cells [5,3] thru [6,5] will not
update themselves when the element values of matrix C
(cell [3,6]) are changed. Refer to Section 6:5 for a
detailed explanation as to why this is the case. To force
these cells to update, you can set the +ReCalc flag
for each of these cells (described in Section 4:1). This
will cause cells [5,3] thru [6,5] to automatically update
their values - each time ANY change is made to the
worksheet.
Example 3) MODIFY MATRIX 'A' SO THAT 2 OF ITS ELEMENT VALUES
CAN BE SET FROM VALUES ENTERED IN OTHER CELLS.
First, enter a value in cell [1,2] and cell [2,2] (any
value). Then modify the formula in cell [3,2] (matrix A)
to read:
@Matrix (3,3,[1,2],1,3,[2,2],5,6)
Now, each time you reset the values in cells [1,2] and [2,2]:
(i) the value of matrix A will change
(ii) matrix A and B will be re-multiplied, updating
the value of matrix C in cell [3,6]
(iii) and, the values displayed in cells [5,3] thru
[6,5] will be updated (provided you set the
+ReCalc flag for these cell).
____________________________
_________________________________________/ Chapter 11 SCRIPTS
INTRODUCTION
Scripts are one of the most powerful features offered by REBEL. They
extend the power and capabilities of the spreadsheet by allowing you to
tailor functions, utilities, and even full applications to fit your
specific needs. They are also easy to use. Simply reference the
library that contains the script you want and REBEL's powerful Script
Manager will handle the rest for you.
REBEL comes with a standard a script library called STDLIB.REB. While
it contains many useful functions, it's far from complete in terms of
what is possible. The documentation for this library is included in
a file called STDLIB.DOC.
_________________________________________
Chapter Contents
Section 11:1 What are scripts?
Section 11:2 Using scripts
Section 11:3 The Script Index
Section 11:4 The "Swap Area"
Section 11:5 Trouble Shooting
Section 11:6 Writing your own scripts
_________________________________________
-----------------------------------------------------------------------
Section 11:1 WHAT ARE SCRIPTS?
-----------------------------------------------------------------------
Scripts are functions or procedures that contain one or more statements
that can be organized and executed in a highly structured manner to
perform a specific task. The structure or syntax used to write scripts
is very similar to the 'C' programming language.
Scripts have several advantages over cell formulas. They allow you to
control the number and the order in which statements are executed
before returning control to the spreadsheet. They can also be used as
building blocks to access other scripts and/or a whole set of built-in
'Toolkit' functions that provide a complete interface to every aspect
of the spreadsheet. And finally, scripts combine the powerful
computational features of a spreadsheet with the flexibility of a
traditional programming language.
In general, scripts are grouped into three major categories: functions,
utilities, and applications.
FUNCTIONS: While REBEL provides 94 built-in functions that perform
a wide range of spreadsheet computations, there are literally
thousands of other functions (geared to specific industries or
occupations) that could be developed. Fortunately, REBEL makes it
is easy to add libraries of such routines with a highly structured,
C-like, script language. These functions (or scripts) look and
behave just like built-in functions and are also automatically
included in REBEL's recalculation engine.
UTILITIES: Scripts can also be designed to perform specialized
tasks that fall under the same category as most 'macros' (i.e.
utilities). Scripts of this type usually perform some action or
command currently not available from the Function Key Menus. As
an added option, these scripts can also be assigned and executed
from a special set of function keys (see Section 4:5).
APPLICATIONS: Some scripts become so powerful that they actually
take over control of the spreadsheet itself. These scripts often
use the spreadsheet only as a powerful computational tool by hiding
the spreadsheet matrix and redesigning the user interface. This
makes the application easier to learn and less intimidating for
the user.
-----------------------------------------------------------------------
Section 11:2 USING SCRIPTS
-----------------------------------------------------------------------
Before a script can be accessed from a library, the name of the library
file must either be 'attached' in REBEL's start up sequence with the -L:
option or attached from within the spreadsheet itself with the 'Attach'
Function Key Command discussed in Section 4:5. Once a library has been
referenced in this fashion, REBEL's Script Manager handles the rest for
you.
For example, to access the scripts in REBEL's standard library,
STDLIB.REB, simply restart the program using the -L: option (followed
immediately by the name of the library):
REBEL -L:STDLIB
It's that simple!
-----------------------------------------------------------------------
Section 11:3 THE SCRIPT INDEX
-----------------------------------------------------------------------
While there is no limit to the number of libraries that can be
attached with the -L: option or with the 'Attach' command, REBEL's
Script Manager will only keep track of the first 64 scripts it
encounters. It does this by loading information about each script into
a special 'Script Index' as each library file is scanned. The
information contained in this index is then used by the Script Manager
to manage and execute scripts in a special memory area called the Swap
Area (discussed in more detail in the next section). The important
thing to remember is that ONLY the scripts found in the Script Index
can be accessed by the Script Manager and that scripts ARE NOT loaded
into the Swap Area until they are actually used in the spreadsheet.
The Script Index contains a great deal of useful information designed
to help you to fine tune your system. You can inspect its contents
with the 'Index' Function Key Command explained in Section 4:5. This
command displays the following information about each script:
- The SIZE of the script in bytes (characters)
- Address of the script within the Swap Area
- The NAME of the library that contains the script
- The library's version number (set by its author)
- The version of the Script Library Builder used to create
the library.
- The recommend 'minimum' version of REBEL to use
NOTE: From time to time, it may become necessary to clear the Script
Index to make room for scripts found in other libraries. This can be
done with the 'Detach' function key command described in Section 4:5 of
this manual.
-----------------------------------------------------------------------
Section 11:4 THE SWAP AREA
-----------------------------------------------------------------------
Before a script can be executed, the Script Manager must copy the body
of the script from a library file to a special memory area called the
Swap Area. Once loaded, scripts will either remain in this area (where
they can be quickly re-executed) or will be temporarily swapped out to
make room for other scripts that require additional memory. In either
case, the Script Manager keeps track of what's going on.
If the Script Manager cannot load a script due to insufficient memory,
it will begin swapping out scripts currently not in use. If enough
memory still cannot be collected after completing this process, the
Script Manager will inform you that the memory in the Swap Area has
been exhausted. You can correct the problem by increasing the size of
the Swap Area (see NOTE below). There are two ways to do this.
First, you can reset the size of the Swap Area by restarting the
program using the -M: option (followed immediately by the size of the
Swap Area - in bytes). For example, a start up sequence that would
attach the scripts contained in library STDLIB.REB and then set the
size of the Swap Area to 2000 bytes would look something like this:
REBEL -L:STDLIB -M:2000
The 'SwpArea' function key command (see Section 4:5) can also be used
to adjust the size of the Swap Area. This command has the added
advantage of allowing you to set the size of the Swap Area without
exiting the program.
While setting the Swap Area to the smallest possible size conserves
memory which can be used for other purposes, it can also slow the
overall performance of recalculations by forcing the system to
continuously swap the same scripts in and out of memory as they are
used. In general, the larger the Swap Area the faster system will run.
You may want to experiment a little to find the best possible trade off
between memory and performance before deciding on the optimum size of
the Swap Area.
NOTE: You can determine the minimum number of bytes required to run
each script by using the 'Index' Function Key command to inspect the
Script Index. (The current size of the Swap Area can be displayed
by executing the 'SwpArea' Function Key command and then pressing the
<Enter> key without an entry.)
-----------------------------------------------------------------------
Section 11:5 TROUBLE SHOOTING
-----------------------------------------------------------------------
When using a script within a cell formula, several problems can occur
that could cause a series of question marks (??????) to be displayed
by the cell. The fastest way to determine what may have gone wrong is
to move the Cell Pointer to the cell containing the problem and then
to enter the Edit Mode (see Chapter 5). This will cause an error
message to be displayed that will hopefully help you solve the problem.
If the error message does not provide you with enough information and
you are sure the problem is being caused by a script, review the
following list of problem scenarios for a possible solution.
ERROR: "script Swap Area is exhausted"
Problem: The Swap Area may be exhausted, even though it is
larger than the biggest script listed in the Script Index.
This can happen when one script calls another script. In
case, both scripts are "in use"; and, therefore, can not be
swapped out.
Solution 1: Use the 'SwpArea' function key command (see
Section 4:5) to increase the size of the Swap Area, a
little at a time, until the script is able to run.
ERROR: "undefined script or function"
Problem: The spreadsheet is unable to locate the script.
Solution 1: The library containing the script you are
attempting to access may not have been properly 'attached'.
Try locating the script in the Script Index. If you can not
find it, try re-attaching the library with the 'Attach'
Function Key command (refer to Section 4:5).
Solution 2: The name of the script may not be spelled
correctly. Remember script names are case sensitive (i.e.
"sum" and "Sum" are not the same).
Solution 3: You may have 'attached' a series of libraries
that (in combination) total more than 64 scripts. As a
result the script you are attempting to access may not have
been loaded in the index. Try clearing the Script Index
with the 'Detach' function key command (Section 4:5) and
re-attaching the library containing the script that you
need - first.
ERROR: "out of memory"
Problem: Scripts containing locally declared variables
dynamically reserve and then free up a portion of memory
for their own use. This memory is not part of the Swap
Area. If a worksheet happens to consume a large amount of
memory, it is possible that these scripts will be unable to
allocate the memory it needs to run.
Solution 1: Try reducing the size of your worksheet.
Hopefully, this will free up enough memory that the script
will be able to run.
ERROR: "syntax error"
Problem: As time goes on and new features are added to
REBEL, it will be possible to write scripts that will not
run properly on an older version of REBEL.
Solution 1: Use the 'Index' command to verify that you are
use a version of REBEL that is capable of running the
script in question. There is no charge to upgrade this
program, if it becomes necessary.
-----------------------------------------------------------------------
Section 11:6 WRITING YOUR OWN SCRIPTS
-----------------------------------------------------------------------
All versions of REBEL 3.0 and higher come with a built-in Script
Manager that can access and execute scripts found in any (.REB) library
file. Writing and creating these libraries, however, is an optional
feature requiring a license for REBEL's Script Library Builder. The
cost of this additional software is $29.00 (see Order Form at the end
of this manual).
Because the subjects related to writing scripts are covered extensively
in the documentation that accompanies the Script Library Builder, only
a brief discussion of these topics is included here. The following
section, therefore, is only intended to be an introduction and is by no
means complete.
_________________________________
One of the first things you'll notice about REBEL's Script Language
(RSL) is that it's a far cry from the 'macro' languages supported by
most spreadsheets. RSL is a highly structured, C-like programming
language that is both powerful and easy to learn. And, if you are an
experienced 'C' programmer, you'll find that writing these scripts
is almost second nature. In fact, the body of many scripts will
actually compile in 'C'. (If you're not familiar with 'C', you can
take comfort in the fact that you will also be learning one of the most
popular programming languages ever developed as you learn to write
scripts.)
Before going into detail on the specific components that make up a
script, it might be useful to see what an actual script looks like.
For this, let's use the famous "Hello world!" example:
hello (val: row, col) /* pass in two values - the cursor pos */
chr: buffer[80]; /* declare a local string of 80 chars */
{
cursor(row,col); /* position cursor on screen */
strcpy(buffer,"Hello world!"); /* save string constant */
fputs(buffer,0); /* write 'string' to screen */
}
The above example is slightly more complicated than it needs to be to
demonstrate local variable declarations and the parameter passing
features of scripts .
Notice that the first line of the script contains the name of the
script followed by the names of two arguments that are passed to it
from another script or from the worksheet. These arguments must be
enclosed within a set of parentheses and can include four distinct data
types: values (val:), arrays (val: []), character strings (chr:), and
cell address pointers (cel:).
Local parameters can also be declared that are only visible while the
script is being executed. If used, these variables MUST precede the
opening curly brace '{' that begins the body of the script. This
differs from 'C', where the local parameters follow the opening brace.
There are 3 data types that can be declared locally: (1) values, (2)
arrays, and (3) character strings.
The body contains the statements that are executed when the script is
run. It follows the local parameter declarations and is enclosed within
a set of curly braces {}.
RSL supports 3 programming constructs (if, for, while) that can be used
to control the order in which statements are executed.
The 'if-else' construct:
if (condition) {
statement(s);
} else if (condition) {
statement(s);
} else {
statement(s);
}
The 'for' construct:
for (initialize; condition; increment) {
statement(s);
}
For example:
for (ii=0; ii<80; ii+=1) { /* blank out 'name' */
name[ii] = ' ';
}
The 'while' construct:
while (condition) {
statement(s);
}
RSL also supports:
comments (/* .. */)
'break'
'continue'
'return'
TOOLKIT FUNCTIONS
In addition to its powerful programming constructs, RSL supports an
extensive set of over 30 Toolkit functions. These routines are
accessible only to scripts and cannot be used within cell formulas.
They extend a wide range of capabilities that include access to the
primary elements that control the spreadsheet, as well as to I/O and
screen handling routines.
NOTE: A more detailed description of each of these functions is
included with the documentation accompaniing REBEL's Script Library
Builder.
fopen - Opens a text file for read, write, or append access.
fclose - Closes a file opened by 'fopen'.
fgets - Reads the first 'n' characters from a text file (or
until an End-of-Line marker is encountered), placing
the characters read into the 'string' parameter. This
routine can also read characters from the keyboard by
setting the 'fp' parameter to zero (0).
fputs - Writes the character in the 'string' parameter to a
text file that has been previously opened by 'fopen'.
This function can also be used to write characters to
the screen by setting the 'fp' parameter to zero (0).
sprintf - Converts and formats an argument list made up of any
number or type of parameters into a character string.
Supports '%s' and '%f' conversions.
getchar - Returns the ASCII value of the next character returned
from the keyboard.
cursor - Moves the screen cursor to a specific row and column
position.
erase - Erases 'n' character on the screen.
clear - Clears the screen.
refresh - Re-displays the spreadsheet.
drawbox - Draws a box of the screen.
savework - Saves a worksheet in REBEL's native .RB2 format.
loadwork - Loads a worksheet that has been saved in REBEL's
native .RB2 format.
report - Copies a portion of a worksheet to a disk file.
freework - Deletes all data at the specified worksheet level.
makentry - Makes a cell entry at a specific location.
getentry - Returns the cell entry (formula) of a cell.
contents - Returns a copy of the text that is displayed by a cell.
recalc - Recomputes a cell's formula.
purgecell - Purges (deletes) a cell from the worksheet.
copycell - Copies a cell to another location.
movecell - Moves a cell to another location on the spreadsheet.
nextcol - Returns the column that contains the next occupied
cell.
nextrow - Returns the next row that contains data.
lastrow - Returns the last row at a level that contains data.
gocell - Moves the Cell Pointer to any location on the
worksheet.
cprow - Returns the row number of the Cell Pointer's current
position.
cpcol - Returns the column number of the Cell Pointer's current
position.
cplvl - Returns the level number of the Cell Pointer's current
position.
cprng - Allows the Cell Pointer to be moved from within a
script.
cwidth - Returns (or sets) the width of a specific column.
attribute - Returns an attribute setting of a cell, given the
attribute's id number. In all, over 20 attribute
settings can be viewed or modified.
seterrno - Clears or sets the 'errno' flag value.
errno - Returns the value of the 'errno' flag.
errlist - Returns a system error message.
_____________________________
________________________________________/ Appendix A SUPPORT
If you have a question regarding this manual or feel the program has a
bug, don't hesitate to contact me. Currently, the only support I'm
able to offer is by mail. I will, however, make every effort to
respond to your questions as soon as possible. When you write, please
include the following:
General information about your computer:
- Your current version of REBEL (version 2.1a, 3.0, etc.)
- The version of DOS you are using (3.1, 5.0, etc..)
- The type of computer you're using (8088, 80286, 80386, etc..)
- Available RAM (512K, 640K, etc..)
- Type of Graphic Card (CGA, EGA, VGA, Hercules, etc..)
- Any other information you feel may be pertinent
Specific information about your problem:
- A SIMPLE example that isolates the problem
- Any error messages displayed when entering the Edit Mode
- The exact steps required to reproduce the problem.
- I can't return it, but a floppy disk that contains the
problem would be very useful.
Send to:
Brad L. Smith
REBEL Software
P.O. Box 270277
Fort Collins, CO 80527
_____________________________
________________________________________/ Appendix B LICENSE
LICENSE AGREEMENT
You are free to use, copy, or distribute the DOS (real mode) version
REBEL 3.0 and higher, provided it meets the terms of this agreement.
This software is copyrighted and should be treated accordingly. It is
not in the public domain and the author retains full ownership. Your
right to use REBEL automatically terminate if you fail to comply with
any provision of this License Agreement or upon written notification
from the author.
Permission is granted to copy and distribute UNMODIFIED copies of this
software - provided that no payment, other than for the transfer
medium, is received. The minimum package of files distributed must
include the following files: REBEL.EXE, CHAP1-4.DOC, CHAP5-11.DOC,
STDLIB.REB, and STDLIB.DOC.
Permission is also granted to include this software with other
products or services provided REBEL's trademark is be clearly
identified and the minimum package of files (described above) is
included.
IMPORTANT! REBEL's Script Library Builder (BUILD.EXE) is not part of
this product! Under no circumstances may this program be copied or
distributed.
WARRANTY DISCLAIMER
THIS SOFTWARE AND MANUAL IS LICENSED "AS IS" AND WITHOUT ANY EXPRESSED
OR IMPLIED WARRANTIES WHATSOEVER. THE USER MUST ASSUME THE ENTIRE RISK:
1) OF USING THIS PRODUCT; 2) OF ANY DAMAGES RESULTING FROM ITS USE;
3) FOR ITS FITNESS FOR ANY PARTICULAR PURPOSE. UNDER NO CIRCUMSTANCE,
WILL LIABILITY EXCEED THE ORIGINAL REGISTRATION FEE, REGARDLESS OF THE
FORM OF CLAIM.
Some states do not allow exclusion of the limit of liability for
consequential or incidental damages, so the above limitation may not
apply to you.
This agreement shall be governed by the laws of the State of Colorado.
Any action or proceeding brought by either party against the other
arising out of or related to this agreement shall be brought only in a
STATE or FEDERAL COURT of competent jurisdiction located in the State
of Colorado. The parties hereby consent to in personam jurisdiction
of said courts.
ORDER FORM
Name (required): __________________________________________________
Company (optional): _______________________________________________
Address: _________________________________________________________
City: _____________________________ State: ____ Zip: __________
Phone: ___________________ (Day)
Where did you here about REBEL? ___________________________________
(AVALIABLE ONLY ON 3.5" DISKS)
REBEL (spreadsheet) ($0.00) ____________
Your current version: _______________________
SCRIPT LIBRARY BUILDER: ($29.00) ____________
X
_____________________________________________
(Print the name of INDIVIDUAL to be licensed)
Includes printed manuals for REBEL and the
Script Library Builder and source code for
the script libraries.
SCRIPT LIBRARY BUILDER (upgrade) ($14.00) ____________
Current License No. _________________________
Your current version: _______________________
SHIPPING AND HANDLING ($6.00 - $15.00 outside U.S.) 6.00
TOTAL (in US currency): ____________
REBEL SOFTWARE - P.O. Box 270277 - Fort Collins, CO - 80527