Between Heaven & Hell 2

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

/ Between Heaven & Hell 2 / BetweenHeavenHell.cdr / 500 / 404 / calc3.doc < prev next >

Wrap

Text File | 1986-12-03 | 69KB | 2,509 lines

- 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 101 - PRINCIPAL purchase price of the car. In any cell, enter: 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. 102 - 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 103 - 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. 104 - 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 105 - 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). 106 - 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. 107 - 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. 108 - 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> 109 - 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. 110 - RANDOM Now use the /Arrange command to sort F5:F50. It doesn't 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. 111 - 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 112 - RATE the effective interest rate. In any cell, enter: 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%. 113 - REPLICATE ____________________________________________________________________ REPLICATE Command (/R) Purpose: Copies data from a cell or range to another cell, range or block, 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 will be prompted: Enter the "from" range. Enter a single cell or a range of cells (example: A1:G1). If the "from" cell is the current cell (where the cell cursor is currently), just press ENTER and the current cell is used. The next prompt is: Enter the "to" range. Once again, enter a single cell or a range of cells. This range specifies the receiving field. If the "to" cell is the current cell, just press ENTER. If your ranges are valid, the data in the "from" range is now copied to the "to" range. During the copying operation if a formula is encountered in the "from" range, a 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 114 - 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. 115 - 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. 116 - 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) 117 - 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. 118 - 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. 119 - 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 will also write DIF format files and comma-delimited ASCII (Mail-merge) files. Prompts: Enter /S. The message Retrieving 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 save the spreadsheet 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 you enter a file name instead, CALC will save the currently displayed spreadsheet onto disk, giving it that name. The file name can be any valid MSDOS file name; i.e. eight characters or less, optional extension, optional drive designation. 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 120 - SAVE return/line feed is written. Quotation marks are blanked 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 121 - SAVE and fifty records long, starting at C11, enter the range 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:. 122 - 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. 123 - 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. 124 - 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) 125 - 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. 126 - 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. 127 - 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) 128 - 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) 129 - 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 130 - TITLE the months and states so they always remain on the 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). 131 - 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 name of the CALC spreadsheet to be read. If the spreadsheet is on a disk other than the default drive, its name may be prefixed by the drive letter and a colon (for example, A:WORK). If the file cannot be found, a message is given. Otherwise, the next prompt appears: 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 specified cell. When the cell is found, its data is placed in the current cell of your spreadsheet. If the 132 - XTERNAL data is text, it appears as text in your spreadsheet. If it is a value or formula, it appears as a value in your spreadsheet. Format items, such as decimal places, justification, 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. As an example, a spreadsheet called STATE references cells in another spreadsheet called COUNTY, using the Xternal command. But COUNTY also references a spread- sheet called CITY. If the figures in CITY are changed, it is necessary to /Load and then /Save COUNTY to update its values. Then the spreadsheet called STATE can be loaded, and will have the most recent values. Database If you replied F to the first prompt, the next prompt is: 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, the next prompt appears: Field to search? (Enter field name, or press TAB/BACKTAB to rotate through the field names.) 133 - 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. 134 - 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 135 - 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 136 - 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. 137 - 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. 138