Xspread Reference Manual James Cornelius Michael Frey Dan Gruber Fang Wang Manual Updated by Robert Parbs II December 12, 1992 Contents Introduction 1 Using the Worksheet 4 Worksheet Structure : : : : : : : : : : : : : 4 : : : : : : : : : : : : : : : : : : : : : : : : : Navigating the Worksheet : : : : : : : : : : 4 : : : : : : : : : : : : : : : : : : : : : : : : : Cell Entry and Editing : : : : : : : : : : : : 5 : : : : : : : : : : : : : : : : : : : : : : : : : Formulas, Cell Expressions, and Functions : 6 : : : : : : : : : : : : : : : : : : : : : : : : : Toggle Commands : : : : : : : : : : : : : : 8 : : : : : : : : : : : : : : : : : : : : : : : : : Miscellaneous Commands : : : : : : : : : : 9 : : : : : : : : : : : : : : : : : : : : : : : : : Alphabetical Command Reference 10 Column/Row Commands 10 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : File Commands : : : : : : 13 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : Graph Commands : : : : 17 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : Matrix Commands : : : : 24 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : 26 Option Commands : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : Range Commands : : : : 30 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : Function Reference 33 Argument types : : : : : : : : : : 33 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : Functions by type : : : : : : : : 33 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : Alphabetical Function Reference 34 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : i Introduction Xspread is an electronic spreadsheet which runs under X Windows. You must be using an X Windows terminal to be able to use this program. Xspread supports many standard spreadsheet features. Among these are: Cell entry and editing. Worksheet size: 702 columns by unlimited rows. File reading and writing. File encryption. Absolute and relative cell references. Numeric and label (i.e.character string) data in cells. Left or right justi cation for labels. Row and column insertion and deletion. Hiding and unhiding of rows and columns. Range names. Manual or automatic recalculation. Numeric operators (+; ; ; =; ^(exponentiation), %(remaindering)) Relational operators (<, <=, >, >=, =, ! = (not equal)) Logical (or, Boolean) operators (&(and), j (or),~(not)) Function references. References to external programs. These are called external functions. Use of the mouse in pointing, and menu selecting. The structure and operation of the spreadsheet is similar to but not identical with popular spreadsheets such as Lotus 1-2-3 and its clones. Like other spreadsheets, the workspace is arranged into rows and columns of cells. Each cell can contain a number, a label (i.e. character string), or a formula which evaluates to a number or label. You can start the program with or without specifying a le to be read in. This le must be a saved worksheet. If a le is specified on the command line, xspread attempts to locate and read in the le. If it is successful, xspread starts with the le's contents in the workspace. If it is unsuccessful or no le is speci ed on the command line, xspread starts with the workspace empty. For a tutorial of the spreadsheet program, type: xspread doc/tutorial This directory also contains other spreadsheet templates which you may be interested in. To start xspread, type the program name, xspread, followed by any command quali ers you want to use and then by the optional le name. The full form of the command line is: 1 xspread -c] -h] -m] -n] -x] -N] -C] -R] -fn font] filename] Here, -c, -h, -m, -n, -r, and -x represent the command line quali ers and lename represents the le name of the optional worksheet le that you can start up with. The other quali ers -N -C -R are only available, if your command line interpreter distinguishes the case of letters (type xspread -h to see if you may use them). Here comes the description of these qualifiers: -c Recalculation is done in column order. When xspread recalculates, it will start at the top of the leftmost column, and recalculate the all the cells from top to bottom. Then, it will recalculate the next column in the same order. It will continue in this fashion until it has recalculated the rightmost column. Xspread does NOT support natural order recalculation. Default: Row order recalculation. -h Display command line help. -m Start with manual recalculation. With this option, the spreadsheet will recalculate values only when the \@" command is used. With automatic recalculation, the spreadsheet recalculates values whenever a cell's contents change. Default: Automatic recalculation. -n Use standard data entry mode. In this mode the user must specify if the data entry item is numeric or a label: If the cell is to be a number, the rst character must be an \=" sign. If the cell is to be a label, the rst character must be a double quote (") for centered text, less than sign (<) for left justi ed, or greater than sign (>) for right justi ed text. Default: Numeric entry mode: If the rst character is a numeral, a \+" sign, or a \ " sign, the cell contains a number. If the rst character is a greater than sign (>), less than sign (<), or double quote ("), the cell contains a label. If it is anything else, the character is a command to xspread. -r Recalculation is done in row order. When xspread recalculates, it will start at the left of the topmost row, and recalculate all the cells from left to right. Then, it will recalculate the next row in the same order. It will continue in this fashion until it has recalculated the bottommost row. Xspread does NOT support natural order recalculation. Default: Row order recalculation. -x Use encryption when reading and writing les. Default: Do not use encryption. -fn Change the font size to whatever, now works!!!. ie. -fn 9x15. -N Do not show the cursor. Default: Display it. 2 The action after the ENTER / RETURN key is released is to move the cursor down the current -C column. The action after the ENTER / RETURN key is released is to move the cursor right in the current -R row. Default: Stay at the current position. 3 Using the Worksheet Worksheet Structure Xspread divides the spreadsheet window into four regions. Xspread uses the top line for displaying the cell address the cursor is on, displaying cell values, and entering commands. The second region consists of the second and the third line. Here xspread display messages or options for the slash (/) command. In the latter case the third line is used to show a short description of the highlighted option. A third region is immediately under the third line and along the left edge of the window. This region shows the column addresses and row addresses. The fourth region is the worksheet work space which is currently displayed. Unlike other spreadsheets, xspread does not have a status line showing such information as the current time, operating mode, or amount of workspace left. The xspread electronic worksheet is organized in two dimensions: vertical columns and hor- izontal rows. The worksheet has 702 columns labeled alphabetically A through ZZ (A through Z and AA through ZZ). The number of rows only depends on the available memory. Rows are numbered from 0 on. Where a row meets a column, the intersection is called a cell. Cells have addresses which consist of their column letter(s) and row number. Examples of cell addresses are A1, E56, and AH187. The upper left corner has cell address \A0". The cell address occupied by the cursor is shown on the top line. If a cell's numeric value is wider than the column width, the cell is lled with asterisks. If the cell's label string is wider than the column width, the display of the label is truncated at the start of the next non-blank cell in the same row. The xspread worksheet window has two cursors. The cell cursor highlights the current cell. The character cursor shows up when you type a command on the top line. The user gives commands to xspread through the use of function keys and slash (/) commands. Unlike other spreadsheets, xspread does NOT assume the existence of dedicated function keys on the terminal keyboard. Therefore, you have to enter all xspread commands using either printable ASCII characters or control key combinations. All commands entered using printable characters are case sensitive. This manual indicates control key combinations by showing a caret (^) immediately prior to the control key's letter. For example, control A is shown as ^A. Navigating the Worksheet Moving the Cursor One Cell at a Time Move the cursor around the worksheet by pressing these control keys. The control key commands always are available even if the character cursor is on the top line. ^B (back) and ^F (forward) move the cursor left and right, respectively. ^P (previous) and ^N (next) move the cursor up and down, respectively. The arrow keys also work. Left and right arrow keys move the cursor left and right. Up and down arrow keys move the cursor up and down. There are some additional cursor control commands available if the character cursor is not on the top line of the window. These commands are intended to make xspread compatible with the vi editor commands: 4 h (back) and l (forward) move the cursor left and right. k (up) and j (down) move the cursor up and down. ^H and the spacebar move the cursor back and forward, respectively. Larger Cursor Moves ^ (top) and # (bottom) move the cursor to the top row and bottom row of the current column, respectively. 0 (zero) (left edge) and $ (right edge) move the cursor to the left edge (column A) and the right edge (the last column) of the worksheet, respectively. b scans the cursor backwards (i.e. to the left and up) to the previous valid (non-blank) cell. w scans the cursor forwards (i.e. to the right and down) to the next valid (non-blank) cell. ^Ed goes to the next non-blank cell in the indicated direction. The character d must be replaced by one of the valid cursor direction indicators (i.e., ^B, ^F, ^P, or ^N). When you execute this command, if the cursor is on a blank cell, it goes in the indicated direction until it reaches the rst non-blank cell. Moving to Speci c Locations g goes to a speci c cell. Xspread prompts for a cell address, range name, a string expression surrounded by quotes, of a number. If you specify a cell address or a range name, xspread goes directly to that cell, or the starting (upper left) cell of the range. If you specify a string expression surrounded by quotes, xspread will search for a cell containing that expression. If you specify a number, xspread will search for a cell containing that number. Searches for either strings or numbers go forward from the current cell. When the search reaches the end of the worksheet, xspread starts the search at cell A0 and searches from there forward to the current cell. Moving to Speci c Locations via the mouse The mouse can also be used for navagation. Pick the cell in the worksheet you would like to place the cursor and `click' with the left or center button. The mouse cursor will go to that cell. Please note that if you press the third button, you will enter the menu. Cell Entry and Editing Cells can contain either numeric or string expressions or constants. Label Entry To enter a label, rst enter one of these characters: >, <, or " (double quote). Entering a letter rst does NOT start a label, since it is necessary to use letters to give commands to xspread. " indicates that the label will be centered in the current cell. < indicates that the label will be entered into the current cell ush left. > and indicates that the label will be entered into the current cell ush right. 5 Numeric Entry To enter a number, you have to know whether or not you are in quick numeric entry mode. If you are in standard mode, rst use an = sign. Xspread prompts you for the expression on the top line. Enter the number or numeric expression. If you are in quick numeric entry mode, start the numeric expression with a digit (0-9), + sign, or sign. Then, nish entering the number or numeric expression. Cell Editing E edits the label that already exists in the current cell. Xspread will display the current label on the top line with the character cursor at the end of the label. You can then edit the label. e edits the numeric value associated with the current cell. Xspread will display the current numeric expression on the top line with the character cursor at the end of the numeric expression. You can then edit the numeric expression. c copies that last marked cell to the current cell. m marks a cell for later use by the copy command. x clears (erases) the current cell. You can use any of the pull commands to retrieve cell contents that were previously deleted. + in standard mode, adds the value of its argument to the value of the current cell and stores the result in the current cell. In quick numeric mode, + introduces a numeric expression or value. in standard mode, subtracts the value of its argument from the value of the current cell and stores the result in the current cell. In quick numeric mode, introduces a numeric expression or value. Formulas, Cell Expressions, and Functions Formulas Without formulas, an electronic spreadsheet would not be any better than its paper counterpart. It is the ability to enter and recalculate formulas that gives an electronic spreadsheet its real power. Formulas can link result cells to other cells in the spreadsheet. These other cells can, in turn, reference still other cells so that a recalculation of the entire spreadsheet can have a cascade e ect. Through formulas, a single cell can a ect cells throughout the entire worksheet. Formulas can reference cells either through the cell's address (e.g. K20) or through de ned range names. Both cell addresses and range names can be either relative, absolute, or a combina- tion of the two. Relative cell addresses and range names change when the cell's formula is copied to another position in the worksheet. Absolute cell addresses and range names do not change when the cell's formula is copied to another position in the worksheet. Cell References The method of specifying absolute cell addresses follows the convention of Lotus 1-2-3. Absolute references are preceded by a dollar sign ($). The dollar sign can precede either the column reference, row reference, or both. Here are some examples: 6 K20 This references cell K20. Both the column and row references change when the cell is copied. $K$20 This references cell K20. Both the column reference and row reference remain xed when the cell is copied. $K20 This references cell K20. The column reference remains xed but the row reference changes when the cell is copied. K$20 This references cell K20. The column reference changes but the row reference remains xed when the cell is copied. These conventions also hold on de ned ranges. Range references vary when formulas contain- ing them are copied. If the range is de ned with xed variable references, the references do not change. Operators Xspread supports a number of operators for use in formulas. The numeric operators for formulas include: + Addition Subtraction Multiplication / Division ^ Exponentiation (raise to a power) % Modulus, or, Remaindering () Parentheses can be used to change the order of operations You can use relational operators to compare two numeric expressions to see if they satisfy the speci ed relation. The result is a logical value, either true or false. The relational operators are: = Equal to ! = Not equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to You can use logical operators to a ect compound logical expressions. The logical operators are: ~ Logical NOT & Logical AND j Logical OR You can use the conditional operator to test for a condition and take action depending on whether that condition is true or false (i.e., perform an IF test). This operator takes the place of the @IF function found in most other spreadsheets. Xspread does not support an @IF function. The conditional operator is: e1?e2:e3 Conditional operator. If expression e1 is true, return the value of expression e2, oth- erwise return the value of expression e3. 7 Function References Xspread supports a number of functions that make it easy to perform calculations of a speci c nature. Formulas can reference any of the functions de ned in the Function Reference section. You can use these function references just as you would any cell or range reference. All functions begin with the \@" character. CAUTION: Since the \@" character by itself is used as a command character (to recalculate the spreadsheet), you MUST precede the \@" character in a function reference with a \+" sign or \ " sign if a function reference is the rst item in a formula. Toggle Commands Xspread has several optional settings which operate like toggle switches. Each switch has two settings. The toggle commands change the settings of the selected switch to its opposite setting without forcing you to go through the slash (/) command tree to set or reset them. All of the toggle commands are of the form ^To, where \o" represents a letter that stands for the option that you want to toggle. The settings of all toggle options are saved with the worksheet when it is written into a worksheet le. The toggle options are: a Automatic / Manual Recalculation. If automatic recalculation is set, every change to the spreadsheet will cause the spreadsheet to be recalculated. If manual recalculation is set, xspread does not recalculate the spreadsheet unless you explicitly issue a recalculation command (@). e External Function Execution. If external functions are enabled, xspread calls them when- ever the screen is updated. If external functions are disabled, any external functions that are referenced are not called during screen updates. If external functions are referenced in the worksheet and they are disabled, xspread prints a warning each time that the screen is updated. The result from @ext() is the value from the previous call, if any, or else a null string. n Quick Numeric Entry. If set, you can start numeric entry with any digit, a plus sign, or a minus sign. If not set, you must start numeric entry with an equals sign. t Top Line Display. If set, the top line of the worksheet window displays the name and value of the current cell. If not set, the top line is blank. x Encryption. If set, worksheet les are encrypted when xspread saves them using the /File Put command and xspread assumes that worksheets that it reads using the /File Get command are encrypted. If not set, xspread does not encrypt les with /File Put nor does it decrypt les with /File Get. $ Dollar Prescale. If set, numeric amounts are automatically scaled by .01 when you enter them into cells. This allows users to avoid typing the decimal points in monetary amounts. If not set, numeric amounts are not scaled. r Newline action. Toggle between no action, move down after entry and move right after entry. z Set newline action limits. The limits are set to the current row and column (for r and z see also set rowlimit=n, collimit=n). 8 Miscellaneous Commands Xspread supports several miscellaneous commands. These do such things as support various options, allow you to bail out of a command that you do not want to execute, and o er on-line help. Here is the list: ^C, Q, q Exit xspread. All three of these commands exit the user from xspread. ESC, ^G Abort Command. Hitting the ESC (escape) key or ^G are the bail out commands. Either one of these will abort the current command and return you to Ready mode. ? Help. This will bring up an index to on-line help. The index will display a list of topics together with the letter that allows you to select a particular topic. The help facility is NOT context sensitive. TAB Point Command. When the character cursor is on the top line, you can use TAB to de ne a range by pointing, instead of by specifying cell addresses. When you press TAB the rst time, xspread enters Point mode. You can then specify the range to be selected by using the cursor control keys. The cell that you were in when you pressed TAB is one corner of the speci ed range. During the point operation, the speci ed range at any time is highlighted. Pressing TAB the second time exits point mode and makes the range selection nal. Three commands redraw the screen: ^L Simple Screen Redraw. This command redraws the screen, displaying the values of ex- pressions. ^R Redraw Screen with Values Highlighted. This redraw command highlights any cells which are referenced by formulas or expressions but which do not contain formulas or expressions. This screen redraw command is useful for showing those values which you need to provide or update. With this command, xspread highlights all cells which contain constant numeric values. ^X Redraw Screen with Expressions Highlighted. This screen redraw command can be used to highlight those cells which contain expressions. Xspread shows all expressions as formulas, not their current values. All expressions are displayed as left-justi ed text. This command makes it easier to check expressions. Three commands display current cell information in the command line: ^A Numeric Value. This command displays the numeric value of the current cell in the com- mand line. ^V Cell Name. This command displays the name of the current cell in the command line. ^W Cell Expression. This command displays the expression attached to the current cell, if any. If there is no expression, this command returns \?". 9 Alphabetical Command Reference /C Column/Row What the /Column/Row commands do These commands perform various operations on entire columns or rows in the worksheet. The Column/Row command provides the following options: Insert Inserts a new row or column into the worksheet. Append Insert a new row or column immediately following the source row or column with a copy of the contents of the source row or column. Delete Deletes a row or column from the worksheet. Pull Reinsert (pull) deleted rows or columns back into the worksheet. Remove Remove expressions from the a ected rows or columns, leaving only the values of the expressions in the a ected cells. Hide Hides a row or column on the worksheet. Show Unhides a row or column on the worksheet. Format Speci es the numeric display format for a column. How to use the /Column/Row commands 1. Place the cursor on a cell in the row or column you want to use the command on. 2. Type /C. 3. Choose one of the Column/Row commands. 4. Choose whether to apply the command to a row or column. /CA Column/Row Append What the command does This command inserts a new row or column which immediately follows the cursor position and copies the contents of the current row or column into the new inserted row or column. How to use it 1. Move the cursor to the cell in a row or column where you want the new row or column to be inserted. Rows are inserted below and columns to the right. 2. Type /CA. 3. Type R for Row or C for Column. 4. Xspread inserts the row or column immediately below or to the right of the cursor position. It then copies the values of the cells in the current row or column into the newly inserted row or column. 10 /CD Column/Row Delete What the command does This command deletes a row or column from the worksheet. The remaining rows or columns are renumbered to close the space. A new row or column is added at the bottom or right edge of the worksheet. How to use the command 1. Move the cursor to the row or column you want to delete. 2. Type /CD. 3. Type R for row or C for column. 4. Press Enter. The row or column at the current cursor position is deleted. /CF Column/Row Format What this command does This command sets column width and the numeric display format for a column. There is no command to format a row. How to use the command 1. Move the cursor to the column you want to format. 2. Type /CF. 3. Enter the column width. 4. Enter the number of digits to follow the decimal place. Values are rounded o to the least signi cant digit displayed. /CH Column/Row Hide What this command does This command hides the current row or column. This keeps it from being displayed. However, it still remains in the worksheet. How to use the command 1. Move the cursor to the row or column you want to format. Type /CH. 2. 3. Type R for row or C for column. 4. Press Enter. The row or column at the current cursor position is hidden. 11 /CI Column/Row Insert What this command does This command inserts a row or column into the worksheet at the current cursor position. The new row appears immediately below the cursor and new columns immediately to the right. How to use the command 1. Move the cursor to a cell in the row or column where you want the new row or column to be inserted. Rows are inserted below and columns to the right. 2. Type /CI. 3. Type R for Row or C for Column. 4. Xspread inserts the row or column immediately below or to the right of the cursor position. /CP Column/Row Pull What this command does This command reinserts (pulls) deleted information back into the worksheet at the current cursor location. /CPR inserts enough rows to hold the last deleted set of cells. /CPC inserts enough columns to hold the last deleted set of cells. /CPM (Merge) does not insert rows or columns; it overwrites the cells beginning at the current cursor location. How to use the command 1. Move the cursor to the position where you want the deleted information to appear. 2. Type /CP. 3. Type R for row, C for column, or M for merge. 4. Press Enter. Xspread inserts the deleted information in the manner speci ed. /CR Column/Row Remove What this command does This command removes formulas in the a ected rows or columns and converts them to the values which were in the cells before the command was executed. How to use the command 1. Move the cursor to a cell in the row or column you want to convert from formulas to formula values. 2. Type /CR. 3. Type R for row or C for column. 4. Press Enter. Xspread converts the formulas to their values in the selected row or column. 12 /CS Column/Row Show What this command does This command shows (unhides) hidden rows or columns. It is the reverse of the /CH (Col- umn/Row Hide) command. How to use the command 1. Type /CS. 2. Type R for row or C for column. 3. Enter a range of rows or columns to be revealed. The default action is the rst range of rows or columns currently hidden. 4. Press Enter. Xspread reveals the selected rows or columns. /F File What the /File commands do These commands transfer information between the current worksheet and les on disk. The /FP (Put), /FT (Table), and /FW (Write) commands can pipe their output to a program. In order to use this feature, type \j progname" to the prompt asking for a lename. The File command provides the following options: Get Retrieves the speci ed les from the disk. Put Saves the current worksheet or a speci ed range to the disk in worksheet format. Write Prints the current worksheet or a speci ed range to a le in the format in which it is displayed on the screen. Table Prints the worksheet or a speci ed range in a format called table format so that table processors such as tbl, L TEX, or TEX can use the data. a Merge Read in data from the named worksheet le and overwrite corresponding cells. Combine Run macros or combine the data in the designated le into the current worksheet. Directory Sets the current default directory for le operations. How to use the /File commands 1. Type /F. 2. Choose one of the File commands. 3. Select the le you want to use. The action you specify is carried out on the selected le. 13 /FC File Combine What this command does This command imports the speci ed le. If the le contains macros, it executes the macros in the le. If the le contains numeric or character spreadsheet data, it overwrites the data in the current worksheet. How to use this command 1. Type /FC. 2. Xspread displays this prompt: >> merge "macro_file" ] "/dir1/.../dirn/ where /dir1.../dirn represent the directory path set using the File Directory command. If no directory has been set, xspread displays the home directory path. 3. Type in the name of the le that you want to combine into the worksheet. If the directory is not the one that you want to use, then delete out the unwanted parts of the directory path and replace it with the path that you want. If you do not specify the path, xspread will use the current directory. 4. Xspread then reads the designated le into the worksheet. If it is a macro le, xspread runs the macros. If the le contains numeric or character data, this data overwrites all corresponding cells in the current worksheet. /FD File Directory What this command does This command sets the default directory to use for the other File commands. How to use this command 1. Type /FD. 2. Xspread displays this prompt: >> mdir "macro_directory" ] "/dir1/.../dirn/ where /dir1.../dirn will be the previous directory setting. If no setting has been made yet, xspread displays the home directory path as default. 3. Change the Unix pathname as needed. The pathname must be a valid Unix directory path expression, and the nal / and " may be omitted. After entering the path, press RETURN. /FG File Get What this command does This command retrieves a worksheet le from the disk. There are no restrictions on the le name; it can be any valid Unix le name. Xspread uses the current directory if no directory path is included in the le name. 14 How to use this command 1. Type /FG. 2. Xspread displays this prompt: >> get "source" ] "/default_path/default_file 3. Enter the le name enclosed in double quotes. Xspread automatically supplies the opening quote, along with the default path and le name. After modifying the le name, press RETURN. /FM File Merge What this command does This command merges the speci ed le with the current worksheet. The speci ed le overwrites the data in the current worksheet. How to use this command 1. Type /FM. 2. Xspread displays this prompt: >> merge "merge_file" ] "/dir1/.../dirn/ where /dir1: : : /dirn represent the directory path set using the File Directory command. If no directory has been set, xspread displays the home directory path. 3. Type in the name of the le that you want to merge into the worksheet. If the directory is not the one that you want to use, then delete out the unwanted parts of the directory path and replace these parts with the path that you want. If you do not specify the path, xspread will use the current directory. 4. Xspread then reads the designated le into the worksheet. Data from the merged le overwrites all corresponding cells in the current worksheet. /FP File Put What this command does This command saves the current worksheet or a speci ed range to the disk. There are no restric- tions on the le name; it can be any valid Unix le name. Xspread uses the current directory if no directory path is included in the le name. How to use this command 1. Type /FP. 2. Xspread displays this prompt: >> put "dest" range ] "/default_path/default_file 15 3. Enter the name of the le enclosed in double quotes. Xspread automatically supplies the opening quote, along with the default path and le name. Optionally, you can add a range speci cation after the closing quote. After modifying the path/ le name, press RETURN. If you specify a range, xspread saves only the indicated range in the named le instead of the whole worksheet. /FT File Table What this command does This command writes out the worksheet or a speci ed range into a le with delimiters suitable for processing by the tbl, L TEX, or TEX table processors. The tblstyle option controls the delimiters a used. How to use this command 1. Type /FT. 2. Xspread displays this prompt: >> tbl "dest" range ] "/default_path/default_file 3. Enter the name of the le enclosed in double quotes. Xspread automatically supplies the opening quote, along with the default path and le name. Optionally, you can add a range speci cation after the closing quote. After modifying the path/ le name, press RETURN. If you specify a range, xspread saves only the indicated range in the named le instead of the whole worksheet. /FW File Write What this command does This command prints the worksheet or a speci ed range out to a le in the format that it appears on the screen. How to use this command 1. Type /FW. 2. Xspread displays this prompt: >> write "dest" range ] "/default_path/default_file 3. Enter the name of the le enclosed in double quotes. Xspread automatically supplies the opening quote, along with the default path and le name. Optionally, you can add a range speci cation after the closing quote. After modifying the le name, press RETURN. If you specify a range, xspread saves only the indicated range in the named le instead of the whole worksheet. 16 G Graph What the /Graph commands do These commands create di erent kinds of graphs from worksheet data. The Graph command provides the following options: Type Selects the type of graph to create (i.e. line, bar, stacked bar, XY, or pie). X Selects X axis data range. A, B, C, D, E, F Set up to six data ranges for the Y axis of a line, bar, stacked bar, or XY graph or for each slice of a pie graph. Reset Clears (resets) all of the parameters for graphs. View Displays the current graph on the screen. Options Allows you to specify legends, formats, titles, scales, and other special format options. Sample Allows you to create and view a sample graph with a sample data set without a ecting the current graph de nitions. How to use the /Graph commands 1. Type /G 2. Type T and select what type of graph you want to make. 3. Type X and specify the data range for the X axis. 4. Type A through F to specify up to 6 data ranges for the Y axis. 5. Type V to view the graph. 6. Type O to go to Options menu. 7. Type S to create a sample graph. 8. Type Q to exit the Graph menu and return to ready mode. /G A-F Graph A-F (Data Ranges) What this command does This allows you to designate up to 6 data ranges for plotting on the Y axis. How to use this command 1. Type a letter from A through F from the Graph menu. 2. Xspread will give you this prompt (where x represents one of the ranges A-F): Input column label for range x -- 2 character max.: Respond with the column address of the range for the Y axis. 17 3. Xspread will give you this prompt (where x represents one of the ranges A-F): Input starting row number, 200 max., for range x: Respond with the row number for the start of the range for the Y axis. 4. Xspread will give you this prompt (where x represents one of the ranges A-F): Input the ending row number >= nn for the range x: Here, nn is the row number that you just speci ed in response to the previous prompt. Respond with the row number for the end of the range for the Y axis. 5. If you want to specify more ranges, use this command again with a di erent letter for the next range. /GO Graph Options What these commands do These commands allow you to add enhancements to your graph. The options remain in e ect until you change them, exit the program, or load a di erent worksheet. Legend Adds text describing each Y axis data range. Format De nes how graph information will be presented. Titles Adds titles at the top of the graph and along the X and Y axes. Grid Adds horizontal and/or vertical lines to your graph. Scale Sets the upper and lower limits for the graph and the number of X axis data points to skip. How to use these commands 1. Type O from the Graph Option menu. 2. Select a graph option from the menu. /GOF Graph Options Format What these commands do These commands control the use of symbols and lines in a line or XY graph. The default setting is for both symbols and lines. Line Connects data points with a line. Symbol Assigns a symbol for each data point in a range with a di erent symbol for each range (A-F). Both Uses both lines and symbols. Neither Uses neither lines nor symbols but displays data labels if they are de ned. 18 How to use these commands 1. Type F from the Graph Options menu. 2. Select a single data range or the entire graph. These are your choices: Graph A B C D E F 3. Choose a format for the speci ed range. The choices are: Lines Symbols Both Neither 4. Continue choosing ranges or formats as desired. 5. To exit the Graph Format menu, press the ESC key. /GOG Graph Options Grid What this command does This command adds or clears grid lines from all graphs except the pie graph. You can choose: Horizontal Draw horizontal grid lines only. Vertical Draw vertical grid lines only. Both Draw both horizontal and vertical grid lines. Clear Do not draw any grid lines. The default setting is no grid lines (clear). How to use the commands 1. Type G from the Graph Options menu. 2. Set the grid option you want. 3. Xspread returns you to the Graph Options menu. When you display the graph, it will have the grid lines that you speci ed. /GOL Graph Options Legend What this command does This command displays descriptive text as a legend for each Y axis data range. How to use the command 1. Type L from the Graph Options menu. 2. Specify the data range (A-F) to be identi ed with a legend. This prompt is displayed: Enter the legend for x : where x is one of \A", \B", \C", \D", \E", or \F". 19 3. Type the legend you want to identify the range with and then press RETURN. You can use up to 39 characters. 4. Xspread then returns you to the Graph Options Legend menu. To exit the Graph Options Legend menu, press the ESC key. If you want to create more than one legend, select another menu item. When you display the graph, it will have the legend that you speci ed. CAUTION: When you select a legend data range, xspread does NOT retain any previously de ned legend for that data range. Instead, xspread erases any previously de ned legend. /GOS Graph Options Scale What these commands do These commands automatically set the scales on the X and Y axes, or let you set them manually. (Scaling does not apply to pie graphs.) They also let you set the \skip factor" for the X axis labels. If you select automatic scaling, the graph is adjusted to include all points in each data range. If you select manual scaling, you specify the upper and lower limits for the axis. You can also set the skip factor for the X axis labels. The skip factor directs xspread to use only every nth value in the X data range as labels on the X axis. X Axis Selects the options for scaling data along the X axis. Y Axis Selects the options for scaling data along the Y axis. Skip Select the skip interval for data labels on the X axis. How to use these commands 1. Type S from the Graph Options menu. 2. Select the desired scaling option. 3. To exit from this menu or any submenu, hit the ESC key. /GOSS Graph Options Scale Skip What this command does This command lets you set the skip factor for displaying X range labels or values along the X axis of the graph. How to use this command 1. Type S from the Graph Options Scale menu. 2. Type the desired skip factor and press RETURN. 20 /GOSX Graph Options Scale X-axis /GOSY Graph Options Scale Y-axis What these commands do These commands scale the X and Y axes. You can specify whether the ranges which are displayed are automatic (i.e. controlled by xspread) or manual (i.e. you decide the low and high limits for the axis displays). If you select manual scaling, you can also specify the lower and upper limits that you can display along the X axis (in XY graphs) and along the Y axis (in all graphs except pie graphs). These commands are independent of each other. You can have automatic scaling on one axis and manual scaling on the other if you so choose. Here are the options: Automatic Speci es that scaling will be automatically controlled by xspread. This is the default option. Manual Speci es that scaling will be controlled by the user. Lower Speci es the lower limit for manual scaling. Upper Speci es the upper limit for manual scaling. How to use these commands 1. Type X or Y from the Graph Options Scale menu. 2. Select and complete the entries for an item. /GOSXA Graph Options Scale X-axis Automatic /GOSYA Graph Options Scale Y-axis Automatic What these commands do These options specify that xspread controls the scaling along the respective axis. Automatic scaling is the default. How to use these commands Type A from the /GOSX or /GOSY menus. /GOSXL Graph Options Scale X-axis Lower /GOSYL Graph Options Scale Y-axis Lower What these commands do These commands set the lower limit along the X axis or Y axis when manual scaling is in use. How to use these commands 1. Type L from the /GOSX or /GOSY menus. 2. Type the lower limit and press RETURN. 21 /GOSXM Graph Options Scale X-axis Manual /GOSYM Graph Options Scale Y-axis Manual What these commands do These options specify that you control the scaling along the respective axis. Xspread will use the values in the Lower and Upper limit settings. How to use these commands Type M from the /GOSX or /GOSY menus. /GOSXU Graph Options Scale X-axis Upper /GOSYU Graph Options Scale Y-axis Upper What these commands do These commands set the upper limit along the X axis or Y axis when manual scaling is in use. How to use these commands 1. Type U from the /GOSX or /GOSY menus. 2. Type the upper limit and press RETURN. /GOT Graph Options Titles What this command does This command lets you de ne titles for the X and Y axes and for the top of the graph. First Places a centered title at the top of the graph. Second Places a centered title under the rst title line. X Places a label below the horizontal (X) axis. Y Puts a label beside the vertical (Y) axis. How to use this command 1. Type T from the Graph Options menu. 2. Specify the position of the title. This prompt is displayed: Enter xxxxx title : where xxxxx is one of \First", \Second", \X", or \Y". 3. Type the title you want to use and then press RETURN. You can use up to 39 characters. 4. Xspread returns you to the Graph Options Titles menu. To exit the Graph Options Titles menu, press the ESC key. If you want to create more than one title, select another menu item. When you display the graph, it will have the titles that you speci ed. 22 CAUTION: When you select a title position, xspread does NOT retain any previously de ned title for that position. Instead, xspread erases any previously de ned title. /GR Graph Reset What this command does This command resets (clears) graph parameters. How to use the command 1. Type R from the Graph menu. Xspread displays this submenu: Graph X A B C D E F Graph Resets all graph parameters. X-F Resets parameters for a speci ed range of values. 2. Select an option from the submenu. Xspread removes the parameters without con rmation. 3. To exit the Graph Reset menu, press the ESC key. /GS Graph Sample What this command does This command creates a new graph menu, similar to the previous one minus the Sample option. While in this menu, a completely di erent graph can be created and viewed, but after leaving this menu the graph will return to it's previous state. /GT Graph Type What this command does This command selects the type of graph to be created. Line Shows the data for each range as coordinates connected by lines. Usually this type of graph plots changes over time. Bar Shows the value of the data as vertical bars. Usually this type of graph compares relation- ships of di erent values. XY Displays the Y values plotted versus a series of X values. It is usually used to plot the results of an equation. Stacked-Bar Shows the values of data as stacked vertical bars. Pie Displays each data value as a segment of a circle. Only one data range can be displayed per pie graph. 23 How to use this command 1. Type T from the Graph menu. Xspread displays this submenu: Line Bar XY Stacked-bar Pie 2. Select the graph type you want to use. /GV Graph View What this command does This command displays the current graph. How to use this command Type V from the Graph menu. Xspread displays the graph in a separate window on the X Windows terminal. Press any key or mouse button (with the mouse pointer in the graph window) to return to the Graph menu. /GX Graph X What this command does This command sets the range of cell labels for the horizontal axis for line, bar, and stacked bar graphs, or for each slice of a pie graph. Numbers and formulas can also be used as labels. For XY graphs, it sets the data range. Ranges comprising blocks of several rows and columns are plotted columnwise. How to use it 1. Type X from the Graph menu. 2. Xspread will give you this prompt: Input column label for range X -- 2 character max.: Respond with the column address of the range for the X axis. 3. Xspread will give you this prompt: Input starting row number, 200 max., for range X: Respond with the row number for the start of the range for the X axis. 4. Xspread will give you this prompt: Input the ending row number >= nn for the range X: Here, nn is the row number that you just speci ed in response to the previous prompt. Respond with the row number for the end of the range for the X axis. Matrix /M What the /Matrix commands do Each command does a particular function with a matrix. Transpose Transposition of a Matrix. 24 Addition Addition of two Matrixs. Subtraction Subtraction of two Matrixs. Multiplication Multiplication of two Matrixs. Inversion Inversion of a Matrix. How to use the /Matrix commands 1. Type /M. 2. Choose the desired option. 3. Set the option to the desired function. 4. Xspread performs the function and pops out of the menu. /MT Matrix Transpose What this command does This function performs the Transposition of a matrix. How to use this command 1. Type /MT. 2. Xspread asks the user for a speci ed cell range to transpose. Enter in the distination range followed by a space and the source range. /MA Matrix Addition What this command does This function performs the Addition of two matrixs. How to use this command 1. Type /MA. 2. Xspread asks the user to specify the range of the rst matrix. Press and then enter in the second matrix. Xspread then asks for a destination range to place the result. /MS Matrix Subtraction What this command does This function performs the Subtraction of two matrixs. How to use this command 1. Type /MS. 2. Xspread asks the user to specify the range of the rst matrix. Press and then enter in the second matrix. Xspread then asks for a destination range to place the result. 25 /MM Matrix Multiplication What this command does This function performs the Multiplication of two matrixs. How to use this command 1. Type /MM. 2. Xspread asks the user to specify the range of the rst matrix. Press and then enter in the second matrix. Xspread then asks for a destination range to place the result. /MI Matrix Inversion What this command does This function performs the Inversion of a matrix. How to use this command 1. Xspread asks the user for a speci ed cell range to invert. Enter in the distination range followed by a space and the source range. 2. Xspread asks /O Option What the /Option commands do These commands set various worksheet options. Auto Sets the recalculation mode to automatic or manual recalculation. Numeric Sets the cell entry mode to quick or standard numeric input. Top Shows or hides the top line display on the screen. Cell Shows or hides the highlighting of the current cell. Encrypt Selects whether the writing of worksheet les and list les should be encrypted. PreScale Selects whether numeric entries should be automatically scaled by .01 when entered. Ext Selects whether external functions are enabled. Set Allows the setting of other options, i.e. recalculation order, the number of recalculation iterations, and the table format for the /FileTable command. How to use the /Option commands 1. Type /O. Choose the desired option. 2. 3. Set the option to the desired setting. Xspread sets the option to the desired setting and returns to ready mode. 4. 26 /OA Option Auto What this command does This command sets the recalculation mode to automatic or manual recalculation. How to use this command 1. Type /OA. 2. Xspread toggles the recalculation mode. If the recalculation mode was previously automatic recalculation, it is set to manual recalculation. If the recalculation mode was previously manual recalculation, it is set to automatic recalculation. /OC Option Cell What this command does This command shows or hides the highlighting of the current cell. How to use this command 1. Type /OC. 2. Xspread toggles the cell highlight mode. If the cell highlight mode was previously enabled, it is disabled. If the cell highlight mode was previously disabled, it is enabled. /OE Option Encrypt What this command does This command selects whether the reading and writing of worksheet les should be encrypted. How to use this command 1. Type /OE. 2. Xspread toggles the encryption mode. If encryption was previously enabled, it is disabled. If encryption was previously disabled, it is enabled. /O Option Ext-func What this command does This command selects whether external functions are enabled or disabled. How to use this command 1. Type /O. Do NOT type the E. 2. Use the cursor control keys to move the cursor over to the Ext-func command. Then press RETURN. 3. Xspread toggles the external function mode. If external functions were previously enabled, they are disabled. If external functions were previously disabled, they are enabled. 27 /ON Option Numeric What this command does This command sets the cell entry mode to either quick or standard numeric input. How to use this command 1. Type /ON. 2. Xspread toggles the cell entry numeric entry mode. If the cell entry mode was previously quick numeric entry, it is set to standard numeric entry. If the cell entry mode was previously standard numeric entry, it is set to quick numeric entry. /OP Option PreScale What this command does This command selects whether numeric entries should be automatically scaled by .01 when en- tered. How to use this command 1. Type /OP. 2. Xspread toggles the numeric prescale mode. If the numeric prescale mode was previously standard, it is set to .01 prescale. If the numeric prescale mode was previously .01 prescale, it is set to standard. /OS Option Set What these commands do These commands allow the user to set other options: the recalc ulation order, the number of recalculation iterations, and the table format for the /FileTable command. Xspread does not support natural order recalculation. The Options Set command provides the following options: byrows Recalculate formulas rst by row and then by column. bycols Recalculate formulas rst by column and then by row. iterations=n Set the number of times the worksheet recalculates. tblstyle=xxx Determine the type of delimiter used when the worksheet exports to table format. Valid values for tblstyle are: 0 Colons (:) are used to separate elds, with no tbl control lines. tbl Colons (:) are used to separate elds, with tbl control lines. latex Tables are formatted to meet L TEX requirements. a tex Tables are formatted to meet TEX requirements. 28 How to use these commands 1. Type /OS. 2. Xspread displays the following prompt on the top two rows of the screen: >> set Options: byrows, bycols, iterations=n, tblstyle=(0|tbl|latex|tex) 3. Enter the option that you want to set, then press RETURN. Xspread sets the option to the speci ed condition. Note that with the iterations and tblstyle options, there can be no intervening spaces. /OT Option Top What this command does This command shows or hides the top line display on the screen. How to use this command 1. Type /OT. 2. Xspread toggles the top line display mode. If the top line display was previously o , it is turned on. If the top line display was previously on, it is turned o . /Q Quit What the /Quit command does The Quit command ends the current worksheet session and returns to Unix. If changes have been made since the last time the worksheet was saved, xspread will ask the user whether the most recent changes should be saved prior to exiting. How to use the /Quit command 1. Type /Q. 2. If the worksheet has been changed, xspread displays the prompt: File "filename" is modified, save before exiting? If you type \Y," xspread displays the message: File "filename" written If you type \N", xspread displays nothing. If the worksheet has not been changed, xspread displays nothing. 3. If the worksheet has not been changed, or you answered N in the previous step, xspread returns to Unix. The command interpreter (shell) prompt appears at the bottom of the screen. 29 R Range What the /Range commands do The Range commands a ect a single cell or rectangular group of adjacent cells. Erase Erases cell contents. Value Convert the values of expressions to their result. Copy Copies the contents of the source range into the destination range. Fill Fills all cells in a range with a certain value. De ne Assign and use a name for a group of cells. Show List the currently de ned range names. Unde ne Delete a previously de ned range name from the list of de ned range names. How to use the /Range commands 1. Type /R. 2. Specify the command you want to use. 3. Specify the range. You must type the cell addresses of two opposite corners, separated by a colon(:). /RC Range Copy What this command does This command the values and formulas in the source range into the destination range. Relative cell references are adjusted for the new position. Absolute cell references are not. CAUTION: The Range Copy command overwrites the contents of the destination cells. How to use this command 1. Type /RC. 2. Xspread displays this prompt: >> copy dest_range src_range] 3. Specify the destination range rst and the source range second. Note that this is the opposite order from Lotus 1-2-3 and other commercial spreadsheets. /RD Range De ne What this command does This command allows you to specify a name for a cell or range of cells. Later, you can use this name instead of cell references in formulas. Range names are case sensitive. For example, Original and ORIGINAL are two di erent range names and can refer to two di erent ranges. 30 How to use this command 1. Type /RD. 2. Xspread displays this prompt: >> define string range] " 3. Enter the range name enclosed in double quotes. Follow this with the speci cation of the range that you want to name. /RE Range Erase What this command does This command erases the contents of cells in a speci ed range. How to use this command 1. Type /RE. 2. Xspread displays this prompt: >> erase range] 3. Specify the range. Xspread erases the cells in the range. /RF Range Fill What this command does This command lls a speci ed range of cells with a designated value. All of the cells can have the same value or each succeeding cell can di er from the previous one by a stated increment. How to use this command 1. Type /RF. 2. Xspread displays this prompt: >> fill range start inc] 3. Specify, in order, the range to be lled, the value to use in the rst cell, and the amount by which the next cell should di er from the previous cell. If all cells are to have the same value, the increment should be zero (0). /RS Range Show What this command does This command shows the user all of the named ranges. 31 How to use this command 1. Type /RS. 2. Xspread displays a list of the currently de ned range names and the ranges that are assigned to those names. This display appears at the bottom of the screen. After all of the range names are displayed, xspread displays this prompt: Press RETURN to continue 3. When you are done looking at the list of range names, press RETURN. Xspread will repaint the current worksheet display. /RU Range Unde ne What this command does This command deletes a range name. It does not delete the cells in the range; it only removes the name of the range. How to use this command 1. Type /RU. 2. Specify the name of the range to be deleted. Xspread deletes the range name. /RV Range Values What this command does This command converts the results of formulas to the values that they produce at the time that the command is executed. How to use this command 1. Type /RV. 2. Xspread displays this prompt: >> value range] 3. Specify the range. Xspread then substitutes the values of the formulas for the formulas. There will be no apparent change in the screen display. 32 Function Reference Functions are built-in formulas which can be used by themselves or as part of formulas in xspread. They begin with an @ symbol which is followed by the function name. Any necessary arguments are enclosed in parentheses, immediately following the function name. The general form of a function reference is: @FUNCTION(argument1,argument2,: : : ,argumentN) Argument types date Unix style serial day number. This is the number of seconds since midnight on January 1, 1970. format A character string containing a valid C language format speci cation for converting character expressions to numeric and vice versa. i The interest rate per period on a loan or investment, expressed as a decimal fraction. n Any integer. pmt The payment made at the end of each term of a loan or investment. position An integer value specifying the position inside a character string. pv The present value of a series of payments. The original amount of a loan or investment. range A range name or cell address. term The number of payment period over the life of a loan or investment. string A character string enclosed in quotes or the cell address of a label. x,y Double precision oating point numbers or cells containing such numbers. Functions by type Date and Time Functions @DATE @HOUR @MONTH @SECOND @MINUTE @NOW @YEAR @DAY Financial Functions @FV @PMT @PV Lookup Functions @INDEX @LOOKUP @STINDEX Mathematical Functions @CEIL @HYPOT @MIN @RND @EXP @LN @NVAL @SQRT @FABS @LOG @PI @FLOOR @MAX @POW Special Functions @EXT Statistical Functions @AVG @MAX @PROD @SUM 33 @COUNT @MIN @STDDEV String Functions @EQS @STON @SUBSTR @FMT @SVAL Trigonometric Functions @ACOS @ATAN2 @RTD @ASIN @COS @SIN @ATAN @DTR @TAN Alphabetical Function Reference @ACOS(x) This function returns the arc cosine, i.e. the angle in radians whose cosine is x. The argument x must be in the range -1 to 1. The angle is in the range from 0 to . @ASIN(x) This function returns the arc sine, i.e. the angle in radians whose since is x. The argument x must be in the range -1 to 1. The angle is in the range from =2 to =2. @ATAN(x) This function returns the arc tangent, i.e. the angle in radians whose tangent is x. The angle is in the range =2 to =2. @ATAN2(x,y) This function returns the arc tangent, i.e. the angle in radians whose tangent is y=x. The angle is in the range to . This function distinguishes between angles that lie in the rst and third quadrants and those that are in the second and fourth. @AVG(range) This function returns the average of the values in the given range. If the range contains blank cells, they are ignored. @CEIL(x) This function returns the smallest integer which is not less than x. @COS(x) This function returns the cosine of x. The argument x must be in radians. @COUNT(range) This function returns the number of cells in the range which are not empty. @DATE(date) This function converts the Unix style date and time serial number to a character string repre- senting the date and time. The character string is in the form: Nnn Mmm dd hh:mm:ss yyyy where Nnn is the name of the day of the week Mmm is name of the month of the year dd is the day of the month hh:mm:ss is the 24-hour time giving hours, minutes, and seconds yyyy is the year @DAY(date) This function converts the date serial number to the day of the month. @DTR(x) This function converts the angle measurement x in degrees to radians. 34 @EQS(string1,string2) This function compares the values of two string expressions. If string1 has the same value as string 2, then it returns 1. Otherwise, it returns 0. @EXP(x) This function returns the value of e (2.718281828...) raised to the power of x. @EXP is the inverse function of @LN. @EXT(string,x) This function allows the user to call external functions from inside a spreadsheet. The external function must be a valid program that Unix can run when the function is called. String contains the program command line that is passed to the Unix command interpreter. x is a numeric value which is passed to the function named in string. the value of x is converted to character format and concatenated to the end of string before the command interpreter is called. The result of @EXT is a character string which contains the rst line which the external program prints to standard output. Any additional output to standard output or any output at all to standard error will mess up the screen. @EXT will return a null string if external functions are disabled, string is null, or the attempt to run the command fails. @FABS(x) This function returns the absolute value of the number speci ed by the argument. The absolute value is either zero or the positive value of the number. @FLOOR(x) This function returns the largest integer which is less than or equal to the value of the argument. @FMT(format,x) This function converts the argument x to a character string using the format speci cation given in format. The argument format must be a character string containing a valid C language style format speci cation. Valid format speci cations are: %ew.d %Ew.d %fw.d %gw.d %Gw.d In each of these, w gives the total width of the eld in characters and d gives the number of characters to the right of the decimal point. @FV(pmt,i,term) This function returns the future value of an ordinary annuity with the payment made at the end of each term, at a xed interest rate. The arguments are the periodic payment amount, the interest rate per term, and the number of terms. The period for expressing the interest rate and number of terms must be the same. For example, if the interest rate is monthly, the number of terms is the number of months. @HOUR(date) This function returns the hour from a date serial number. The hours are the number of hours since midnight. Thus, 0 represents midnight and 23 represents 11 p.m. @HYPOT(x,y) px2 + y2. This function returns the length of the hypotenuse of a right triangle, i.e. @INDEX(n,range) This function returns the numeric contents of a cell speci ed by the index number n and the range. range is any single row or column in the worksheet. The range cells are numbered from 1 to n, starting with the leftmost cell in the row or the topmost cell in the column. @LN(x) This function returns the natural logarithm (base e) of x. x must be a value greater than 0. @LOG(x) This function returns the common logarithm (base 10) of x. x must be a value greater than 0. @LOOKUP(x,range) 35 @LOOKUP(string,range) These functions return the contents of a cell from a table. The table can be either two rows or two columns. The numeric function compares the value of x to the table located in the row or column range. The function searches the row or column for the last value less than or equal to x. If range is a row, the function returns the value in the next row and the same column. If range is a column, the function returns the value in the same row and the next column. The string function compares the value of string to the table located in the row or column range. The function searches the row or column for and exact string match. If range is a row, the function returns the value in the next row and the same column. If range is a column, the function returns the value in the same row and the next column. @MAX(range) @MAX(x1,x2,...) These functions return the largest value speci ed by the arguments. The arguments can be either a single range or a list of numeric expressions separated by commas. @MIN(range) @MIN(x1,x2,...) These functions return the smallest value speci ed by the arguments. The arguments can be either a single range or a list of numeric expressions separated by commas. @MINUTE(date) This function returns the number of minutes since the last whole hour from a date serial number. The minutes are represented as 0 to 59. @MONTH(date) This function returns the number of the month from a date serial number. The months are represented as 1 (January) to 12 (December). @NOW This function returns the serial day number of the current date and time. @NVAL(string,n) This function returns the numeric value of the cell speci ed by the arguments. The string ar- gument speci es the column (\A", \B", etc.) and the argument n speci es the row number. If either of the arguments are outside of the worksheet limits or the cell has no numeric value, the function returns 0. @PI This function returns the value of (3.141592654...). @PMT(pv,i,term) This function returns the payment for an ordinary annuity with the payment made at the end of each term. The arguments are the principal present value of the loan amount, the periodic interest rate, and the number of terms (usually months) for paying o the loan. The period for expressing the interest rate and number of terms must be the same. For example, if the interest rate is monthly, the number of terms is the number of months. @POW(x,y) This function returns the result of x raised to the power y , i.e. xy . x must be nonnegative. @PROD(range) This function returns the product of all the nonblank cells in the given range. @PV(pmt,i,term) This function returns the present value of an ordinary annuity with the payment made at the end of each term, at a xed interest rate. The arguments are the periodic payment amount, the interest rate per term, and the number of terms. The period for expressing the interest rate and 36 number of terms must be the same. For example, if the interest rate is monthly, the number of terms is the number of months. @RND(x) This function returns the value that round o x to the nearest integer. @RTD(x) This function converts the angle measurement x in radians to degrees. @SECOND(date) This function returns the number of seconds since the last full minute from a date serial number. @SIN(x) This function returns the sine of x which is an angle in radians. @SQRT(x) This function returns the square root of x. x must be nonnegative. @STDDEV(range) This function returns the sample standard deviation of the cell values in range. @STINDEX(n,range) This function returns the character string contents of a cell speci ed by the index number n and the range. range is any single row or column in the worksheet. The range cells are numbered from 1 to n, starting with the leftmost cell in the row or the topmost cell in the column. @STON(string) This function converts string to its numeric value. string must be a valid numeric expression. @SVAL(string,n) This function returns the string value of the cell speci ed by the arguments. The string argument speci es the column (\A", \B", etc.) and the argument n speci es the row number. If either of the arguments are outside of the worksheet limits or the cell has no string value, the function returns a null string. @SUBSTR(string,position1,position2) This function returns the characters from position1 through and including position2 from the designated string. The rst character in string is at position number 1. If position 2 is greater than the length of the string, position2 is the length of the string. If position1 is less than 1 or greater than position2, the function returns the null string. @SUM(range) This function returns the sum of all the nonblank cells in the given range. The function ignores empty cells and treats labels as 0. @TAN(x) This function returns the tangent of x which is an angle in radians. @YEAR(date) This function returns the year from a date serial number. Valid years start with 1970. The latest valid year is system dependent. 37