ProfitPress Mega CDROM2 Shareware Freeware (MSDOS)(1992)(Eng)

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

/ ProfitPress Mega CDROM2 …eeware (MSDOS)(1992)(Eng) / ProfitPress-MegaCDROM2.B6I / APPS / BUSINESS / INSTACA3.ZIP / FILES.LZH / INSTA.DOC < prev next >

Wrap

Text File | 1991-05-31 | 370.8 KB | 9,241 lines

InstaCalc (tm) Shareware User Manual Version 3 (C) Copyright FormalSoft 1986-91 All Rights Reserved No part of this documentation may be reproduced in whole or in part without the prior written permission of FormalSoft. Trademarks: FormalSoft and InstaCalc are trademarks of FormalSoft. All other brand and product names are trademarks or registered trademarks of their respective companies. FormalSoft * PO Box 1913 * Sandy, UT 84091 * 801-565-0971 WELCOME TO INSTACALC Thank you for evaluating InstaCalc, our PC Magazine Editor's Choice award winning spreadsheet. Although InstaCalc is extremely powerful, we are certain you will find it to be the easiest spreadsheet you have ever used. We encourage you to pass copies of this program on to your friends and associates, so they too can see the power and ease of InstaCalc. LICENSE AGREEMENT InstaCalc is distributed as Shareware. It is not free software, nor is it in the public domain. You are granted a limited license to use InstaCalc on a trial basis. If you want to continue using InstaCalc, you should register with FormalSoft using the form provided on the next page. A single registered copy of InstaCalc may be used in any situation where only one copy of InstaCalc is running at a time, such as: - by a single user on all his/her machines, such as a work machine and a home machine (where the user won't be running multiple copies at the same time). - or on a single machine used by multiple users (where only one user can use InstaCalc at a time). If InstaCalc will be used by more than one user at a time, then a copy must be purchased for each machine. We offer site licenses and quantity discounts. Call us for details. You are also granted a limited license to copy and distribute InstaCalc for evaluation by others on the following conditions: - You may NOT charge anything for InstaCalc itself. You may charge a copying fee (not to exceed $8) to cover disks and handling. - You may NOT copy the printed documentation in any manner or form. - You may NOT bundle InstaCalc with any other product without prior written permission from FormalSoft. - You may NOT distribute versions of InstaCalc which have been modified in any way. - You must include ALL of the files which are distributed with InstaCalc. These files must be ARCHIVED together if you place them on an electronic bulletin board. 1 Registration Form ----------------- TO ORDER: 1. Send this form & payment to: FormalSoft InstaCalc Registration P.O. Box 1913 Sandy, UT. 84091-1913 2. Or give us a call at: 801-565-0971 (VISA/MC/AMEX) I want to receive the most recent version of InstaCalc, the printed manual, and to be eligible for technical support. Please send me ____ copies of InstaCalc. I have enclosed $49 for each copy ordered, plus $5 shipping and handling. * Utah residents please add 6.25% sales tax. * Foreign orders add $10 for air mail. Amount enclosed:_____________ * We also offer quantity discounts and site licenses. Call us for details. Disk size: [ ] 5.25" [ ] 3.5" Name:________________________________________________________________ Company:_____________________________________________________________ Address:_____________________________________________________________ City, State, Zip:____________________________________________________ Country:_____________________________________________________________ [ ] CHECK (US $ drawn on US bank only) [ ] VISA/MC [ ] AMEX Credit card #_______________________________ Exp date_______________ Signature_____________________________________________________________ Where did you get your copy from?____________________________________ 2 INSTACALC FEATURES InstaCalc is a state of the art spreadsheet, extremely powerful yet easy to learn and use. **************************************************************** * And InstaCalc is a PC Magazine Editor's Choice award winner! * **************************************************************** InstaCalc can be run either as a standalone spreadsheet, or as a pop up spreadsheet which can cut and paste your spreadsheet data with other programs. Below are just a few of InstaCalc's capabilities. Pop up option uses less than 15K RAM Cut and paste with other programs Direct import and export of: 1-2-3 files dBase files DIF files ASCII files Powerful macro environment Full application language for/next, repeat/until if/else/endif windowing commands moving bar menu systems access to DOS/printer Macro editor Debugging single stepping conditional breakpoints Macro recorder Business graphics Expanded memory support File encryption File linking 100 built-in @functions Goal seeking Auditing EGA 43 line support Search and replace Database entry forms Sort on up to 9 keys Query worksheet databases Query dBase files Mouse support Visual point & shoot file manager Context sensitive help Menu system shows all levels of the menu on screen Set and view options from full page menus Index to function keys across bottom of screen 3 CONTENTS GETTING STARTED . . . . . . . . . . . . . . . . . . . . . . . . . 2 INSTALLING INSTACALC . . . . . . . . . . . . . . . . . . . . 2 HOW TO RUN INSTACALC . . . . . . . . . . . . . . . . . . . . 2 STARTUP OPTIONS . . . . . . . . . . . . . . . . . . . . . . 3 CUSTOMIZING INSTACALC . . . . . . . . . . . . . . . . . . . 3 USING DATA FROM EARLIER VERSIONS OF INSTACALC . . . . . . . 4 INSTACALC BASICS . . . . . . . . . . . . . . . . . . . . . . . . 5 THE WORKSHEET . . . . . . . . . . . . . . . . . . . . . . . 5 HOW TO GET HELP . . . . . . . . . . . . . . . . . . . . . . 6 MOVING AROUND THE WORKSHEET . . . . . . . . . . . . . . . . 6 ENTERING AND EDITING DATA . . . . . . . . . . . . . . . . . 8 HOW TO ENTER AND USE FORMULAS . . . . . . . . . . . . . . . 10 RELATIVE AND ABSOLUTE CELL REFERENCES . . . . . . . . . . . 12 DEFINING A RANGE OF CELLS . . . . . . . . . . . . . . . . . 12 BUILT-IN @FUNCTIONS . . . . . . . . . . . . . . . . . . . . 14 USING THE COMMAND MENUS . . . . . . . . . . . . . . . . . . 14 USING THE FUNCTION KEYS . . . . . . . . . . . . . . . . . . 15 SAVING AND RETRIEVING DATA . . . . . . . . . . . . . . . . . 15 USING THE FILE MANAGER . . . . . . . . . . . . . . . . . . . 15 CHANGING YOUR SPREADSHEET'S APPEARANCE . . . . . . . . . . . 16 PRINTING YOUR DATA . . . . . . . . . . . . . . . . . . . . . 17 GRAPHING YOUR DATA . . . . . . . . . . . . . . . . . . . . . 18 RECORDING A MACRO . . . . . . . . . . . . . . . . . . . . . 18 THE INSTACALC TEXT EDITOR . . . . . . . . . . . . . . . . . 19 THE INSTACALC MACRO LANGUAGE . . . . . . . . . . . . . . . . 19 CUTTING DATA FROM OTHER PROGRAMS . . . . . . . . . . . . . . 19 PASTING DATA TO OTHER PROGRAMS . . . . . . . . . . . . . . . 20 REFERENCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 THE FUNCTION KEYS . . . . . . . . . . . . . . . . . . . . . 21 INSTACALC COMMAND MENUS . . . . . . . . . . . . . . . . . . 24 BUILT-IN FUNCTIONS . . . . . . . . . . . . . . . . . . . . . 63 INSTACALC APPLICATIONS . . . . . . . . . . . . . . . . . . . . . 112 CREATING AN APPLICATION . . . . . . . . . . . . . . . . . . 112 RUNNING AN APPLICATION . . . . . . . . . . . . . . . . . . . 113 LANGUAGE DESCRIPTION . . . . . . . . . . . . . . . . . . . . 114 SPECIAL KEY NAMES . . . . . . . . . . . . . . . . . . . . . 116 THE INSTACALC COMMAND ENVIRONMENT . . . . . . . . . . . . . 117 1 GETTING STARTED INSTALLING INSTACALC The InstaCalc disk is in a compressed form to fit on a single disk. An installation program is included which will automatically install InstaCalc on your hard disk or another floppy. To run this program: 1. Turn on your computer. 2. Insert the InstaCalc disk into drive A. 3. Type A: at the DOS prompt and press <Enter>. 4. Type INSTALL and press <Enter>. 5. Respond to the prompts as directed. HOW TO RUN INSTACALC InstaCalc may be run in two different modes; as a standalone spreadsheet, or as a pop up spreadsheet. Standalone To run InstaCalc as a standalone spreadsheet: - Turn on your computer - Change to the directory where the InstaCalc files are - Type INSTA Pop up To run InstaCalc as a pop up spreadsheet: - Turn on your computer - Change to the directory where the InstaCalc files are - Type INSTA /p This will install InstaCalc into memory and return you to the DOS prompt. InstaCalc will then look at every keystroke you type (don't worry, it isn't doing anything to hurt them). InstaCalc is simply looking for a particular key sequence known as the Hot Key. The Hot Key is initially defined to be <Alt>I, but you can change this with the setup program described later. Once you have loaded InstaCalc into memory, you may go about doing whatever you need to do, whether it is running DOS commands or other programs. Then, whenever you need the power of InstaCalc, simply type <Alt>I (or whatever you have defined the Hot Key to be) and InstaCalc will pop into action. InstaCalc should be loaded after memory resident programs which are not called up by a hot key (such as RAM disks, print spoolers, etc). When InstaCalc is run in pop up mode, it will only use 15K of memory, swapping the rest of the program in and out from your hard disk, extended, or expanded memory. When swapping from disk, there will be a delay from the time you press the Hot Key and when the program pops up (and pops down when you are done). The message Swapping in and Swapping out will be displayed at these 2 times. If you want to unload InstaCalc from memory, simply type INSTA /u NOTE FOR WORDPERFECT USERS: Sometimes when popping InstaCalc up through WordPerfect on an EGA or VGA system, the InstaCalc screen will have a strange appearance (such as text in italics, odd colors, etc). This is because WordPerfect sometimes uses special "fonts" on screen, and InstaCalc must use the current "font" when it pops up through WordPerfect. There are two ways around this: (1) In WordPerfect, go into the setup screen, and set the Colors/Fonts/Attributes to "Normal Font Only". or (2) Run the InstaCalc SETUP.EXE program, and make sure the colors you select for the various parts of the screen come from the left 8 columns of the color selection box (the right 8 columns of colors are the ones that cause the problems). STARTUP OPTIONS InstaCalc provides a variety of options you can use when you first start up the program. Switches InstaCalc provides the following command line switches: /Pmemory Install InstaCalc as a popup spreadsheet. If you use the /P alone, InstaCalc will allocate 40K of memory for your data. If you want more (or less) memory to store your data in, then add the number of bytes desired immediately after the /P. /U Unload InstaCalc from memory. CUSTOMIZING INSTACALC InstaCalc includes a setup program which allows you to customize the functionality and appearance of InstaCalc on your system. To run the setup program, simply type: SETUP at the DOS prompt. The setup program allows you to change the following settings: Display in B&W Setting this option to YES causes InstaCalc to use 3 black and white whether a color card is detected or not. This is useful when your system has a color card but a monochrome monitor. Currency format This option allows you to select the format to use for numbers and currency. Normally InstaCalc uses a period for the decimal point, and a comma as the separator between thousands, millions, etc. Many countries use these characters in the opposite manner. If you choose to use the comma for the decimal point, then you must use the colon to separate arguments in @functions. You may also select which character (or characters) to use for your currency symbol. You may replace the $ symbol with any other available symbol. You may also set the currency symbol to be after the value rather than before the value. Hot Key This option allows you to select the Hot Key that will be used to pop up InstaCalc in TSR mode. The Hot key may be any of the following key combinations: <Ctrl> with any letter, <Alt> with any letter, <Alt> with a function key (<F1> to <F10>), <Alt> with one of the number keys along the top row, <Alt> with the - or = key. Colors This option allows you to select which colors InstaCalc should use for the different parts of the display. Select the part of the screen you want to change the color of (for example, the axes and cell pointer). A window will pop up which displays all of the colors available. You select the color you want by moving the cursor to that color with the arrow keys and pressing <Enter>. The sample display will be updated to show the new color you just selected. Exit Pressing X will exit from SETUP, and will save your changes to the INSTA.CFG file. USING DATA FROM EARLIER VERSIONS OF INSTACALC If you have data from earlier versions of InstaCalc, you will need to convert them before using them with InstaCalc 3.0. To perform this conversion, use the EXPORT.COM program which comes with the earlier versions to convert your data to a 1-2-3 WK1 file. For example, if you have an InstaCalc file named MYFILE.INS, you would type: EXPORT WK1 MYFILE at the DOS prompt. This will read the InstaCalc file and create a file named MYFILE.WK1. You can then read the newly created MYFILE.WK1 file into InstaCalc 3.0 using the /File Import 1-2-3 command. 4 INSTACALC BASICS THE WORKSHEET A spreadsheet may be thought of as a large electronic worksheet, much like a piece of paper divided into rows and columns, like this: A B C D E F G H --------------------------------- 1| | | | | | | | | |---|---|---|---|---|---|---|---| 2| | | | | | | | | |---|---|---|---|---|---|---|---| 3| | | | | | | | | |---|---|---|---|---|---|---|---| 4| | | | | | | | | |---|---|---|---|---|---|---|---| 5| | | | | | | | | |---|---|---|---|---|---|---|---| 6| | | | | | | | | |---|---|---|---|---|---|---|---| 7| | | | | | | | | --------------------------------- An InstaCalc worksheet has 256 columns and 4096 rows. In order to distinguish columns from each other, InstaCalc assigns a letter of the alphabet to each column. The first 26 columns are labeled with the letters A through Z. Since there are 256 columns but only 26 letters, the remaining columns must use 2 letters to identify them. InstaCalc assigns each of the rows in a worksheet a number from 1 to 4096. The top row in a worksheet is row 1 and the bottom row is row 4096. The area where a row and a column cross is known as a cell. Each cell in the Worksheet is identified by the letter (or letters) of the column it is in, and the number of the row it is in. For example, the cell that is in column R and row 29 is named R29 (the column letters always come before the row number). Each one of the cells may contain some text, a number, or a formula. Since it is impossible to view all 4096 rows and 256 columns at once, the screen display provides a window looking into the Worksheet. The letters of the columns which are on the screen are displayed in a horizontal bar above the columns, while the numbers of the rows which are on the screen are displayed in a vertical bar to the left of the rows. A status line is located in the upper left corner of the screen and looks something like this: A1: (W9) BLANK <Default> Each component of the status line tells you certain information about the current cell. A1 tells you that the cell you are currently at is in column A and row 1. (W9) tells you that the column you are in is 9 characters wide. 5 BLANK tells you that the cell you are at is currently blank. This item tells you what is currently in the cell: BLANK - a blank cell TEXT - a line of text NUMBER - a number FORMULA - a formula with a numeric result TXTFORM - a formula with a text result FORMAT - a blank cell which contains formatting information ERROR - a formula which has an error value The bottom line of the display is an index to the function keys <F1> through <F10> so you don't have to memorize or look them up. Pressing <Ctrl> changes this index to reflect the operation of the function keys when used in combination with the <Ctrl> key. HOW TO GET HELP InstaCalc provides extensive online help for its features. To call up this help, simply press the <F1> Help key at any point in the program. A help screen will pop up which provides information about the operation you are attempting. InstaCalc's help is context-sensitive. This means that InstaCalc is always keeping track of what you are doing so that it can provide the proper help screen when you ask for it. For example, if you press <F1> when you are trying to copy a cell, then a help screen which explains that feature will pop up. InstaCalc's help is also interactive. This means that the help screen which pops up will also list several other help screens which are related to what you are doing. You may view one of these related help screens by simply using the <Left> and <Right> keys (or moving the mouse) to move the highlight bar to the name of the help screen desired, and then pressing the <Enter> key or left mouse button, or by simply pressing the first letter of the desired help screen's name. You may also call up a list of help screens called the <HELP INDEX> by pressing the <F1> key a second time, or by pressing the right button on your mouse. After you have read the help screens that interest you, simply press the <Esc> key (or both mouse keys simultaneously) to return to what you were doing. MOVING AROUND THE WORKSHEET You move from cell to cell by moving the "cell pointer". The cell pointer is a highlighted bar on the screen which "points to" the cell in the Worksheet which is the current cell. The address of the current cell is displayed on the top line of the InstaCalc screen. 6 InstaCalc provides many ways to move the cell pointer. <Up> <Down> <Left> <Right> These keys move the cell pointer up, down, left, or right one cell position. <Home> This key moves the cell pointer to cell A1. <End> This key operates in a different manner than the other movement keys. Pressing <End> does not move the cell pointer itself, but changes the way the <Left>, <Right>, <Up>, <Down>, <PgUp>, <PgDn> and <Home> keys work. If you press the <End> key and then one of the arrow keys or <PgUp> or <PgDn>, then the cell pointer will move in one of the following ways: - If the cell pointer is in a blank cell, it will move in the direction selected to the first non blank cell. - If the cell pointer is in a non blank cell, it will move in the direction selected to the last non blank cell before a blank cell. If you press the <End> key followed immediately by the <Home> key, the cell pointer will move to the cell in the lower right corner of the sheet. If you want to cancel out the effect of the <End> key after you have pressed it, simply press <End> a second time. <Ctrl><Right> This key sequence moves the cell pointer one screen's worth of columns to the right. This key sequence is generated by holding down the <Ctrl> key and pressing <Right>. <Ctrl><Left> This key sequence moves the cell pointer one screen's worth of columns to the left. This key sequence is generated by holding down the <Ctrl> key and pressing <Left>. <PgUp> This key sequence moves the cell pointer up one screen's worth of rows. This key sequence is generated by holding down <Ctrl> and pressing <PgUp>. <PgDn> This key sequence moves the cell pointer down one screen's worth of rows. This key sequence is generated by holding down <Ctrl> and pressing <PgDn>. <F5> This key moves the cell pointer anywhere you tell it to. InstaCalc will ask you which cell you want to go to, and you just type in the cell name and hit <Enter>. In addition to the various movement keys, InstaCalc provides built-in mouse support. If you have a mouse and have installed your mouse driver before running InstaCalc, then you can move the cell pointer by 7 simply moving the mouse in the direction you want to go. ENTERING AND EDITING DATA You can enter numbers or text into any of the cells in the Worksheet. Just move the cell pointer to the cell you want to enter a number or text in, and type the number or text. You may enter up to 240 characters into each cell. The number or text will be entered into the cell as soon as you press <Enter> or one of the movement keys (<Up>, <Down>, <Left>, <Right>, <PgUp>, or <PgDn>). If you finish entering the data using the <Enter> key, the cell pointer will remain positioned at the same cell. If you finish entering the data using one of the movement keys, the data will be entered into the current cell, and then the cell pointer will move to another cell in the direction you specified. Numbers A number can range in value from -10^37 to 10^37, and can have up to 11 significant places. You can enter a number into a cell using either standard notation (like 12.345) or scientific notation (like 1234.5e-2). Text If you enter a line of text which contains more characters than the column is wide, it will spill over into adjacent cells (if the adjacent cells are blank). If the adjacent cells contain data, then the extra characters in the text string will be hidden from view (but they will still be in the cell). If you want to see the entire string, you must widen the column containing the text. The way your text is displayed in a cell depends on the first character of the text. There are four (4) possible characters which can precede your text and which affect the way the text is displayed. These characters are: ' causes the text to be left justified in the cell " causes the text to be right justified in the cell ^ causes the text to be centered in the cell \ causes the next character to be repeated If you enter some text without one of the above special characters as the first character, InstaCalc will automatically place the global justify character on the text for you. You can select or change this global justify character with the /Worksheet Justify command on the main menu. Once the text has been entered into a cell, you may change the justification using either the /Cell Justify or the /Range Justify commands (or use the <F2>Edit function key to enter edit mode and just change the justification character to the one you want). Editing If you need to change an entry in a cell, but don't want to retype the entire thing, InstaCalc provides the ability to edit your entry. Simply move the cell pointer to the cell you want to edit, and press the <F2> Edit key. If there is anything in the cell to edit, InstaCalc will enter its editing mode. Also, if you make an error when entering a 8 formula this editing capability will be called automatically. When you are in edit mode, the following editing keys are available: <Left> moves the cursor one character to the left. <Right> moves the cursor one character to the right. <Home> moves the cursor to the first character. <End> moves the cursor to the last character. <Ctrl><Right> moves the cursor one word to the right. <Ctrl><Left> moves the cursor one word to the left. <BkSp> deletes the character to the left of the cursor and shifts the following characters in the line to the left to fill in the empty space. <Ctrl><BkSp> deletes the entire line. This is not the same as erasing the cell. The cell will still take up memory, but it will contain a blank string. To erase the cell completely, use the /Cell Erase command. <Del> deletes the character underneath the cursor and shifts the following characters in the line to the left to fill in the empty space. <Ins> toggles between insert and overwrite modes. When you are in insert mode, typing a character will insert the new character into the line at the point where the cursor is. The character under the cursor and those to the right of the cursor are shifted to the right to make room for the new character. When you are in overwrite mode, typing a character will replace the character under the cursor with the new character. When you are in insert mode, the cursor will be an underline. In overwrite mode the cursor will be a solid block. 9 HOW TO ENTER AND USE FORMULAS One of the most useful features of any spreadsheet is the ability to perform calculations using the numbers and text entered in other cells. This is done by entering a formula into a cell. To enter a formula, you simply move the cell pointer to the cell you want the formula to be in, type the formula, then press <Enter>. When a cell contains a formula, it will display the value that the formula evaluates to, rather than the formula itself. An InstaCalc formula must begin with an equal sign '=' and may be made up of numbers (or text in some cases), references to values in other cells, built-in functions, and mathematical, logical, and comparison operators. Example Let's say you have a number in cell B5 and another in cell C6, and you would like to add these two numbers and place the result in cell A4. Simply move the cell pointer to A4, and type in the following formula: =B5+C6 (followed by <Enter>). Cell A4 now contains the formula you entered, but it will display the result of the formula. If you later change the number in either B5 or C6, the formula in A4 will be recalculated and the new value will be displayed. You can use the following operators in a formula (a and b can be any number, cell reference, function, or formula): Mathematical a+b Addition a-b Subtraction a*b Multiplication a/b Division a^b Exponentiation Logical a&b And (is true only if a is true AND b is true) a|b Or (is true is a is true OR b is true) ~b Not (false if b is true, and true if b is false) Comparison a=b is true if a is equal to b a<>b is true if a is NOT equal to b a<b is true if a is less than b a>b is true if a is greater than b a<=b is true if a is less than or equal to b a>=b is true is a is greater than or equal to b You can also use the comparison operators to compare text strings with each other. Two strings are considered equal if they are the same length, and contain the same characters in the same order. String comparisons are case insensitive, which means that two letters are considered the same regardless of whether one is uppercase and one is lowercase. For example, the string Test is considered equal to the string test. In addition, the + operator can also be used to concatenate 2 strings. For example, the formula: ="This is"+" a test" returns the string This is a test. 10 If you enter a formula which uses more than one operator, the operations are always performed in a specific order (called precedence). The order of precedence is: 1. Exponentiation ^ 2. Negation - 3. Multiplication and Division * / 4. Addition and Subtraction + - 5. Comparison = < > <= >= <> 6. Not ~ 7. And/Or & | where exponentiation is performed before negation and so on. If you want to change the order in which operations are performed, then you must use parentheses in your formulas. Operations which you place in parentheses will be performed before those outside the parentheses. For example, the formula =2+3*4 evaluates as 14 (not 20) because the multiply has a higher precedence than the addition. If you want the addition to be performed first, then use parentheses to make the equation look like =(2+3)*4 In addition to typing cell references in formulas, you can also enter cell references by pointing to them with the cell pointer. At any point in entering a formula where a cell reference is allowed, you can simply press any of the cell pointer movement keys to begin pointing to a cell or range of cells. The address of the cell which the cell pointer is on will appear in the formula. As you move the cell pointer around, the reference in the formula will be adjusted to reflect the new position of the cell pointer. When the cell pointer is highlighting the cell reference you want, either press the <Enter> key or the character you want to immediately follow the cell reference. If you press the <Enter> key, the reference will be entered and you may continue to enter the formula. If you press another key, the reference and the key you pressed will be entered on the formula line, and you may continue entering the formula. If you change your mind about pointing to a cell reference, simply press the <Esc> key and you will be returned to formula entry without any cell reference being entered. If you have named any cells with the /Range Name command, you may also select a cell or range reference from a list of these references. Simply press the <F3> key when you want to select a named range, and a menu will pop up which lists all the currently defined range names. You can then select the range name you want, and it will be placed into your formula for you. The <F3> key will only pop up the range name list when it is legal to enter a cell or range reference. If you enter a formula which InstaCalc can't evaluate, the formula you 11 entered will be displayed on the command line and the editing facility will be invoked. The cursor will be placed near the part of the formula which InstaCalc didn't like, and you may fix the formula using the editing commands. If you enter (or edit) a formula and the word CIRC appears in the upper right corner of the screen, this means a circular reference exists in your worksheet. A circular reference means that a formula in your worksheet depends on the value of the cell it is in. For example, if cell A1 contains the formula =2*A1, then the formula must use the current value of cell A1 to calculate the next value of cell A1. If you have a circular reference, the status window (<Ctrl><F8>) will tell you which cell it is. RELATIVE AND ABSOLUTE CELL REFERENCES When a formula references another cell, there are two different ways that the cell can be referenced; absolute and relative. The purpose of relative and absolute references is to let InstaCalc know what to do when moving formulas from cell to cell. It is easy to distinguish between an absolute and a relative cell reference. An absolute cell reference uses the $ character in front of each of its coordinates, like $A$1, while a relative reference uses no special characters, like A1. Absolute An absolute cell reference in a formula points to the exact same cell, regardless of where the formula is moved or copied to. Relative A relative cell reference in a formula points to the cell which is a certain number of rows and columns away. If the formula is moved, the new relative cell reference will be adjusted to point to the cell the same number of rows and columns away from the new formula location. It doesn't matter whether a cell reference is absolute or relative when a formula is evaluated; the result will be the same in either case. For example, the formula =2*A1 will have the same value as =2*$A$1. You can have both absolute and relative references in the same equation if you want. DEFINING A RANGE OF CELLS Many of InstaCalc's commands can operate on more than one cell at a time. These commands will usually ask you to define a range of cells to perform the command on. A reference to a range is made up of the cells at opposite corners of the range separated by two periods. For example, the range reference A1..Z20 contains all the cells in the range bordered by columns A through Z and rows 1 through 20. When a command asks you to define a range of cells, there are 5 different options. 12 - You can simply type in the range reference. - You can type in the name of a cell surrounded by square brackets. InstaCalc will read the name of the range out of that cell. This option allows your application programs to indirectly supply a range reference to a command. Example If cell B5 contains the string 'G1..J10, then typing [B5] when you are asked to define a range will cause InstaCalc to use the range G1..J10 for the command. - If you have named a range using the /Range Name command, you may simply type in the name. Example If you assigned the name COSTS to the range G1..J10, then typing COSTS when you are asked to define a range will cause InstaCalc to use the range G1..J10 for the command. - Pressing the <F3> Range key will bring up a menu of all the currently defined range names. You may pick a range from this list by highlighting the name and pressing the <Enter> key. If there are more than 16 range names defined, you may press the <PgUp> and <PgDn> keys to view more range names. - You can highlight the range on screen using the cursor keys. When you are defining a range, all the visible cells in the range will be highlighted on the screen. On the command line you will see either a single cell reference or a range reference. A single cell reference means that an anchor cell has not been selected. In this case, pressing a movement key simply moves the cell pointer to a new cell, but does not highlight (or define) any other cells. If an anchor cell has been selected then all cells between the anchor cell and the current cell are defined (and highlighted if visible). You can use any of the movement keys (including <F5> GoTo) to move the cell pointer when highlighting a range of cells. In addition, the following keys are active when defining a range: <Enter> This key causes all the cells in the range on the command line to be defined. If only a single cell reference is displayed when you press <Enter>, then only that cell will be defined. . The period key sets the anchor to the current cell. If an anchor was already set before you pressed the period, then it is released before the new one is set. <Esc> This key releases the anchor if it is set. Pressing <Esc> when the anchor is not set cancels the range selection, and returns you to whatever you were doing before you were asked to define a range. <F4> This key cycles the range cell references among the absolute and relative types. If no anchor is set then the first coordinate is adjusted. If the anchor has 13 been set then <F4> will adjust the non-anchor reference. BUILT-IN @FUNCTIONS InstaCalc provides built-in functions which you can use in your formulas. These built-in functions each begin with the character @, and are often called at functions. A detailed list of these functions is in Built-In Functions in the reference section of this manual. USING THE COMMAND MENUS When you want to execute a command in InstaCalc, it will usually be through InstaCalc's menu system. A menu is simply a list of commands which you can pick from. Through the menus you can manipulate cells, ranges, or the entire Worksheet, graph or print data, manipulate files, or even temporarily exit to the operating system. To bring up InstaCalc's menu system, you simply press the / key. If you are using a mouse, then you may also press the left mouse button to bring up the menu. The menu system uses a sliding bar to show your choices. There are three ways you can choose an option: - Press the <Left> or <Right> key until the sliding bar is highlighting the option you want. Then press <Enter> to make the choice. The <Home> key can be pressed to highlight the first menu choice, and pressing the <End> key will highlight the last menu choice. - If you are using a mouse, you may move the sliding bar by moving the mouse back and forth. Move the mouse until the sliding bar is highlighting the option you want. Then press the left mouse button to make the choice. - As a shortcut you can simply press the highlighted letter of the desired item (usually the first character) to select that option. A help line appears above the main menu. This line describes the highlighted menu option. When you move the sliding bar to a new item, this help line will change to display a line of help about the newly highlighted item. When you choose an option from the menu, a new submenu will pop up which works the same as the main menu. As each new submenu is chosen, the previous menus remain on-screen, providing you with a "tree" of your commands. To go back one level, simply press the <Esc> key (or the right mouse button). To clear all the menus from the screen at once, press the <BkSp> key (or both mouse buttons simultaneously. 14 A detailed list of menu commands can be found in InstaCalc Command Menus in the reference section of this manual. USING THE FUNCTION KEYS Several of InstaCalc's commands are assigned to one of the 10 function keys instead of or in addition to the command menus. Several other commands are also assigned to one of the function keys with the <Ctrl> key pressed. To execute one of these commands, simply hold down the <Ctrl> key and then press the appropriate function key. SAVING AND RETRIEVING DATA As you are creating and using spreadsheets, your data is stored in the memory of your PC. You should store your spreadsheets on disk periodically throughout the day or session and definitely at the end of each day. In the event of a power outage or if you should forgetfully turn off the machine before a save has been done, the data entered since the last save to disk is lost. Saving You can save your worksheet to a file on disk using the /File Save command. If you just want to save a small part of your spreadsheet, the /File Xtract command will provide that capability. Retrieving When you want to read a file off the disk back into memory, you should use the /File Retrieve command. Importing The /File Import command lets you bring in data from just about any source and use it in your spreadsheet. InstaCalc can import data from Lotus 1-2-3 (WKS and WK1), dBase II/III/III+ , DIF and ASCII files. Exporting InstaCalc can export its data in several different formats, and most software packages are capable of reading at least one of them. InstaCalc can export Lotus 1-2-3 (in two forms, WKS or WK1), dBase II or dBase III or III+, DIF, or ASCII files. USING THE FILE MANAGER InstaCalc provides many features designed to simplify its use. One of these features is the Visual File Manager. The File Manager allows you to manipulate files, directories, and drives by simply pointing and selecting. It may be accessed by two methods. - Any time you are asked for a file name, pressing the <Enter> key without a file name will bring the File Manager forward to assist you. - To access the complete listing of files, select /File Files from the command menu. 15 CHANGING YOUR SPREADSHEET'S APPEARANCE InstaCalc lets you change the way numbers are displayed on the screen by assigning a "format" to cells. You might set a cell to the Currency format, for example, so that the number in that cell will be displayed with a leading dollar sign. Changing the format of a cell does not change the value stored in the cell, only the way it is displayed on the screen (and printouts). Normally, when you enter data into a cell, the cell will take on the global (Worksheet) format. You can see what this format is by pressing <Ctrl><F8> to bring up the status window. The Worksheet format may be changed by using the /Worksheet Format command. The format of an individual cell may be changed using the /Cell Format command, and the format of a range of cells may be changed using the /Range Format command. You should use care when formatting ranges of blank cells, because formatted cells use up memory, even if the cells' contents are blank. A cell may be set to any of the following formats: General The value is displayed with only the necessary number of decimal places. Currency The value is displayed with a preceding dollar sign and a selected number of decimal places. If the value is negative, the value is enclosed in parentheses rather than displayed with a negative sign. There are two sub-options: Comma A comma will be inserted every three whole digits. (Example: $103,297.25) NoComma No comma is inserted in the number. Fixed The value is displayed with a fixed number of decimal places. Values which are very large or very small will appear in scientific notation. Four sub-options are available: Comma A comma will be inserted every three whole digits (Example: 12,345.67). Negative numbers are preceded by a negative sign. NoComma No comma is inserted in the number. Negative numbers are preceded by a negative sign. A () No comma is inserted in the number. Negative numbers are enclosed in parentheses. B (,) A comma will be inserted every three whole digits. Negative numbers are enclosed in parentheses. Percent The value is displayed as a percentage with a fixed number of decimal places. The value is multiplied by 100, and is displayed with a trailing percent sign %. Two sub-options are available: 16 Comma A comma will be inserted every three whole digits. (Example: 1,024%) NoComma No comma is inserted in the number. Science The value is displayed in exponential form, and shows the power of 10 that the number must be multiplied by. For example, 1.78E+07 means 1.78 times 10^7. Time A time number (usually provided by one of the InstaCalc time functions) is displayed in one of two ways. AM/PM The time appears in the a.m. or p.m. form. Thus, 5:00 in the evening appears as 5:00 p.m. Military The time appears as it would on a 24-hour clock. Thus, 5:00 in the evening appears as 17:00. Date A date number (usually provided by one of the InstaCalc date functions) is displayed in one of five ways. dd-mmm-yy June 10, 1960 appears as 10-Jun-60. dd-mmm June 10, 1960 appears as 10-Jun. mmm-yy June 10, 1960 appears as Jun-60. mm/dd/yy June 10, 1960 appears as 06/10/60. month dd, yyyy June 10, 1960 appears as June 10, 1960. Hidden The value will not be displayed on the screen. If you later want to unhide a hidden cell, simply set it to any of the other formats. Label All formulas (rather than their values) will be displayed on screen. Reset Resets the cell to the default format. If the formatted value contains more characters than the width of the column it is in, the cell display will be filled with a row of asterisks like *******. You can fix this condition in one of two ways. - Select a display format for the cell which requires fewer characters to display (or reduce the number of digits after the decimal point to be displayed). - Increase the width of the column so the formatted cell will fit in it. You can do this with the /Cell Width command. PRINTING YOUR DATA InstaCalc allows you to print out the data you have entered and gives you control over what is printed and how the printout looks. You can print your entire worksheet or any portion of it, including border rows and columns. 17 Just define the range to print with the /Print Block command, set any options using the /Print Options command, then send the data to your printer with the /Print Go command. More details on printing is available in the reference section of this manual. GRAPHING YOUR DATA Once your data has been entered InstaCalc can provide you with a graphic representation of that data in only a few keystrokes. For example, if you wanted to print a bar graph of your data: 1. Select /Graph from the main menu to begin. 2. Select the Type option. A menu will appear from which you may select the graph type. Select Bar from the menu. 3. Next, you must tell InstaCalc what data you wish to have graphed. This is done by defining a range of data for each axis. The menu allows you to define the range of data to graph on the X axis and up to 6 data ranges to graph along the Y axis. These ranges are named A through F on the menu. 4. You may add titles, labels, and legends to your graph using the Options selection. 5. You can now view your graph by selecting View from the menu. When you are satisfied with the choices that you have made, you may Print your graph. RECORDING A MACRO Although InstaCalc's macro language is powerful enough to create sophisticated business applications, there are many times when you will want to simply repeat a sequence of keystrokes over and over. InstaCalc's Macro Recorder provides an easy way to create and play back often repeated key sequences. The macro recorder allows you to assign a sequence of keystrokes to a single key, and when you press that single key, it will automatically play back the keystroke sequence for you. To begin recording a macro, you simply press the <Ctrl><F10> key. You then press the key you want to assign the macro to, and the word REC will begin flashing in the upper right corner of the screen. As long as REC is flashing, InstaCalc will be memorizing any further keystrokes you type. When you want to stop recording, just press the <Ctrl><F10> key again. Your macro will be stored in a file whose name corresponds to the key you assigned the macro to. For example, a macro assigned to <Ctrl>T would be named CTRLT.MAC. Later, when you press <Ctrl>T, InstaCalc will look for the macro file CTRLT.MAC, and play back the keystrokes which are saved there. You can assign a macro to just about any key which isn't already defined 18 and used by InstaCalc. For example, you can't redefine the arrow keys to do something else, but you can assign other keys to do the same thing as the arrow keys. THE INSTACALC TEXT EDITOR InstaCalc provides a simple text editor which you can use to create and edit your application programs. This editor can be called up in two different ways. - The /Macro Edit command will ask you for the name of a macro. You may enter a file name, or press <Enter> to select a file from the file manager. You will then be placed in the InstaCalc text editor. - You may press <F5> when you are using the /File Files command to edit the highlighted file. Details on the Text Editor are provided in the /Macro Edit section of this manual. THE INSTACALC MACRO LANGUAGE In addition to the macro recorder, InstaCalc provides a built in programming language which provides additional capabilities such as windowing, menu creation, control structures, and printer and DOS access. An InstaCalc application program simply consists of a text file containing the commands which you would like InstaCalc to execute. You may give your program files any valid DOS name, but the extension must be .MAC (for example MYPROG.MAC). Your application program can consist of keystroke macros, InstaCalc Command Environment (ICE) commands, or both. The InstaCalc Command Environment also provides the ability to debug your macros. For more details on ICE and macros, see the section titled INSTACALC APPLICATIONS. CUTTING DATA FROM OTHER PROGRAMS InstaCalc provides the ability to import data directly from the screen of another program into the worksheet. This allows you to import text and numbers from your word processor, data base manager, or any other type of program. You may import multiple columns off the screen at the same time. When you are in your application program and you want to cut data on the screen into InstaCalc: - Press the Hot Key (InstaCalc must have been loaded before you started up your application program). This will pop up InstaCalc. - Move the cell pointer to the cell where you want the first line of the imported data to be placed. 19 - Press <F7>Cut. This will switch the display back to the screen you want to cut from. - Use the arrow keys to move the cursor to the upper left corner of the block you want to cut, then press the period key. This sets the anchor for the block. - Use the arrow keys to begin marking the block you want to cut. The block will be highlighted on the screen. - Once you have highlighted the column you want to cut, either press <Enter> to cut that column, or press the period key again to set the anchor for another column. - If you choose to cut more than one column off the screen at a time, continue to set the anchor, highlight the column, set the anchor for the next column and so on. Once you have highlighted all of the columns you want to cut, press <Enter> to finish cutting the data from the screen. - Pressing <Esc> at any time will cancel the last selection. PASTING DATA TO OTHER PROGRAMS InstaCalc provides the ability to paste data from the Worksheet directly into another program. This allows you to paste spreadsheet data directly into a word processor document or database record without even having to exit your program. When you want to paste spreadsheet data into another program: - Make sure the cursor in the other program is where you want the exported data to be placed. If you are pasting into your editor or word processor, make sure any "auto indent" feature is turned off, or each line of the pasted data will shift further to the right. - Press the Hot Key (InstaCalc must have been loaded before you started up your application program). This will pop up InstaCalc. - Press <F8>Paste. - Use the arrow keys to move the cursor to the upper left corner of the block you want to paste, then press the period key. This sets the anchor for the block. - Use the arrow keys to begin marking the block you want to paste. The block will be highlighted on the screen. - Once you have highlighted the block you want to paste, press <Enter> to select that block for pasting. - Select the key you want each row terminated with. You may select <Enter>, <Down>, or <Right>. - Pressing <Esc> at any time will cancel the last selection. 20 REFERENCE THE FUNCTION KEYS Several of InstaCalc's commands are assigned to one of the 10 function keys instead of or in addition to the command menus. Several other commands are also assigned to one of the function keys with the <Ctrl> key pressed. To execute one of these commands, simply hold down the <Ctrl> key and then press the appropriate function key. <F1> Help This key calls up context sensitive help. <F2> Edit This key allows you to edit the current cell. <F3> Range This key calls up a menu of defined range names. This key can be used any time you are asked to define a cell or range. You may pick a range from this list by highlighting the name and pressing the <Enter> key. If there are more than 16 range names defined, you may press the <PgUp> and <PgDn> keys to view more range names. <F4> Absolute This key cycles absolute and relative references when you are pointing out cell references with the cursor keys. If no anchor is set then the first coordinate is adjusted. If the anchor has been set then <F4> will adjust the non-anchor reference. <F5> GoTo This key allows you to move the cell pointer to another cell. You will be asked for the name of the cell to move to. For example, if you enter Q23, then the cell pointer will be moved to that cell. If you enter a range name, then the cell pointer will be moved to the cell in the upper left corner of the range. You may also press the <F3> Range key and pick a range name from a menu of existing names. <F6> Search This key allows you to search for some text in a cell, and allows you to move the cell pointer to that cell or replace the text with other text. Find This option allows you to enter the text you want to search for. Replace with This option allows you to enter the text you want to replace the Find text with. Ignore case If you set this option to No, then the Find text must match text in the Worksheet exactly to be considered a match. If this option is Yes, then the Find text will match text in the Worksheet if the letters are the same, even if some are uppercase and some are lower case. Block This option allows you to define the 21 range of cells you want to search. This allows you to limit your search to as small a range of cells as you want. Start This command begins the search and replace operation. InstaCalc will begin searching through the defined range, and will stop when it finds a match. The matching cell and its contents will be displayed, and you will be asked if you want to perform the Replace. There are 4 options. No No, don't perform the replace, but continue searching. Yes Yes, perform the replace, and then continue searching. All Yes, perform the replace on this, and all other matches which you find. This is a global search and replace, and you will not be asked for any further confirmation. GoTo No, don't perform the replace, and just move the cell pointer to this new cell and quit any further searching. If you just want to quit, then you can simply press the <Esc> key. <F7> Cut This key allows you to cut data off the screen of the program you popped up through (if you started InstaCalc with the /p option). See the section titled Cutting Data From Other Programs. <F8> Paste This key allows you to paste data from InstaCalc into the program you popped up through (if you started InstaCalc with the /p option). See the section titled Pasting Data To Other Programs. <F9> Recalc This key recalculates the entire worksheet. This command is only necessary when you have used the /Worksheet Calc Manual command to disable automatic recalculation. <F10> Graph This key displays the currently defined graph. This key is simply a shortcut for the /Graph View command. <^F1> Help This key brings up the Help Index of the help system. <^F2> Debug This key brings up the application program debug menu when you are single stepping through an application program. Simply select Edit from the menu, then type in the name of the cell you want to examine. InstaCalc 22 will display the current contents of the cell, and will allow you to modify those contents if you want. When you press <Enter>, the InstaCalc will resume single stepping through the application. <^F3> Break This key breaks out of a running application. If you press this key when an application program is running, it will abort the application unless the application program has used the #BREAK command to disable breaking. <^F4> Mark This key marks the current cell so that you can later return to it with the <Ctrl><F5> key. For example, if you press <Ctrl><F4> when the cell pointer is on cell Q53, then you can move anywhere in the worksheet and return to cell Q53 by simply pressing the <Ctrl><F5> key. <^F5> ToMark This key returns to the last cell marked with <Ctrl><F4>. <^F6> Files This key calls up the File Manager. You will be asked to enter a mask to tell InstaCalc which files you want to be displayed. Pressing <Enter> will use the Mask *.*, which will display all of the files in the current directory. <^F7> EGA43 This key toggles 43/50 line mode on EGA/VGA monitors. If you have an EGA or VGA monitor, this function allows you to see twice as many rows on the screen as normal. <^F8> Status This key displays a status window. The status window lists the current directory and file name, the amount of memory available, the current date and time, and the current default settings. <^F9> Enter This key toggles Enter mode. This mode is especially useful for repetitive data entry. When you are in Enter mode, pressing the <Enter> key to enter data into a cell will enter that data and them move the cell pointer in the last direction moved. For example, if the cell pointer last moved down, then typing in a number and pressing <Enter> will enter that number into the highlighted cell, and will move the cell pointer down to the next cell. This allows you to use the numeric keypad and the <Enter> key to enter rows or columns of data. <^F10> Learn This key turns the macro recorder on and off. The macro recorder allows you to assign a sequence of keystrokes to a single key, and when you press that single key, it will automatically play back the keystroke sequence for you. If a macro already exists for the key you want to record to, InstaCalc will provide the following options: 23 Replace Replace the existing macro with this new one. Edit Edit the existing macro. Cancel Cancel this operation. INSTACALC COMMAND MENUS When you want to execute a command in InstaCalc, it will usually be through InstaCalc's menu system. A menu is simply a list of commands which you can pick from. Through the menus you can manipulate cells, ranges, or the entire Worksheet, graph or print data, manipulate files, or even temporarily exit to the operating system. To bring up InstaCalc's menu system, you simply press the / key (or press the left button on your mouse). At the top of the InstaCalc screen you will see the following menu. Cell Range Worksheet File Print Data Graph OpSys Quit The menu system uses a sliding bar to show your choices. There are two ways to choose an option: - Press the <Left> or <Right> key until the sliding bar is highlighting the option you want. Then press <Enter> to make the choice. The <Home> key can be pressed to highlight the first menu choice, and pressing the <End> key will highlight the last menu choice. - As a shortcut you can simply press the highlighted letter of the desired item (usually the first character) to select that option. A help line appears above the main menu. This line describes the highlighted menu option. When you move the sliding bar to a new item, this help line will change to display a line of help about the newly highlighted item. When you choose an option from the menu, a new submenu will pop up which works exactly the same way as the main menu. As each new submenu is chosen, the previous menus remain on-screen, providing you with a "tree" of your commands. To go back one level, simply press the <Esc> key. To clear all of the menus from the screen at once, press the <BkSp> key. 24 --------------------------------------------------------------------- /Cell Format --------------------------------------------------------------------- Function Change the way a value in a cell is displayed. Remarks This command allows you to modify the display of the current cell by setting its format. If the value using the format you select is too long to fit into the cell's assigned width, a row of asterisks will appear. Options General, Currency, Fixed, Percent, Science, Time, Date, Hidden, Label, Reset --------------------------------------------------------------------- /Cell Width --------------------------------------------------------------------- Function Change a column width. Remarks This command allows you to make the column the cell pointer is in wider or narrower (or even hide it). Options The /Cell Width command has two options: Set The set option allows you to change the width. You will be asked how wide the column should be. You may either enter a number, or use the arrow keys to change the width. <Left> will narrow the column, and <Right> will widen the column. Reset Resets the column the cell pointer is in to the global width. --------------------------------------------------------------------- /Cell Erase --------------------------------------------------------------------- Function Erase a cell from memory. Remarks This command erases the contents of the current cell. The cell will retain its formatting. 25 --------------------------------------------------------------------- /Cell Justify --------------------------------------------------------------------- Function Justify text in a cell. Remarks This command allows you to change the way text strings are displayed in the cell which the cell pointer is in. The command has three options: Options Left Justifies text in the current cell to the left edge of the cell. When a label is entered, it will appear beginning in the leftmost character of the cell. Right Justifies text in the current cell to the right edge of the cell. When a label is entered, it will appear flush right in the cell. Center Justifies text in the current cell to the center of the cell. When a label is entered, it will appear centered in the cell. --------------------------------------------------------------------- /Cell Copy --------------------------------------------------------------------- Function Copy a cell to another cell or range of cells. Remarks This command copies the contents of the cell that the cell pointer is in to another cell or range of cells. You will be asked to define the cell or range of cells you want to copy to. If you copy the cell to more than one cell, an individual copy will be made in each of the destination cell. If the source cell contains a formula, all relative references will be adjusted when the formula is copied. Example Cell B12 sums up the cells above it with the formula: =@SUM(B1..B11) You want cells C12 through X12 to sum up the cells above each of them. Simply move the cell pointer to cell B12, select /Cell Copy, and define the range to copy to as C12..X12. The formula will be copied to each of those cells, and the relative references in the formula will be adjusted so that they sum up the proper cells. For example, cell S12 will contain the formula: =@SUM(S1..S11) 26 --------------------------------------------------------------------- /Cell Move --------------------------------------------------------------------- Function Move the contents of a cell to another cell. Remarks This command moves the contents of the cell that the cell pointer is in to a different location. You will be asked to define the destination cell. If the current cell contains a formula, then the references will be adjusted to point to the same data. References in other cells which refer to the moved cell will also be adjusted so that they continue to point to the same data. --------------------------------------------------------------------- /Cell Protect --------------------------------------------------------------------- Function Set protection on a cell. Remarks This command allows the current cell to be protected against changes. If, for example, the cell contains a formula that you do not want changed, use the /Cell Protect Yes command to guard against changes. If you thereafter want to edit or change that formula, use the /Cell Protect No command to unprotect the cell. The default mode is "protected." A protected cell may still be modified until Worksheet protection is enabled using the /Worksheet Protect Enable command. Worksheet Protect is much like a "master switch" which turns protection capabilities on and off for the entire Worksheet. Options Yes Enables protection on the current cell. No Disables protection on the current cell. --------------------------------------------------------------------- /Range Format --------------------------------------------------------------------- Function Change the way values in a range of cells is displayed. Remarks This command allows you to choose the best format for the cells in a range. You will be asked to define the range of cells you want to change the format of. Options General, Currency, Fixed, Percent, Science, Time, Date, Hidden, Label, Reset 27 --------------------------------------------------------------------- /Range Width --------------------------------------------------------------------- Function Set the width of several columns at once. Remarks This command allows you to make several columns wider or narrower (or even hide them). You will be asked to define the range of columns to widen or narrow. Options The /Range Width command has two options: Set The set option allows you to change the width of several columns at the same time. You may either enter a number, or use the arrow keys to change the width. <Left> will narrow the column, and <Right> will widen the column. Reset Resets the columns in the range to the global width. --------------------------------------------------------------------- /Range Erase --------------------------------------------------------------------- Function Erase a range of cell's contents from memory. Remarks This command erases the contents of a range of cells. You will be asked to define the range of cells you want to erase. --------------------------------------------------------------------- /Range Justify --------------------------------------------------------------------- Function Justify text in a range of cells. Remarks This command allows you to change the way text strings are displayed in each cell in a range. You will be asked to define the range of cells you want to change. Options Left Justifies text in each cell of the range to the left edge of the cell. When a label is entered, it will appear beginning in the leftmost character of the cell. Right Justifies text in each cell of the range to the right edge of the cell. When a label is entered, it will appear flush right in the cell. Center Justifies text in each cell of the range to the center of the cell. When a label is entered, it will appear centered in the cell. 28 --------------------------------------------------------------------- /Range Copy --------------------------------------------------------------------- Function Copy a range of cells to another location. Remarks This command copies the contents of a range of cells to another location. You will first be asked to define the cell or range of cells you want to copy (source range), and then the location you want to copy to (destination). If the destination you define is a single cell, the source range will be copied starting at that location. If the destination contains more than one cell, the source range will be copied to each cell in the destination range. This is useful for copying a range of cells to several adjacent locations at once. If any of the source cells contain a formula, all relative references will be adjusted when the formula is copied. Example Column A contains a list of formulas from row 1 through 10. You want to copy this range of formulas to columns B through F. Select /Range Copy, and select the range A1..A10 as the source range. Since you want to copy the range to columns B through F, you should select the range B1..F1 as the destination. InstaCalc will then copy the source range to each cell in the destination range. --------------------------------------------------------------------- /Range Move --------------------------------------------------------------------- Function Move the contents of a range of cells. Remarks This command moves the contents of a range of cells to a different location. You will be asked to first define the range of cells to move (source range), and then the destination. If any of the cells in the source range contains a formula, then the references will be adjusted to continue to point to the same data. References in other cells which refer to the moved cell will also be adjusted so that they continue to point to the same data. When you define the destination, you only need to supply a single cell address; the source range will be copied with the upper left corner in the destination cell. --------------------------------------------------------------------- /Range Name --------------------------------------------------------------------- Function Give a name to a range of cells. Remarks This command allows you to give a name to a cell or range of cells. This name can then be used anywhere that a cell address or range reference can be used by either typing in the range's name or by pressing the <F3> Range key and 29 selecting the name from a menu. Options Create Allows you to name the cell or range. When this option is selected, you are asked what you want to name the cell or range. The name must begin with a letter of the alphabet and can be up to 8 characters long. You are then asked to define the range to name by typing in the range address or by pointing out the range with the cursor keys. Delete Releases the range name which is associated with a range. You will be asked to type in the name to delete. You may press <Enter> without typing a name, and a menu of the current range names will appear, and you may select the name to delete from the menu. Up to 16 names will be displayed at a time. If you don't see the name you want to delete, use the <PgDn> and <PgUp> keys to see more names. Reset Deletes all range names. List Causes a list of the current range names to appear. Up to 16 names will be displayed at a time. Use <PgUp> and <PgDn> to see more names. Example If you assigned the name PRICES to the range B3..C6, the formula @AVG(PRICES) will be identical to the formula @AVG(B3..C6). Other /Range commands which request a range reference, such as /Range Erase, will accept the name as an address. Pressing <F3> will bring up a menu of range names. The <F5> GoTo key will also accept range names. --------------------------------------------------------------------- /Range Protect --------------------------------------------------------------------- Function Set protection on a range of cells. Remarks This command allows you to protect a range of cells. If, for example, the range contains formulas that you do not want changed, use the /Range Protect Yes command to guard against changes. If you thereafter want to edit or change a cell in the range, use the /Cell Protect No command to unprotect the cell. The default mode is "protected." A protected cell may still be modified until Worksheet protection is enabled using the /Worksheet Protect Enable command. Worksheet Protect is much like a "master switch" which turns protection capabilities on and off for the entire Worksheet. You will be asked to define the range you want to protect. Options Yes Enables protection on the selected range. No Disables protection on the selected range. 30 --------------------------------------------------------------------- /Worksheet Format --------------------------------------------------------------------- Function Set the global default format. Remarks This command allows you to choose a global format for the entire Worksheet. Options General, Currency, Fixed, Percent, Science, Time, Date, Hidden, Label --------------------------------------------------------------------- /Worksheet Width --------------------------------------------------------------------- Function Set the global default width. Remarks This command allows you to set the default width of all columns in the Worksheet. You will be prompted to enter the new global column width. You may either enter a number, or use the arrow keys to change the width. <Left> will narrow the column, and <Right> will widen the column. --------------------------------------------------------------------- /Worksheet Erase --------------------------------------------------------------------- Function Erase the worksheet from memory. Remarks This command erases the current Worksheet's contents entirely. All of the Worksheet settings are restored to their defaults. You will be asked to confirm whether you really want to erase the worksheet. Options Yes Erase the Worksheet from memory. No Don't erase the Worksheet from memory. --------------------------------------------------------------------- /Worksheet Justify --------------------------------------------------------------------- Function Set the global default text justification. Remarks This command allows you to change the way text strings are displayed in each cell of the Worksheet. This command will not change they way existing text cells are justified. It only determines how text entered in the future will be displayed. The command has three options: Options Left Justifies text in cells to the left edge of the cell. When a label is entered, it will appear beginning in the leftmost character of the cell. Right Justifies text in cells to the right edge of the cell. When a label is entered, it will appear 31 flush right in the cell. Center Justifies text in cells to the center of the cell. When a label is entered, it will appear centered in the cell. --------------------------------------------------------------------- /Worksheet Ins Column --------------------------------------------------------------------- Function Insert one or more blank columns. Remarks This command inserts one or more blank columns at the current cell pointer position. The current column and all the columns to the right will be shifted right to make room for the blank columns. Relative references in formulas will be updated by the change; absolute references will remain unchanged. You will be asked how many blank columns you want to insert. Use the arrow keys to highlight how may columns should be inserted. If you highlight 2 columns, then 2 columns will be inserted. --------------------------------------------------------------------- /Worksheet Insert Row --------------------------------------------------------------------- Function Insert one or more blank rows. Remarks This command inserts one or more blank rows at the current cell pointer position. The current row and all the rows below will be shifted down to make room for the blank rows. Relative references in formulas will be updated by the change; absolute references will remain unchanged. You will be asked how many blank rows you want to insert. Use the arrow keys to highlight the number of rows that should be inserted. If you highlight 3 rows, then 3 rows will be inserted. 32 --------------------------------------------------------------------- /Worksheet Del Column --------------------------------------------------------------------- Function Delete one or more columns. Remarks This command deletes one or more columns at the current cell pointer position. The contents of the deleted columns will be erased, and all of the columns to the right will be shifted left to fill in the deleted columns. Relative references in formulas will be updated by the change; absolute references will remain unchanged. Use the arrow keys to highlight the columns that should be deleted. --------------------------------------------------------------------- /Worksheet Del Row --------------------------------------------------------------------- Function Delete one or more rows. Remarks This command deletes one or more rows at the current cell pointer position. The contents of the deleted rows will be erased, and all of the rows below will be shifted up to fill in the deleted rows. Relative references in formulas will be updated by the change; absolute references will remain unchanged. Use the arrow keys to highlight the rows that should be deleted. --------------------------------------------------------------------- /Worksheet Title --------------------------------------------------------------------- Function Lock title rows and columns on the screen. Remarks This command allows you to lock (or hold) rows and columns on screen. The command acts as a toggle; invoking it a first time turns the title locking ON, and invoking it a second time turns the title locking OFF. With title locking ON, the rows above the current cell and the columns to the left of the current cell will be locked on screen. The cell pointer will not move into the locked title rows and columns. --------------------------------------------------------------------- /Worksheet Protect --------------------------------------------------------------------- Function Enable or disable cell protection. Remarks This command enables or disables the protection in the Worksheet. When the Worksheet protection is disabled, any cell in the Worksheet may be modified, regardless of whether the cell is protected or not. When the Worksheet protection is enabled, those cells which are protected using the cell or range protection commands may not be modified or altered in any way. Options Enable Turns Worksheet protection on. Disable Turns Worksheet protection off. 33 --------------------------------------------------------------------- /Worksheet Zero --------------------------------------------------------------------- Function Suppress the display of zeros. Remarks This command allows you to suppress the display of all cells which have a value of zero. Options No Don't suppress the display of zeros. Yes Suppress the display of zeros. --------------------------------------------------------------------- /Worksheet Calc --------------------------------------------------------------------- Function Select recalculation options. Remarks This command allows you to select whether InstaCalc should recalculate the Worksheet after any cell is changed, or only when the <F9> Recalc key is pressed. Options Auto Recalculate the cells which need recalculation after any cell is changed. Manual Only recalculate the worksheet when the <F9> Recalc key is pressed. Since the recalculation state of the worksheet will usually not be current, InstaCalc will write the word CALC in the upper right corner when the worksheet needs to be recalculated. Limited Allows you to limit recalculation to a range of cells. You will be asked to define a range (or range) of cells which should be recalculated instead of the entire worksheet. Full Restores recalculation to the entire worksheet. 34 --------------------------------------------------------------------- /File Retrieve --------------------------------------------------------------------- Function Retrieves a worksheet from disk. Remarks This command retrieves a file from disk into memory. You will be asked for the name of the file to retrieve. If you press the <Enter> key without a file name, the InstaCalc file manager will appear, from which you may select a file with the cursor keys. When a file is read in, all of the data, settings, and current face and pointer position are loaded. All current data will be cleared from memory when a new file is loaded. InstaCalc checks to see if you have made any changes to the current file, and will ask if you want to save your data before loading the new file. If the file was saved with a password (encrypted), then you will be asked to enter the password to unlock it. If you provide the wrong password, an error message will appear and the file will not be retrieved into memory. --------------------------------------------------------------------- /File Save --------------------------------------------------------------------- Function Saves the worksheet in memory to disk. Remarks This command asks you for a name to save the Worksheet as, then proceeds to save the file to disk in the current directory. Pressing the <Enter> key without a file name brings up the InstaCalc File Manager, from which a file may be selected with the cursor keys. In addition to data, all of the Worksheet settings are saved to disk. If the file name you provide already exists in the current directory, InstaCalc will ask you whether you want to overwrite the existing file, or back it up. If you select Backup, the extension of the existing file's name will be changed from .INS to .BAK, and the current file will receive the .INS extension. InstaCalc also allows you to password protect (encrypt) your file as it is saved to disk. You will be asked to enter a password to lock the file with. This allows you to generate files which others cannot access unless they know the password. To password protect a file, you must enter the letter P following the file name. For example, if you want to save the current file to the name SECRET.INS, and you want the file to be password protected, you should enter: SECRET P 35 when you are asked to enter the name of the file to save. You would then be asked to enter a password. After you have entered the password, you will be asked to type the same password in to verify that it was typed in correctly. IMPORTANT - Make sure you do not forget your password for the file. There is no way to recover the file if you forget your password. --------------------------------------------------------------------- /File Directory --------------------------------------------------------------------- Function Changes the current directory. Remarks This command allows you to change the current directory or drive. You may enter either a full DOS pathname or simply a subdirectory name to be appended to the current directory. Pressing the <Enter> key without a file name brings up the InstaCalc File Manager, from which a directory may be selected with the cursor keys. --------------------------------------------------------------------- /File Files --------------------------------------------------------------------- Function Brings up the InstaCalc File Manager. Remarks This command presents a File Manager, and allows "point-and- shoot" operations on files, directories, and drives. You will be asked to enter a mask. A mask is simply a way of telling InstaCalc which files you want to be displayed. You may use the asterisk * in the mask. The asterisk will match any set of characters. For example, if you enter the mask *.BAK, then the file manager will list every file which ends with .BAK. If you enter the mask Q*.*, then all of the files beginning with the letter Q will be displayed. Pressing <Enter> will use the Mask *.*, which will display all of the files in the current directory. Options <Up> <Down> Using these keys, you can highlight files, drives, or directories (depending on what area of the file manager the highlighted bar is in). Operations can then be performed on the selected file, drive, or directory. <Left> <Right> Allows you to change files, drives, or directories. If, for example, the highlighted bar is in the "Directory" section of the file manager, and you wanted to change drives, hitting the left arrow key will move the bar into the "Drive" section. A new drive may be selected by using the down arrow and Enter keys. <Enter> Causes the highlighted file, drive, or directory to be selected. 36 <F1> Causes a Help screen to appear, giving help on how to use the File Manager. <F2> Causes the currently highlighted file to be loaded. <F3> Causes the highlighted file to be combined. For more details see /File Combine. <F4> Causes the highlighted file to be imported. For more details see /File Import. <F5> Allows you to edit the contents of the highlighted text file. Although the macro editor will be used, you are not limited to editing just macro (.MAC) files. See /Macro Edit for details on the options available when editing. <F6> Causes the highlighted text file to be printed. This function is primarily for printing out application programs. <F7> Causes the highlighted file to run, if it is a program or batch file, and if enough memory is available. <F8> Allows you to rename the highlighted file. You will be prompted for a new name. <F9> Copies the highlighted file to another drive, directory, or name. You will be prompted for a new drive, directory, or name. <F10> Deletes the highlighted file. InstaCalc will request confirmation before actually deleting the file. <Ctrl><F2> Scans the current drive to update the directory tree. The File Manager also simplifies the tasks of changing drives and directories. To change to another directory, simply press the <Left> key to highlight the word DIRECTORIES at the top of the screen, then use the <Up> and <Down> keys to highlight the name of the directory you want to change to. Once the name is highlighted, press the <Enter> key and InstaCalc will change to that directory and list the files in that directory. Changing drives is just as easy. Simply press the <Left> key until the word DRV is highlighted at the top of the screen, then press the letter of the drive you want to change to. For example, if you want to change to drive A:, then just press A. 37 --------------------------------------------------------------------- /File Combine --------------------------------------------------------------------- Function Combines all or part of a worksheet on disk with the worksheet currently in memory. Remarks This command allows you to combine several InstaCalc files into a single file. You will be asked for the name of the file to be read in, and the file will be loaded without clearing the Worksheet. Pressing the <Enter> key without a file name brings up the InstaCalc File Manager, from which a file may be selected with the cursor keys. The original cells will remain unchanged unless the new file contains data in the same cells. Blank cells in the new file will not affect existing cells in the Worksheet. Current Worksheet settings will be retained. Options Copy The data in the new file will be combined into the current Worksheet. If the new file and the current Worksheet contain any data in the same cell, the data in the new file will be copied over the data already in memory. Add The data in the new file will be combined into the current Worksheet. If the new file and the current Worksheet contain values in the same cell, the value in the new file will be added to the value already in memory. Since this command is primarily for consolidating data from several worksheets, cells containing text will not be combined into the current file. Subtract The data in the new file will be combined into the current Worksheet. If the new file and the current Worksheet contain values in the same cell, the value in the new file will be subtracted from the value already in memory. Since this command is primarily for consolidating data from several worksheets, cells containing text will not be combined into the current file. Each of these options has two suboptions which determine how much of the new file should be combined in. All Combines in the entire file. Every cell in the new file will be combined into the same location in the current Worksheet. For example, cell X23 will be combined into cell X23 in the current worksheet. Part Combines in a range from the file. The cells in the range you combine in will be combined starting at the location of the cell pointer. For example, if the cell pointer is in cell D5, and you combine in the range A1..D12 from the other file, then cell A1 in the new file will be combined into cell D5 in the current worksheet. 38 You will be asked for the range in the other file which you want to combine in. If the range you want to combine in was named with the /Range Name command, you can press the <F3> Range key to bring up a menu of all the named ranges in the other file. --------------------------------------------------------------------- /File Xtract --------------------------------------------------------------------- Function Saves part of the worksheet in memory to disk. Remarks This command is similar to the /File Save command, except that it saves a range of cells rather than the entire Worksheet. You will be asked for the name of the file to save to, and to define the range of cells to save. InstaCalc saves all of the Worksheet settings with the saved range. --------------------------------------------------------------------- /File Import --------------------------------------------------------------------- Function Imports data from 1-2-3, dBase, DIF, or ASCII files. Remarks This command imports data files from other programs into InstaCalc. You will be asked for the name of the file to import. Pressing <Enter> without a file name brings up the InstaCalc File Manager, from which a file may be selected with the cursor keys. Options 1-2-3 Imports a Lotus 1-2-3 (tm) file. If InstaCalc encounters a formula which uses an unsupported @function, it will read the value of the formula rather than the formula itself. dBase Imports selected records from a dBase (tm) file. Fields will appear as columns, and records will appear as rows. You do not have to import every field of the data base. Before the import begins, InstaCalc will step through the names of each field and ask if you want to import that field. You may select: Yes Import this field. No Don't import this field. All Import this and all the remaining fields. You will then be asked to enter a criteria. The criteria is a string which tells InstaCalc which records you want to import. The criteria can be any valid InstaCalc formula which returns a TRUE or FALSE value. The criteria may treat any of the dBase field names as if they were range names in 39 the formula. The criteria can use any of the comparison operators (=,<>,<=, etc) or logical operators (&,|, or ~). If you press <Enter> without entering a criteria, then InstaCalc will import every record from the dBase file (if there is enough memory to hold them). Some example criteria are: STATE="Utah" All records where the STATE field contains Utah (STATE="Utah")&(LASTNAME="Smith") All records where the STATE field contains Utah and the LASTNAME field contains Smith (STATE="Utah")&~(LASTNAME="Smith") All records where the STATE field contains Utah but the LASTNAME field does not contain Smith (STATE="Utah")|(STATE="California") All records where the STATE field contains Utah or California QUANTITY>100 All records where the QUANTITY field contains a value greater than 100 At any time while entering the criteria, you may press <F3> to bring up a menu of the field names which are available. The only limitation to the criteria is that you may only use the names of fields which you are actually importing. For example, if you select No when asked if InstaCalc should import the field called STATE, then you may not use STATE in your criteria. DIF Imports a Data Interchange Format (DIF) file. Many databases and spreadsheets are capable of creating DIF files. ASCII Imports a comma delimited or space delimited ASCII file. Each line in the ASCII file will be imported into a row in the worksheet. Each line may contain multiple fields (cells) of data, each of which must be separated by a comma or space. If a string which contains spaces or commas is to be imported into a single cell, then that string must be surrounded by quotation marks "like this". 40 --------------------------------------------------------------------- /File Export --------------------------------------------------------------------- Function Exports part of the worksheet to a 1-2-3, dBase, DIF, or ASCII file. Remarks This command exports a range of cells to the file format of another program. You will be asked for the file name you want to export to, and to define the range of cells you want to export. If the file name you provide exists, InstaCalc will ask you if you want to Overwrite the existing file or Cancel the operation. Options 1-2-3 This option allows you to export the range to a 1- 2-3 R1 (WKS) or R2 (WK1) file. You must select from a menu which version you want to export to. Any InstaCalc formula which contains an @function which 1-2-3 doesn't support will be exported as a value rather than a formula. dBase This option allows you to export the range to a dBase II, III, or III+ file. You must select from a menu which version you want to export to. InstaCalc will use the top row of the range you define as the field names for the data to be exported. The widths of each column will be used as the field width in the dBase file. Before the actual export is performed, InstaCalc will step through each field name and ask you what type of field it should be. The available field types are: Character The values in the field are text. Number The values in the field are numbers. Logical The values in the field are TRUE or FALSE. Date The values in the field are dates. This option is available only when you export to dBase III or III+. DIF The file will be exported in the DIF (Data Interchange Format) format. ASCII The file will be exported to a comma delimited ASCII file. Each row of the range will be on a separate line of the ASCII file, and the value of each cell in the row will be separated by a comma. Text items will be surrounded by quotes ". 41 --------------------------------------------------------------------- /File Link --------------------------------------------------------------------- Function Link to a .INS file on disk. Remarks This command allows you to link multiple spreadsheets together. You are not limited to linking a single cell. You may link to an entire range within another spreadsheet. The current cell will contain the link. If you change the value of the cell in the other spreadsheet, this "link cell" will change accordingly. If you create a link to a range of cells in another file, the link cell will take on the same value as the cell in the upper left corner of the range in the other file. The cells to the right of and below the link cell will take on the values of corresponding cells from the other file. If a change is made to any of the linked cells in the other worksheet, the current worksheet will be updated to reflect those changes. Each time you retrieve a file which is linked to other files, you will be asked if you want to update all of the links. If you select Yes then InstaCalc will update every link in the file. If you select No then InstaCalc will not update any of the links in the file. If none of the files which were linked to have been changed, then selecting No can greatly speed up the retrieval of the file. If your file has a large number of links in it, but only one of the links needs to be updated, you should select No, and then use the /File Link Update command to update the single link. Options Create Create or edit a link to another worksheet. You will be asked for two items. - The name of the file you want to link to. The file must already be in existence. - The name of the range you want to link to. Delete Delete an existing link to another worksheet. A menu of all existing links will appear, and you may pick the link you want to delete. List List all of the links in the current Worksheet. Edit Allows you to edit an existing link to another file. A menu of all the links in the file will be displayed, from which you may select the link you want to edit. You may then change either the file to link to, or the range in the file, or both. 42 Update Allows you to update a link to another file. A menu of all the links in the file will be displayed, and you may select the link you wish to be updated. --------------------------------------------------------------------- /Print Block --------------------------------------------------------------------- Function Defines the range of cells to print. Remarks This command allows you to define a range to print. You will be asked to define the range to be printed. The range will not actually be printed until the /Print Go command is issued. --------------------------------------------------------------------- /Print Rows --------------------------------------------------------------------- Function Defines rows to print above the print range on each page. Remarks This command allows you to define one or more rows which will be printed above your data on each page. This command is useful when you are printing a range which has more rows than will fit on a single sheet of paper; it allows you to have a row of header data printed at the top of each page. --------------------------------------------------------------------- /Print Columns --------------------------------------------------------------------- Function Defines columns to print to the left of the print range on each page. Remarks This command allows you to define one or more columns which will be printed to the left of your data on each page. This command is useful when you are printing a range which has more columns than will fit on a single sheet of paper; it allows you to have a column of titles printed at the left of each page. --------------------------------------------------------------------- /Print Undefine --------------------------------------------------------------------- Function Undefines the current Print, Rows, and Columns ranges. Remarks Since InstaCalc remembers which range you printed last, it is sometimes necessary to reset the /Print Block setting. When doing so, it is usually easiest to clear the previous block setting entirely by using the /Print Undefine command. Options Block Clears the previous /Print Block setting. 43 Rows Clears the previous /Print Rows setting. Columns Clears the previous /Print Columns setting. All Clears all previous /Print range settings. --------------------------------------------------------------------- /Print Align --------------------------------------------------------------------- Function Align the paper in the printer. Remarks This command sets the current position of the printhead as the top of the page. This is important; the printer and the program will be keeping individual track of the top of the page. It is good habit to precede the /Print Go command with the /Print Align command. --------------------------------------------------------------------- /Print Line --------------------------------------------------------------------- Function Advance the printer by one line. Remarks This command sends a line feed to the printer, causing the printer to print a blank line. Since the printer and the program are keeping separate track of the pagination, sending a line feed through the printer control panel will cause the program's pagination to be off by a line, resulting in unwanted offsets in your printout. More importantly, though, it is usually easier to advance the paper one line using this command than by using the printer's control panel. --------------------------------------------------------------------- /Print Page --------------------------------------------------------------------- Function Advance the printer to the top of the next page. Remarks This command sends a form feed to the printer, causing the paper to advance one page. This is usually done at the end of a printout, since printing will stop with the last line actually printed. Thus, to remove the page from the printer, simply select the /Print Page command. Since the printer and the program are keeping separate track of the pagination, sending a form feed through the printer control panel will cause the program's pagination to be off, resulting in unwanted offsets in your printout. 44 --------------------------------------------------------------------- /Print Options --------------------------------------------------------------------- Function Set options for your printout. Remarks This command allows complete control over the format of the printout. There are numerous options, all visible from a single pop-up screen. You may select an option either by pressing the first letter of the option name (highlighted), or by using the arrow keys to highlight the option and pressing the <Enter> key. Options Left Margin Lets you set the number of characters from left edge of the page to begin printing. Right Margin Lets you set the number of characters from left edge of the page that you want your right margin set to. The right margin must be greater than the left margin or an error will occur. Top Margin Lets you set the number of blank lines to print at the top of each page. If you set this option to 3, InstaCalc will begin printing on the fourth line of the page. Bottom Margin Lets you set the number of blank lines to print at the bottom of each page. InstaCalc will automatically advance the paper to the next page before it resumes printing. Page Length Lets you set the number of physical lines on a page. On an 8 1/2" x 11" paper at 6 lines per inch, there are 66 lines. InstaCalc must have this information so that it can keep track of where it is on the page. Header Allows you to enter a header, which will appear at the top of every page. There are two special characters that you can use in headers: @ will be replaced by the current date. # will be replaced by the current page number. The header may start with one of the text justify characters to determine where on the line the header will be placed. These characters are ' left justify the header " right justify the header ^ center the header If no header is defined, then a blank line will be printed in its place. 45 Footer Allows you to enter a footer, which will appear at the bottom of every page. Special characters are available as in the header. If no footer is defined, then a blank line will be printed in its place. Mode Lets you decide what type of printout you want. Display The printout will appear as the file does on-screen; that is, the values calculated by the formulas will be printed. Formula The formulas themselves will be printed, one cell per line. This is useful for documentation and error-handling. Output Lets you decide where to send your output. Printer All output will be sent to the printer. File All output will be sent to a file. You will be asked for the name of the file you want to send the printout to. Number (page) Displays the current page number, which you can set or reset. Device Allows you to select the printer and port to send the printout to. Setup string The setup string is a sequence of characters which will be sent to your printer before the range is printed. It is generally used to send control sequences to your printer to turn on special features such as condensed printing. Most printer codes contain control characters which can't be entered directly. To enter control characters, simply enter a backslash character \ followed by the 3 digit ASCII code (in decimal) for the control character. For example, since the ASCII value (in decimal) for the <Esc> character is 27, you would use a setup string of \027E to send an <Esc>E to the printer (this sequence selects emphasized print on an EPSON printer). 46 --------------------------------------------------------------------- /Print Go --------------------------------------------------------------------- Function Send your data to the printer (or print file). Remarks This command begins the process of printing. InstaCalc will not begin printing until it receives this command. If the range you are trying to print is wider than the left and right margins allow, InstaCalc will ask if you want if to break the page into smaller pieces for printing. These pieces can then be put back together into a larger sheet. If you don't want the printout to be broken into smaller pieces, then you must go back into the /Print Options and reset your left and right margins to allow for a wider printout. If you want to abort the print job after it has started, simply press the <Esc> key (printers with built-in print buffers will continue to print until the print buffer has been emptied). --------------------------------------------------------------------- /Graph --------------------------------------------------------------------- Function Define, view, and print graphs. Remarks This command allows you to define the ranges to be graphed, set options for the graph, and then view or print the graph. Options Type Select the type of graph. The following types of graphs are available. Bar Bar chart. Stacked Bar Stacked bar chart. Line Line chart. Pie Pie chart using data in range A. X-Y X-Y chart. Range X must contain the X axis coordinates for the values in ranges A through F. Area Area chart. Hi-Lo Hi-Lo chart. Range A contains the high values, and range B contains the low values. A..F Allows you to select up to 6 ranges of data to graph. With the exception of pie charts and hi-lo graphs, all of these ranges can be displayed on a graph at the same time. Simply define the range of cells you want to be graphed. You will also be asked to enter a legend which describes the data in the range. This legend will be printed on the graph to 47 differentiate between the different graph ranges. X Axis Allows you to select a range of cells containing the labels which will be used on a graph's X axis. You will be prompted for the range containing the labels. For most graph types the cells in this range may contain text, values, or both. For X-Y graphs, this range must contain the x axis values which correspond to the graph ranges (A through F). Defining this range is mandatory for X-Y graphs. Reset Resets the ranges of the graph, allowing you to undefine data ranges to be charted. View Displays the defined graph on your screen. Once the graph is displayed, pressing any key will return you to the worksheet. Print Prints the defined graph to the printer. The current printer type and port is displayed next to the print command. To change this printer (or port), use the /Print Options Device command. Options Allows you to set various options for the graph. Title Places a title at the top of the current graph. You will be asked to enter some text. Labels Allows you to label the X and Y axes of the graph. The X label will be placed horizontally under the x axis, and the Y label will be placed vertically to the left of the y axis. You will be asked to enter some text for both the X and Y axis labels. X axis Allows you to select the format and scaling of the x axis. This option is useful only for X-Y graphs. There are 2 types of scaling. Auto InstaCalc will select the minimum and maximum values to use for the x axis. Manual InstaCalc will ask you for the minimum and maximum values to use for the x axis. InstaCalc may adjust these values a little bit if necessary. Manual scaling is useful when you want to print several different graphs using the same scale. 48 You will also be asked to select the format to use to display values along the x axis. All of the formatting options from the /Cell Format command (such as fixed, currency, scientific, etc) are available. Y axis Allows you to select the format and scaling of the y axis. This option is used for all graph types except pie charts. There are 2 types of scaling. Auto InstaCalc will select the minimum and maximum values to use for the y axis. Manual InstaCalc will ask you for the minimum and maximum values to use for the y axis. InstaCalc may adjust these values a little bit if necessary. Manual scaling is useful when you want to print several different graphs using the same scale. You will also be asked to select the format to use to display values along the y axis. All of the formatting options from the /Cell Format command (such as fixed, currency, scientific, etc) are available. Spacing Allows you to choose the spacing of the values along the x axis of the graph. This option is useful when graph range X contains a large number of values to place along the axis. You will be asked to enter a numerical value. If you enter 1, then every value from graph range X will be displayed. If you enter 2, then every second value from graph range X will be displayed; 3 means that every third value will be displayed, and so on. For example, if the x axis values are Jan, Feb, Mar, April, and May, choosing a spacing of 1 causes each of the values to appear. Choosing 2 will cause only Jan, Mar, and May to appear. PieFormat Allows you to select how to format the values displayed around the pie chart. Although each slice of the pie will be labeled with the contents of the X graph range, you may also display a value for the slice. You may select from the following options. % The percent of the whole will be 49 displayed. For example, if the slice is one quarter of the pie, 25% will be displayed. $ The value of the slice (from graph range A) will be displayed as a currency value. Value The actual value of the slice (from graph range A) will be displayed. None No value will be displayed. AltTic Determines whether the values placed along the x axis should be staggered. This allows a few more values (or longer ones) to be placed along the x axis and yet still remain readable. Yes Stagger the x axis values. No Don't stagger the x axis values. 50 --------------------------------------------------------------------- /Data Sort --------------------------------------------------------------------- Function Sort a range of data. Remarks This command allows you to sort rows or columns of data, in either ascending or descending order. A window of options will appear on the screen. To change an option, either press the key corresponding to the first letter of the option name (D for Direction for example), or use the cursor keys to move the lightbar to the option you want to change and press the <Enter> key. Options Direction Determines how your data is to be sorted. Row Rows will be sorted. Column Columns will be sorted. Block You will be asked to define the range to sort. This range should include all of the rows or columns which you want rearranged. 1st - 9th Key The Key options indicate the fields to be used in determining the sorted (new) order of the data. You will be prompted to indicate the column or row that contains the data by which the sort is to be performed. The column or row may be indicated either by pointing or by typing an appropriate cell address. You will also be asked whether the sort should be in Ascending or Descending order. Sorting the data in ascending order causes numerical data to be sorted from the smallest number to the largest number (1, 2, 3, 4, etc.), while textual data will be sorted alphabetically (A, B, C, D, etc). Sorting the data in descending order has the opposite effect; numbers will be arranged from large to small (5,4,3,2,1, etc), and textual data will be sorted in reverse alphabetical order (Z, Y, X, W, etc). Reset This command resets all of the sort settings. This function is necessary when you need to undefine the sort keys. Start Pressing the Start key in the options menu will cause the sort to be performed as indicated in the menu. 51 --------------------------------------------------------------------- /Data Fill --------------------------------------------------------------------- Function Fill a range of cells with data. Remarks This command allows you to fill a specified range of cells with text or numbers. You will be asked for an equation which is used to determine the values placed in each of the cells to be filled. A variable named # is initialized to 0 and is incremented by 1 for each cell in the range. The # variable may be used anywhere in the equation to provide an endless number of sequences. The equation entered with this command may be any legal formula containing numbers, operators, cell or range references, functions, or the # variable. Cell references in the formula are treated as relative unless you specifically make them absolute using the $ symbol. Examples # generates a sequence starting with 0 and incrementing by 1. 1000+# generates a sequence starting with 1000 and incrementing by 1. 5+#*3 generates a sequence starting with 5 and incrementing by 3. @TODAY+# generates a sequence of date numbers starting with the current date. --------------------------------------------------------------------- /Data Value --------------------------------------------------------------------- Function Convert formulas in a range to their values. Remarks This command converts formulas in a range to their values. You will be asked to define a range of cells to convert. Since formulas require more memory than values, this command helps conserve memory when you just need a set of values rather than the formulas. 52 --------------------------------------------------------------------- /Data Modify --------------------------------------------------------------------- Function Modify all numerical values in a range. Remarks This command allows you to modify all of the values in a range. You may specify that each cell in a specified range be multiplied by 5, or subtracted from the average value of the range, or that they be modified in any of the ways that a legal formula may provide. You will be asked to define the range to modify, as well as an equation to modify the cells with. This command provides a variable named # which is set to the value of each cell in the range. This variable may be used just like a number or cell reference in the formula. Example Below are examples of equations and the way they modify the range. 2*# doubles all of the values in the range. #^2 squares all of the values in the range. #+3 adds 3 to all of the values in the range. @ABS(#) makes all of the values in the range positive. --------------------------------------------------------------------- /Data Base --------------------------------------------------------------------- Function Define and use a range of cells as a database. Remarks This command allows you to define and use a range of cells as a database. Each column in the range will be treated as a database field, and each row in the range will be treated as a record in the database. You may have up to 23 fields (columns) in your database. If the range you define is wider than 23 columns, all columns past the 23rd will be ignored. The top cell in each column must contain the name of that field. In addition, InstaCalc will use the format of the header cell (whether Currency, Date, etc) for all data entered into that field. This means it is not necessary to pre-format entire columns of blank cells before using the database form. Options Range You will be asked to define the range of cells to use for your database. Each column in the range will be treated as a database field, and each row in the range will be treated as a record in the database. View This command displays the defined range in a 53 database form view, using the information you provided in the cell at the top of each column. The following commands are available in the form view: Next Move to the next record in the database. Prior Move to the prior record in the database. Begin Move to the first record in the database. End Move to the last record in the database. Modify Modify the currently displayed record. When modifying a record, the fields will be accessed in the order of the columns in the worksheet. You may use <Enter> to accept the value of each field and move to the next field, or you may use <Up> and <Down> to move back and forth between fields. When you are finished modifying the record, simply press <F10> to return to the database menu. Add Allows you to add new records to the database. Add is similar to Modify except that you may press <F9> to accept the entered data and move to a new (blank) record. Pressing <F10> accepts the entered record and returns you to the database menu. Find Find a record in the database. You will be asked to enter criteria for the record(s) to search for. This criteria can use field names, comparison operators (=,<>,<=, etc), logical operators (&,|, or ~) or even @functions. For example, if you want to find all records where the last name is Smith and the state is NY, then you might use the following criteria: (LNAME="Smith")&(STATE="NY") Del Delete the currently displayed record. You will be asked to confirm this selection. 54 --------------------------------------------------------------------- /Data Goalseek --------------------------------------------------------------------- Function Find the value which causes a worksheet model to give a particular result. Remarks The /Data Goalseek command allows you to easily determine what value must be entered into a cell to make a formula give a desired result. The formula does not have to directly reference the cell, and the model may be as complex as desired. The more complex the worksheet, however, the longer the goalseeking will take. To perform the goalseeking, select the /Data Goalseek command, then: 1. Select the formula you want to achieve a particular goal value. 2. Enter the goal value you want that formula to achieve. 3. Select the cell which InstaCalc should adjust to achieve that goal. Options After InstaCalc finds a solution, it displays the solution and provides the following options: Use Enter the new value into the cell which InstaCalc was adjusting. Ignore Restore the original value to the cell which InstaCalc was adjusting. Example You have a complex financial worksheet model where cell Z73 contains a formula which determines profit, and cell C5 is the number of items which need to be sold to achieve that profit. To determine how many items you must sell to achieve $10,000 profit: 1. Select /Data Goalseek. 2. Mark cell Z73 as the goal cell. 3. Enter 10000 as the goal value. 4. Mark cell C5 as the cell to change. InstaCalc will then seek the value for cell C5 which causes cell Z73 to return 10000. InstaCalc will display the solution and will ask if you want to use the new value which it just found, or if you want to ignore the new value. --------------------------------------------------------------------- /Data matriX --------------------------------------------------------------------- Function Perform matrix operations on ranges. Remarks The /Data matriX command allows you to add, multiply, and transpose ranges of cells. 55 Options Multiply Multiplies two ranges. You will be asked to define the two ranges to multiply together. The number of columns in the first range must be the same as the number of rows in the second range. You will also be asked to define an output range where the result will be placed. Add Adds two ranges together. You will be asked to define the two ranges to add together. The two ranges must each have the same number of rows and columns. You will also be asked to define an output range where the result will be placed. Transpose Transposes (swaps columns with rows) a range. You will be asked to define the range to transpose, as well as an output range to place the result. --------------------------------------------------------------------- /Data Query --------------------------------------------------------------------- Function Extract or delete records from a worksheet based database. Remarks The /Data Query command allows you to extract (copy) selected database records (or parts of records) from an input range to an output range, or to delete selected records from an input range. Options Input The input range consists of a single record per row, where the first row in the range contains the field names for the database. Output The output range is used as the location to place the records extracted from the input range. If you only define the output range as one row, InstaCalc will automatically extend the range to row 4096. Criteria The criteria is a string which tells InstaCalc which records you are interested in extracting or deleting. The criteria can be any valid InstaCalc formula which returns a TRUE or FALSE value. The criteria may treat any of the database field names as if they were range names in the formula. The criteria can use any of the comparison operators (=,<>,<=, etc) or logical operators (&,|, or ~). Some example criteria are: STATE="Utah" All records where the STATE field contains Utah 56 (STATE="Utah")&(LASTNAME="Smith") All records where the STATE field contains Utah and the LASTNAME field contains Smith (STATE="Utah")&~(LASTNAME="Smith") All records where the STATE field contains Utah but the LASTNAME field does not contain Smith (STATE="Utah")|(STATE="California") All records where the STATE field contains Utah or California QUANTITY>100 All records where the QUANTITY field contains a value greater than 100 Extract Allows you to extract (copy) selected records (and fields) from the Input range to the Output range. The top row of the Output range must contain the names of the specific fields you want extracted. This allows you to extract only those fields you are interested in. The Output range is erased before the records are extracted. If more records match the criteria than will fit in the Output range, an error message will appear. Delete Allows you to delete records which match the Criteria from the input range. You will be asked to confirm this selection with a Yes or No answer. --------------------------------------------------------------------- /Data Dist --------------------------------------------------------------------- Function Generates a frequency distribution of a range of cells. Remarks This command generates a frequency distribution of a range of cells. You simply define the range of cells which contain the values you want a distribution of, and a "bin range", which is simply a column of increasing values. InstaCalc will look at the first value in the "bin range", count how many cells in the value range are less than or equal to that value, and insert that count into the cell to the right of the first bin. InstaCalc then looks at the second value in the "bin range", counts how many cells in the value range are less than or equal to that value but are greater than the previous bin value, and inserts that count into the cell to the right of 57 the second bin. This continues through the last cell in the "bin range". Finally, InstaCalc counts the number of cells in the value range which are greater than the last cell in the "bin range", and places that count to the right of the cell just under the "bin range". Summary 1. Define the range containing your values. 2. Define the column range containing your bin values. The bin values must be in increasing order. 3. InstaCalc will determine how many cells in the value range fall in each of the "bins" in the bin range. The result for each bin will be placed in the cell to the right of each bin. By graphing (as a bar graph) the result of a data distribution, you will obtain a histogram of the original data. --------------------------------------------------------------------- /Data Audit --------------------------------------------------------------------- Function Allows you to audit (error check) your worksheet. Remarks This command provides several auditing capabilities which can assist you in finding errors which sneak into your worksheets. Options Trace Allows you to highlight all formulas which refer to a specific cell. InstaCalc will enter a trace mode, where pressing <Enter> on any cell will highlight all formulas which reference that cell. All the standard movement keys are available to move through the worksheet to find highlighted cells which may not be on the screen. To exit from Trace mode, simply press <Esc>. Map Displays your worksheet in a compressed form where each cell is represented by one of the following characters: + Formula # Number A Text ? Error This allows you to easily view the overall structure of your worksheet. The map view can help you locate errors which may normally be 58 difficult to spot, such as a number cell accidentally placed in a column of formulas. You may use any of the movement keys to move through your worksheet, and the contents of the current cell will be displayed on the command line as usual. You will not be able to edit cells or access the menu from this view however. To exit from map mode, simply press the <Esc> key. --------------------------------------------------------------------- /Macro Edit --------------------------------------------------------------------- Function Edit or create a macro. Remarks This command allows you to edit or create a macro. You will be asked for the name of a macro to edit. Pressing <Enter> without entering a macro name will cause the InstaCalc File Manager to appear on-screen; the macro name may then be chosen from the File Manager. Once a macro is chosen, the InstaCalc Macro Editor will display the macro and allow you to edit it. The following commands are available for editing the macro. <Up> <Down> <Left> <Right> The arrow keys move the cursor up or down one line, or left or right one character. <Home> This key moves the cursor to the beginning of the line. <End> This key moves the cursor to the end of the line. <Ctrl><Right> This key moves the cursor one word to the right. <Ctrl><Left> This key moves the cursor one word to the left. <Ctrl><Home> This key moves the cursor to the top of the file. <Ctrl><End> This key moves the cursor to the end of the file. <PgDn> This key moves the cursor down one screen. <PgUp> This key moves the cursor up one screen. <BkSp> This key deletes the character to the left of the cursor and shifts the following characters in the line to the left to fill in the empty space. <Ctrl><BkSp> This key deletes the entire line. <Del> This key deletes the character underneath the 59 cursor and shifts the following characters in the line to the left to fill in the empty space. <Ins> This key toggles between insert and overwrite modes. When you are in insert mode, typing a character will insert the new character into the line at the point where the cursor is. The character under the cursor and those to the right of the cursor are shifted to the right to make room for the new character. When you are in overwrite mode, typing a character will replace the character under the cursor with the new character. When you are in insert mode, the cursor will be an underline. In overwrite mode the cursor will be a solid block. <F1> Help This key causes a help screen to appear which lists all of the commands available for text editing. <F2> Mark This key turns on marking at the current line. Moving the cursor after pressing this key causes lines between that line and the new cursor position to be highlighted. Pressing <F2> a second time will turn off text marking. <F3> Cut Pressing this key with marking turned on (<F2>) causes the marked lines to be cut out of the file and placed in a buffer. You may then move to another location in the file and use the <F4> key to paste the lines back into the file. The buffer will only hold one block of lines, so if you cut data a second time, the new data replaces the older data in the buffer. <F4> Paste This keys causes the contents of the buffer to be pasted into the file at the cursor location. This does not clear the buffer, so you may paste the same buffer data into more than one location. <F5> DelLine This command deletes the line the cursor is on. <F6> Search This command allows you to search for a string in the file. You will be asked to enter the text you want to search for. Simply enter the text, and the cursor will be moved to the next occurrence of that text. The search is case insensitive, which means that hello will match hello, Hello, or HeLlO. <F7> Save This command allows you to save the current 60 file to disk without having to exit. <F10> Exit This command allows you to exit from the editor. If you have made any changes to the file, you will be asked if you want to save it first. --------------------------------------------------------------------- /Macro List --------------------------------------------------------------------- Function List all macros in the current directory. Remarks This command lists all of the macros in the current directory. The command uses the InstaCalc File Manager to find files with the .MAC extension. --------------------------------------------------------------------- /Macro Run --------------------------------------------------------------------- Function Run a macro or application program. Remarks This command executes a macro (or application program). You will be asked for the name of a macro to run. Pressing the <Enter> key without indicating a macro to be run will cause the InstaCalc File Manager to appear on-screen. You may then choose the macro to be run using the File Manager. For information on creating macros and application programs, see the section InstaCalc Applications. --------------------------------------------------------------------- /OpSys --------------------------------------------------------------------- Function Temporarily returns to DOS. Remarks This command allows you to temporarily return to the DOS command line, where you can execute DOS commands or other programs. When you are ready to return to InstaCalc, simply type: EXIT at the DOS prompt. IMPORTANT: Never run TSRs from the /OpSys command line (this includes the DOS PRINT command). Doing so can cause unexpected problems to occur. This command is not available when running as a pop up. 61 --------------------------------------------------------------------- /Quit --------------------------------------------------------------------- Function Exits from InstaCalc. Remarks This command allows you to leave InstaCalc entirely. This is an intelligent exit command; if you have made changes, InstaCalc will allow you to save the file before exiting. If no changes have been made, InstaCalc will simply exit the program. When running InstaCalc as a pop up program, you will not be asked to confirm exiting, because your data is not lost when you quit. Options If changes have been made to the Worksheet since the last time you saved it, you will be asked if you want to lose the changes you made: No Returns you to the Worksheet without exiting the program. Yes Exits InstaCalc. Any changes made to the Worksheet will be lost. Save Saves the Worksheet, then exits the InstaCalc program. 62 BUILT-IN FUNCTIONS InstaCalc provides built-in functions which you can use in your formulas. These built-in functions each begin with the character @, and are often called at functions. Most of these functions require one or more arguments. An argument is a value which the function uses as an input to provide an output value. An argument may be a number, a text string, a reference to a value in another cell, or another formula. In the case of the statistical functions, an argument may also be a range of cells. If a function requires arguments, then they are enclosed in parentheses and immediately follow the name of the @function. If more than one argument is required, they must be separated from each other with a comma. A built-in function can return either a numerical or text value and may be treated exactly like a number or text would be. Example A cell contains the formula =2*@ABS(-3). @ABS is the name of a built-in function which returns the absolute (positive) value of its argument. -3 is the argument to the function. The function returns a value of 3, and is used in the rest of the formula just like the number 3. The value of the entire formula is 6 (2 times the value of the @function). If the value that a function returns is an error, it will return the special value ERROR. A formula which contains a function with a value of ERROR will also return ERROR as the value for the cell it is in. 63 Mathematical InstaCalc's math functions allow your formulas to perform more sophisticated mathematical operations than the operators alone can provide. Trigonometric InstaCalc's trigonometry functions work the same way as the mathematical functions, but they specialize in working with angles. All angle arguments and results are in radians. To convert from degrees to radians, use the @RADIAN function. To convert from radians to degrees, use the @DEGREE function. Statistical InstaCalc's statistical functions allow you to analyze and summarize your data in a variety of ways. The statistical functions calculate and return various statistics of a list of values. The list may consist of numbers, references to values in other cells, formulas, or a range of cells. Ranges of cells are defined by using the cell names of two opposite corners of the range separated by two periods (for example: C14..D16). Each individual item in the list must be separated by a comma. For example, suppose you want to sum 1.23, the value in the cell A1, and the values in each cell in the range defined by the cells C14 and D16. You would simply use the formula: =@SUM(1.23,A1,C14..D16) Financial InstaCalc's financial functions allow you to perform many powerful analyses. Functions include formulas to compute time value of money calculations, depreciation calculations, and growth rate calculations. Date/Time InstaCalc represents dates as the number of days since December 31, 1899. For example: Jan 1, 1900 = 1 Jan 2, 1900 = 2 and so on. Time is represented as a fraction of a day (starting at midnight). For example: midnight = 0.0 noon = 0.5 because it is half way through the day, 6:00PM = 0.75 because it is 3/4 way through the day, and so on. This allows dates and times to be manipulated with ordinary math operations. For example, you could find 64 out what day, month, and year 90 days from today is by simply adding 90 to the number which represents today's date. Logical The InstaCalc logical functions evaluate their arguments to determine one thing: truth or falsity. Typically, logical functions supply the value 1 as the representative of truth, and 0 as the representative of falsity. Macro InstaCalc provides several @functions which are designed to be used in application programs. String The InstaCalc string functions allow you to process character strings more efficiently. They are often used when importing data from other programs, printing, and in macros that process strings. Data InstaCalc's data functions allow your formulas to access data in a variety of ways, from lookup tables, to access to global and cell statistics. --------------------------------------------------------------------- @ABS Mathematical --------------------------------------------------------------------- Function Returns the absolute (positive) value of a number. Syntax @ABS(x) where x is a number, a cell address, or a calculated value. Example =@ABS(-212) = 212 =@ABS(25.3) = 25.3 =@ABS(B5) = 13, if the value of B5 is 13 or -13 =@ABS(2-3) = 1 --------------------------------------------------------------------- @ACOS Trigonometry --------------------------------------------------------------------- Function Returns the arc cosine of a number. Syntax @ACOS(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the angle whose cosine is x. The value of x must be between -1 and 1, or @ACOS will return the value ERROR. The value of the angle returned will be in radians, and will lie between 0 and pi. If you want the result to be in degrees instead, then use the @DEGREES function to convert the result. Example =@ACOS(-0.990) = 3 =@ACOS(-1) = 3.14159 65 --------------------------------------------------------------------- @ASIN Trigonometry --------------------------------------------------------------------- Function Returns the arc sine of a number. Syntax @ASIN(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the angle whose sine is x. The value of x must be between -1 to 1, or @ASIN will return the value ERROR. The value of the angle returned will be in radians, and will lie between pi/2 and -pi/2. If you want the result to be in degrees instead, then use the @DEGREES function to convert the result. Example =@ASIN(0.1411) = 0.141572 =@ASIN(1) = 1.570796 =@DEGREES(@ASIN(1)) = 90 --------------------------------------------------------------------- @ATAN Trigonometry --------------------------------------------------------------------- Function Returns the arc tangent of a number. Syntax @ATAN(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the angle whose tangent is x. The value of the angle returned is in radians. If you want the result to be in degrees instead, then use the @DEGREES function to convert the result. Since the @ATAN function finds an angle based on a single value, the result is limited to quadrants I and IV (-pi/2 to pi/2). If you want to find a 4 quadrant angle, you must use the @ATAN2 function. Example =@ATAN(1) = 0.7854 =@DEGREES(@ATAN(1)) = 45 --------------------------------------------------------------------- @ATAN2 Trigonometry --------------------------------------------------------------------- Function Returns the four quadrant arc tangent of y/x. Syntax @ATAN2(x,y) where x and y are numbers, cell addresses, or calculated values. Remarks This function returns the angle whose tangent is y/x. The @ATAN2 function calculates the value of the arc tangent differently than the @ATAN function. Where the @ATAN function takes as its argument the value of the tangent, the 66 @ATAN2 function calculates the value of the arc tangent using the x and y coordinates. The result will be a full 4 quadrant angle, and will be between -pi and pi. Example =@ATAN2(1,1) = 0.7854 =@DEGREE(@ATAN2(1,1)) = 45 =@DEGREE(@ATAN2(-1,-1)) = -135 --------------------------------------------------------------------- @AVG Statistical --------------------------------------------------------------------- Function Returns the average of a list of values. Syntax @AVG(list) where list is a list of values separated by commas, or a range containing values, or some combination of the two. Remarks This function calculates the average, or mean, of the values in list. The @AVG function will completely ignore all items in the list which are text or blank. This means that if a text string is in a range in list, you do not have to worry about it being used incorrectly in the calculation. Example =@AVG(0,1,2,3,4,5,6,7,8,9) = 4.5 If the cells A1 through A10 contain the values listed above, then =@AVG(A1..A10) = 4.5 =@AVG(1234,6435,2567) = 3412 --------------------------------------------------------------------- @CELL Data --------------------------------------------------------------------- Function Returns information about a cell. Syntax @CELL(attribute,range) where attribute is a string in quotations or a cell reference; and range is a cell range, named or otherwise. Remarks This function returns information about the upper left cell in range. Although the information is only for a single cell, range must be entered as a range instead of a single cell. Attribute must be one of the following strings: "address", "col", "contents", "format", "prefix", "protect", "row", or "type". @CELL is primarily for use in applications to determine certain characteristics about a cell. 67 attribute @CELL returns "address" the name of the cell (for example B5) "col" the column number of the cell (1 through 256) "contents" the contents of the cell "format" the formatting of the cell 0 Default 1 Fixed 2 Currency 3 Percent 4 Scientific 5 Hidden 6 Text 7 Fixed with commas 8 Currency with commas 9 Percent with commas 10 dd-mmm-yy 11 dd-mmm 12 mmm-yy 13 mm/dd/yy 14 Month DD, YYYY 15 HH:MM AM/PM 16 HH:MM Military 17 General "prefix" the justification character of the cell ' if left justified " if right justified ^ if centered blank if the cell doesn't contain text "protect" the protection status of the cell 0 = Not protected, 1 = Protected "row" the row number of the cell (1 through 4096) "type" the type of data in the cell. BLANK, FORMAT, NUMBER, TEXT, FORMULA, TXTFORM, ERROR "width" the width of the column the cell is in. --------------------------------------------------------------------- @CHANGED Macro --------------------------------------------------------------------- Function Returns the save status of the Worksheet. Syntax @CHANGED Remarks This function returns a value of 1 (TRUE) if any changes have been made to the Worksheet since it was last saved, and 0 (FALSE) if no changes have been made. This function is useful in applications to easily determine 68 whether the application needs to save the current file or not. Example #IF @changed '/fsMYFILE<Enter> #ENDIF These 3 lines in an application program cause InstaCalc to test whether any changes have been made to the Worksheet, and do a /File Save if they have. --------------------------------------------------------------------- @CHAR String --------------------------------------------------------------------- Function Returns a character, based on its ASCII value. Syntax @CHAR(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the ASCII character for the value x. The value of x must lie in the range 1 to 255. Example =@CHAR(42) = * =@CHAR(55) = 7 =@CHAR(80) = P =@CHAR(113) = q --------------------------------------------------------------------- @CHOOSE Data --------------------------------------------------------------------- Function Choose an item from a list. Syntax @CHOOSE(x,y1,y2,y3...yn) where x is an offset value, and y1...yn is the list of items to choose from. Remarks This function performs a simple lookup based on the value of x. If x has a value of 1, then @CHOOSE will return y1; if x has a value of 2, then @CHOOSE will return y2, and so on. The items in the list can be numeric, text, or both. If x is less than 1 or greater than the number of items in the list, then @CHOOSE will return the value ERROR. Example If A1 contains the value 3, then @CHOOSE(A1,"Sun","Mon","Tue","Wed","Thu","Fri","Sat")=Tue and =@CHOOSE(A1,23,"Item 2",17) = 17 =@CHOOSE(4,"Item 1","Item 2","Item 3") = ERROR 69 --------------------------------------------------------------------- @CINT Financial --------------------------------------------------------------------- Function Returns the principal and interest after compounding. Syntax @CINT(principal,int,n,q) where principal, int, n, and q are numbers, cell addresses, or calculated values. Remarks This function returns the total amount of principal and interest accumulated after compounding. principal is the starting principal amount, int is the rate of interest per period, n is the number of periods, and q is the number of times per period that interest is compounded. --------------------------------------------------------------------- @CNAME Data --------------------------------------------------------------------- Function Returns the name of a cell. Syntax @CNAME(col,row) where col and row are numbers, cell addresses, or calculated values. Remarks This functions returns the name of a cell based on the column and row number of the cell. This function is especially useful in application program commands which require the name of a cell. Example =@CNAME(1,1,1) = A1 =@CNAME(28,2,3) = AB2 --------------------------------------------------------------------- @CODE String --------------------------------------------------------------------- Function Returns the ASCII value of a character. Syntax @CODE(string) where string is a string in quotations, or a cell address. Remarks This function returns the ASCII value of the first character in string. If the length of string is zero, or is not a string, then @CODE will return the value ERROR. Example =@CODE("Test") = 84 =@CODE("test") = 116 =@CODE("@") = 64 =@CODE(" ") = 32 70 --------------------------------------------------------------------- @COLS Data --------------------------------------------------------------------- Function Returns the number of columns in a range. Syntax @COLS(range) where range is a cell range, either named or otherwise. Remarks This function returns the number of columns in range. Example If the range A1..G20 is named BUILDINGS then =@COLS(BUILDINGS) = 7 --------------------------------------------------------------------- @COS Trigonometry --------------------------------------------------------------------- Function Returns the cosine of an angle. Syntax @COS(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the cosine of the angle x. x must be in radians. If x is in degrees, then you must use the @RADIANS function to convert x to radians first. The result of @COS will be between -1 and 1. Since the secant of an angle is simply the reciprocal of the @COS function, you can calculate the secant of an angle by dividing 1 by the cosine of the angle. Example =@COS(3) = -0.9900 =@COS(@PI) = -1 =@COS(@RADIANS(180)) = -1 =1/@COS(4) = -1.52988 (the secant of 4) --------------------------------------------------------------------- @COSH Trigonometry --------------------------------------------------------------------- Function Returns the hyperbolic cosine of an angle. Syntax @COSH(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the hyperbolic cosine of the angle x. x must be in radians. If x is in degrees, then you must use the @RADIANS function to convert x to radians first. Example =@COSH(3) = 10.068 =@COSH(@PI) = 11.592 =@COSH(@RADIANS(180)) = 11.592 71 --------------------------------------------------------------------- @COUNT Statistical --------------------------------------------------------------------- Function Returns the number of values in a list. Syntax @COUNT(list) where list is a list of items separated by commas, or a range containing values, or some combination of the two. Remarks This function counts all of the cells in list which contain data. Blank cells in a range are not counted, but individual cell references are always counted, even if they are blank. Example =@COUNT(0,1,2,3,4,5,6,7,8,9) = 10 If range A1 to A10 contained a list of names, then =@COUNT(A1..A10) = 10 If 5 of those cells were deleted, then =@COUNT(A1..A10) = 5 and =@COUNT(A1..A10,C5) = 6, even if cell C5 is blank. --------------------------------------------------------------------- @CTERM Financial --------------------------------------------------------------------- Function Returns the number of periods for an investment to reach a future value. Syntax @CTERM(rate,fv,pv) where rate, fv and pv are numbers, cell addresses, or calculated values. Remarks This function returns the number of periods needed to reach a given future value, using a specified initial investment and a periodic interest rate. rate is the periodic interest rate, fv is the future value to be reached, and pv is the initial investment. @CTERM is often used in retirement planning, where a fixed amount of money (usually large) is necessary to sustain retirement. @CTERM can determine whether a certain investment is likely to reach the monetary goal in the necessary time period. 72 --------------------------------------------------------------------- @DATE Date/Time --------------------------------------------------------------------- Function Returns a date number for a date. Syntax @DATE(year,month,day) where year, month, and day are numbers, cell addresses, or calculated values. Remarks This function returns the date number for year, month, and day. The date number is the number of days from Dec 31, 1899 to the given date. Formatting a cell containing a date number using the /Range Format Date commands will turn the number into a readable date. The key use for all date commands is in date arithmetic--the determination of time between two periods. Example How many days are between Jan. 16, 1959 and June 10, 1960? =@DATE(60,6,10)-@DATE(59,1,16) = 511. What date is 90 days after April 15, 1989? =@DATE(89,4,15)+90 = 32703 Formatting this value as a date gives July 14, 1989. --------------------------------------------------------------------- @DAY Date/Time --------------------------------------------------------------------- Function Returns the day of the month. Syntax @DAY(date number) where date number is a number, cell address, or calculated value. Remarks This function returns the day of the month of date number. The @DAY function is most often used to avoid the often tedious process of looking up dates; clearly, it is possible to simply look at a cell that is formatted as a date to discover what number is the day that is contained there. Using the @DAY function allows you to bypass the lookup; the function extracts the day automatically. Example =@DAY(@DATE(89,1,16)) = 16 --------------------------------------------------------------------- @DDB Financial --------------------------------------------------------------------- Function Returns the amount of depreciation of an asset using the double-declining balance method. Syntax @DDB(cost,salvage,life,period) where cost, salvage, life, and period are numbers, cell 73 addresses, or calculated values. Remarks This function calculates the amount of depreciation of an asset using the Double-Declining Balance method of accelerated depreciation. cost is the cost of the asset, salvage is the salvage value of the asset after it is totally depreciated, life is the useful life of the asset, and period is the period within that life in which the depreciation is being calculated. The depreciation for a period is: (book value * 2) / life where the book value for a period is: cost - (total depreciation for all prior periods) The total depreciation over the life of the asset will never exceed the assets cost - salvage value. 74 --------------------------------------------------------------------- @DEGREES Trigonometry --------------------------------------------------------------------- Function Converts radians to degrees. Syntax @DEGREES(x) where x is a number, a cell address, or a calculated value. Remarks Although all of InstaCalc's trigonometry functions operate on angular values expressed in radians, it is often easier to analyze angular data when expressed in degrees. The @DEGREES function provides an easy way to convert a value from radians to degrees. Example If cell A1 were a value in radians, and you wanted its value in degrees, entering =@DEGREES(A1) would return the correct value in degrees. =@DEGREES(@PI) = 180 =@DEGREES(@PI/2) = 90 --------------------------------------------------------------------- @DIR Macro --------------------------------------------------------------------- Function Returns the name of the current directory. Syntax @DIR Remarks This function returns the name of the current directory. The @DIR function is used almost exclusively with #IF commands in applications, to test whether the current directory is the proper directory. Example #IF @DIR="C:\InstaCalc\FINANCE" '/fsDATA<Enter> #ENDIF will cause an application to save a file named DATA.INS if the current directory is c:\InstaCalc\finance. --------------------------------------------------------------------- @ENVIRON Macro --------------------------------------------------------------------- Function Returns the value of an environment variable. Syntax @ENVIRON(var) Remarks This function allows your applications to check the DOS environment for variables which may be stored there. Example =@ENVIRON("COMSPEC") = "C:\COMMAND.COM" 75 If your path is set to c:\dos;c:\toolbox then =@ENVIRON("PATH") = "C:\DOS;C:\TOOLBOX" --------------------------------------------------------------------- @ERROR Data --------------------------------------------------------------------- Function Returns the value ERROR. Syntax @ERROR Remarks This function causes the ERROR flag to appear in the cell in which it is entered. The @ERROR function is usually used as an argument in an @IF function. Often it is used to flag values that may be outside of a prescribed range. Example This formula will multiply B5 by 1.1, unless B5 is greater than C5. If this happens, it will flag you with an ERROR. =@IF(B5>C5,@ERROR,B5*1.1) --------------------------------------------------------------------- @EXACT String --------------------------------------------------------------------- Function Compares two text strings. Syntax @EXACT(string1,string2) where string1 and string2 are strings in quotations or cell addresses. Remarks This function returns the value 1 (TRUE) if string1 and string2 are identical, or the value 0 (FALSE) if they are not. Comparing two strings with the @EXACT function is different than comparing them with the = operator. Strings compared with the @EXACT function must be exactly the same for a TRUE result, while strings compared with the = operator may differ in case and still be considered equal. Example If cell D80 contains the string InstaCalc: =@EXACT("InstaCalc",D80) = 1 (TRUE) =@EXACT("INSTACALC",D80) = 0 (FALSE) 76 --------------------------------------------------------------------- @EXP Mathematical --------------------------------------------------------------------- Function Returns e raised to a power. Syntax @EXP(x) where x is a number, a cell address, or a calculated value. Remarks This function returns e^x. e is approximately 2.71828. x must be less than 85.2 or an overflow will occur and the value ERROR will be returned. This function is the inverse of the @LN function. To use the value of e in a formula, use @EXP(1). Example =@EXP(3) = 20.0855 =@EXP(90) = ERROR =@EXP(1) = 2.71828 =@EXP(@LN(5)) = 5 --------------------------------------------------------------------- @FACT Mathematical --------------------------------------------------------------------- Function Returns the factorial of a number. Syntax @FACT(x) where x is a number, a cell address, or a calculated value. Remarks The factorial of x is x * (x-1) * (x-2)...* 1. x must be between 0 and 30 (inclusive), otherwise @FACT will overflow and return ERROR. x should be an integer; if it is not an integer, the program will use the value of the argument rounded to the nearest integer. Example =@FACT(5) = 5*4*3*2*1 = 120 =@FACT(4.7) = 120 =@FACT(45) = ERROR --------------------------------------------------------------------- @FALSE Logical --------------------------------------------------------------------- Function Returns the value FALSE (0). Syntax @FALSE Remarks This function returns the value 0 (FALSE). The primary purpose of the @FALSE function is to help make logical formulas easier to read. Example @IF(B4>1,@TRUE,@FALSE)=0, if B4 is not greater than 1 77 --------------------------------------------------------------------- @FILE Macro --------------------------------------------------------------------- Function Returns various information regarding a file. Syntax @FILE(filename,attribute) where filename and attribute are strings in quotations or cell addresses. Remarks This function returns various information about the file named filename. Attribute is one of the following strings which tells what information to return: "PATH", "DATE", or "EXIST". filename can be a full path name, or just a file name by itself. If filename is just the name of the file, InstaCalc will look for the file in the current directory. @FILE is intended for use in application programs only to test various characteristics of files your program uses. "PATH" returns the full pathname of filename. "DATE" returns a combined date and time number for the date and time that filename was last written to. "EXIST" returns TRUE (logical 1) if filename exists, and FALSE (logical 0) if it doesn't. --------------------------------------------------------------------- @FIND String --------------------------------------------------------------------- Function Finds the occurrence of a string in a longer string. Syntax @FIND(substring,string,n) where substring and string are strings in quotations, or cell addresses, and n is a number, cell address, or calculated value. Remarks substring is the part of a string to find, string is the longer string in which to find the substring, and n designates where in the string to begin looking. The search is case-sensitive. @FIND returns the position in string where substring is found. That is, if substring begins at the 20th character string, 20 will be returned. If no match is found, @FIND returns a value of 0. Example To find the word "time" in the phrase, "Now is the time for all good men to come to the aid of their country,": If the longer string is entered in cell D2, then =@FIND("time",D2,1) = 12 You can also search for the second occurrence of "the" in the above phrase with 78 =@FIND("the",D2,15) InstaCalc will begin the search at the 15th character, and will return the position, 45. --------------------------------------------------------------------- @FNAME Macro --------------------------------------------------------------------- Function Returns the name of the file currently in memory. Syntax @FNAME Remarks The @FNAME function returns the name of the file currently being worked on. The @FNAME function is typically used in macros to determine the current file name without user intervention. --------------------------------------------------------------------- @FORMAT String --------------------------------------------------------------------- Function Converts a number to a string using a selected format. Syntax @FORMAT(x,form,dp) where x, form, and dp are numbers, cell addresses, or calculated values. Remarks This function converts a number to a string using the format form and dp decimal places. @FORMAT is especially useful in applications where you want to print out values using a specific format such as currency. form FORMAT 0 Default 1 Fixed 2 Currency 3 Percent 4 Scientific 5 Hidden 6 Text 7 Fixed with commas 8 Currency with commas 9 Percent with commas 10 dd-mmm-yy 11 dd-mmm 12 mmm-yy 13 mm/dd/yy 14 Month DD, YYYY 15 HH:MM AM/PM 16 HH:MM Military 17 General Example =@FORMAT(1234.567,8,2) = $1,234.57 79 =@FORMAT(0.12345,3,2) = 12.35% =@FORMAT(33000,14,0) = May 7, 1990 --------------------------------------------------------------------- @FRAC Mathematical --------------------------------------------------------------------- Function Returns the fractional part of a number. Syntax @FRAC(x) where x is a number, a cell address, or a calculated value. Remarks This function returns only the part of x which comes after the decimal point. If x is negative, then the value returned by @FRAC will be negative also. Example =@FRAC(1.23) = 0.23 =@FRAC(-1.23) = -0.23 =@FRAC(@PI) = 0.14159 --------------------------------------------------------------------- @FV Financial --------------------------------------------------------------------- Function Returns the future value of an annuity. Syntax @FV(pmt,int,term) where pmt, int, term are numbers, cell addresses, or calculated values. Remarks This function calculates the future value of an annuity. pmt is the amount of payment per period in the annuity, int is the discount rate per period, and term is the total number of payments to be made. @FV assumes that payments are being made at the end of the period. If payments are due at the beginning of the period, simply multiply the answer by (1+int). IMPORTANT: The interest rate must be a per period rate. For example, if the term is 240, indicating (probably) monthly payments over twenty years, the interest rate must also be a monthly rate. Thus, a 12% yearly rate would be entered as 1%. Example To calculate the future value of an ordinary annuity in which the payments are $500.00, made monthly for 20 years, and the yearly interest rate is 10%: =@FV(500,0.1/12,240) = 379,684.42 =@FV(500,0.1/12,240)*(1+0.1/12) = 382,848.45 80 --------------------------------------------------------------------- @GLOBAL Data --------------------------------------------------------------------- Function Returns information about the current Worksheet. Syntax @GLOBAL(attribute) Remarks This function returns global information about the current worksheet. Attribute is one of the following strings which tells what information to return: "prefix", "protect", "width", "format","calc", "insert", or "enter". @GLOBAL is intended for use in application programs to test various characteristics of the current worksheet. attribute @GLOBAL returns "prefix" the global justification character ' if left justified " if right justified ^ if centered "protect" the Worksheet protection 0 = Disabled, 1 = Enabled "width" the global column width "format" the global format 0 Default 1 Fixed 2 Currency 3 Percent 4 Scientific 5 Hidden 6 Text 7 Fixed with commas 8 Currency with commas 9 Percent with commas 10 dd-mmm-yy 11 dd-mmm 12 mmm-yy 13 mm/dd/yy 14 Month DD, YYYY 15 HH:MM AM/PM 16 HH:MM Military 17 General "calc" the recalculation mode 0 = Manual, 1 = Automatic "insert" the status of insert mode 0 = Off, 1 = On "enter" the status of <Ctrl><F9> Enter mode 0 = Off, 1 = On 81 --------------------------------------------------------------------- @HLOOKUP Data --------------------------------------------------------------------- Function Looks up an item in a horizontal lookup table. Syntax @HLOOKUP(x,range,row offset) where range is a range of cells, named or otherwise; row offset is a number, cell address, or calculated value; and x is a number, text, cell address, or calculated value. Remarks This function looks up and retrieves a value or text from a horizontal lookup table. range is the range of cells which makes up the lookup table. If x is text, @HLOOKUP will compare x with each cell in the top row of range, and if a match is found, will move row offset rows from that cell, and will return the value in the resulting cell. If x does not match any of the cells in the top row of range, then @HLOOKUP will return the value ERROR. If x is a value instead of text, @HLOOKUP will compare x with each cell in the top row of range until it finds a cell which is greater than or equal to x. This means that the values in the top row of range must be in ascending order for @HLOOKUP to work correctly. If the value in the cell is equal to x, @HLOOKUP will consider it a match. If the cell is greater than x, @HLOOKUP will move back to the previous cell and consider it a match. For example, if the values in the top row of range are 100, 200, 300, and 400, and the value of x is 250, then @HLOOKUP will search until it reaches 300, and will then move back and use the cell containing 200 as the match. When a match is found, @HLOOKUP will move row offset rows from that cell, and will return the value in the resulting cell. If x is smaller than the first cell in the top row, @HLOOKUP will return the value ERROR. 82 --------------------------------------------------------------------- @HOUR Date/Time --------------------------------------------------------------------- Function Returns the hour of a time number. Syntax @HOUR(time number) where time number is a number, cell address, or calculated value. Remarks This function returns the hour of time number. A result of 0 represents midnight, 1 represents 1:00 p.m., and 23 represents 11:00 p.m. time number should be a value between 0 and 1. InstaCalc will not ignore a mixed number; it will simply use the fractional part. The @HOUR function is most often used in conjunction with other date and time functions to return only the hour portion of a previously entered value. Example =@HOUR(@NOW) = the current hour (in military time) =@HOUR(@TIME(13,40,55)) = 13 --------------------------------------------------------------------- @IF Logical --------------------------------------------------------------------- Function Returns one of two values depending on a condition. Syntax @IF(condition,x,y) where condition is a conditional statement, often in the form of an equation, other times in the form of a "greater than" or "less than" statement (or other inequality); and x and y are values, functions, or strings. Remarks This functions evaluates condition and returns x if it is TRUE, and y if it is FALSE. @IF is one of the most powerful InstaCalc functions. It not only evaluates whether a condition exists, it also takes action depending on the existence or non-existence of the condition. More importantly, conditions can be built which are very complex, and may incorporate the Boolean operators (&, |, and ~) in addition to the logical operators. Nested @IF functions are allowed. x and y may be both numeric, both text, or one of each. 83 --------------------------------------------------------------------- @INDEX Data --------------------------------------------------------------------- Function Looks up data in a array. Syntax @INDEX(range,col,row) where range is a range of cells, named or otherwise; and col and row are numbers, cell addresses, or calculated values. Remarks This function looks up and returns data from an array. range is the array, and col and row are the offsets into the array. The top row of range has a row offset of 0 and the left column of range has a column offset of 0. --------------------------------------------------------------------- @INT Mathematical --------------------------------------------------------------------- Function Returns the integer part of a number. Syntax @INT(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the integer part of a value, which is the part of the argument which is to the left (in front of) the decimal point. The sign of the value returned is the same as that of the argument. The @INT function does not round values for further calculation. (To round numbers, use @ROUND.) It merely truncates the numbers; only the integer part of the value is used in further calculations. The sign of the value can be either positive or negative. Example =@INT(-2.34) = -2 =@INT(1.2) = 1 =@INT(1.99) = 1 =@INT(@PI) = 3 --------------------------------------------------------------------- @IRR Financial --------------------------------------------------------------------- Function Returns the internal rate of return of a series of cash flows. Syntax @IRR(estimate,range) where estimate is a number, cell address, or calculated value; and range is a range of cells, named or otherwise. Remarks This function calculates the discount rate at which the present value of an outlay (investment) equals the present value of cash inflows stemming (presumably) from that investment. Another way of saying this is that it is the discount rate at which the net present value of outlays and 84 inflows is zero. estimate should be a value between 0 and 1 that represents a reasonable guess at the rate of return for the investment, and range is a range of cells that contain the net cash flows resulting from the investment. The first value in the range must be negative, indicating an investment was made which constituted an outflow of moneys. It is assumed that the cash flows that appear in the range are received at regular intervals. They need not be even, but they should at least approximate regular flows. The @IRR function further assumes that the income from the investment is reinvested at the rate of return. If this not the case (as with a capital investment in plant equipment), @IRR will be overstated. The amount of overstatement depends on the disparity between the assumed reinvestment and the actual rate of reinvestment. Finally, since @IRR is calculated by iteration, a really poor estimate may cause the function to misstate the discount rate. --------------------------------------------------------------------- @ISERR Logical --------------------------------------------------------------------- Function Tests whether an item is an error. Syntax @ISERR(x) where x is a cell reference or a formula. Remarks This function is used to determine whether x is the value ERROR. @ISERR is used to stop ERROR values from carrying through to other parts of the worksheet. Example @ISERR(1/0) = 1 (TRUE) @ISERR(A1) = 1 if cell A1 contains the value ERROR; otherwise it equals 0. --------------------------------------------------------------------- @ISNUM Logical --------------------------------------------------------------------- Function Tests whether an item is a number. Syntax @ISNUM(x) where x is a cell address or a formula. Remarks This function returns the value 1 (TRUE) if x is a numerical value; otherwise it returns the value 0 (FALSE). Example @ISNUM(1) = 1 (TRUE) 85 @ISNUM("text") = 0 (FALSE) @ISNUM(A1) = 1 if cell A1 contains a numeric value; otherwise it equals 0. --------------------------------------------------------------------- @ISTEXT Logical --------------------------------------------------------------------- Function Tests whether an item is text. Syntax @ISTEXT(x) where x is a cell address or a formula. Remarks This function returns the value 1 (TRUE) if x is a textual value; otherwise it returns the value 0 (FALSE). Example @ISTEXT(1) = 0 (FALSE) @ISTEXT("text") = 1 (TRUE) @ISTEXT(A1) = 1 if cell A1 contains a textual value; otherwise it equals 0. --------------------------------------------------------------------- @LEFT String --------------------------------------------------------------------- Function Returns the left part of a text string. Syntax @LEFT(string,n) where string is a string in quotations, or a cell address; and n is a number, cell address, or calculated value. Remarks This function returns the leftmost n characters from string. @LEFT is often used when extracting portions of a string-- for example, an area code or the first digits of a billing code. --------------------------------------------------------------------- @LEN String --------------------------------------------------------------------- Function Returns the length of a text string. Syntax @LEN(string) where string is a string in quotations, or a cell address. Remarks This function returns the number of characters in string. Example If cell A5 contains 'Goodbye and B9 contains ' cruel world then =@LEN(A5) = 7 =@LEN(B9) = 12 86 =@LEN(A5+B9) = 19 --------------------------------------------------------------------- @LN Mathematical --------------------------------------------------------------------- Function Returns the natural logarithm of a number. Syntax @LN(x) where x is a number, a cell address, or a calculated value. Remarks This function calculates the natural logarithm (base e) of x. It is the inverse of the @EXP function. The value cannot be negative or equal to zero, or an ERROR value will be returned. Example =@LN(1000) = 6.9078 =@LN(@EXP(3)) = 3 since @LN is the inverse of the @EXP function. --------------------------------------------------------------------- @LOG Mathematical --------------------------------------------------------------------- Function Returns the common logarithm of a number. Syntax @LOG(x) where x is a number, a cell address, or a calculated value. Remarks The @LOG function returns the logarithm of x. It uses the base 10, and is the reciprocal of exponentiation. The value must be greater than zero; a negative or zero value returns an ERROR value. Example =@LOG(1000) = 3 =@LOG(10^5) = 5 =@LOG(-12) = ERROR 87 --------------------------------------------------------------------- @LOGB Mathematical --------------------------------------------------------------------- Function Returns the logarithm (base b) of a number. Syntax @LOGB(x,b) where x and b are numbers, cell addresses, or calculated values. Remarks This function calculates the logarithm of x. It uses the base b, and is the reciprocal of exponentiation. x must be greater than zero, otherwise @LOGB will return the value ERROR. Example =@LOGB(1000,17) = 2.438134 =@LOGB(17^5,17) = 5 =@LOGB(-12,3) = ERROR --------------------------------------------------------------------- @LOWER String --------------------------------------------------------------------- Function Converts all the characters of a text string to lowercase. Syntax @LOWER(string) where string is a string in quotations, or a cell address. Remarks The @LOWER function converts all of the characters in string to lowercase letters. Example If A1 contains the text "Instruction Manual", then =@LOWER(A1) = instruction manual --------------------------------------------------------------------- @MAX Statistical --------------------------------------------------------------------- Function Returns the maximum value from a list of values. Syntax @MAX(list) where list is a list of values separated by commas, or a range containing values, or some combination of the two. Remarks This function returns the largest value in list. When lists become very large, it becomes useful to easily and quickly determine the largest value in the list. The @MAX function accommodates this need. Blank and text cells are ignored by @MAX. If the list consists entirely of blank cells, the value ERROR will be returned, because you can't determine a maximum of no values. Example =@MAX(0,1,2,3,4,5,6,7,8,9) = 9 88 --------------------------------------------------------------------- @MEMORY Macro --------------------------------------------------------------------- Function Returns the amount of memory available. Syntax @MEMORY Remarks This function returns the number of bytes of memory available. --------------------------------------------------------------------- @MID String --------------------------------------------------------------------- Function Returns the midsection of a text string. Syntax @MID(string,start,n) where string is a string in quotations, or a cell address; and start and n are numbers, cell addresses, or calculated values. Remarks This function searches string, starting with the start character, and extracts and returns n characters. --------------------------------------------------------------------- @MIN Statistical --------------------------------------------------------------------- Function Returns the minimum value from a list of values. Syntax @MIN(list) where list is a list of values separated by commas, or a range containing values, or some combination of the two. Remarks This function returns the smallest value in list. When lists become very large, it becomes useful to easily and quickly determine the smallest value in the list. The @MIN function accommodates this need. Blank and text cells are ignored. If the list consists entirely of blank cells, the value ERROR will be returned because you can't determine the minimum of no values. Example =@MIN(0,1,2,3,4,5,6,7,8,9) = 0 If cells A1 to A10 contained those values, but in a different order, the formula =@MIN(A1..A10) would still return the value 0. --------------------------------------------------------------------- @MINUTE Date/Time --------------------------------------------------------------------- Function Returns the minute of a time number. Syntax @MINUTE(time number) 89 where time number is a number, cell address, or calculated value. Remarks This function returns the number of the minute (between 0 and 59) represented by time number. time number should be a value between 0 and 1 (not inclusive). InstaCalc will not ignore mixed numbers; rather, it will extract only the fractional part. @MINUTE is most often useful in conjunction with other date and time functions. @MINUTE will extract only the minute portion of a time value. Example To find the current minute, and enter it into a cell, enter: =@MINUTE(@NOW) =@MINUTE(@TIME(10,20,30)) = 20 --------------------------------------------------------------------- @MOD Mathematical --------------------------------------------------------------------- Function Returns the remainder after dividing two numbers. Syntax @MOD(x,y) where x and y are numbers, cell addresses, or calculated values. Remarks This function returns the remainder after dividing x by y. @MOD function will typically be used any time that it is necessary to use the remainder in calculations. @MOD can be used to determine if x is odd or even by setting y to 2. If the result is 0, then x is even; if the result is 1, then x is odd. If y has a value of 0, ERROR will be returned, since dividing by 0 is illegal. Example =@MOD(5,2) = 1 =@MOD(6,2) = 0 =@MOD(12,7) = 5 --------------------------------------------------------------------- @MONTH Date/Time --------------------------------------------------------------------- Function Returns the month of a date number. Syntax @MONTH(date number) where date number is a number, cell address, or calculated value. Remarks This function returns only the month portion (1..12) of date number. Most often, the @MONTH function is used in conjunction with other date function. @MONTH extracts the month portion of 90 the date given in its argument. Example If the current month is April, then =@MONTH(@NOW) = 4 =@MONTH(@DATE(89,11,3)) = 11 --------------------------------------------------------------------- @NMONTH Date/Time --------------------------------------------------------------------- Function Returns the name of a month. Syntax @NMONTH(x) where x is a number, cell address, or calculated value. Remarks The @NMONTH function returns the name of month x. @NMONTH is often used in conjunction with the @MONTH function to return the month name of a date number. Example If A1 contains the date number 32524 (the date number for 1/16/89), then =@NMONTH(@MONTH(A1)) = January If the current month is October, then =@NMONTH(@MONTH(@NOW)) = October 91 --------------------------------------------------------------------- @NOW Date/Time --------------------------------------------------------------------- Function Returns the current date and time numbers. Syntax @NOW Remarks This function reads the system clock and returns a combined date and time number for the current date and time. The integer part of the result will be the date number for the current date, and the fractional part of the result will be the time number for the current time. --------------------------------------------------------------------- @NPV Financial --------------------------------------------------------------------- Function Returns the net present value of a series of future cash flows. Syntax @NPV(int,range) where int is a number, a cell address, or a calculated value, and range is a range of cells, named or otherwise. Remarks This function calculates the net present value of a series of future cash flows in range, given the discount rate int. Each of the future cash flows are brought back to the present; that is, each flow receives its equivalent present value. The period of the discount must agree with the period of the cash flows. That is, if the cash flows are yearly flows, then the discount rate must be a yearly rate; if the cash flows are monthly, then the discount rate must be monthly. Generally, yearly cash flows will be accurate enough. Even if the flows actually come on a monthly basis, summing up those flows into a single yearly flow will only change the answer by a small percentage. @NPV assumes that the period between cash flows is constant. @NPV assumes end-of-period-payments. For beginning-of- period-payments, multiply the answer by (1+int). --------------------------------------------------------------------- @PI Trigonometry --------------------------------------------------------------------- Function Returns the value of pi (3.14159...). Syntax @PI Remarks The @PI function simply returns the value of pi, 3.141592635... Example Entering the formula 2*@PI*5.3 returns the circumference of a circle with a radius of 5.3, or 33.30088. 92 --------------------------------------------------------------------- @PMT Financial --------------------------------------------------------------------- Function Returns the payment required to amortize a loan amount. Syntax @PMT(prin,int,term) where prin, int, and term are numbers, cell addresses, or calculated values. Remarks This function calculates the periodic payment required to amortize a loan amount, given the amount of principal, the periodic interest rate, and the number of payments. prin is the total amount of principal, int is the periodic interest rate, and term is the number of payments in the loan. The @PMT function assumes a fixed rate of interest. It also requires (like all of the financial functions) that the interest and term in the argument are expressed in like intervals; that is, a monthly term requires a monthly interest rate. Example If you were looking at a home that would require a $75,000 loan, at 10% yearly interest for 30 years (360 monthly payments), how much would your monthly payment be? =@PMT(75000,0.1/12,360) = $658.18. 93 --------------------------------------------------------------------- @POINTER Data --------------------------------------------------------------------- Function Returns information about the current cell. Syntax @POINTER(attribute) where attribute is a string in quotations or a cell reference. Remarks This function returns various types of information about the cell which the cell pointer is currently in. This function is intended for use in application programs which need information about the current cell. attribute determines what information is returned, and must be one of the following strings; "address", "col", "contents", "format", "prefix", "protect", "row", or "type". Example attribute @POINTER returns "address" the name of the current cell (for example B5) "col" the column number of the current cell (1 thru 256) "contents" the contents of the current cell "format" the formatting of the current cell 0 Default 1 Fixed 2 Currency 3 Percent 4 Scientific 5 Hidden 6 Text 7 Fixed with commas 8 Currency with commas 9 Percent with commas 10 dd-mmm-yy 11 dd-mmm 12 mmm-yy 13 mm/dd/yy 14 Month DD, YYYY 15 HH:MM AM/PM 16 HH:MM Military 17 General "prefix" the justification character of the current cell ' if left justified " if right justified ^ if centered blank if the cell doesn't contain text "protect" the protection status of the current cell 0 = Not protected, 1 = Protected "row" the row number of the current cell (1 through 4096) 94 "type" the type of data in the current cell BLANK, FORMAT, NUMBER, TEXT, FORMULA, TXTFORM, ERROR --------------------------------------------------------------------- @PROPER String --------------------------------------------------------------------- Function Capitalizes the first letter of each word in a text string. Syntax @PROPER(string) where string is a string in quotations, or a cell address. Remarks This function converts the first letter of words in string to uppercase. @PROPER will only recognize words that are separated by spaces, or other non alpha characters. Example =@PROPER("this is a test") = This Is A Test =@PROPER("mid-size") = Mid-Size since the dash is a non alpha character. --------------------------------------------------------------------- @PV Financial --------------------------------------------------------------------- Function Returns the present value of an annuity. Syntax @PV(pmt,int,term) where pmt, int, and term are numbers, cell addresses, or calculated values. Remarks This function calculates the present value of an annuity, given the periodic payment, the periodic interest rate, and the number of periods. pmt is the payment per period, int is the interest rate per period, and term is the total number of payments. The @PV function does precisely the same thing as the @NPV function, except that all payments are equal. Thus, it is only necessary to enter the amount once, rather than as a series of values. The interest rate and term must agree; monthly payments must be accompanied by a monthly term. @PV assumes end-of-period-payments. For beginning-of- period-payments, multiply the answer by (1+int). Example To calculate the present value of an annuity in which the 95 payments are made monthly for 36 months, the interest rate is 10% per year, and the payments are $400, enter the formula: =@PV(400,0.1/12,36) InstaCalc returns the value $12,396.42. --------------------------------------------------------------------- @QUARTER Date/Time --------------------------------------------------------------------- Function The Quarter Function Syntax @QUARTER(date number) where date number is a number, cell address, or calculated value. Remarks This function returns the number of the quarter of the year (1 to 4) of date number. The quarters are: Quarter 1, January 1 to March 31; Quarter 2, April 1 to June 30; Quarter 3, July 1 to September 30; and Quarter 4, October 1 to December 31. Example If cell A1 contains the date number of Feb. 1, 1989 (32540), then =@QUARTER(A1) = 1 (the first quarter) --------------------------------------------------------------------- @RADIANS Trigonometry --------------------------------------------------------------------- Function Converts degrees to radians. Syntax @RADIANS(x) where x is a number, a cell address, or a calculated value. Remarks This function converts the angle x from degrees to radians. Since InstaCalc's trigonometry functions require angles to be expressed in radians, the @RADIANS function is useful for making this conversion. Example =@RADIANS(180) = 3.14159 =@RADIANS(45) = 0.7854 96 --------------------------------------------------------------------- @RAND Mathematical --------------------------------------------------------------------- Function Returns a uniformly distributed random number. Syntax @RAND Remarks This function generates a random number which is greater than or equal to zero and is less than one. The value of @RAND will change to a new random value each time the cell (not the Worksheet) is recalculated. If you want the numbers to fall within a specified range, multiply the @RAND result by the difference between the high and low values in a range; then add this result to the lower value in the range. Example If you want to generate a random number which falls between 20 and 25, then use 20+(5*@RAND) --------------------------------------------------------------------- @RATE Financial --------------------------------------------------------------------- Function Returns the interest rate at which a present value will grow to a future value. Syntax @RATE(pv,fv,term) where pv, fv, and term are numbers, cell addresses, or calculated values. Remarks This function returns the interest rate at which a present value will grow into a future value during a number of periods. pv is the present value amount of an investment, fv is the future value amount of the investment, and term is the number of periods. The periods are assumed to be regular intervals. Converting the compound growth rate on a yearly basis, given that the term is expressed in months, simply requires that the answer be multiplied by 12. Example If you want to calculate the periodic interest rate that will be required to make an investment of $1,000 grow into $1,750 in five years, =@RATE(1000,1750,5) = 0.1184 (or 11.84%) 97 --------------------------------------------------------------------- @REPEAT String --------------------------------------------------------------------- Function Repeats text a given number of times. Syntax @REPEAT(string,n) where string is a string in quotations or a cell address; and n is a number, cell address, or calculated value. Remarks This function returns string repeated n times. Example =@REPEAT("abc",3) = abcabcabc =@REPEAT("-",15) = --------------- --------------------------------------------------------------------- @REPLACE String --------------------------------------------------------------------- Function Replaces a substring in a string with a new string. Syntax @REPLACE(string,start,n,new string) where string and new string are strings in quotations or cell addresses; and start and n are numbers, cell addresses, or calculated values. Remarks This function removes n characters from string at position start, and inserts new string at the same position. @REPLACE not only allows replacement of a string; it also serves as an insert or a delete function. If n is set to zero, this is simply an insert function. If new string is empty, this serves as a delete function. Example If A1 contains the string This is a test, then =@REPLACE(A1,11,4,"game") = This is a game =@REPLACE(A1,11,0," game") = This is a game test =@REPLACE(A1,6,5,"") = This test --------------------------------------------------------------------- @RIGHT String --------------------------------------------------------------------- Function Returns the right part of a text string. Syntax @RIGHT(string,n) where string is a string in quotations or a cell address, and n is a number, cell address, or calculated value. Remarks This function extracts and returns n characters from the end of string. If n is greater than the number of characters in string, then the entire string is returned. See Also @LEFT, @MID. Example Column A contains a list of customer phone numbers. You want to create customer numbers by using the last four 98 digits of the telephone number. Just use =@RIGHT(A1,4) for the first number. If the telephone number were (208)882- 8656, InstaCalc would return the string 8656. =@RIGHT("this is a test",8) = s a test --------------------------------------------------------------------- @ROUND Mathematical --------------------------------------------------------------------- Function Rounds a value to a given number of decimal places. Syntax @ROUND(x,n) where x and n are numbers, cell addresses, or calculated values. Remarks This function returns the value of x rounded to n places. n can be either positive or negative; when n is negative, the program rounds n digits to the left of the decimal point. When n is positive, the program rounds n digits to the right of the decimal. Example =@ROUND(123.456,-2) = 100 =@ROUND(123.456,-1) = 120 =@ROUND(123.456,2) = 123.46 =@ROUND(123.456,0) = 123 --------------------------------------------------------------------- @ROWS Data --------------------------------------------------------------------- Function Returns the number of rows in a range. Syntax @ROWS(range) Remarks This function returns the number of rows in range. @ROWS is particularly useful when used with named ranges. Example If the range A1..B10 is named COST then =@ROWS(COST) = 10 --------------------------------------------------------------------- @SAMPSTD Statistical --------------------------------------------------------------------- Function Returns the standard deviation of a list of sample values. Syntax @SAMPSTD(list) where list is a list of values separated by commas, or a range containing values, or some combination of the two. Remarks This function calculates the standard deviation of the values in list. 99 Standard deviation is a measure of dispersion; that is, it measures the degree to which each value in list varies from the mean of the values in list. @SAMPSTD will completely ignore all items in list which are text or blank. This means that if a text string is in a range in list, you do not have to worry about it being used incorrectly in the calculation. If you want to calculate the standard deviation of an entire population rather than a sample of a population, you must use @STD. Example =@SAMPSTD(0,1,2,3,4,5,6,7,8,9) = 3.028 Similarly, if cells A1 to A10 contained those values, the formula =@SAMPSTD(A1..A10) would return the value 3.028 --------------------------------------------------------------------- @SAMPVAR Statistical --------------------------------------------------------------------- Function Returns the variance of a list of sample values. Syntax @SAMPVAR(list) where list is a list of values separated by commas, or a range containing values, or some combination of the two. Remarks This function returns the variance of the values contained in list. The @SAMPVAR function measures dispersion, much like the @SAMPSTD function. In fact, the variance of list is simply the square of its standard deviation. The @SAMPVAR function will completely ignore all items in the list which are text or blank. This means that if a text string is in a range in list, you do not have to worry about it being used incorrectly in the calculation. If you want to calculate the variance of an entire population rather than a sample of a population, you must use @VAR. Example =@SAMPVAR(0,1,2,3,4,5,6,7,8,9) = 9.1666 Similarly, if cells A1 to A10 contain those values =@SAMPVAR(A1..A10) = 9.1666 100 --------------------------------------------------------------------- @SECOND Date/Time --------------------------------------------------------------------- Function Returns the second of a time number. Syntax @SECOND(time number) where time number is a number, a cell address, or a calculated value. Remarks This function returns the value in seconds (between 0 and 59) of time number. time number should be a value between 0 and 1. InstaCalc will not ignore mixed numbers; rather, it will extract only the fractional part. The argument may be either a value, a cell reference to a value, or a cell reference to a formula which calculates a value. @SECOND is used whenever it is important to extract only the second portion of a time value. This function is most often used in conjunction with other time functions, such as @NOW or @TIME. Example If the current time were 10:43.34, then =@SECOND(@NOW) = 34 =@SECOND(@TIME(10,20,30)) = 30 --------------------------------------------------------------------- @SIGN Mathematical --------------------------------------------------------------------- Function Returns the sign of a number. Syntax @SIGN(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the sign of its x. If x is negative, the function returns -1. If x positive, the function returns 1. If x is 0, the function returns 0. Example =@SIGN(23.4) = 1 =@SIGN(-13.9) = -1 =@SIGN(0) = 0 =@SIGN(@PI) = 1 --------------------------------------------------------------------- @SIN Trigonometry --------------------------------------------------------------------- Function Returns the sine of an angle. Syntax @SIN(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the sine of the angle x. x must be in 101 radians. If x is in degrees, then the @RADIANS function must be used to convert x to radians. The value returned by the @SIN function will always lie between -1 and 1. Example =@SIN(3) = 0.1411 =@SIN(@PI) = 0 =@SIN(@RADIANS(90)) = 1 --------------------------------------------------------------------- @SINH Trigonometry --------------------------------------------------------------------- Function Returns the hyperbolic sine of an angle. Syntax @SINH(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the hyperbolic sine of the angle x. x must be in radians. If x is in degrees, then the @RADIANS function must be used to convert x to radians. Example =@SINH(3) = 10.018 =@SINH(@PI) = 11.549 --------------------------------------------------------------------- @SLN Financial --------------------------------------------------------------------- Function Returns the amount of depreciation of an asset using the straight line method. Syntax @SLN(cost,salvage,life) where cost, salvage, and life are numbers, cell addresses, or calculated values. Remarks This function calculates and returns the amount of depreciation of an asset using the Straight-Line method of accelerated depreciation. cost is the cost of the asset, salvage is the salvage value of the asset after it is totally depreciated, and life is the useful life of the asset. --------------------------------------------------------------------- @SQRT Mathematical --------------------------------------------------------------------- Function Returns the square root of a value. Syntax @SQRT(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the square root of x. x must be positive, since any two negative numbers multiply to a positive number. 102 Since @SQRT will not work with negative numbers, it will often be necessary to convert negative values to positive ones. To do this, simply combine the @SQRT function with the @ABS function. Example =@SQRT(16) = 4 =@SQRT(-9) = ERROR =@SQRT(@ABS(-9)) = 3 --------------------------------------------------------------------- @STD Statistical --------------------------------------------------------------------- Function Returns the standard deviation of a list of values. Syntax @STD(list) where list is a list of values separated by commas, or a range containing values, or some combination of the two. Remarks This function calculates the standard deviation of the values in list. Standard deviation is a measure of dispersion; that is, it measures the degree to which each value in list varies from the mean of the values in list. @STD will completely ignore all items in list which are text or blank. This means that if a text string is in a range in list, you do not have to worry about it being used incorrectly in the calculation. @STD calculates the standard deviation of an entire population. If you want to calculate the standard deviation of a sample of a population rather than the entire population, you must use @SAMPSTD. Example =@STD(0,1,2,3,4,5,6,7,8,9) = 2.87 Similarly, if cells A1 to A10 contained those values, the formula =@STD(A1..A10) would return the value 2.87 --------------------------------------------------------------------- @STRING String --------------------------------------------------------------------- Function Converts a value to a string. Syntax @STRING(x,n) where x and n are values, cell addresses, or calculated values. Remarks This function converts x to a string with n decimal places. To perform string operations on values, you must use either 103 the @STRING or @FORMAT functions to convert the value to a string. Example If cell A1 contains the function @PI, then =@STRING(A1,3) will return the string 3.142 --------------------------------------------------------------------- @SUM Statistical --------------------------------------------------------------------- Function Returns the sum of a list of values. Syntax @SUM(list) where list is a list of values separated by commas, or a range containing values, or some combination of the two. Remarks This function adds up all of the values contained in list. @SUM will completely ignore all items in list which are text or blank. This means that if a text string is in a range in list, you do not have to worry about it being used incorrectly in the calculation. Example =@SUM(0,1,2,3,4,5,6,7,8,9) = 45 Similarly, if cells A1 to A10 contained those same values, entering the formula =@SUM(A1..A10) would return the value 45. --------------------------------------------------------------------- @SYD Financial --------------------------------------------------------------------- Function Returns the amount of depreciation of an asset using the Sum of the Years' Digits method. Syntax @SYD(cost,salvage,life,period) where cost, salvage, life, and period are numbers, cell addresses, or calculated values. Remarks This function calculates the amount of depreciation of an asset using the Sum-of-the-Years'-Digits method of accelerated depreciation. cost is the cost of the asset, salvage is the salvage value of the asset after it is totally depreciated, life is the useful life of the asset, and period is the period within that life in which the depreciation is being calculated. Example Your company purchased a piece of equipment last year for $40,000. Its scrap value has been determined to be $11,000, 104 and its useful life is seven years. How much depreciation should be assigned to it in this, its second year of useful life? =@SYD(40000,11000,7,2) = $6,214.29 --------------------------------------------------------------------- @TAN Trigonometry --------------------------------------------------------------------- Function Returns the tangent of an angle. Syntax @TAN(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the tangent of the angle x. x must be expressed in radians. If x is in degrees, then the @RADIANS function must be used to convert x to radians. Example =@TAN(3) = -0.1425. =@TAN(7) = 0.8714. --------------------------------------------------------------------- @TANH Trigonometry --------------------------------------------------------------------- Function Returns the hyperbolic tangent of an angle. Syntax @TANH(x) where x is a number, a cell address, or a calculated value. Remarks This function returns the hyperbolic tangent of the angle x. x must be expressed in radians. If x is in degrees, then the @RADIANS function must be used to convert x to radians. Example =@TANH(3) = 0.99505 =@TANH(0) = 0 --------------------------------------------------------------------- @TERM Financial --------------------------------------------------------------------- Function Returns the number of periods for an investment to reach a future value. Syntax @TERM(pmt,int,fv) where pmt, int, and fv are numbers, cell addresses, or calculated values. Remarks This function returns the number of terms needed to reach a specified future value, given an interest rate (rate of return) and the value of equal payment to be made each period. pmt is the amount of the equal periodic payments, int is the 105 interest rate or expected rate of return, and fv is the future value to be reached. Many investments (including many people's savings accounts) take a series of equal payments. To determine how long it will take to accumulate a specific future value, use the @TERM function. Example You deposit $500 per month in your savings account, which earns 7% annually. You need $9,000 to make the down payment on your first home. How many months will it take to accumulate that much? First, convert the yearly interest to months. Divide .07 by 12; monthly interest is .00583. Enter the formula: =@TERM(500,0.00583,9000) The formula returns the number of months to achieve the goal, 17. --------------------------------------------------------------------- @TIME Date/Time --------------------------------------------------------------------- Function Returns a time number for a time. Syntax @TIME(hour,min,sec) where hour, min, and sec are numbers, cell addresses, or calculated values. Remarks This function returns a time number between 0 and 1, from hour, min, and sec. hour is the hour of the day (between 0 and 23), min is the minute of the hour (between 0 and 59), and sec is the seconds of the minute (between 0 and 59). It is usually necessary to format the cell containing @TIME using the /Cell Format Date command. Example If cell A4 contains the following formula and is formatted in a time format, then =@TIME(15,12,25) in cell A4 would return the time: 3:12:25 --------------------------------------------------------------------- @TODAY Date/Time --------------------------------------------------------------------- Function Returns the date number of the current date. Syntax @TODAY Remarks This function reads the system clock and returns the date 106 number for the current date. --------------------------------------------------------------------- @TRIM String --------------------------------------------------------------------- Function Trims all extra spaces out of a text string. Syntax @TRIM(string) where string is a string in quotations, or a cell address. Remarks This function strips all extra spaces from string. The result contains only single spaces between words. Often, importing ASCII files will produce strings with extraneous spaces. The problem is overcome with @TRIM. Example The imported string in A1 reads: This is a test. Entering the formula =@TRIM(A1) produces the new string: This is a test. --------------------------------------------------------------------- @TRUE Logical --------------------------------------------------------------------- Function Returns the value TRUE (1). Syntax @TRUE Remarks This function returns the value 1 (TRUE). 107 --------------------------------------------------------------------- @UPPER String --------------------------------------------------------------------- Function Converts all the characters of a text string to uppercase. Syntax @UPPER(string) where string is a string in quotations, or a cell address. Remarks The @UPPER function converts all of the characters in string to uppercase. Example If cell A1 contains the text "Instruction Manual" =@UPPER(A1) = INSTRUCTION MANUAL --------------------------------------------------------------------- @VALUE String --------------------------------------------------------------------- Function Returns the value that a text string looks like. Syntax @VALUE(string) where string is a string in quotations or a cell address. Remarks This function converts string into a value that can be used in formulas, equations, and other calculations. @VALUE is especially useful for converting mixed fractions (such as stock values) into numerical values. Example If A1 contains the label, "4.34" =@VALUE(A1) = 4.34 =@VALUE("3 1/4") = 3.25 --------------------------------------------------------------------- @VAR Statistical --------------------------------------------------------------------- Function Returns the variance of a list of values. Syntax @VAR(list) where list is a list of values separated by commas, or a range containing values, or some combination of the two. Remarks This function returns the variance of the values contained in list. The @VAR function measures dispersion, much like the @STD function. In fact, the variance of list is simply the square of its standard deviation. The @VAR function will completely ignore all items in the list which are text or blank. This means that if a text string is in a range in list, you do not have to worry about it being used incorrectly in the calculation. @VAR calculates the variance of an entire population. If 108 you want to calculate the variance of a sample of a population rather than the entire population, you must use @SAMPVAR. Example =@VAR(0,1,2,3,4,5,6,7,8,9) = 8.25 Similarly, if cells A1 to A10 contain those values =@VAR(A1..A10) = 8.25 --------------------------------------------------------------------- @VLOOKUP Data --------------------------------------------------------------------- Function Looks up an item in a vertical lookup table. Syntax @VLOOKUP(x,range,col offset) where range is a range of cells, named or otherwise; and col offset is a number, cell address, or calculated value; and x is a number, text, cell address, or calculated value. Remarks This function looks up and retrieves a value or text from a vertical lookup table. range is the range of cells which makes up the lookup table. If x is text, @VLOOKUP will compare x with each cell in the left column of range, and if a match is found, will move col offset columns from that cell, and will return the value in the resulting cell. If x does not match any of the cells in the left column of range, then @VLOOKUP will return the value ERROR. If x is a value instead of text, @VLOOKUP will compare x with each cell in the left column of range until it finds a cell which is greater than or equal to x. This means that the values in the left column of range must be in ascending order for @VLOOKUP to work correctly. If the value in the cell is equal to x, @VLOOKUP will consider it a match. If the cell is greater than x, @VLOOKUP will move up to the previous cell and consider it a match. For example, if the values in the left column of range are 100, 200, 300, and 400, and the value of x is 250, then @VLOOKUP will search until it reaches 300, and will then move back and use the cell containing 200 as the match. When a match is found, @VLOOKUP will move col offset columns from that cell, and will return the value in the resulting cell. If x is smaller than the first cell in the left column, @VLOOKUP will return the value ERROR. 109 --------------------------------------------------------------------- @WEEKDAY Date/Time --------------------------------------------------------------------- Function Returns the day of the week for a date number. Syntax @WEEKDAY(date number) where date number is a number, a cell address, or a calculated value. Remarks This function returns the day of the week (numbered 1 through 7, with 1 being Sunday and 7 being Saturday) for date number. Example Cell A1 contains the date number (32540) that is equivalent to the date 2/1/89. Entering the formula =@WEEKDAY(A1) = 4, (Wednesday) --------------------------------------------------------------------- @YEAR Date/Time --------------------------------------------------------------------- Function Returns the year of a date number. Syntax @YEAR(date number) where date number is a number, a cell address, or a calculated value. Remarks This function returns the year of date number. For years in the 1900's, the final 2 digits are returned. For the year 2000 @YEAR will return the value 100. Again, the date number is usually supplied by one of the other date functions; this function is used to extract the year from one of those functions. Example CURRENT YEAR @YEAR(@NOW) returns 1980 80 1999 99 2000 100 2001 101 --------------------------------------------------------------------- @@ Data --------------------------------------------------------------------- Function Returns data from a cell through an indirect reference. Syntax @@(cellname) where cellname is a string in quotations or a cell address Remarks This function will return the contents of the cell named cellname. If cellname is a string which looks like a cell address, then @@ will read and return the contents of the cell named cellname. If cellname is a cell reference, then @@ will use 110 the contents of cellname as the name of the cell to retrieve data from. @@ is typically used in macros, where you may need to retrieve the contents of certain cells automatically. Example If cell B5 contains the text Overdue and cell A1 contains the text 'B5, then @@(A1) and @@("B5") will both return the label Overdue 111 INSTACALC APPLICATIONS InstaCalc provides a programming environment which you can use to create any application, from a simple time-saving keystroke repeating routine to a fully customized business application. An InstaCalc application simply consists of a text file containing the commands which you would like InstaCalc to execute. You may give your program files any valid DOS name, but the extension must be .MAC (for example MYPROG.MAC). Your program can consist of keystroke macros, InstaCalc Command Environment (ICE) commands, or both. Macros A keystroke macro is simply a sequence of keystrokes following the apostrophe character '. When InstaCalc encounters one of these keystroke sequences in your program, it simply treats the keystrokes as if you typed them in yourself. For example, if you place the line: '/wic<Enter> into your application program, then InstaCalc will automatically insert two columns when it executes this line. It does this by first typing the / character (which will bring up the menu), then W (for WorkSheet), then I (for insert), then C (for column), and finally the <Enter> key (to complete the command). Commands The InstaCalc Command Environment (ICE) is a set of instructions which provides flexible I/O capabilities, numerous control structures, printer and DOS access, and more. In fact, you may find that the ICE commands are so versatile that many of your application programs will need few if any keystroke macros. CREATING AN APPLICATION The easiest way to create a keystroke macro is with InstaCalc's Macro Recorder. To begin recording a macro, you simply press the <Ctrl><F10> key. You then press the key you want to assign the macro to, and begin typing the keys to record. When you are ready to stop recording, just press the <Ctrl><F10> key again. Your macro will be stored in a file whose name corresponds to the key you assigned the macro to. For example, a macro assigned to <Ctrl>T would be named CTRLT.MAC. Later, when you press <Ctrl>T, InstaCalc will look for the application file CTRLT.MAC, and run it if it exists. If it doesn't exist, then an error message will pop up. Applications or keystroke macros can be assigned to just about any key which isn't already defined and used by InstaCalc. For example, you can't redefine the arrow keys to do something else, but you can assign other keys to do the same thing as the arrow keys. Applications may also be created with a text editor, just as you would write a program in any programming language. InstaCalc applications 112 are not limited to simple keystroke macros. InstaCalc supplies a rich programming language which provides flexible I/O capabilities, numerous control structures (If/Then/Else, For/Next, Repeat/Until, While/Endwhile, etc), access to DOS commands and external programs, and more. These applications can be written using either the simple editor which InstaCalc provides (/Macro Edit), or outside of InstaCalc with your own text editor. You may give your application any valid DOS name (the extension must be .MAC), including one of the keystroke names (such as ALTX.MAC). This allows you to run extremely powerful applications at the press of a key. There are a number of advantages to having applications reside in a file rather than in the worksheet itself. - The application can load and save data files without being written over by the incoming worksheet. - You can create large libraries of applications and macros which can be used in any worksheet without having to load the entire library into memory each time. - Large applications can be broken into smaller parts, and only the part which is running needs to be in memory. This means you can create and run applications which would normally require more memory than is available. RUNNING AN APPLICATION Once you have created your application program, you will probably want to run it. There are many ways to run an InstaCalc application program which you have created. /Macro Run You can run an application program from the menus using the /Macro Run command. You will be asked for the name of the application program to run. If you press <Enter> without entering a name, the InstaCalc file manager will pop up from which you may select the application from. Keystroke If you have named your application with one of the keystroke names, you can run the application by pressing the key. For example, if the application file is named CTRLX.MAC then pressing <Ctrl>X will run the application. Worksheet You can also give your application the same name as one of your worksheets (except that the extension will be .MAC instead of .INS). When a new data file (.INS) is retrieved, InstaCalc will check for a corresponding application to run. For example, when you retrieve INVOICE.INS, then InstaCalc will look for and run the application INVOICE.MAC (if it exists). 113 If an error is encountered when running an application, then an error message will first be displayed. Then, when you press a key to continue, InstaCalc will load the application into the editor, and place you on the line where the error occurred to allow you to fix the problem. LANGUAGE DESCRIPTION An InstaCalc program is one or more lines of text residing in an ASCII file. There are four different types of lines which you can put in your application; comments, labels, macros, and commands. The first character of each line of text determines what type of command that line is. Each line of your application program will begin with one of four different characters, a pound sign (#), an apostrophe ('), a colon (:), or an exclamation mark (!). Comments A line which begins with an exclamation mark (!) is simply a comment. All of the characters on the line after the ! are ignored when InstaCalc runs the application program. The sole purpose of comments is to make your programs easier to understand when you read them. Example: ! This is a comment This line is ignored when InstaCalc encounters it in an application program. Labels A line which begins with a colon (:) is a label. A label is simply a string which marks a specific position in the application program. Example: :MyLabel This line does nothing when it is encountered in an InstaCalc application program. Some InstaCalc ICE commands (such as #GOTO) use labels as a place to jump to for further processing. Macros A line which begins with an apostrophe (') is a sequence of direct keystrokes (keystroke macro) which you want InstaCalc to execute. Keystrokes which are printable ASCII characters (letters, numbers, symbols, etc.) are displayed in their printable form. Keystrokes which are NOT printable ASCII characters (such as function keys, Ctrl and Alt key sequences, cursor movement keys, etc.) are displayed as the name of the key enclosed in angle brackets <>. For example, the <Enter> key is displayed as <Enter>. Example: 114 '/cfcn2<Enter> This line causes the current cell to be formatted as currency with 2 decimal places and no commas. This is done because InstaCalc processes each keystroke in the line just as if you typed it in yourself. - The / key is processed, which summons the main menu - The C key is processed, which selects the "Cell" option - The F key is processed, which selects the "Format" option - The C key is processed, which selects the "Currency" option - The N key is processed, which selects the "NoComma" option - The number "2" is processed, which types 2 in as the number of decimal places - The <Enter> causes the <Enter> key to be processed (this is one of the special keys), which enters the number 2 as the number of decimal places. Keystroke macros can even be read from cells. Simply follow the apostrophe with the cell name enclosed in square brackets. For example, if you want the keystrokes to be read from cell B5, simply use the line: '[B5] Commands A line which begins with a pound sign (#) is an InstaCalc Command Environment (ICE) command. It is these commands which provide the muscle for sophisticated application development. These commands are described in the next section. 115 SPECIAL KEY NAMES Although the letter, number, and symbol keys can be directly referenced by their character, several keys cannot be referred to using a single character. InstaCalc gives each of these keys its own name. When one of these keystrokes is encountered in a keystroke macro, the name of the key is surrounded by angle brackets <> so that InstaCalc can tell it apart from regular characters. When the keystroke is used as the name of an application file, then the angle brackets (and certain other characters) are not used because DOS will not allow them. Below is a list of special keystrokes, and the name of the keystrokes when used both in a macro and as a file name. KEY SEQUENCE KEYSTROKE NAME FILE NAME <BkSp> <BkSp> N/A <Ctrl><BkSp> <^BkSp> N/A <Esc> <Esc> N/A <Enter> <Enter> N/A <Tab> <Tab> N/A <Ctrl>A- <Ctrl>Z * <CtrlA> - <CtrlZ> CTRLA.MAC-CTRLZ.MAC <Alt>A- <Alt>Z <AltA> - <AltZ> ALTA.MAC-ALTZ.MAC <Home> <Home> N/A <End> <End> N/A <Left> <Left> N/A <Right> <Right> N/A <Up> <Up> N/A <Down> <Down> N/A <PgUp> <PgUp> N/A <PgDn> <PgDn> N/A <Ins> <Ins> N/A <Del> <Del> N/A <F1> - <F10> <F1>-<F10> N/A <Shift><F1>- <Shift><F10> <ShF1>-<ShF10> SHF1.MAC-SHF10.MAC <Ctrl><F1>- <Ctrl><F10> <^F1>-<^F10> N/A <Alt><F1>- <Alt><F10> <AltF1>-<AltF10> ALTF1-ALTF10.MAC <Ctrl><Left> <^Left> N/A <Ctrl><Right> <^Right> N/A <Ctrl><Home> <^Home> N/A <Ctrl><End> <^End> N/A <Ctrl><PgUp> <^PgUp> N/A <Ctrl><PgDn> <^PgDn> N/A * <Ctrl>H, <Ctrl>I, and <Ctrl>M are not available as file names. 116 THE INSTACALC COMMAND ENVIRONMENT The InstaCalc Command Environment (ICE) is a set of powerful commands which you can use in your application programs to create menus, pop up windows, access files, and even run other programs. Each ICE command is a single word prefixed with the # character (for example #OUTPUT). Most of these ICE commands require additional information on the same line. These additional pieces of information are known as arguments. If you have more than one argument on a line, you must separate them from each other with either a space or a comma. Also, if any of your arguments are text strings which have a space or comma in it, then you must surround the text string with quotes so that InstaCalc knows to keep the text together. Example The command #OUTPUT (which writes text onto the screen) requires 3 arguments; the column and row on the screen to write to, as well as the text to write, like this: #OUTPUT 5,10 "This is some text" The ICE command is #OUTPUT, and the three arguments are 5, 10, and "This is some text". The arguments for each of the ICE commands is considered by default to be either a constant numeric or text value. There are many times, however, when it is desirable (or necessary) to use the result of a formula instead. InstaCalc allows you provide a formula for an argument by simply enclosing the argument in square brackets [] (you don't need to add the leading equal sign). Example The format for the #OUTPUT command is: #OUTPUT x,y line By default, InstaCalc expects something like: #OUTPUT 5,10 "This is some text" This line in an application tells InstaCalc to write the string This is some text starting in the 5th column and 10th row of the screen. Suppose, however, that you wanted to output the value of cell A1 instead of the constant "This is some text". Then you would simply use the line: #OUTPUT 5,10 [A1] Or if you wanted to output the contents of cell A1 in all upper case you would use: #OUTPUT 5,10 [@upper(A1)] This method works just as well for numeric arguments as for text arguments. Suppose you wanted to output the string "This is some text" starting in the 5th column, but you 117 wanted the row to be the value in cell A1. You would use: #OUTPUT 5,[A1] "This is some text" INPUT/OUTPUT The I/O commands provide a variety of ways to read data from the keyboard, output to the screen and printer. There are also commands to create customized help and error systems. You may also generate delays and audible tones. DATA The data commands allow your application to move to or manipulate data in cells of the worksheet. CONTROL The control commands provide the ability to loop, perform conditional or unconditional branching, and call subroutines. EXTERNAL The external commands allow you to run DOS commands, other programs, or other InstaCalc application programs as subroutines. DEBUGGING The debugging commands allow you to single step, set conditional or unconditional breakpoints, and evaluate and change cells while an application is running. 118 --------------------------------------------------------------------- #ASSIGN Data --------------------------------------------------------------------- Function Assigns a value to a cell. Syntax #ASSIGN cellname value Remarks This command allows you to assign a value to a cell without having to move to it first. cellname can be either a cell address or a named range. value can be either a number or text. Example #ASSIGN B5 123.45 #ASSIGN C6 "January" #ASSIGN interest .1 #ASSIGN A1 [@SIN(A1)] --------------------------------------------------------------------- #BLOCK Input/Output --------------------------------------------------------------------- Function Allows you to define a range by highlighting or typing the name. Syntax #BLOCK cellname prompt Remarks This command displays prompt and allows you to highlight a range of cells. The range selected is stored as a text string in cellname. This cell can then be used as an indirect address in range functions by entering cellname in square brackets when asked to define a range. Example #BLOCK D3 "Fill which range with zeros?" '/df[D3]<Enter>0<Enter> will ask you Fill which range with zeros? and allow you to highlight the range. If you highlight the range A1..B5, then the string 'A1..B5 will be placed in cell D3. When the /Data Fill command asks for the range to fill, the sequence [D3] will be entered, and InstaCalc will use the contents of D3 as the range to fill. So the range A1..B5 will be filled with zeros. --------------------------------------------------------------------- #BOX Input/Output --------------------------------------------------------------------- Function Displays a box on the screen. Syntax #BOX x1,y1,x2,y2 save title Remarks This command displays a box on the screen. The upper left corner of the box is located at screen position x1, y1, and the lower right corner of the box is located at position x2, y2. save determines whether InstaCalc saves the contents of the screen under the box before displaying it. If save=0, then the screen contents are not saved. If save=1, then the screen contents are saved, and can be restored using the 119 #CLEARBOX command. Saving the screen contents requires extra memory, so if it isn't necessary to restore the contents, it is best to set save to 0. The #RESTORE command will restore the InstaCalc screen even if it wasn't saved, so you can use #BOX with save=0 to clear the screen to begin your application. Example #BOX 1,1,80,25 0 "Main Screen" this command clears the screen with the title Main Screen. --------------------------------------------------------------------- #BREAK Control --------------------------------------------------------------------- Function Enables/disables breaking out of programs. Syntax #BREAK 0/1 Remarks This command allows you to enable or disable the ability to break out of an application program while it is running by pressing <Ctrl><F3>. By default, an application will exit when <Ctrl><F3> is pressed. This command provides the capability to prevent a user from breaking out of your application. It is best to leave breaking enabled when creating your application. Example #BREAK 0 causes your program to ignore <Ctrl><F3> when it is pressed. --------------------------------------------------------------------- #CALL Control --------------------------------------------------------------------- Function Calls a subroutine. Syntax #CALL label / #RETURN Remarks This command causes execution of the program to continue at label. When the #RETURN statement is encountered, the program branches back to the line immediately following the original #CALL command. Example #CALL MyLabel #OUTPUT 10,11 "Print this second" #EXIT :MyLabel #OUTPUT 10,10 "Print this first" #RETURN The #CALL statement branches to MyLabel and executes lines until the #RETURN command, then returns back to the line following the #CALL statement. 120 --------------------------------------------------------------------- #CLEARBOX Input/Output --------------------------------------------------------------------- Function Clears the most recent box placed on the screen. Syntax #CLEARBOX Remarks This command clears the last box placed on the screen (by #BOX or #WINDOW) and replaces the data which was under it. This command only has an effect if #BOX or #WINDOW set the save option to 1 (meaning to save the screen contents). Example !This line draws a box on the screen #BOX 10,10,40,15 1 "My Box" !This line clears the box off the screen #CLEARBOX --------------------------------------------------------------------- #DEC Data --------------------------------------------------------------------- Function Decrements a cell's value. Syntax #DEC cellname x Remarks This command decrements (reduces) the value in cellname by x. If you don't supply x, then a value of 1 is assumed. Example #DEC B5 6.05 decrements the value in B5 by 6.05 --------------------------------------------------------------------- #DELAY Input/Output --------------------------------------------------------------------- Function Generates a delay in your application program. Syntax #DELAY milliseconds Remarks This command allows you to insert delays into your application programs. Example #DELAY 1000 pauses for one second (1000 milliseconds) 121 --------------------------------------------------------------------- #DELETE File --------------------------------------------------------------------- Function Deletes a file from the disk. Syntax #DELETE filename Remarks This command will delete filename from the disk (if it exists). Example #DELETE myfile.dat erases the file named myfile.dat from the current directory. --------------------------------------------------------------------- #DOS External --------------------------------------------------------------------- Function Runs a DOS command or external program as if it were being run from the DOS prompt. Syntax #DOS command Remarks This command allows you to perform a DOS command or run a program just as if you had typed the command at the DOS prompt. There must be enough memory available to run the program or the command will be ignored. Example #DOS "dir *.INS" displays a directory of all InstaCalc data files --------------------------------------------------------------------- #EDIT Input/Output --------------------------------------------------------------------- Function Prompts and allows editing of a cell's contents. Syntax #EDIT x,y cellname len prompt Remarks This command displays prompt at position x,y on the screen, followed by the contents of cellname. You are then allowed to edit the contents of cellname. len is the maximum number of characters which is displayed and which you are allowed to enter or edit. Example #EDIT 40,4 a1 6 "Invoice #" displays Invoice # at position 40,4 on the screen, followed by the contents of cell A1. You will only be allowed to enter 6 characters (or less). 122 --------------------------------------------------------------------- #EDITERR Debugging --------------------------------------------------------------------- Function Enables or disables automatic editing of errors. Syntax #EDITERR 0/1 Remarks When InstaCalc encounters an error in an application, it displays an error message, then calls up the macro in the macro editor. The cursor is placed on the line where the error occurred so that you can fix the problem. There may be times when you do not want InstaCalc to call up the application for editing. #EDITERR allows you to disable this feature. Example #EDITERR 0 disables automatic error editing --------------------------------------------------------------------- #EXIT Control --------------------------------------------------------------------- Function Ends the application program. Syntax #EXIT Remarks This command terminates the current application program. If the current program was called by another program using the #RUN command, then control is returned to the calling program. --------------------------------------------------------------------- #FOR / #NEXT Control --------------------------------------------------------------------- Function Repeat commands a fixed number of times. Syntax #FOR cellname start end step {some commands} #NEXT Remarks This command creates a loop which can perform a set of commands a certain number of times. The value of start is placed in cellname. and as long as the value of cellname does not exceed the value of end, the set of commands between the #FOR and #NEXT command will be executed. Each time the #NEXT command branches back to the #FOR command, the value in cellname will be incremented by step and compared with end. It is also legal for step to be a negative value, in which case the #FOR loop will continue until start is less than end. The value in cellname may be used anywhere within the loop. 123 Example #FOR a1 1 7 2 #OUTPUT 1,[a1] "Hello World" #NEXT Writes the string "Hello World" on the screen on lines 1, 3, 5, and 7. --------------------------------------------------------------------- #GOTO Control --------------------------------------------------------------------- Function Branches to another part of the program. Syntax #GOTO label Remarks This command causes the program to continue execution at the given label. The label may be either before or after the #GOTO, but it must be in the same program file. Example #GOTO MyLabel #OUTPUT 10,10 "Don't print this" :MyLabel #OUTPUT 10,10 "Print this instead" --------------------------------------------------------------------- #IF / #ELSE / #ENDIF Control --------------------------------------------------------------------- Function Executes a set of commands if a condition is true. Syntax #IF boolean formula {some commands} #ELSE {some commands} #ENDIF Remarks This command evaluates boolean formula, and if it is true, continues executing commands until #ELSE or #ENDIF is reached. All commands between the #ELSE and #ENDIF will be ignored. If boolean formula is false, then the program branches from the #IF to the #ELSE command (if there is one) and executes the commands between #ELSE and #ENDIF. Example #IF a1=1 #OUTPUT 10,10 "It's equal to one" #ELSE #OUTPUT 10,10 "It's not equal to one" #ENDIF If the value of cell A1 equals one, then the line It's equal to one will be output, otherwise the line It's not equal to one will be output. 124 --------------------------------------------------------------------- #INC Data --------------------------------------------------------------------- Function Increments a cell's value. Syntax #INC cellname x Remarks This command increments (increases) the value of cellname by x. If you do not specify x, then a value of 1 is assumed. Example #INC B5 6.05 increments the value in B5 by 6.05 --------------------------------------------------------------------- #INPUT Input/Output --------------------------------------------------------------------- Function Prompts for and accepts input from the keyboard. Syntax #INPUT x,y cellname len prompt Remarks This command displays prompt at position x,y on the screen. You are then allowed to enter some text which will be entered into cellname. len is the maximum number of characters which you are allowed to enter. Example #INPUT 40,4 a1 6 "Enter the sales for May " displays Enter the sales for May at position 40,4 on the screen. You will only be allowed to enter 6 characters (or less). Whatever you type will be entered into cell A1. --------------------------------------------------------------------- #KEY Input/Output --------------------------------------------------------------------- Function Prompts for and reads a keystroke. Syntax #KEY x,y cellname prompt maskchars Remarks This command displays prompt at position x,y, and waits for you to press a key. The #KEY command will automatically place the keystroke pressed into cellname. The returned keystroke will be a letter, number, or symbol if it is regular ASCII. If it is a special key like a control or alternate sequence, cursor key, or function key, it will be in angle brackets like this: <Enter>, <PgUp>, <F1> or <^S>. maskchars is an optional argument which allows you to force #KEY to accept only certain characters. If you leave maskchars blank, then #KEY will accept any key pressed. If maskchars is a character (or list of characters), then #KEY will only accept those characters (upper and lower case letters are treated as the same character). Example #KEY 10,10 a1 "Continue?:" y n 125 displays Continue?: at screen position 10,10, waits for y or n to be pressed, and then places the name of the key pressed in cell A1. --------------------------------------------------------------------- #LOCATE Data --------------------------------------------------------------------- Function Moves the cell pointer relatively. Syntax #LOCATE column row Remarks This command allows you to move the cell pointer by several rows and columns at a time. column is the number of columns you want to move to the right, and row is the number of rows you want to move down. You may set row or column to a negative value to move in the opposite direction, or to 0 if you don't want to move either direction. Example #LOCATE 1 -2 3 moves the cell pointer right 1 column and up 2 rows. --------------------------------------------------------------------- #MENU Input/Output --------------------------------------------------------------------- Function Creates and displays a menu on the screen. Syntax #MENU x,y cellname helpline title #HMENU x,y cellname helpline title Remarks These commands (along with the #MENUITEM command) allow you to create and display customized menus from your application programs (#MENU generates a vertical menu, #HMENU generates a horizontal menu). The menu will be placed on the screen at position x,y, and title will be placed at the top of the menu. Each item to be displayed in the menu are defined by #MENUITEM commands following the #MENU command. The help line for each item will be placed on the screen on line number helpline. When the user selects an item from the menu, InstaCalc will enter the item number into cellname. For example, if the user selects the 3rd item from the menu, InstaCalc will enter the value 3 into cellname. If the user escapes from the menu by pressing the <Esc> key, InstaCalc will enter the value 0 into cellname. Example #MENU 20,10 A1 1 "My Menu" #MENUITEM "Add" "Add a record" #MENUITEM "Delete" "Delete a record" #MENUITEM "Quit" "Exit from menu" These lines create and display a menu with 3 items; Add, Delete, and Quit. If Delete is selected, then the value 2 is entered into cell A1. 126 --------------------------------------------------------------------- #MENUITEM Input/Output --------------------------------------------------------------------- Function Defines an item and its help line in a menu. Syntax #MENUITEM item help Remarks This command defines one of the items in a menu. item and help are both text strings enclosed in quotes. item and help can also be cell references or text formulas enclosed in square brackets to allow you to build menu selection items on the fly. --------------------------------------------------------------------- #OUTPUT Input/Output --------------------------------------------------------------------- Function Displays text on the screen. Syntax #OUTPUT x,y line Remarks This command allows your application program to write text to the screen. #OUTPUT will display line at column x and row y on the screen. x must be in the range 1 to 80, and y must be in the range 1 to 25. Example #OUTPUT 40,4 "Hi there" #OUTPUT 40,4 [A1] #OUTPUT [A2],10 "Another line" 127 --------------------------------------------------------------------- #PASSWORD Input/Output --------------------------------------------------------------------- Function Prompts for and accepts a password. Syntax #PASSWORD x,y cellname len prompt Remarks This command is exactly the same as the #INPUT command, except that keystrokes which you enter are not displayed. This command displays prompt at position x,y on the screen. You are then allowed to enter some text which will be entered into cellname. len is the maximum number of characters which you are allowed to enter. Example #PASSWORD 40,4 a1 6 "Enter the magic word " displays Enter the magic word at position 40,4 on the screen, followed by 6 box characters. You will only be allowed to enter 6 characters (or less), and you will not be able to see the actual characters that you type. Whatever you type will be entered into cell A1. --------------------------------------------------------------------- #PRINT / #PRINTLN Input/Output --------------------------------------------------------------------- Function Sends data to your printer. Syntax #PRINT line #PRINTLN line Remarks These two commands allow your program to send line to your printer. The only difference between the #PRINT and #PRINTLN commands is that the #PRINTLN command sends a CR/LF command to the printer after it has sent line. This means that you can use #PRINT several times in a row to print data on the same line. If you want to just send a blank line to the printer, you can simply use the command #PRINTLN without any argument. If you want to print numbers, they must be converted to a string first. The best way to do this is with the @FORMAT function, which allows you to format the number in any of InstaCalc's formats. Example #PRINT "Hi there" #PRINTLN "Hi there" #PRINT [A1] #PRINTLN [A1] --------------------------------------------------------------------- #REPEAT / #UNTIL Control --------------------------------------------------------------------- Function Repeat some commands until a condition exists. Syntax #REPEAT {some commands} #UNTIL boolean formula 128 Remarks This command repeats a command or set of commands until a condition becomes true. InstaCalc executes the commands after the #REPEAT until the #UNTIL command is reached. InstaCalc then evaluates boolean formula, and branches back to the #REPEAT command if it is false. boolean formula may be any legal InstaCalc formula which returns a TRUE or FALSE value. Example #REPEAT #INC a1 #UNTIL a1>30 The contents of cell A1 is incremented until the contents of A1 is greater than 30. --------------------------------------------------------------------- #RESTORE Input/Output --------------------------------------------------------------------- Function Restores the InstaCalc screen. Syntax #RESTORE Remarks This command erases all boxes and restores the InstaCalc screen. This command should be used by all application programs before exiting. Example #BOX 1,1,80,25 0 "Main Title" #WAIT 10,10 "Press any key to continue" #RESTORE These lines draw a full screen box, wait for you to press a key, then restores the InstaCalc screen. --------------------------------------------------------------------- #RUN External --------------------------------------------------------------------- Function Runs an external InstaCalc application as a subroutine. Syntax #RUN application Remarks This command allows you to start running another InstaCalc application file (.MAC) as a subroutine. The other .MAC file will be loaded into memory and executed. When the other .MAC file is finished running, it will be removed from memory, and control will return to the program containing the #RUN command. Example #RUN myprog runs the InstaCalc program "MYPROG.MAC". When MYPROG.MAC has finished running, control will be returned to the current application at the line following the #RUN command. 129 --------------------------------------------------------------------- #SETCOLOR Input/Output --------------------------------------------------------------------- Function Sets the colors which the various I/O commands will use. Syntax #SETCOLOR item background foreground Remarks This command allows you to set and change the various colors which other I/O commands use when writing to the screen. item is a value corresponding to a different item which can be displayed: item DISPLAY 1 Frame around the menu 2 Title of the menu 3 Main body of the menu 4 Moving bar of the menu 5 Highlighted select character in the menu 6 Help line for the menu 7 Prompt for #EDIT, #INPUT, #KEY 8 Editing/output field 9 Frame around the window 10 The title of the window 11 The body of the window background and foreground are values corresponding to the background and foreground colors to use: 0=Black 8=Dark Gray 1=Blue 9=Light Blue 2=Green 10=Light Green 3=Cyan 11=Light Cyan 4=Red 12=Light Red 5=Magenta 13=Light Magenta 6=Brown 14=Yellow 7=Light Gray 15=White Example #SETCOLOR 4 1 15 will set the color of the moving bar of a menu created with #MENU to white on blue --------------------------------------------------------------------- #STEP Debugging --------------------------------------------------------------------- Function Enables or disables single stepping. Syntax #STEP 0/1 Remarks This single command allows you to single step through an application, set conditional breakpoints, and inspect and edit cells while your application is running. When the command #STEP 1 is encountered in a program, single stepping is enabled. When you are in single step mode, the bottom line of the screen will display the prompt "SS:", 130 along with the next line of the application to be executed. You must press a key (the space bar is good for this) to execute each line of the program. This allows you to view the operation of your program one line at a time rather than have it whiz through at breakneck speed. At any point while you are single stepping through your application you may press <Ctrl><F2>. This key will bring up a menu of options which allow you to view or change values of cells which your application might be using. When the command #STEP 0 is encountered in a program, single stepping is disabled and execution of the program will continue normally. Since single stepping can be turned on or off at any point in your program, #STEP can be used to set breakpoints in your application. Simply put a #STEP 1 command at any point of your program where you want to break from your program. If you want to set a conditional break point (where the program will stop only when a certain condition exists), then use a formula argument for #STEP instead of just 1. Example #STEP 1 turns on single stepping at any point in the program. #STEP [A5>3] turns on single stepping only if the value in A5 is greater than 3. --------------------------------------------------------------------- #UPDATE Input/Output --------------------------------------------------------------------- Function Enables or disables screen updating. Syntax #UPDATE 0/1 Remarks This command toggles InstaCalc screen updating on and off. This command is useful to keep screen updating from overwriting boxes and windows which your application has placed on screen. #UPDATE 0 disables screen updating, and #UPDATE 1 enables screen updating. #UPDATE can be used at any point in the application to turn updating on or off. Example #UPDATE 0 disables screen updating. 131 ---------------------------------------------------------------------- #WAIT Input/Output ---------------------------------------------------------------------- Function Prompt and wait for a key press. Syntax #WAIT x,y prompt Remarks This command displays prompt at screen position x,y, and then waits for you to press a key. This command is similar to the #KEY command, except that the keystroke is not saved for processing. Example #WAIT 10,10 "Press a key to continue" displays Press a key to continue and waits for you to press a key. --------------------------------------------------------------------- #WHILE / #ENDWHILE Control --------------------------------------------------------------------- Function Repeat some commands while a condition exists. Syntax #WHILE boolean formula {some commands} #ENDWHILE Remarks This command repeat a command or set of commands as long as boolean formula is TRUE. If boolean formula is FALSE when the #WHILE is first reached, the commands will not be executed at all. The #ENDWHILE command will cause execution to branch back to #WHILE so that boolean formula may be tested again. Example #WHILE a1<10 #INC a1 1.3 #ENDWHILE While the value in cell a1 is less than 10, InstaCalc will increment the value of cell a1 by 1.3. --------------------------------------------------------------------- #WINDOW Input/Output --------------------------------------------------------------------- Function Displays a window on the screen. Syntax #WINDOW x1,y1 save title Remarks This command is a special version of the #BOX command. This command, along with the #WINTEXT command, allows you to easily create text windows without having to calculate how wide and tall the box should be, or what screen coordinates to use to display text in the box. This command displays a box on the screen. The upper left corner of the box is located at screen position x1,y1, and the position of the 132 lower right corner of the box is calculated by InstaCalc based on the number and length of the following #WINTEXT commands. save determines whether InstaCalc saves the contents of the screen under the box before displaying it. If save equals 0, then the screen contents are not saved. If save equals 1, then the screen contents are saved, and can be restored using the #CLEARBOX command. Example #WINDOW 10,5 1 "My Window" #WINTEXT "Here is some text" #WINTEXT ["Name: "+a1] #WINTEXT "Some more text" These lines will create a box with the title My Window at position 10,5 on the screen. The box will be large enough to hold 3 lines of text, and wide enough to hold the widest of the 3 lines. --------------------------------------------------------------------- #WINTEXT Input/Output --------------------------------------------------------------------- Function Displays text in a window. Syntax #WINTEXT line Remarks This command displays line in the currently defined window. #WINDOW will automatically adjust the size of the window to hold the width of line. 133 INDEX @functions 63 Delete records 57 1-2-3 39, 41 Deleting Absolute cell reference 12 columns 33 Addition 10 rows 33 Anchor 13 DIF 40, 41 And 10 Directory 36, 75 Application 112 Distribution 57 breaking from 23, 120 Division 10 debugging 22, 123, Dollar format 16 130 Editing 8 editor 19, 59 Editor 19 running 61, 113 EGA 23 Arguments 63, 117 End key 7 Arrow keys 7 Erasing ASCII 40, 41 blocks 28 Asterisk 17 cells 25 Auditing 58 rows 33 Bin 57 worksheet 31 Block name 11, 13, 21, 30 Exiting 62 Blocks, defining 12 Exponentiation 10 Branching commands 118 Export 20 Break point 131 Exporting CALC 34 1-2-3 41 Cell 5, 12 ASCII 41 Cell pointer 6 dBase 41 CIRC 12 DIF 41 Circular reference 12 Extract 39 Colors 4, 130 Extract records 57 Column width 5, 17, 25, Feature List 3 28, 31 File Manager 15, 35, 36 Combining files 38 Financial functions 64 Comma 16 Fixed format 16 Command line 3 Formatting cells 16, 25, Commands 24 27, 31 Comparison 10 Formulas 10 Configuration 3 Frequency distribution 57 Converting from earlier Function keys 21 versions 4 Functions 63 Copying General format 16 blocks 29 Goalseek 55 cell 26 GoTo 21 Criteria 39, 56 Graphing 18, 47 Currency format 16 Header 45 Customize 3 Help 6, 21 Cut 19 Hidden Data entry 8, 23 cells 17 Data functions 65 columns 25 Database 53 Highlighting blocks 12 Date formats 17 ICE 112 Date functions 64 Import 19 DBase 39, 41 Importing Decimal places 16 1-2-3 39 Defaults 3 ASCII 40 Defining blocks 12 dBase 39 134 DIF 40 text 17, 43-47 Inserting Programming 117 columns 32 Protection 27, 30, 33 rows 32 Query 56 InstaCalc Command Quitting 62 Environment 112 REC 18 Installing InstaCalc 2 Recalculation 34 Justify 8 Recording macros 18, 23 License 1 References 12 Linking Registration 2 42 Relative cell reference 12 Locking Replaying macros 18 cells 33 Retrieving files 35, 38 files 35 Saving files 35, 39 titles 33 Scaling 48 Logical functions 65 Scientific format 17 Lookup tables 82, 109 Search & replace 21 Looping commands 118 Setup strings 27 Lotus 39, 41 Shell to DOS 61 Macro 112 Single step 130 @functions 65 Sort 51 breaking from 23, 120 Statistical functions 64 debugging 22, 123, Status 23 130 String functions 65 editor 19, 59 Subroutines 118 recorder 18, 23 Subtraction 10 running 61, 113 Switches 3 Map view 58 Text 8 Margins 45, 47 Text editor 19 Mark 23 Text functions 65 Mask 23, 36 Time formats 17 Math functions 64 Time functions 64 Matrix 55 Title locking 33 Memory 23, 89 Titles 48 Memory resident 2 ToMark 23 Menu 24 Trace 58 Mouse 6, 7, 14 Transpose 56 Movement 6 Trigonometry functions 64 Moving TSR 2 blocks 29 VGA 23 cell 27 Window 132 Multiplication 10 WK1 39, 41 Named blocks 29 WKS 39, 41 Not 10 WordPerfect 3 Numbers 8 Worksheet 5 Operating system 61 Xtract 39 Operators 10 Zero suppression 34 Or 10 Order form 2 Page number 45, 46 Password 35, 128 Paste 20 Percent format 16 Point mode 11 Pop up 2 Precedence 11 Printing 135 136