home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Between Heaven & Hell 2
/
BetweenHeavenHell.cdr
/
500
/
404
/
calc2.doc
< prev
next >
Wrap
Text File
|
1986-12-03
|
92KB
|
3,301 lines
-
FIX
____________________________________________________________________
FIX Function
Purpose: Converts a number or formula to an integer, and returns
the resulting value to the current cell or formula. Does
not return the next lower number when negative, as does
the INT function.
Format: FIX(-2.5) Returns -2
FIX(A1) If A1 contains -2.5, returns -2.
FIX(A1*2/A5) Resolves the formula, then returns the
fixed decimal portion of the result
Remarks: FIX(x) can be entered into a cell, or the FIX function
can be used in a formula.
All digits to the right of the decimal are removed, and
the ones to the left of the decimal become the returned
value. This differs from the INT function, which returns
the next lower negative number.
Example: Enter the following formulas into the specified cells.
The resulting values are shown:
Cell Formula Result
A1: FIX(23.999) 23
A2: FIX(-1.567) -1
A3: INT(23.999) 23
A4: INT(-1.567) -2
51
-
FORMAT
____________________________________________________________________
FORMAT Command (/F)
Purpose: Sets the formatting options for a cell, a range of cells
or the entire spreadsheet.
Prompts: Enter /F and you will be prompted:
Enter range to be formatted (or ALL).
Enter a single cell, a range of cells (which may be a
block), or ALL. To format only the current cell, press
ENTER or comma. The next prompt is:
Decimals Width Justify Commas $ ( %
Protect Unprotect Hide Bargraph Zero-blank
Enter D, W, J, C, $, (, %, P, U, H, B or Z to indicate
which type of formatting you want to do. The next prompt
depends on which option you select:
Decimals
Number of decimal places (0 to 12), Floating, Scientific.
The number of places to the right of the decimal can be
changed using this command. This number of decimals is
used on the screen display, on printed output, and in
computations.
"Floating" works like the F setting on a calculator. All
significant decimals to the right of the decimal are used
in computations. If the column is wide enough, all
significant decimals are displayed and printed. If the
column is not wide enough, CALC drops enough decimals
on the right so the number fits in the column. For
example, if the formula 2000/3 is entered in a column
that is ten characters wide, the number 666.666667 is
displayed. If the column is narrowed to five characters,
52
-
FORMAT
the number 666.7 is displayed. Reducing the column width
to four or three characters displays the number as 667
(with no decimal point). If the column width is narrowed
to two, the << symbols print, indicating the number will
not fit in the cell.
"Scientific" format displays the number in exponential
form, similar to scientific notation. The mantissa and
exponent are separated by the letter E. For example,
3E-5 is the same as .00003 and -3E+10 is the same as
-30,000,000,000.
Width
Enter column width (0 to 75).
The width of a column or range of columns can be changed
using this command. A column width of zero causes the
column to be "hidden", allowing data to be stored without
being seen.
Justify
Right-justify, Left-justify or Center.
Specify "L" to have numbers shifted to the left side of
the column. Specify "R" to have text shifted to the right
side of the column. "C" centers the cell's contents.
CALC defaults to right-justify for numbers and left-
justify for text unless overridden by this command.
Commas
Commas between thousands (Y or N).
To have 12345.00 print as 12,345.00 specify "Y" to this
prompt. To remove the commas from a number, specify
"N". For example, if the number is a year such as 1985
you would not want it to print as 1,985.
$
Print leading dollar sign ($) on numbers (Y or N).
If you specify Y, a dollar sign will be printed
immediately to the left of the most significant digit.
53
-
FORMAT
(
Enclose negative numbers in parentheses (Y or N).
If Y is specified, a negative number will have
parentheses around it, rather than a minus sign. To
switch back to minus sign, specify N. Note: When this
format option is specified, positive numbers have one
blank space to the right. This is necessary so a column
of positive and negative numbers line up properly.
%
Print a percent sign (%) to the right of numbers (Y or N).
If Y is specified, a percent sign is printed on the right
of the number. This option does not affect computation.
Protect
F = protect formulas, V = protect values, T = protect text,
N = protect non-blank cells, A = protect all cells in range
When a cell is protected, nothing can be entered into it.
This keeps formulas and headings from accidentally being
destroyed by overtyping. When a cell is protected, the
word PROTECTED appears on the message line when the
cursor moves to that cell. Also, if you have configured
CALC accordingly, protected fields can be displayed
in a different color than the display area (or in inverse
video on a monochrome monitor). Once a cell is protected,
it can be unprotected using the Unprotect format option,
discussed next.
From the message, you can see there are several options
for protecting ranges of cells. The Protect Option has no
global setting. All cells are unprotected at startup, and
if you protect ALL, every cell in the spreadsheet is
individually protected.
Unprotect
F=unprotect formulas, V=unprotect values, T=unprotect text,
N=unprotect non-blank cells, A=unprotect all cells in range
When a cell has been protected, nothing can be entered
into it. To remove the protection so the cell's contents
can be changed, use the Unprotect option.
54
-
FORMAT
From the message, you can see there are several options
for unprotecting ranges of cells. The Unprotect Option
has no global setting. All cells are unprotected at
startup.
Hide
P=hide cells on printer only, S=hide cells on screen only,
B=hide cells on printer and screen, N=Don't hide cells.
A hidden cell is one which has something in it, but its
contents are not displayed on reports and/or the screen.
Sometimes it is useful to hide some cells such as those
containing lookup tables. Sometimes there is confidential
data used in calculations which should not be included on
hard copy reports. Once a cell is hidden, you can still
see its contents by either moving the cursor to the cell
and looking at the message line, or by using the /GF
command to display formulas on the screen. /GF ignores
the hidden cell flag.
There are several options for hiding/unhiding ranges of
cells. The Hide Option has no global setting. All cells
are unhidden at startup.
Bargraph
Print numbers in bar graph format 1, 2 or 3 (N=no graph).
If a cell contains a value or formula, the result will be
displayed as a string of bar graph characters. By
entering a 1, 2 or 3, up to three different bar graph
patterns/colors can be displayed.
If the bar graph exceeds the width of the cell, the last
character displays a > character, indicating the bar
continues beyond the column. If the bar graph is in a
right-justified cell, the bar graph is right-justified,
and the > sign becomes a < sign on the left.
When a bar graph cell is printed, one of three characters
is printed. CALC's default characters for printed bar
graphs are ****, //// and ]]]] , which are not nearly as
attractive as the patterns on the screen. If your printer
55
-
FORMAT
has a graphics character set, it is possible to configure
CALC to print the same patterns as appear on the
screen, or others if you prefer. See the section titled
Customizing CALC for information on changing printed
bar graph characters, screen bar graph characters and
screen bar graph colors.
Zero-blank
Display zeros as blanks (spaces) (Y or N).
If this option has been set for a cell, and the cell
contains a value or formula which resolves to zero, the
cell will appear empty. At startup, the default is to
display zeros (0, .0, .00, etc.) in all cells. CALC
has always had a global option to not display zeros. The
/G,Z command still exists, and is effectively the same as
/F,ALL,Z.
Remarks: Each of the formatting options which have responses of
"Y" or "N" will also accept a response of ENTER. If you
press the ENTER key instead of Y or N, the formatting
options for the specified cell(s) clear the format
option, so the cell's format reverts to the default
(global) setting.
After responding to one of the formatting options,
CALC repeats the prior prompt, allowing you to specify
more than one formatting option for a cell or range.
A cell can be formatted before it has data put in it, and
the format options take effect as soon as data is put in
it. However, empty cells' formats are not saved when a
spreadsheet is /Saved.
When individual cells are formatted, two characters of
memory are used per cell. But when ALL is specified, a
"global default" code is changed in CALC. Specifying
ALL uses none of the spreadsheet memory. Specifying a
range of cells causes two bytes of memory to be used for
56
-
FORMAT
every cell in the range. So for example, this statement:
/F A1:Z99,D,3
uses 5K of memory, while this statement:
/F ALL,D,3
uses no memory at all. So on large spreadsheets it is a
good idea to determine the most common default values
first, and set them with the "ALL" global format option.
Then the cells that vary from the default can be changed
individually. This not only saves memory, it usually
saves typing as well.
The global format defaults are as follows:
Decimals: 2
Width: 11 (all columns)
Justify: Left for text, Right for numbers
Commas: Yes
Protected: No
Bargraph: No
Hidden: No
Zero-blank: No
$, (, %: No
These global defaults can be permanently changed by
specifying ALL, then using the /Configure,Save command
to permanently save them in CALC.PRO. See the section
titled Customizing CALC for more information.
Example: EXAMPLE #1: A series of year numbers have been entered
into column B, starting with 1983. The problem is that
the standard defaults cause the years to print as
1,983.00 instead of 1983. Use the /F command first to
turn off the decimals, then to turn off the commas:
First enter: /F B1:B50,D,0
then enter: C,N
57
-
FORMAT
The years will now print as four-digit numbers without
commas and decimals. To also left-justify the years, and
their heading which is a text field: "Years", within
column B, enter:
/F B1:B50,J,L
EXAMPLE #2: We want to prepare a spreadsheet to produce
the following report:
Period ------- Sales -------
1/85 $50,000 *****
2/85 $59,000 ******
3/85 $42,400 ****
4/85 $92,800 *********
5/85 $121,000 ************
Begin by entering the numbers and text. This is what they
look like before formatting:
A B C D E
1 Period ------- Sales -------
2 1.00 / 85.00 50,000.00 5.00
3 2.00 / 85.00 59,000.00 6.00
4 3.00 / 85.00 42,400.00 4.00
5 4.00 / 85.00 92,800.00 9.00
6 5.00 / 85.00 121,000.00 12.00
Note that we could have entered the month/year as text
fields. But by making them numeric we can use the
Replicate command to enter them, saving considerable
typing on large spreadsheets. (See /Replicate for an
example of how to use it on columns A and B above.) To
get the formatting right on the spreadsheet above, start
by specifying a global default of no decimals:
/F ALL D 0 (ENTER)
58
-
FORMAT
Next, change the column widths. Columns A and C need to
be two characters wide. Column B needs to be one
character wide. Column D, no change, and column E should
be wide enough to handle a lengthy graph, say forty
characters:
/F A1:C20 W 2 (ENTER) (ENTER)
/F B1:B20 W 1 (ENTER) (ENTER)
/F E1:E20 W 40 (ENTER)
Next specify Bargraph format for column E. Notice that
you didn't hit ENTER twice on the previous command, so
you need not retype /F E1:E20. Just type:
B 1 Y (ENTER) (ENTER)
And finally, put floating dollar signs on column D:
/F D1:D20 $ Y (ENTER) (ENTER)
On the screen, the bargraphs do not appear as asterisks;
they are shaded bars. When printed, they will appear as
asterisks. If your printer has graphic characters, it is
possible to print special characters like the ones on the
screen. See the section titled Customizing CALC
for more information.
One suggestion on the graphic display in column E: use
formulas based on column D. For example, in cell E2,
instead of entering "5", enter "D2/10000". In that way,
if column D's figures are changed, the graphs are
immediately updated. Also, it makes the graph column easy
to replicate.
59
-
GLOBAL
____________________________________________________________________
GLOBAL Command (/G)
Purpose: Sets various system options of CALC, including border
display, row/column calculation sequence, automatic
recalculation, and printing zeros as blanks.
Prompts: Enter /G and you will be prompted:
Border, Row, Column, Manual, Auto,
Formula, Zero-blank, Smart-cursor
To select a global option, enter the first letter of the
option. It is not necessary to press ENTER.
Remarks:
Border
The BORDER option turns the screen borders on and off.
If your screen has borders on the top and left edge, use
this option to turn them off. To turn them back on, use
this option a second time. Note: this option has no
effect on printed reports. There is a special option in
the /P command for printing borders.
Row and Column
The ROW and COLUMN options indicate whether calculation
should proceed row-by-row, or column-by-column. Each time
a calculation is done, you can watch the cursor "ripple"
down the screen, recalculating each of the formulas in
your spreadsheet. In many cases, a formula will refer to
a cell that contains another formula. When this happens,
the formulas must be evaluated in the proper sequence, or
you will not get the answer you were expecting. Most
spreadsheets are designed for row-by-row calculation, so
ROW is the default at startup time. If you design a
spreadsheet that requires calculation to be done down the
60
-
GLOBAL
columns instead of across the rows, specify the COLUMN
option.
If your spreadsheet has forward references (references to
formulas which have not been recalculated yet), and
neither ROW or COLUMN will work properly, you may
need to use iterative calculation. See the ITERATIONS
option in the section titled "Customizing CALC"
for more information.
Warning: Be careful to not create spreadsheets with
"circular references": two formulas refer to each other,
or "A" refers to "B" refers to "C" refers to "A". When
you suspect a circular reference, press ! to recalculate.
If one or more of the numbers on your spreadsheet keep
changing each time you recalculate, your spreadsheet
probably has a circular reference, and may need to be
restructured to eliminate the problem.
Manual and Auto
The MANUAL and AUTO options tell CALC whether
to automatically recalculate each time a number or
formula is entered, or whether to wait and not calculate
until the ! is pressed. At startup, the AUTO option is
set. As numbers and formulas are entered, the message
"CALCULATING" will appear, and the cursor will "ripple"
down the screen each time before the next field can be
entered. As the spreadsheet gets larger, this pause can
become annoying. To turn off automatic recalculation,
select the MANUAL option.
Formula
The FORMULA option tells CALC to display cells'
contents on the screen instead of the cells' values. For
example, if cell A5 contains A1+A1 and displays 12.34
on the screen, /GF causes A1+A1 to be displayed on the
screen, in the cell. Typing /GF a second time returns the
display to its normal mode.
61
-
GLOBAL
In Formula mode, text is displayed left-justified in its
cell, with a leading quotation mark. Values and formulas
are displayed exactly as they were entered. Any text,
formulas or values which do not fit in the column overlap
into adjacent empty columns on the right.
In Formula mode, Protected fields have a leading "P" on
the left in inverse video. Hidden fields' contents are
not hidden in Formula mode.
Zero-Blank
The ZERO-BLANK option tells CALC to display all
cells whose contents are zero as blank cells. At startup,
the default is to display zeros (0, .0, .00, etc.) in the
cell. Select this option to display them as blanks. To
restore the original default of displaying zeros as 0,
.0, etc. use /Global, Zero-blank a second time. See also
/Format, Zero-blank.
Cursor
The Smart-cursor option changes the way the ENTER key
works. If the Smart Cursor has been turned on,
CALC "remembers" the direction the arrow key moved
previously. When the ENTER key is pressed, the cell
cursor automatically moves in the remembered direction.
When the Smart Cursor is turned on, an arrow appears in
the lower left corner of the screen, indicating the
direction the cursor will move next. When ENTER is
pressed, the cell cursor moves in that direction.
Accountants and others who are accustomed to ten-key
entry of columns of numbers will find Smart Cursor
especially useful. To enter a column of numbers with
Smart Cursor, enter the first number and move the cursor
downward. Then type /GS to turn on the Smart Cursor.
A tiny down-arrow appears in the lower-left corner of the
screen. Put your ten-key pad in numeric lock, enter the
next number on it ten-key style, and then press the ENTER
key with your forefinger. The number is entered and the
62
-
GLOBAL
cursor automatically advances downward. For experienced
ten-key operators, this is a very fast way to enter
numbers.
Example: A spreadsheet is getting large, and every time a number
is typed in, a recalculation is done which takes several
seconds to complete. Turn off automatic recalculation
with this command:
/G M
Later when recalculation is desired, you can type ! to
force recalculation. If you get to the point of fine-
tuning the spreadsheet and would like to have
recalculation turned back on, enter:
/G A
63
-
IF
____________________________________________________________________
IF Function
Purpose: Evaluates an expression as true or false, and returns the
"then" argument if true, or the "else" argument if false.
Format: IF(A1=5,1,0) If A1 contains a 5, a 1 is returned.
Otherwise a 0 is returned.
IF(A1>B5,D3,99) If the number in A1 is greater than
the number in B5, the contents of D3
are returned. Otherwise the number
99 is returned.
Alternate form:
IF A1=5 THEN 1 ELSE 0 (Same as first example above)
IF A1 > B5 THEN D3 ELSE 99 (Same as second example)
Remarks: The three operands of the IF function can best be
described as follows:
IF(this expression is true,then return this
expression,otherwise return this expression)
If the first operand contains two expressions separated
by a relational operator, the expressions are resolved,
then compared. If the resulting comparison is true, the
second operand is used. If the comparison is false, the
64
-
IF
third operand is used. Valid relational operators are:
Operator Meaning Example
= Equal A1 = 0
<> or >< Not equal A1 <> B1
< Less than A1 < B1+3
> Greater than A1 > .0987
<= or =< Less than or equal to .987 <= A1
>= or => Greater than or B1*3 >= 5-A1
equal to
If the first operand contains no relational operator, it
is resolved and compared to zero. If it is not zero, the
second operand is used. If it is zero, the third operand
is used. For example, enter IF(5,3,2) in a cell, and the
number 3 will appear, since 5 is non-zero.
Multiple comparisons may be done in the first operand,
using Boolean (logical) operators. For example, if a
number being tested can be either 1 or 2, the IF function
could be written like this:
IF A1=1 OR A1=2 THEN ... ELSE ...
The second and third operands of the IF function may be
any valid value, cell reference or formula. They may not
be text. However, text may be displayed by putting it in
another cell and referring to that cell in an IF operand.
For example, if A5 contains the text "Out of stock", and
at A8 we entered IF(1=1,A5) then cell A8 would display
the text "Out of stock". Another example of this feature
is shown below, in the example section.
There is an alternate form of the IF function which some
people find easier to read. The parentheses are optional,
and the words THEN and ELSE can be used instead of
commas. THEN and ELSE must have at least one space to
65
-
IF
the left and right. Spaces can be used freely in any of
the operands, except that cell references cannot have
spaces in them; i.e. A11 can't be A 11. For example:
IF A1 = 5 THEN 1 ELSE 0
IF A1 > B5 THEN D3
The IF function may be used in formulas. For example,
this is a valid expression:
1000+A1*IF(B1,C1,0)
The IF function may also be nested. For example, this is
a valid expression:
IF(A1>0,B5,IF(A1=0,B6,B7))
The expression tests A1 to see if it is greater than
zero. If it is, the contents of B5 are returned. But if
the number in A1 is not greater than zero, another test
is done. Is it equal to zero? If so, return B6, otherwise
return B7.
An IF statement may be nested in the second and third
operands, but may not appear in the first operand of
another IF statement. The nesting of IF statements can be
as many levels as you want, but the real restriction is
the size of the formula that can be entered (currently 74
characters). By breaking them up into multiple cells that
refer to one another, very complex relational expressions
can be generated. Keep in mind, however, that evaluating
complex expressions can slow down recalculation
considerably.
Example: EXAMPLE #1: A salesman's commission is 12% of gross
sales, but the salesman is always guaranteed to make at
least a minimum of $1500. Gross sales are in cell B5.
Compute his commission in C5, using the IF function. Move
66
-
IF
the cursor to C5, and enter:
IF(B5*.12>1500,B5*.12,1500)
If gross sales times .12 are greater than $1500, pay the
salesman that amount. But if gross sales times .12 are
less than or equal to $1500, pay him a flat $1500.
If the spreadsheet listed several salesmen down the
columns, this formula could be copied to all of them,
using the /Replicate command.
EXAMPLE #2: A spreadsheet contains the payroll for an
employee. The pay periods are numbered 1 through 24, in
cells A3 through A26. The columns look like this:
A B C D
1 Pay Gross YTD
2 Period Wage Gross FICA
The employee's gross pay for each period is in column B.
Year-to-date gross pay is in column C, which is simply
the running total of column B. FICA for each period is in
column D.
The FICA computation can normally be done with a simple
multiplication: gross wage times .067. But if the
employee makes more than $37,800 his FICA deduction
should stop. Assume that all the other numbers and
formulas have been filled in for the 24 pay periods, and
use the IF function to compute FICA in D3 through D26. In
D3, put the following formula:
IF(C3<37800,B3,IF(C3-B3>37800,0,37800-C3+B3))*.067
The formula says: If YTD gross wage is less than 37800,
then use the current gross wage, times .067. But if last
period's YTD gross (C3-B3) was over 37800, then the
ceiling has been reached, so FICA is zero. Finally, if we
are in the period where only part of the wage is subject
67
-
IF
to FICA, compute that amount (37800-C3+B3) and multiply
it times .067.
Now use the /Replicate command to copy D3 to cells D4
through D26. When it asks Adjust - Y/N? (or A) , reply A.
EXAMPLE #3: A spreadsheet contains student names in A5
through A30, and test scores in B5 through B30. We want
to show each student's grade in column C, using the
following table:
Score of 90 to 100 - grade is A
Score of 80 to 89 - B
Score of 55 to 79 - C
Score of 40 to 54 - D
Score of 0 to 39 - F
Out to the right of the spreadsheet, in cells G1 to G5,
enter the text data:
G1: A
G2: B
G3: C
G4: D
G5: F
At cell C5, enter this expression:
IF(B5>89,G1,IF(B5>79,G2,IF(B5>54,G3,IF(B5>39,G4,G5))))
Then use the /Replicate command to copy the cell to C6
through C30. When /R asks Adjust - Y/N? (or A) reply
Y to the cells in column B, and N to the cells in column
G, which are constant data.
The table in column G can be hidden from view on a
printed report or on the screen by setting the column
width to zero, or by using the /Format, Hide command.
68
-
INSERT
____________________________________________________________________
INSERT Command (/I)
Purpose: Inserts a row or column in the spreadsheet.
Prompts: Enter /I and you will be asked:
Insert Row or Column?
to which you reply either R or C. The next prompt is:
Enter row number. New row will be inserted above it.
or
Enter Column letter. New column will be inserted to left.
The default, if ENTER is pressed, is to use the row
number or column letter of the current cell (the one
where the cell cursor is currently located).
Remarks: If a column is inserted, all the columns to the right of
it shift right. Likewise, if a row is inserted, all the
rows below it shift down to make room. Any formulas
referring to the shifted cells have their coordinates
adjusted so they point at the same data.
When a column is inserted, the column widths are shifted
to the right with their data. The new column retains the
same column width it originally had.
Another way to insert a row is to move the cell cursor
one row below where the new row is desired and press
CONTROL-N.
Example: A spreadsheet lists all customers and their aged accounts
receivable. A new customer needs to be added between rows
21 and 22. To insert the row, either move the cursor to
row 22 and enter:
/I R (ENTER)
69
-
INSERT
or if you are not at row 22, enter:
/I R 22 (ENTER)
Row 22 becomes blank, and the data that was on row 22
moves to 23, 23 moves to 24, etc.
70
-
INT
____________________________________________________________________
INT Function
Purpose: Converts a number or formula to an integer, and returns
the resulting value to the current cell or formula.
Returns the next lower number when negative. (See also
FIX function.)
Format: INT(5.1) Returns 5
INT(A1) If A1 contains -2.5, returns -3
INT(A1*2/A5) Resolves the formula, then returns the
fixed decimal portion of the result
Remarks: INT(x) can be entered into a cell, or the INT function
can be used in a formula.
All digits to the right of the decimal are removed. If
the number is negative, INT returns the next lower
negative number. This differs from the FIX function,
which returns the integer portion of a number, regardless
of sign.
Example: Enter the following formulas into the specified cells.
The resulting values are shown:
Cell Formula Result
A1: INT(23.999) 23
A2: INT(-1.567) -2
A3: FIX(23.999) 23
A4: FIX(-1.567) -1
71
-
KEYWORD
____________________________________________________________________
KEYWORD Function
Purpose: Displays date, time and page number information. The
format is user-defined.
Format: KEYWORD(keywords)
KEYWORD(DATE) Displays today's date in the cell
Example: October 9, 1985
KEYWORD(TIME) Displays the current time of day in
the cell: 8:30:10 PM
KEYWORD(DA-MO-YR HO:MI page #)
Displays the day number, month number
and year separated by dashes, then
the hour and minute separated by a
colon, then the word "page" followed
by the currently printing page
number. On the screen it looks like:
9-10-85 20:30 page 1
Remarks: KEYWORD can be entered into a cell with any length of
keyword argument enclosed in parentheses. KEYWORD
cannot be used in an IF statement or formula.
The text string enclosed in parentheses is scanned for
several valid keywords (listed below) and substitutions
are made. Characters which are not keywords are left in
the string. In the example above, the keywords used are
DA, MO, YR, HO, MI and #. The dashes, colon, spaces,
and the word "page" are not keywords, so they remain
where they are in the text string.
The keyword function is handled internally as a formula,
but its contents are displayed as text. Text is left-
72
-
KEYWORD
justified unless overridden by /Format. Text will overlap
into an adjacent empty cell.
The date and time displayed are MSDOS system date and
time. If your computer does not have a battery backup
clock/calendar, you will need to enter the system date
and time using the DOS commands DATE and TIME each
time you turn on your computer.
Keyword Sample Result Explanation
DATE or May 9, 1985 Month name, day number and year number.
TODAY Same as KEYWORD(Month DA, YEAR)
TIME 8:30:10 PM Current hour, minute and second. The time
is updated each time a recalculation is
done, or when the cursor moves to the cell.
Month June Current month name
MONTH JUNE Current month name, all capital letters
Mon Jun First three letters of month name
MON JUN First three letters of month name, all caps
MO 9 Month number. If less than 10, only one digit
MZ 09 Month number. If less than 10, leading zero.
DA 3 Day number. If less than 10, only one digit
DZ 03 Day number. If less than 10, leading zero.
YEAR 1985 Four digit year number.
YE 85 Two digit year number. YR and yr also valid.
HO 8 Current hour. No leading zero. Displayed in
military time if no AM/PM keyword is in
73
-
KEYWORD
the string (see below). If AM/PM appears,
display will be twelve-hour clock, and the
AM/PM string will be changed accordingly.
HZ 08 Current hour. Same as HO, but if less than
ten, leading zero is printed.
MI 30 Current minute. Two digits.
SE 10 Current second. Two digits.
AM, PM AM Variants: AM PM am pm A.M. P.M. a.m. p.m.
If one of these strings appear, time is
displayed in twelve-hour format.
# 1 When printing, displays current page number.
74
-
LOAD
____________________________________________________________________
LOAD Command (/L)
Purpose: Loads a spreadsheet file into memory so it can be altered
or printed. /Load can also be used to read DIF files,
ASCII Mail-merge files, File Express databases, or PC-File
databases. There is also a Consolidate option, which
allows spreadsheets with the same format to be
consolidated.
Prompts: Enter /L. The message Loading program. stand by . . .
appears for a moment, as the Load/Save program is brought
into computer memory. Then the prompt appears:
Enter the file name. (Enter A:, B:, etc. for directory.)
At this point, you have two choices: display a disk
directory or load a file. If you press ENTER, the
directory of the currently logged disk drive is
displayed. By entering A:, B:, etc. you can display disk
directories on other drives.
If instead you enter a file name, CALC will load that
file. The file must be one of the following:
. A CALC spreadsheet file created with the /Save
command
. A DIF format file (data interchange format) with an
extension of .DIF
. A sequential ASCII file with fields comma-delimited,
text in quotation marks, records ending with carriage
return/line feed, and a file extension of .WS
. A File Express or PC-File database. Specify the database
name with an extension of .HDR (the header file for the
75
-
LOAD
database)
. A CALC spreadsheet file created with PC-File's
Export command or a BASIC program
The file name can be any valid MS-DOS file name; i.e.
eight characters or less, optional extension, optional
drive designation. DIF files must have an extension of
.DIF; comma-delimited ASCII files must have an extension
of .WS; databases must have an extension of .HDR.
Some examples of valid file names are:
LOAN1 CALC file
B:WORKSHT5.OLD CALC file on drive B:
TEST.DIF DIF format file
ADDRESS.WS ASCII comma-delimited file
CUSTOMER.HDR File Express database
Spreadsheet files
If the file being loaded is a CALC spreadsheet file,
the next prompt is:
All, Part or Consolidate?
If you press "A" (All) the entire spreadsheet is loaded.
If you press "P" (Part) a prompt is given to enter the
range of cells; only those cells are loaded into the
spreadsheet area. On either of these options, if the
spreadsheet area has data in it, a warning message is
given and the area can be cleared by pressing Z, or left
intact by pressing ENTER.
If you select "C" (Consolidate) only the cells with
values are input from the spreadsheet files. But instead
of replacing the value in the cell, it is added to the
current value of the cell. Formulas and text are not read
when Consolidate is selected. So if you have three
76
-
LOAD
spreadsheets to combine, load the first one normally,
specifying All, then load the second and third specifying
Consolidate. The formulas and text from the first one
will remain untouched. When you are done, press ! and the
formulas will recalculate using consolidated values.
DIF, WS, HDR Files
When loading a DIF, WS or HDR file, different prompts
are given. First, you are asked:
Enter the starting cell or cell range.
The default is cell A1. To start the load at C11, enter
C11. To confine the data loaded to three columns wide and
fifty records long, starting at C11, enter C11:E60.
The second prompt asks:
Enter R to load by rows, C to load by columns.
The default is R, since this will put one record on each
line, which is the most common method. If you specify C,
the file will be read into the spreadsheet area one
record per column, effectively rotating it a quarter
turn.
DIF, HDR and WS files all have means of differentiating
text from numbers. None of these file types accomodate
formulas. The /Save command can save files in DIF and
WS format, but they are not usually good formats for
saving spreadsheets because formulas and formatting info
are not saved. Use the CALC format for saving
CALC spreadsheets.
Remarks: When the file is opened, if it does not exist, or if it
is not a file that can be read by CALC, an error
message is given, and you are prompted to try again.
As the file is loading, the cursor coordinate in the
77
-
LOAD
lower left corner of the screen displays the progress of
the load. When the load is completed, the spreadsheet
is displayed.
CALC does not clear the spreadsheet area before
loading a file. This allows two or more spreadsheets to
be loaded and combined. However, if the spreadsheet area
contains data, a warning message is given. You may press
ENTER to keep the data in the spreadsheet area, or press
Z to clear the area before loading.
When loading a CALC spreadsheet, more than just the
data in the cells is loaded. All global settings, printer
settings, column widths and the current cell position are
loaded as well. So if you save a spreadsheet at the end
of the day, then reload it the next morning, everything
will be back the way it was.
To exit from /L without loading a file, press ESCAPE.
If your computer has only one diskette drive, do not
attempt to /Load or /Save to drive B:. CALC requires
that the program diskette remain in the drive at all
times. The message file and the file overlay program are
both on drive A:, and are needed continually during the
loading process.
78
-
LOG
____________________________________________________________________
LOG Function
Purpose: Computes the natural logarithm of a number, and returns
the resulting value to the current cell or formula.
Format: LOG(2.718282) Returns 1
LOG(A1) If A1 contains e, returns 1
LOG(A1*2/A5) Resolves the formula, then returns
the natural log of the result
Remarks: LOG(x) can be entered into a cell, or the LOG function
can be used in a formula.
The natural logarithm is the logarithm to the base e.
Example: Enter the following formulas into the specified cells.
The resulting values are shown:
Cell Formula Result
A1: LOG(2.718282) 1
A2: LOG(2.718282*2.718282) 2
A3: LOG(45/7) 1.86
79
-
LOOKUP
____________________________________________________________________
LOOKUP Function
Purpose: Searches a table of ascending numbers, finds the closest
matching entry, and returns a value from an adjacent
cell.
Format: LOOKUP(A1,J1:J20) Searches J1:J20 comparing them
to A1. First cell greater than A1
returns previous value in column K.
LOOKUP(A1,J1:J20,3) Same as above, but returns the
value in column M.
Remarks: This function is useful for tax table lookups, price
table lookups, etc. where the table is small enough to be
entered as part of the spreadsheet.
The first argument is the "search key". For a tax table
lookup, it's taxable income. In a price table lookup,
it's the part number to be found. The search key can be a
number, or a formula, or a cell reference.
The second argument is the "search table". It is a range
of cells (two cell references separated by a colon),
running down a column or across a row. These cells
contain the table lookup values. In a tax table lookup,
these cells contain the income thresholds for each tax
bracket. In a price table lookup, these cells might
contain part numbers.
The LOOKUP function looks through the "search table"
until it finds a number that is greater than the "search
key". When it finds the number, it returns the value in
the previous cell to the right (if the search table is a
partial column) or the previous cell below (if the search
80
-
LOOKUP
table is a partial row).
The values in the search table must be in ascending
order, and no two cells in the search table can contain
the same value.
There is an optional third argument on the LOOKUP
function, called "offset". Offset defaults to 1 if it is
not present. Entering 2 in the third argument tells
LOOKUP to go two columns to the right of (or two rows
below) the search table to get its result. If offset is
zero, the search table argument itself is returned. If
offset is negative, LOOKUP moves to the other side of
the search table for its result. Offset can be a number,
or a formula, or a cell that contains a number.
Example: EXAMPLE #1: A tax table set for LOOKUP might look
like this:
A B C
1 Over Tax + %
2
3 0 0 .00
4 2,300 0 .11
5 3,400 121 .12
6 4,400 241 .14
7 6,500 535 .15
8 8,500 835 .16
9 10,800 1,203 .18
10 12,900 1,581 .20
11 15,000 2,001 .23
The tax thresholds are listed in column A, the base tax
in B and the percent to be applied to excess in C.
Further down the spreadsheet we enter:
81
-
LOOKUP
A B C
15 Taxable Income: $9,000.00
16
17 Tax bracket: LOOKUP(C15,A3:A11,0)
18 Base tax amount: LOOKUP(C15,A3:A11,1)
19 % to apply to excess: LOOKUP(C15,A3:A11,2)
20 Excess tax amount: (C15-C17)*C19
21
22 Total tax: C18+C20
Which yields the following results:
A B C
15 Taxable Income: $9,000.00
16
17 Tax threshold: $8,500.00
18 Base tax amount: $835.00
19 % to apply to excess: .16
20 Excess tax amount: $80.00
21
22 Total tax: $915.00
The example was broken up into pieces for demonstration
purposes. More likely, the spreadsheet would simply
contain a cell with "Total tax", using this formula:
LOOKUP(C15,A3:A11)+(C15-LOOKUP(C15,A3:A11,0))*LOOKUP(C15,A3:A11,2)
EXAMPLE #2: A lookup table is set up which contains a
list of item numbers in column A, their description in
item B, and their list price in column C:
A B C
50 Part Description List Price
51 1001 Printer Stand 95.00
52 1002 Paper Tray 40.00
53 1003 Tractor Feed Option 125.00
54 1004 Ribbon Cartridge 9.95
55 (etc.)
82
-
LOOKUP
At the top of the spreadsheet is an invoice, as follows:
A B C D E
1 INVOICE
2
3 Part Description List Price Qty Ext.
4
5 LOOKUP(A5,A51:A99) LOOKUP(A5,A51:A99,2) C5*D5
6 LOOKUP(A6,A51:A99) LOOKUP(A6,A51:A99,2) C6*D6
7 LOOKUP(A7,A51:A99) LOOKUP(A7,A51:A99,2) C7*D7
8 LOOKUP(A8,A51:A99) LOOKUP(A8,A51:A99,2) C8*D8
... (etc.)
20 INVOICE TOTAL SUM(E5:E19)
21 SALES TAX E20*.078
22 AMOUNT DUE E20+E21
To produce an invoice, load the invoice spreadsheet from
disk, enter the part numbers and quantities, then press !
to calculate. The descriptions and prices are looked up,
the invoice is extended, sales tax is computed, and the
invoice is totalled. When /Printing the invoice, specify
a cell range of A1:E22, so the price table doesn't print.
There is an alternate method for preparing invoices with
CALC described in the examples of the NOPRINT
function.
83
-
LPI
____________________________________________________________________
LPI Function
Purpose: Changes the line spacing on the printer to 6 or 8 lines
per inch.
Format: LPI(6) Changes to 6 lines per inch
LPI(8) Changes to 8 lines per inch
Remarks: When LPI(n) is entered into a cell, it displays as
[LPI(n)] on the screen. When the cell is printed, control
characters are sent to the printer which change the line
spacing, then the cell is printed as if it were empty.
LPI can be an operand in an IF, but cannot be used in a
formula.
If your printer is not an IBM, Epson, or Epson
compatible, then CALC must be configured for your
printer before using the LPI function. See the section
titled "Customizing CALC" for more information.
CALC defaults to six lines per inch, and 58 printed
lines per page. When using LPI(8), you will probably want
to increase the number of lines per page (one of the
/Print prompts) to 75 or 80.
Example: A report lists all 70 of a company's locations, and they
won't all fit on one page with headings and totals. We
want the report on a single sheet of paper, and it
doesn't matter if the lines are "crunched" tightly
together.
At the top of the report, in any cell, type LPI(8) and
press ENTER. At the bottom of the report, in any cell,
type LPI(6) to reset your printer for future printing at
six lines per inch. Now /Print the report, and specify a
"page length" of 80. The report is printed on one page.
84
-
MAX
____________________________________________________________________
MAX Function
Purpose: Finds the largest number in a range of numbers and
returns it to the current cell or formula.
Format: MAX(A1:A20) Searches a column of numbers
MAX(A1:E1) Searches a row of numbers
MAX(A1:D20) Searches a block of numbers
Remarks: MAX(m:n) can be entered into a cell, causing the largest
number in the range to appear as the cell value. Or the
MAX function can be used in a formula.
The coordinate range specified in a MAX function may be
down a column, such as MAX(A1:A20), it may be across a
row, such as MAX(A1:E1), or it may be a block of cells
(designated by the upper-left and lower-right
coordinates), such as MAX(A1:D20).
Negative numbers are considered smaller than zero; i.e.
the MAX of 1, 0 and -5, is 1.
If the specified range contains any empty cells, they are
ignored. If the range contains any cells with text,
ERROR is returned.
Example: Column B has a column of monthly sales from B7 to B26.
The highest monthly sales figure is to be shown in B28.
Move the cell cursor to B28, and enter:
MAX(B7:B26)
After recalculation, B28 contains the highest number in
the column.
85
-
MIN
____________________________________________________________________
MIN Function
Purpose: Finds the smallest number in a range of numbers and
returns it to the current cell or formula.
Format: MIN(A1:A20) Searches a column of numbers
MIN(A1:E1) Searches a row of numbers
MIN(A1:D20) Searches a block of numbers
Remarks: MIN(m:n) can be entered into a cell, causing the smallest
number in the range to appear as the cell value. Or the
MIN function can be used in a formula.
The coordinate range specified in a MIN function may be
down a column, such as MIN(A1:A20), it may be across a
row, such as MIN(A1:E1), or it may be a block of cells
(designated by the upper-left and lower-right
coordinates), such as MIN(A1:D20).
Negative numbers are considered smaller than zero; i.e.
the MIN of 1, 0 and -1, is -1.
If the specified range contains any empty cells, they are
ignored. If the range contains any cells with text,
ERROR is returned.
Example: Column B has a column of monthly sales from B7 to B26.
The lowest monthly sales figure is to be shown in B27.
Move the cell cursor to B27, and enter:
MIN(B7:B26)
After recalculation, B27 contains the lowest number in
the column.
86
-
NOLF
____________________________________________________________________
NOLF Function
Purpose: Causes the current line to be printed with only a
carriage return, and no line feed. On many printers, this
allows two or more lines to be printed "on top of" one
another. This is useful for boldfacing, underlining and
overstriking.
Format: NOLF Suppresses line feed when line is printed
Remarks: NOLF can be entered into a cell, or can be the second or
third operand in an IF function. It cannot be used in a
formula. NOLF displays as [NOLF] on the screen.
Regardless of which column NOLF is in, it takes effect at
the time its line is printed. The cell with NOLF is
treated as if it were empty when the print line is built,
then when the line is printed, a carriage return is
sent to the printer without a line feed character.
On most printers, NOLF keeps the paper from advancing so
the next line overprints the NOLF line. However, some
brands of printers advance the paper anyway when a
carriage return is received, so NOLF has no effect on
those printers.
Example: EXAMPLE #1: At the end of a report we want to underline
the columns of numbers. In the past, we have always moved
down one line and typed underscores, leaving a blank line
above the underscores:
992.00 4.01 7,122.50
__________ __________ __________
TOTAL $4,305.00 $132.22 $92,122.50
87
-
NOLF
This is acceptable, but we would prefer the underscores
to be immediately under the last line of numbers. So on
the line immediately above the underscore line, we enter
NOLF into any cell on that line. Now the line prints:
992.00 4.01 7,122.50 __________ __________ __________
TOTAL $4,305.00 $132.22 $92,122.50
EXAMPLE #2: On the same report described in example 1,
we want the grand total line to print in "boldface"; i.e.
darker than the rest of the report. Some printers may
have a boldface font option, in which case the
appropriate control codes could be typed into the
"TOTAL text field. Most printers can print boldface by
"overstriking" -- printing the same text two or three
times, to make the characters darker.
To boldface the total line, start by reproducing the line
a second time, exactly as it appears. Use the /Replicate
command to copy it to the line below it (with no
adjustment).
In any empty cell on the first total line, enter NOLF.
When the first total line is printed, the printer returns
the carriage but does not advance the paper. Then it
prints the second total line over the first, making it
darker. If a double-strike does not make the line dark
enough, it may be necessary to print it three times, with
NOLF in the first two lines.
88
-
NOPRINT
____________________________________________________________________
NOPRINT Function
Purpose: Causes the line where it appears to not be printed. Using
NOPRINT and IF together allows selective printing.
Format: NOPRINT Suppresses printing
Remarks: NOPRINT can be entered into a cell, or can be the second
or third operand in an IF function. It cannot be used in
a formula. NOPRINT displays as [NOPRINT] on the screen.
Any line with [NOPRINT] in one of its cells is not
printed.
Selective printing based on data values can be done by
specifying NOPRINT in an IF function. For example:
IF A5=0 THEN NOPRINT does not print if A5 is zero, and
prints if A5 is non-zero.
Example: A company has invoices which can include any of 80 line
items. However, a typical invoice only has a few items on
it. Every time we print an invoice, we don't want all 80
items printing; they take up two pages, and the extra
lines with zero quantities are superfluous.
Start by setting up a "skeleton" or "master" spreadsheet
that has all 80 items, one per line, with descriptions
and prices, but with no quantities. The first few lines
of the master invoice might look like this:
89
-
NOPRINT
A B C D E
1 INVOICE
2 Date:
3 Sold to:
4
5
6 Qty Part# Description Price Extended
7 1001 Printer Stand 95.00 A7*D7
8 1002 Paper Tray 40.00 A8*D8
9 1003 Tractor Feed Option 125.00 A9*D9
10 1004 Ribbon Cartridge 9.95 A10*D10
(etc.)
The invoice spreadsheet contains a line for each of
eighty products, with their descriptions, prices, part
number, and any other information we want. The quantities
are empty, so they can be filled in later. Column E has
the extended amount; it is a formula with the quantity
times the price.
In a real invoice application, there is usually quite a
bit more other info at the top, such as sold to, ship to,
PO number, date, invoice number, etc. At the bottom there
are invoice totals, sales tax, shipping charges, invoice
discounts, etc. most of which can be computed by CALC.
Now, back to the NOPRINT function. How do we keep lines
from printing that have no quantities? It's easy. Go to
cell F7 and enter:
IF(A7=0,NOPRINT)
Or you may prefer the alternate form of the IF function:
IF A7 = 0 THEN NOPRINT
Now use the /Replicate command to copy the formulas in
E7 and F7 all the way down to the last product in line
86. Note that [NOPRINT] appears in all the cells in
90
-
NOPRINT
column F, because all the quantities are zero. As soon as
a quantity is entered into column A (and recalculation is
done), the [NOPRINT] on that line becomes .00 and the
line now prints. Since we don't want .00's printing on
our invoice, use the /Format, Zero-blank command to hide
them. Or change the width of column F to zero, which also
hides them.
Before entering any quantities, set recalculation to
manual with /GM and save the spreadsheet, giving it a
name like INVOICE.MAS. It is now the master invoice
spreadsheet. Later, to prepare an invoice, load the
master spreadsheet, type in the quantities, and /Print.
If you want to save the new invoice, give it a unique
name (such as the invoice number, or customer name)
As an added sophistication, you may want to protect all
the fields on the spreadsheet except the quantity
column and shipping info, so only they can be entered. It
keeps other fields from being accidentally altered, and
it also allows you to clear all the unprotected fields
with the /Blank,ALL command, which does not clear
protected fields.
91
-
NPV
____________________________________________________________________
NPV Function
Purpose: Computes the net present value of a stream of flows, at a
specified discount rate, and returns the result to the
current cell or formula.
Format: NPV(.12,A1:A20) Rate specified as value
NPV(A1,B1:E1) Rate in another cell
NPV(A1/100,B1:E1) Rate specified as formula
Remarks: The first operand of the NPV function is the discount
rate. It may be a value, a cell reference, or a formula.
The interval between the stream of flows must be
constant, and determined by the rate; i.e. if the flows
are annual, use an annual discount rate. If the flows are
monthly, use a monthly discount rate.
The second operand of the NPV function is a range of
contiguous cells, which contains the stream of flows.
Example: A client has an annual income of $40,000 and he expects
it to increase 5% annually. Make a spreadsheet showing
his annual income for the next ten years, total income
for ten years, and net present value of the total income,
assuming an annual discount rate of 8%:
A1: 40000
A2: A1*1.05
A3-A10: /R A2 A3:A10 (adjust - Y)
A12: SUM(A1:A10)
A13: NPV(.08,A1:A10)
Cell A13 will contain the net present value of the ten
years' income. For larger spreadsheets using NPV several
times, it is a good idea to put the discount in another
92
-
NPV
cell, so it can be easily changed without having to enter
the formulas again. In the example above, make these two
changes:
B1: .08
A13: NPV(B1,A1:A10)
93
-
PAGE
____________________________________________________________________
PAGE Function
Purpose: Advances a printed spreadsheet to a new page. Optional
operand allows conditional page break.
Format: PAGE Advances to next page after
printing current line
PAGE(3) Advances to next page if there
are fewer than three lines left
on the current page
Remarks: When PAGE is entered into a cell, it displays as [PAGE]
on the screen. When PAGE(n) is entered, [PAGE(n)] is
displayed on the screen. The value n must be a positive
integer between 0 and 99. It cannot be a cell reference
or formula.
When a spreadsheet is printed, a line count is kept
internally, to count the number of lines printed on a
page. When this line counter reaches its maximum (the
"lines per page" value specified in /Print), a "page
break" occurs. At page break time, the following things
happen:
. If footings are specified, paper is advanced to the
footing area, and footings are printed
. A form feed is sent to the printer, to advance to top
of page
. If Pause option is specified, a message is given to
press ENTER to print the next page
. Line counter is reset to zero
94
-
PAGE
. If Border option is specified, top borders are printed
. If headings are specified, they are printed, and the
line counter is incremented accordingly
When PAGE is encountered during printing, the line it
appears in is printed first, then the steps listed above
are followed.
When PAGE(n) is encountered during printing, the line it
appears in is printed first, then the current line count
is compared to n. If there is not enough room on the page
for n more lines, the page break steps listed above are
followed. If there is enough room, the PAGE(n) function
is ignored.
Note that PAGE(n) refers to the next n lines. The current
line PAGE or PAGE(n) appears in is printed on the
current page.
PAGE and PAGE(n) can be the second or third operands in
an IF function, allowing page breaks to be even further
controlled by data in the spreadsheet. The PAGE function
cannot be used in a formula.
95
-
PAGE
Example: A spreadsheet lists an employee, then has five lines of
information about that employee. If the bottom of the
page is approaching, we don't want to print only one or
two lines of data on the current page, then print the
last four or five lines on the next page. By adding a
PAGE(5) function ahead of each employee, all five of
their lines will always appear on the same page. The
screen might look like this:
A B C
1 [PAGE(5)]
2 Employee A - line 1
3 line 2
4 line 3
5 line 4
6 line 5
7 [PAGE(5)]
8 Employee B - line 1
9 - line 2
(etc.)
96
-
PAYMENT
____________________________________________________________________
PAYMENT Function
Purpose: Computes the payment amount per period for a given
principal amount, rate and number of periods. (See also
PRINCIPAL, RATE and PERIODS functions.)
Format: PAYMENT(principal,rate,periods)
PAYMENT(1000,.01,12) Returns 88.85
PAYMENT(A1,.18/12,36) If A1 is 1000, returns 36.16
PAYMENT(B1,100,A1*2/A5) Resolves the formula, then
computes the payment
Remarks: PAYMENT(x,y,z) can be entered into a cell, or the
PAYMENT function can be used in a formula.
All three operands must be specified. The rate operand
is the rate per period. The period length needs to be
consistent; i.e. in the first example above, the term is
twelve months, so the rate is a monthly rate, and
the payment amount returned is the payment per month.
One note on amortizations: as the truth-in-lending laws
so vividly indicate, there are many ways to amortize. If
CALC comes up with a different answer than your bank,
it may be because of their compounding method, or because
of rounding. Generally, CALC's calculation method is
mathematically sound, and yields the same result as an
interest amortization table.
Example: A local used car dealer offers a time-payment plan. A
$5,000 car requires no down payment, and requires 36
monthly payments at 18% annual interest. Compute the
97
-
PAYMENT
monthly payment. In any cell, enter:
PAYMENT(5000,.18/12,36)
The cell displays 180.77, which is the monthly payment.
The interest rate of .18 was an annual rate, so we used
.18/12 for the monthly rate.
98
-
PERIODS
____________________________________________________________________
PERIODS Function
Purpose: Computes the term of a loan (number of periods) for a
given principal amount, payment and interest rate. (See
also PRINCIPAL, PAYMENT and RATE functions.)
Format: PERIODS(principal,payment,rate)
PERIODS(1000,88.85,.01) Returns 12
PERIODS(A1,36.16,.18/12) If A1 is 1000, returns 36
PERIODS(B1,100,A1*2/A5) Resolves the formula, then
computes the periods
Remarks: PERIODS(x,y,z) can be entered into a cell, or the
PERIODS function can be used in a formula. TERM is an
alternate name for the PERIODS function.
All three operands must be specified. The rate operand
is the rate per period. The payment and rate need to be
consistent; i.e. in the first example above, the rate is
a monthly rate, and the payment amount is monthly, so the
number returned is the number of months.
One note on amortizations: as the truth-in-lending laws
so vividly indicate, there are many ways to amortize. If
CALC comes up with a different answer than your bank,
it may be because of their compounding method, or because
of rounding. Generally, CALC's calculation method is
mathematically sound, and yields the same result as an
interest amortization table.
99
-
PERIODS
Example: A local used car dealer offers a time-payment plan. A
$5,000 car requires no down payment, and has monthly
payments of 180.77 at 18% annual interest. Compute the
number of months of payments. In any cell, enter:
PERIODS(5000,180.77,.18/12)
The cell displays 36, which is the number of months. The
interest rate of .18 was an annual rate, so we used
.18/12 for the monthly rate.
100
