Boston 2

home *** CD-ROM | disk | FTP | other *** search

/ Boston 2 / boston-2.iso / DOS / ANWENDUN / EXP_CALC / CALC3.DOC < prev next >

Wrap

Text File | 1993-12-01 | 75KB | 3,235 lines

- 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 cell, so it can be easily changed without having to enter 94 - NPV the formulas again. In the example above, make these two changes: B1: .08 A13: NPV(B1,A1:A10) 95 - 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 * If Border option is specified, top borders are printed 96 - PAGE * 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. 97 - 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.) 98 - 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 monthly payment. In any cell, enter: 99 - PAYMENT 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. 100 - 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. 101 - 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. 102 - PRINCIPAL PRINCIPAL Function Purpose: Computes the principal amount for a given payment amount, interest rate and number of periods. (See also PAYMENT, RATE and PERIODS functions.) Format: PRINCIPAL(payment,rate,period) PRINCIPAL(88.85,.01,12) Returns 1000 PRINCIPAL(A1,.18/12,36) If A1 is 36.16, returns 1000 PRINCIPAL(B1,100,A1*2/A5) Resolves the formula, then computes the principal Remarks: PRINCIPAL(x,y,z) can be entered into a cell, or the PRINCIPAL function can be used in a formula. This function can also be spelled PRINCIPLE. All three operands must be specified. The payment, rate and periods need to be consistent; i.e. in the first example above, the rate is a monthly rate, the payment amount is monthly, and the periods are 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. Example: A local used car dealer offers a time-payment plan. A car requires no down payment, and has monthly payments of 180.77 for 36 months at 18% annual interest. Compute the purchase price of the car. In any cell, enter: 103 - PRINCIPAL PRINCIPAL(180.77,.18/12,36) The cell displays 5000 which is the principal amount, or purchase price of the car. Note that the interest rate of .18 was annual, so we used .18/12 for the monthly rate. 104 - PRINT PRINT Command (/P) Purpose: Prints a hard copy of the spreadsheet on the printer. Prompts: Enter /P and you will be prompted: Enter range to be printed (or ENTER for all). The word ALL appears on the entry line. To print the complete spreadsheet, press ENTER. To print only part of the spreadsheet, type the coordinates as a range (for example: A1:G15), then press ENTER or comma. The next prompt is: Enter page width (number of columns across printer). The number 80 will appear on the entry line. If the printer is set up to accomodate an eighty-character printout, just press ENTER. Otherwise, enter the width of the printer page. The next prompt is: Enter page length (number of lines per page). The number 58 will appear on the entry line. If you are printing on normal eleven-inch paper, at six lines per inch, press ENTER. Otherwise, figure out how many lines will print on a page and enter it. The next prompt is: D=dbl space, S=setup, P=pause, C=contents, M=mult copies B=border, O=offset, T=top margin, H=headings, F=footings 105 - PRINT If no special options are desired, press ENTER. Otherwise enter one or more option characters: D - Double space the printout. S - Prompt for printer setup codes. (Details below.) P - Pause after printing each page. C - Print contents and attributes of cells. M - Print multiple copies of the report (default 1). B - Display row and column borders on the printed copy. O - Override default left margin width (default 7). T - Override default top margin height (default 2). H - Prompt for heading line range. F - Prompt for footing line range. The last prompt from the Print command is: Printer or Disk. Specify P to send the output directly to the printer. If D is specified, you are prompted: Enter the file name. Enter a valid MSDOS file name, and the printed output will be written to a file by that name. You can print it later with the MSDOS "COPY filename PRN:" command, or access it with your word processor for further editing. Remarks: CALC prints an output page only as wide as the specified "page width". When the spreadsheet has been completely printed for that width, CALC checks to see if some of your spreadsheet was not printed. If so, CALC makes a second pass, printing the right-hand side of the spreadsheet. If it is extremely wide, then CALC continues making passes through the spreadsheet until it is all printed. These pages can then be attached to produce a wide spreadsheet on a narrow printer. 106 - PRINT When CALC asks for the "page width" (number of columns across the printer), it is asking for a width that: 1. your printer is physically capable of printing; 2. won't run off the right edge of the paper; and 3. takes character size into account. Since CALC has no way of determining any of these three factors, it needs some help. If your printer is capable of printing only 80 characters in 10 cpi mode, and you specify a page width of 96, CALC sends 96 characters at a time to the printer. The effect is a "double-spaced" report, with the data from the right side of the report "wrapped around" onto a second line. If a page width of 80 had been specified, CALC would print the left 80 characters of the report, then skip to a new page and print the rest of the characters. The two pages can then be taped together as a wide report. PRINTER SETUP There are hundreds of brands of printers, and most of them have their own unique code structure for setting options such as characters per inch, lines per inch, double-wide, and so forth. The standard version of CALC is configured for the IBM/Epson printer, because more than 65% of the CALC users have an Epson or compatible. The /Configure option supports three other brands of printers, which account for another 20% of CALC users. Generic support for other printers has been provided in the .PRO configuration options of CALC. The section titled Customizing CALC gives more information. In addition, there is the "Setup" option of the /Print command. If "S" was specified above, the following prompt is given next: Enter printer setup codes, then ENTER. For some printers, the setup codes have been built into CALC's /Configure routines. The standard diskette 107 - PRINT comes configured for Epson (IBM) printers. The setup prompt will have a second line saying: Epson printers: "A"=10 cpi, "B"=12 cpi, "C"=17 cpi. If your CALC has been configured for another printer, that printer's name will appear instead of Epson. If the named printer matches your printer, you can alternately enter the letters A, B or C to set your printer spacing to 10, 12 or 17 characters per inch, respectively. If CALC has not been configured for your printer, you can still send setup codes to the printer. Just Press any key (other than A, B or C) at this time, and its ASCII value will be sent directly to the printer. For example, if the sequence "ESC,M" sets your printer to twelve characters per inch, press the Escape key, then the capital M. When all the setup codes have been entered, press ENTER. Some printer setup codes are special ASCII characters. If you need to send an ASCII 15 to your printer, for example, there are two ways to do this: 1. Hold down CTRL and press O (the letter). CTRL-O at the keyboard generates ASCII 15. (O is the 15th letter of the alphabet. CTRL-A generates ASCII 1, etc.) 2. Hold down the ALT key, and type the number you want sent on the numeric keypad. For example, to send an ASCII 15, hold down ALT, press the one then the five key, and let go of ALT. An ASCII 15 is sent. Be sure to type the number on the numeric keypad, and not on the numeric row across the top of the keyboard. There are two other methods for getting setup codes to your printer. The easiest way is to just type the special codes into a text string at the beginning of your spreadsheet. Type a quotation mark, then type the codes, using either of the methods above (Ctrl key or Alt key). 108 - PRINT Another method is the TRANSLATE PRINT x TO yyy configuration option in the .PRO file. See the section titled Customizing CALC for more information. Example: EXAMPLE #1: The spreadsheet is only four columns wide, and 99 lines long. No options are required. Enter: /P (ENTER) (ENTER) (ENTER) (ENTER) (ENTER) (ENTER) By pressing ENTER on all six prompts, you tell CALC to use its defaults for all prompts: * The entire spreadsheet is printed. * The page width is 80 characters, which is adequate for this narrow spreadsheet. * The page length default is 58 lines printed per page. * Borders are not printed, because the B option was not specified. * The printout is single spaced. * There is a 7 character left margin (page offset). * There is a two space top margin (two blank lines are printed at the top of each page). * One copy of the spreadsheet is printed. * No headings or footings are printed. * The output goes directly to the printer. 109 - PRINT EXAMPLE #2: The spreadsheet is 230 characters wide. The printer has wide paper in it, and with compressed print can print 232 characters across. The report is to be double-spaced. Enter: /P 232,,DO (ENTER) Since you entered an option of "O", CALC prompts for a page offset. Enter 2. Since the printer will only handle 232 characters, and the spreadsheet is 230 characters wide, you can only afford a two character left margin. If you didn't change the offset, the last five characters of the spreadsheet would print on a second page. 110 - QUIT QUIT Command (/Q) Purpose: Exits from CALC to the operating system. Prompts: If the spreadsheet currently in memory has not been saved, it will be lost. Use the Save Command (/S) to save the spreadsheet before exiting. To prevent accidental loss of a spreadsheet, CALC asks for confirmation if data has been altered in the spreadsheet area: The current spreadsheet was changed but not saved. Enter Y to quit. Enter N to return to spreadsheet. Enter S to save spreadsheet. If "S" is entered, control is passed to the /Save command. Remarks: Normally CALC returns to DOS after /Q. However, there is a .PRO file option called ON EXIT RUN program which invokes an .EXE file on exit from CALC. See the section titled "Customizing CALC" for more information. Example: You have saved the current spreadsheet and want to exit so you can run another program. Enter: /Q Since you just saved the spreadsheet, and have made no further changes on it, CALC ends. The screen is cleared, and at the top of the screen is the DOS prompt: A> 111 - RANDOM RANDOM Function Purpose: Returns a random number between zero and one. Format: RANDOM Returns a random number between 0 and 1 Remarks: This function is useful in some statistical applications and in sequencing events randomly, such as in scheduling sporting events. To compute a random number in the range 0 to 25, put 25 into A1, then enter the following formula in another cell: INT(RANDOM * (A1+1)) To compute a random number between 0 and n (any positive integer), enter n in A1 and use the formula above. Example: EXAMPLE #1: Compute a random number between 1 and 52. This number might identify the week in a year, or it might identify a card in a deck of cards. Enter the following formula in any cell: INT(RANDOM * (51+1)) + 1 EXAMPLE #2: A spreadsheet contains a list of sporting events, listed one per line in lines 5 to 50. We want to randomly sort them. Start by entering: RANDOM * 1000 in cell F5, then replicate the formula from F5 to F6:F50. Now use the /Arrange command to sort F5:F50. It doesn't 112 - RANDOM matter if the sort is ascending or descending, and no options are required, so just type: /A C F5:F50 (ENTER) (ENTER) and the lines are sorted in a random sequence. 113 - RATE RATE Function Purpose: Computes an interest rate for a given principal amount, payment and number of periods. (See also PRINCIPAL, PAYMENT and PERIODS functions.) Format: RATE(principal,payment,term) RATE(1000,88.85,12) Returns .01 (1% rate) RATE(A1,36.16,36) * 12 If A1 contains 1000, returns .18 (18% rate) RATE(B1,100,A1*2/A5) Resolves the formula, then computes the rate Remarks: RATE(x,y,z) can be entered into a cell, or the RATE function can be used in a formula. INTEREST is an alternate name for the RATE function. All three operands must be specified. The payment operand is the payment per period. The period length needs to be consistent; i.e. in the first example above, the term is twelve months, so the payment is a monthly payment, and the interest rate returned is the rate per month, compounded monthly. One note on amortizations: as the truth-in-lending laws so vividly indicate, there are many ways to compute interest. 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 has monthly payments of $180.77 per month for 36 months. Compute the effective interest rate. In any cell, enter: 114 - RATE RATE(5000,180.77,36) * 12 The cell displays .18, indicating the interest rate is 18 percent. Formatting the cell for three decimal places shows the number as .180, so it is exactly 18%. 115 - REPLICATE REPLICATE Command (/R) Purpose: Copies data from a cell, a row, a column, a range of rows, a range of columns, or a block of cells to another range of cells, and optionally adjusts the cell coordinates in formulas. The format attributes such as decimals, commas, etc. are copied along with the data. This powerful command allows cells to be replicated over and over, with formulas adjusted, saving considerable data entry. Prompts: Enter /R and you are prompted: Enter the "from" range. Examples: A5 G 22 B:J 5:12 A2:J20 As the examples show, you may enter a single cell, a single column, a range of columns, a single line, a range of lines, or a block of cells. The next prompt is: Enter the "to" range. Once again, enter a single cell, a single column, a range of columns, a single line, a range of lines, or a block of cells. If your ranges are valid, the data is moved from the "from" range to the "to" range. If a formula is encountered in the "from" range, an adjustment message is given for each of the variables in the formula. For example: Replicating cell A5. Adjust A3 Y or N? (or A for all) This sample message says that in the "from" cell A5 it found a formula. That formula contained a reference to A3. If you reply Y to this message, each replication is adjusted so A3 becomes A4, then A5, then A6, etc. If you 116 - REPLICATE reply N to this message, all replications refer to A3, unchanged. See the examples below for more information. Remarks: A single cell may be replicated to a group of contiguous cells, either across a row or down a column. Specify the single cell as the "from" cell, then specify the range of "to" cells, either across a row or down a column. A group of contiguous cells may be replicated to another group of cells. If the "from" group are all in a row, then the "to" group must either be all in a row, or they must be a "block" of cells. To illustrate the latter case, consider this example: A B C D E 1 Qtr 1 Qtr 2 Qtr 3 Qtr 4 2 3 Sales (from) (to) (to) (to) 4 CGS (from) (to) (to) (to) 5 Gross (from) (to) (to) (to) Values or formulas have been entered into column B for the first quarter, and now you want to replicate those formulas to the next three quarters. To accomplish this, enter: /R B3:B5,C3:E5 (ENTER) The "from" range, B3:B5 is replicated to each of the columns C, D and E. In this example each of the values in B3 through B5 are copied to the three adjacent columns. If there are any formulas in B3:B5, CALC asks for adjustment (see example below). The replicate command may be used to copy values, formulas or text without adjustment. Just reply "N" to any "adjust" messages. 117 - REPLICATE Example: EXAMPLE #1: Referring to the example above in the Remarks section, let's look at the contents of each cell. Suppose the contents prior to replicating are: A B C D E 1 Qtr 1 Qtr 2 Qtr 3 Qtr 4 2 3 Sales 250000 4 CGS B3*.7 5 Gross B3-B4 Performing the replicate command discussed earlier: /R B3:B5,C3:E5 (ENTER) causes the contents of B3, B4 and B5 to be copied to the next three columns. But in the process of copying, CALC issues three messages: Replicating cell B4. Adjust B3 Y or N? (or A for all) Replicating cell B5. Adjust B3 Y or N? (or A for all) Replicating cell B5. Adjust B4 Y or N? (or A for all) In this example, reply "Y" to all three of the messages. (Or reply "A" to the first message, and the other two messages will not be given.) The result is: A B C D E 1 Qtr 1 Qtr 2 Qtr 3 Qtr 4 2 3 Sales 250000 250000 250000 250000 4 CGS B3*.7 C3*.7 D3*.7 E3*.7 5 Gross B3-B4 C3-C4 D3-D4 E3-E4 Without the adjustment, the formulas in rows 4 and 5 would still be pointing at column B. By adjusting, they now point to their respective columns. 118 - REPLICATE EXAMPLE #2: At the beginning of this manual in the section titled "A Brief Tutorial" there was an exercise which computed interest on a savings account for three years. Now let's use the Replicate Command to carry the computation out for twenty years. The original spreadsheet looked like this: A B C D 1 2 Compute Annual Interest 3 4 Rate: 5.50 5 6 Year Balance Interest 7 1983 5,000.00 275.00 8 1984 5,275.00 290.13 9 1985 5,565.13 306.08 10 11 Totals 5,871.21 871.21 This time, rather than type in all the data for every year, let's set up the first and second detail lines: B7 1983 (A value instead of text.) C7 5000 D7 C7*C4/100 B8 B7+1 (A formula instead of text.) C8 C7+D7 D8 C8*C4/100 Notice that the year number is set up as a value instead of text. This allows it to be incremented by one for the next twenty years, rather than typing in the year numbers twenty times. Of course the years will be right justified and will contain commas and decimals, so use the /F command to format column B as follows: /F B1:B256,J,L (left justify) /F B1:B256,D,0 (no decimals) /F B1:B256,C,N (no commas) 119 - REPLICATE Now replicate row 8. Twenty years will go all the way down to line 26. The command looks like this: /R B8:D8,B9:D26 This Replicate Command takes a little longer, because of its size. During the Replicate there are five "adjust" messages: Replicating cell B8. Adjust B7 Y or N? (or A for all) Replicating cell C8. Adjust C7 Y or N? (or A for all) Replicating cell C8. Adjust D7 Y or N? (or A for all) Replicating cell D8. Adjust C8 Y or N? (or A for all) Replicating cell D8. Adjust C4 Y or N? (or A for all) Here is a case where adjustment is required on all but one of the fields: the last one. If you responded "A" for all, or "Y" to all these messages, you would get some strange results. C4 is the interest rate field, which is a fixed field at the top of the spreadsheet. The interest rate is going to stay in C4 forever. So you must respond "N" to the last "adjust" message, or the Replicate command will use a different (and unpredictable) interest for every year: C4, C5, C6, C7, etc. So give it four Y's and an N. Now the replicate is complete. In the tutorial there was a "Totals" line. So, at B28 enter the text "Totals", at C28 enter the formula C26+D26, and at D28 enter the formula SUM(D7:D26). The spreadsheet recalculates and the interest for 20 years is displayed. 120 - ROUND ROUND Function Purpose: Rounds a formula or value to a specified number of decimal places. Format: ROUND(1.1234,2) Returns 1.12 ROUND(A1,2) If A1 = 1.125, returns 1.13 ROUND(A1*2/B5,A6) Resolves formula, then rounds its result Remarks: Rounding is automatically done by CALC, when displaying a number in a cell. A more common means of rounding is to use /Format and simply change the number of decimals displayed on the screen. However, there are occasional computations which require rounding to be performed in-line. Or if your CALC has been customized to default to floating decimals, the ROUND function is useful for controlling the maximum number of decimals displayed. 121 - SAVE SAVE Command (/S) Purpose: Saves a spreadsheet file on disk so it can be retrieved later for altering or printing. Files are usually in CALC spreadsheet format, but /Save can also write DIF files and comma-delimited ASCII (Mail-merge) files. Prompts: Enter /S. The first prompt is: Enter the drive and path (optional). If you are saving the spreadsheet to the default drive and path, press ENTER. Otherwise, enter any valid drive and path, then press ENTER. There is a brief pause, then a window appears listing all the files with an extension of CAL. The second prompt is displayed: Enter the file name. If this spreadsheet had been loaded earlier, then its name appears as the default. To save it with the same name, just press ENTER. If you want to save the file with the name of another existing spreadsheet, use the down arrow or up arrow to select a file name. Or type the file name instead, CALC saves the currently displayed spreadsheet onto disk, giving it the name you specified. If the file name has an extension of .DIF, the file is saved in DIF (Data Interchange Format) format. If the file name has an extension of .WS, the file is saved in comma-delimited ASCII format, sometimes called Mail-merge format. Each cell across the specified cell range is written as either a comma (if it is empty), a number followed by a comma (if it is a value or formula), or a text string enclosed in quotation marks (if it is text). At the end of each line, a carriage return/line feed is written. Quotation marks are blanked 122 - SAVE in text fields. This is a standard technique for saving sequential data. Note that neither DIF nor WS format have a means of handling formulas; they are strictly text and value oriented. So if you save a spreadsheet in DIF or WS format, it is a good idea to keep a backup copy of the spreadsheet in CALC spreadsheet format, so formulas and formatting options are saved. Some examples of valid file names are: LOAN1 B:WORKSHT5.OLD X A:HOMEWRK.A ADDRESS.WS TEST.DIF After entering the file name, press ENTER. The file is opened. If a file with that name already exists on the disk, you are prompted: File exists. Overwrite or Backup? If the file with the same name can be erased, and this one written over it, reply "O". If you want to save the old file as a backup, reply "B" and it is renamed with an extension of .BAK, then the current file will be saved. To enter a different file name, press BACKSPACE and CALC prompts you for a different name. If the file being saved is a DIF or WS file, two extra prompts are given. The first is: Enter the cell range to be saved (or ALL). The default is ALL, but a block of cells can be specified instead. To confine the data saved to three columns wide and fifty records long, starting at C11, enter the range 123 - SAVE C11:E60. The second prompt asks: Enter R to save by rows, C to save by columns. The default is R, since this processes one line at a time, moving left to right across the columns, which is the most common method. If you specify C, the file is written one column at a time, moving down the lines, effectively rotating it a quarter turn. As the file is being saved, the cursor coordinate in the lower left corner of the screen displays the progress. A message appears saying Saving file; stand by . . .. When this message goes away, the save is completed. The contents, value and attributes of each cell are saved, as well as the column widths, current cell cursor position, current settings of the global options, and current settings of the print options. If your computer has only one diskette drive, do not attempt to /Save or /Load 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. If your system has only one drive and no hard disk, you will need to save your files directly onto your CALC working diskette in drive A:. 124 - SAVE Example: A spreadsheet has been completed and printed. It is to be saved on disk for future reference. It is to be called "PAYABLES". Type: /S PAYABLES (ENTER) A month ago a spreadsheet with the same name was saved on this disk. The message: File Exists. Overwrite or Backup? appears. Since the new spreadsheet is an updated version of old PAYABLES file, reply B. Last month's PAYABLES file is renamed PAYABLES.BAK, and the new spreadsheet is saved with the name PAYABLES. 125 - SGN SGN Function Purpose: Determines the sign of a number, and returns 1 if the number is positive, 0 if the number is zero, or -1 if the number is negative. The value is returned to the current cell or formula. Format: SGN(-35) Returns -1. SGN(A1) If A1 = 35, then SGN(A1) = 1 If A1 = 0, then SGN(A1) = 0 If A1 = -35, then SGN(A1) = -1 SGN(A1*2/B5) Resolves formula, then determines the sign of the result. Remarks: SGN(x) can be entered into a cell, and used as the cell value; or the SGN function can be used in a formula, and/or may have a formula as its argument. Example: A spreadsheet lists sales figures in column A. Then in column B it assigns a value of 1 if the sales figure to the left is below average, 2 if the figure equals the average, and 3 if the figure is above the average. Enter this formula into cell B1: SGN(A1-AVERAGE(A1:A20))+2 Use the Replicate command to copy the formula to cells B1 through B20. 126 - SIN SIN Function Purpose: Computes the trigonometric sine of a cell or formula and returns the value to the current cell or formula. Format: SIN(1.57079) Returns 1 SIN(A1) If A1 = 1.57079, returns 1 SIN(A1*2/B5) Resolves formula, then computes sine Remarks: SIN(x) can be entered into a cell, causing the sine of a number to be computed, and used as the cell value. Or the SIN function can be used in a formula, and/or may have a formula as its argument. Example: Set up a simple spreadsheet which allows a value in radians to be entered, and returns the sine: A1: Radians: A2: Sine: B1: 1.57079 B2: SIN(B1) When a value is typed into B1, the sine is displayed in B2. Enter 1.57079 in B1, and 1 is returned in B2. Now change the spreadsheet so degrees can be entered instead of radians: A1: Degrees: A2: Sine: B1: 90 B2: SIN(B1*3.14159/180) 127 - SQR SQR Function Purpose: Computes the square root of a value, cell or formula and returns the result to the current cell or formula. Format: SQR(25) Returns 5 SQR(A1) If A1 = 25, returns 5 SQR(A1*2/B5) Resolves formula, then computes the square root Remarks: SQR(x) can be entered into a cell, causing the square root of a number to be computed, and used as the cell value. Or the SQR function can be used in a formula, and/or may have a formula as its argument. Example: Set up a spreadsheet that shows the square roots of all numbers from 1 to 100. Start by entering: A1: 1 A2: A1+1 B1: SQR(A1) B2: SQR(A2) So far we have the square roots of 1 and 2. To carry the table out to 100, enter: /R A2:B2 A3:B100 When asked to adjust A1 and A2, reply Y to both. When replication and calcluation are done, the spreadsheet contains the numbers 1 to 100 in column A, and the square roots of 1 to 100 in column B. 128 - STDEV STDEV Function Purpose: Computes the standard deviation of a range of numbers. Format: STDEV(A1:A20) Computes standard deviation of a column of numbers STDEV(A1:E1) Computes standard deviation of a row of numbers STDEV(A1:D20) Computes standard deviation of a block of numbers Remarks: STDEV(m:n) can be entered into a cell, returning the standard deviation of the specified range, and used as the cell value. Or the STDEV function can be used in a formula. The coordinate range specified in a STDEV function may be down a column, such as STDEV(A1:A20), it may be across a row, such as STDEV(A1:E1), or it may be a block of cells (designated by the upper-left and lower-right coordinates), such as STDEV(A1:D20). Example: Column B has a string of numbers from B7 to B26 of which we want to compute the standard deviation. The result is to be placed in B27. At B27 enter: STDEV(B7:B26) After recalculation, B27 contains the standard deviation of the range. 129 - SUM SUM Function Purpose: Sums a range of numbers and returns the result to the current cell or formula. Format: SUM(A1:A20) Sums a column of numbers SUM(A1:E1) Sums a row of numbers SUM(A1:D20) Sums a block of numbers Remarks: SUM(m:n) can be entered into a cell, causing the specified range to be added up, and used as the cell value. Or the SUM function can be used in a formula. The coordinate range specified in a SUM function may be down a column, such as SUM(A1:A20), it may be across a row, such as SUM(A1:E1), or it may be a block of cells (designated by the upper-left and lower-right coordinates), such as SUM(A1:D20). Example: EXAMPLE #1: Column B has a string of numbers from B7 to B26 which are to be added up. The result is to be placed in B27. At B27 enter: SUM(B7:B26) After recalculation, B27 contains the sum. EXAMPLE #2: A spreadsheet has a block of expense dollar amounts, running from C8 to J15. These are to be subtracted from the gross profit figure in C6, and the result is to be printed in C17. Move the cell cursor to C17 and enter: C6-SUM(C8:J15) 130 - TAN TAN Function Purpose: Computes the trigonometric tangent of a value, cell or formula and returns the result to the current cell or formula. Format: TAN(.7854) Returns 1 TAN(A1) If A1 = .7854, returns 1 TAN(A1*2/B5) Resolves formula, then computes tangent Remarks: TAN(x) can be entered into a cell, causing the tangent of a number to be computed, and used as the cell value. Or the TAN function can be used in a formula, and/or may have a formula as its argument. Example: Set up a simple spreadsheet which allows a value in radians to be entered, and returns the tangent: A1: Radians: A2: Tangent: B1: .7854 B2: TAN(B1) When a value is typed into B1, the tangent is displayed in B2. Enter .7854 in B1, and 1 is returned in B2. Now change the spreadsheet so degrees can be entered instead of radians: A1: Degrees: A2: Tangent: B1: 45 B2: TAN(B1*3.14159/180) 131 - TITLE TITLE Command (/T) Purpose: Locks spreadsheet titles along the top of the screen and/or along the left side, so they remain in view. Prompts: Begin by moving the cell cursor to the line just below and/or the column to the right of the titles to be locked; i.e. the first "unlocked" cell. Then enter /T and you are prompted: Horizontal, Vertical, Both or None. To lock one or more title lines at the top of the screen, enter "H". To lock one or more columns along the left edge of the screen, enter "V". To lock titles both vertically and horizontally, enter "B". Remarks: Title locking may be turned off with the /TN command, or it may be turned off by using the = (Goto) option to jump to a cell above, or left of, the locked titles. Example: The following spreadsheet fills up the screen and overflows both to the right and the bottom: A B C D E 1 Jan 85 Feb 85 Mar 85 Apr 85 2 Oregon 540 3 Florida 441 4 New York 662 5 Illinois 293 Whenever we scroll downward off the bottom of the screen, the month names on line 1 disappear. Scrolling to the right causes the state names to disappear. To lock both the months and states so they always remain on the 132 - TITLE screen, move the cell cursor to B2, enter /T B (/Title,Both). To lock only the months, move the cell cursor anywhere on line 2 and enter /T H (/Title,Horizontal). To lock only the state names, move the cell cursor anywhere in column B and enter /T V (/Title,Vertical). 133 - UTILITY UTILITY Command (/U) Purpose: Allows files to be deleted or renamed, and allows the user to temporarily exit to DOS, then return to CALC, without saving the current spreadsheet. Prompts: Enter /U and the following prompt is displayed: Delete a file, Rename a file, or Shell to DOS. If you press D, you are prompted for the drive and path, then a window lists all the files in the specified path. Use the down arrow or up arrow to select a file, or type the file name and press ENTER. The file is deleted. If you press R, you are prompted for the drive and path, and a window lists all the files. Use the down arrow or up arrow to select one, or type the name of the file to be renamed. The third prompt asks for the new name the file is to be given. If you press S, the screen is cleared and the following is displayed: Type EXIT to return to CALC. C:CALC> At this point you are back at DOS, although CALC is still in memory. You can use any DOS command, and can even run some small programs, provided they don't use much memory. When you are finished, type EXIT to return to CALC. 134 - WINDOW WINDOW Command (/W) Purpose: Allows the spreadsheet screen to be split into two "windows", either vertically or horizontally. This permits you to look at two parts of a spreadsheet at the same time. Prompts: Enter /W and the following prompt is displayed: Horizontal, Vertical, Off, Synchronized, Unsynchronized Press H to split the screen horizontally into two windows. The screen is split at the line the cursor is on. To make the upper window larger, move the cursor further down the screen first. The cell cursor remains in the lower window. It can be moved to the upper window by pressing the semi- colon (;) key. Press ; a second time to return the cell cursor to the lower window. Press V to split the screen vertically into two windows. The screen is split at the column the cursor is on. To make the left window larger, move the cursor further to the right of the screen first. The semicolon key moves the cursor between windows. Press O to turn off windowing and return to the normal screen. Press S to synchronize the windows. When horizontal windows are synchronized, both windows always display the same columns. When vertical windows are synchronized, both windows always display the same lines. Press U to unsynchronize the windows. When windows are unsynchronized, scrolling around in one window leaves the other window unchanged. Unsynchronized is the default. 135 - XTERNAL XTERNAL Command (/X) Purpose: Reads data from another CALC spreadsheet, or from a File Express or PC-FILE database, and places the data into the current cell. Prompts: Position the cell cursor on the cell which will contain the data, then enter /X. You are prompted: Enter C to get data from a spreadsheet. Enter F to get data from a database. The Xternal command can reach into a spreadsheet file, or a database and extract data. Enter C or F to indicate the type of file. Subsequent prompts are specific to either spreadsheets or databases. CALC Spreadsheet If you replied "C" to the previous prompt, the next prompt is: Enter the drive and path name (optional). Specify the drive and path where the calc spreadsheet is located, then press ENTER. There is a brief pause while the directory is read, then a window appears displaying spreadsheets with .CAL extensions. This prompt appears: Enter the name of the CALC spreadsheet to be read. Use the down arrow and up arrow to select a file, or type the name of the file and press ENTER. The next prompt is: Enter the cell to be read. Any cell coordinate from A1 to BL256 is valid. After pressing ENTER, the message "Stand by. Search in progress." is given, and CALC searches the file for the 136 - XTERNAL specified cell. When the cell is found, its data is placed in the current cell of your spreadsheet. If the data is text, it appears as text in your spreadsheet. If it is a value or formula, it appears as a value in your spread- sheet. Format items, such as decimal places, justifica- tion, etc. are not copied. The data can be formatted as desired using the /Format command. If the requested cell has no data, a warning message is given. The ERROR designation is placed in the current cell, and the cell's contents are the words NO DATA. The Xternal command only goes one level deep in its search for data. In other words, if the requested cell is also an external reference to a third spreadsheet, CALC does not access the third spreadsheet. It extracts the value of the specified cell as of the last time it was saved. Database If you replied F to the first prompt, the next prompt is: Enter the drive and path name (optional). Specify the drive and path where the database is located, then press ENTER. There is a brief pause while the directory is read, then a window appears displaying all the databases. The following prompt appears: Enter the name of the database to be accessed. If the database is on a disk other than the default drive, its name may be prefixed by the drive letter and a colon (for example, A:CUST). If the database cannot be found, a message is given. Otherwise, this prompt appears: Field to search? (Enter field name, or press TAB/BACKTAB to rotate through the field names.) 137 - XTERNAL You can enter a field name at this time, or press the TAB key, which displays each of the field names in the database consecutively. When you find the field name you want, press ENTER to go on to the next prompt. Note: when typing the field name, it is not necessary to type the entire name. CALC will find the first field name that matches the characters you entered. Also, CALC ignores upper and lower case, converting everything to upper case before comparison. Now the next prompt: Look for? Enter the character string to be searched for. For example, if you are searching a customer file and you specified "customer number" as the "field to search", then enter the customer number here. CALC compares for the length of the data entered; i.e. if you enter ABC, CALC finds the record whose search field starts with ABC. The next prompt is: If there is more than one record which matches search data, which one should be used? (1 to 999, or ALL.) Normally the response to this prompt is 1, which tells CALC to read the first matching record it finds. The 1 has already been filled in as the default, so just press ENTER. However, if there are multiple matches in your file, and you want to bypass the first two records, enter 3, and CALC skips the first two records. Or the word ALL can be entered in response to this prompt, causing all matching records to be read, and numeric fields totalled. If the retrieved field (next prompt) is a numeric field, the total of that field from all matching records is put into the current cell of your spreadsheet. See the examples below for more discussion of the "ALL" option. 138 - XTERNAL The final prompt is: Field to retrieve? This is the name of the database field whose contents are to be placed in your spreadsheet's current cell. Just as above, with the "field to search" prompt, you can use the TAB and BACKTAB keys to rotate through the valid field names. When you find the field name you want, press ENTER to go on to the next prompt. Note: when typing the field name, it is not necessary to type the entire name. CALC finds the first field name that matches the characters entered. Also, CALC ignores upper and lower case, converting everything to upper case before comparison. If the retrieved field's name ends with a "#", the data is placed in your spreadsheet cell as a value. Otherwise it is placed in the cell as text. If no record is found which matches the requested search, a warning message is given, and the ERROR condition is set for the cell. Remarks: External references are not limited to just one spreadsheet or database. Any number of external spreadsheets and/or databases can be accessed from a single spreadsheet. The speed of the accesses varies depending on the number of different spreadsheets and databases being accessed, and the sizes of those spreadsheets and databases. The fields entered in a /Xternal command are saved with the cell contents, just as a formula is saved with its cell. When the cell cursor is moved to a cell with an external reference, the external reference parameters are displayed beneath the cell contents. To change one or more parameters in an external 139 - XTERNAL reference, move the cell cursor to the cell to be changed, and enter /X. The normal /X prompts are given, but the parameter values are already filled in for you on the command line. Just press ENTER until you come to the parameter to be changed, then type the new value. To delete an external reference, simply enter another value in the cell, or use the /Blank command to clear the cell's contents. Cells with external references can be referenced in formulas and functions just like any other cells. External references are saved with the cell's data at /Save time, and reloaded at /Load time. One additional step takes place at /Load time: each external reference is re-resolved; i.e. the data is looked up again in the referenced spreadsheet or database. If the data has changed since the last /Save, the new data is substituted. Of course, this means that a spreadsheet with external references take longer to load. It also means that the externally referenced files must be online in order to load the spreadsheet. Example: EXAMPLE #1: Preparing a profit and loss statement using CALC, we realize that the expense totals to be displayed are already in another spreadsheet. They are the total lines of the detail spreadsheet called CHEKBOOK, and are in cells B65 through G65. Since CHEKBOOK is updated every month, we can use external references to pull the data into our monthly P & L, and eliminate the need to re-key the data each month. Starting with the first expense, wages and salaries, move the cursor to C18 where the dollar amount is to be displayed. If any extra formatting is to be done, such as leading dollar signs, etc. it can be done now, or after the data is retrieved. Now enter: /X C CHEKBOOK B65 140 - XTERNAL CALC finds the spreadsheet called CHEKBOOK, and retrieves the contents of cell B65. Since B65 contains a dollar amount, that amount is put into cell C18 of our P&L spreadsheet as a value, rather than text. To retrieve the remaining expense amounts into cells C19 through C23, we repeat the /X command above, referencing cells C65 through G65. The command must be retyped for each cell. There is no Replicate command for external references (not yet, anyway). When finished, we can show the total expenses either by retrieving them from CHEKBOOK using /X, or we can simply total up the numbers in C18 through C23 using the SUM(C18:C23) command. EXAMPLE #2: Working on the same P & L described in example 1, we now want to show the total sales for the month, in two categories: retail sales and wholesale sales. All the sales invoices for the month have been entered into a File Express database called MTDSALES, with invoice number, date, customer number and amount. The first three letters of the invoice number are "RTL" for retail sales and "WHL" for wholesale sales. Start by moving the cell cursor to the cell where the retail sales amount is to be displayed. Now enter /X and respond to the prompts as shown: CALC or FILE: F Name of database: MTDSALES Field to search: Invoice number Look for: RTL Which one: ALL Field to retrieve: Amount# Now we wait for a few seconds while CALC reads the database. Since we specified ALL, it will read every record whose invoice number starts with RTL, and total up the Amount# field. When the search is done, the sum of Amount# from all the RTL records appears on our spreadsheet in the current cell. To print the total wholesale sales, move down one cell and enter the same /X command as above, but substituting WHL for RTL. 141 - ZAP ZAP Command (/Z) Purpose: Clears the spreadsheet area and resets all the system defaults. Prompts: Enter /Z and you will be prompted: Clear contents? (Y or N). To clear the current contents of every cell, and reset all the system defaults, enter "Y". To leave the current spreadsheet as is, reply "N" or ESCAPE. Remarks: If the spreadsheet currently in memory has not been saved, it will be lost. Use the Save Command (/S) to save the spreadsheet before exiting. To prevent accidental loss of data, CALC asks for confirmation (Clear contents?) before clearing. An alternate method of clearing memory is to use the /Q command to exit to the operating system, then restart CALC. Example: The spreadsheet currently on the screen has been saved on disk. The spreadsheet area is to be cleared. Enter: /Z Y A message appears saying Clearing contents. Stand by... and after a pause, a blank screen appears with the cursor at A1 and the column widths reset to the default length. 142