Q`8 HN0"lh MNuTHIS DISK HAS BEEN IMMUNIZED AGAINST MOST VIRUSES BY THE 'ULTIMATE VIRUS KILLER' VERSION 6.3 BY RICHARD KARSMAKERS, *THE* ATARI VIRUS KILLER!!!puke'(*** =m@`  @`! #@%`')+-/1 3@5`79;=?A C@E`GIKMOQ S@oWY[]_a c@e`gikmoq s@u`wy{} @` @ ` @ ` @ ` @ ` ǀ ɠ @ ` ׀ ٠  @` @`!Aa   Aa!!#A%a')+-/1!3A5a79;=?A!CAEaGIKMOQ!SAUaWY]_a!cAeagikmo/sAuaw}!Ao/!Aa!Aa!Aa!Aǁɡ!Aaׁ!Aa!Aa " O b  !"!B!b!!!!!"!""#B"%'")+""/#1"#3B#5b#7#9#;#=#?$A"$CB$Eb$G$I$K$M$O%Q"%SUb%W%%[%]%_&a"&cB&eb&g&i&k&m&o'q"'O'ub'w''{'}'("(B(b((((()")o))0))*"*B*b*****+"+B+b+++++,",B,b,ǂ,ɢ,,,-"-B-b-ׂ-٢---.".B.b...../"/B/b////#0C0c00 0 0 01#1C1c111112!#2#C2%c2'2)2@`  @`! #@%`')+-/1 3@5`79;=?A C@E`GIKMOQ S@oWY[]_a c@e`gikmoq s@u`wy{} @` @ ` @ ` @ ` @ ` ǀ ɠ @ ` ׀ ٠  @` @`!Aa   Aa!!#A%a')+-/1!3A5a79;=?A!CAEaGIKMOQ!SAUaWY]_a!cAeagikmo/sAuaw}!Ao/!Aa!Aa!Aa!Aǁɡ!Aaׁ!Aa!Aa " O b  !"!B!b!!!!!"!""#B"%'")+""/#1"#3B#5b#7#9#;#=#?$A"$CB$Eb$G$I$K$M$O%Q"%SUb%W%%[%]%_&a"&cB&eb&g&i&k&m&o'q"'O'ub'w''{'}'("(B(b((((()")o))0))*"*B*b*****+"+B+b+++++,",B,b,ǂ,ɢ,,,-"-B-b-ׂ-٢---.".B.b...../"/B/b////#0C0c00 0 0 01#1C1c111112!#2#C2%c2'2)2LDW1 SWR`HLDW2 SWR`UGULDW3 SWR`LDW4 SWR[`*"SUPRHUEYSWR`[CAD3D_20SWRZt `oCJUGGLER SWRt qLIVINGS1SWRSt yMETROPLSSWRt {) ML_BBALLSWRt ~VIXEN SWR`OGRE SWRt ORGAMES SWRMt 9PIN_WIZ SWRt $SANTAPARSWRt %SCORE4 SWRXt ,STDOCTORSWRt STOCKMKTSWRt +ST_WARS SWRt 6\SUPERDIRSWR t &5 SUPRBIKESWRGt * SWIFTCLCSWR@t -tL_NINJA SWRTWINGS SWR t YDfWRITE90 SWRlt sdARCHIPELSWRt y?CRAZYII SWRt DESOLATRSWR<t _NINJA SWR t LEG_SWRDSWRt AUTO `SEWERSOFT PRESENTS..... LDW POWER SPREADSHEET For the Atari ST and MEGA User's Manual Logical Design Works, Inc. From the 'Super' Collection of DR.J TABLE OF CONTENTS Introduction to LDW POWER What is the LDW POWER Spreadsheet? ................................ 5 About This Manual ................................................. 5 Setting Things Up ................................................. 6 Tour of the LDW POWER Screen ...................................... 6 The Control Panel ................................................. 8 The Menu Bar .................................................... 8 Selecting Menu Items ............................................ 9 Buttons and Indicators .......................................... 9 Status Line ..................................................... 13 Input Line ...................................................... 13 Moving Around the Worksheet ....................................... 14 Scroll Bars ..................................................... 14 SCRL Button [Undo key equivalent] ............................... 14 END Button [Shift + Home] ....................................... 14 HOME Icon [Home key equivalent] ................................. 15 END + HOME Sequence ............................................. 15 Moving with the Mouse ........................................... 15 The Keyboard .................................................... 15 Range Basics ...................................................... 16 Selecting a Range ............................................... 16 Entering and Editing Cell Contents ................................ 18 Making Cell Entries ............................................. 19 Editing Cell Entries ............................................ 25 Cell Formats ...................................................... 26 Fixed Format .................................................... 26 Scientific Format ............................................... 26 Currency Format ................................................. 26 Comma (,) Format ................................................ 27 General Format .................................................. 27 +/- Format ...................................................... 27 Percent Format .................................................. 27 Date Format ..................................................... 27 Text Format ..................................................... 28 Hidden Format ................................................... 28 Commands Worksheet Menu .................................................... 29 Worksheet Global ................................................ 29 Worksheet Insert ................................................ 37 Worksheet Delete ................................................ 38 Worksheet Column ................................................ 38 Worksheet Erase ................................................. 39 Worksheet Titles ................................................ 39 Worksheet Window ................................................ 40 Worksheet Status ................................................ 40 Worksheet Page .................................................. 41 Range Menu ........................................................ 41 Range Format .................................................... 41 Range Style ..................................................... 42 Range Layout .................................................... 42 Range Erase ..................................................... 42 Range Name ...................................................... 42 Range Justify ................................................... 44 Range Protect ................................................... 44 Range Unprotect ................................................. 44 Range Input ..................................................... 45 Range Value ..................................................... 45 Range Transpose ................................................. 45 Copying and Moving Entries ........................................ 46 Copy ............................................................ 46 Move ............................................................ 47 File Management ................................................... 48 File Selector Box ............................................... 48 File Retrieve ................................................... 49 Automatic-Load Worksheet ........................................ 49 File Save ....................................................... 49 File Password ................................................... 51 File Combine .................................................... 51 File eXtract .................................................... 53 File Erase ...................................................... 54 File List ....................................................... 54 File Import ..................................................... 55 File Directory .................................................. 56 Print Menu ........................................................ 56 Print Range ..................................................... 56 Print Line ...................................................... 57 Print Page ...................................................... 57 Print Options ................................................... 57 Print Clear ..................................................... 61 Print Align ..................................................... 61 Print Go ........................................................ 62 Print Quit ...................................................... 62 Print File ...................................................... 62 Graphs ............................................................ 62 Graph Type ...................................................... 63 Ranges .......................................................... 64 Graph Reset ..................................................... 65 Graph View ...................................................... 65 Graph Save ...................................................... 65 Graph Options ................................................... 66 Graph Name ...................................................... 69 Database Management ............................................... 70 The Basics ...................................................... 70 Data Query ...................................................... 71 Data Fill ....................................................... 76 Data Table ...................................................... 76 Data Sort ....................................................... 78 Data Distribution ............................................... 79 Data Parse ...................................................... 80 Database Statistical Functions .................................. 82 Summary Notes ................................................... 83 Macros ............................................................ 84 Creating a Macro ................................................ 84 Naming Macros ................................................... 86 Running Macros .................................................. 86 Stopping Macros ................................................. 86 The Step Mode ................................................... 87 Errors in Macros ................................................ 87 Interactive Macros .............................................. 87 /X Commands ..................................................... 88 Saving and Retrieving Macros .................................... 91 Additional Notes on Macros ...................................... 92 Exiting LDW POWER ................................................. 92 Functions The Form of Functions ............................................. 93 Summary of Functions .............................................. 93 Mathematical Functions .......................................... 94 String Functions ................................................ 94 Logical Functions ............................................... 95 Special Functions ............................................... 95 Financial Functions ............................................. 95 Date and Time Functions ......................................... 96 Statistical Functions ........................................... 96 Alphabetical Function Reference ................................... 96 Appendixes Exchanging Files Between LDW POWER and 1-2-3 ...................... 109 Sideways Printing ................................................. 110 Error Messages .................................................... 111 LDW POWER Special Keys ............................................ 116 Glossary .......................................................... 118 INTRODUCTION TO LDW POWER WHAT IS THE LDW POWER SPREADSHEET? By purchasing the LDW POWER Spreadsheet, you have chosen one of the most powerful and versatile spreadsheet packages available for the Atari ST line of computers. Integrating the power of a full featured spreadsheet, sophisticated graphing capabilities, and a convenient data management system, LDW POWER will certainly prove to be a outstanding formidable tool in your software collection. Some of its outstanding features include: . a worksheet size of 256 columns by 8192 rows . close to 300 commands . over 80 functions . 18 different display formats . simultaneous operation of up to four worksheet windows . graphing capabilities which allow you to create different styles of graphs directly from the data in your worksheet . a user-friendly GEM oriented environment with mouse interface and convenient pull-down menus . a unique Macro Recorder tool which allows you to record actual keystrokes in creating macros . a unique NOTE feature which allows you to write a brief memo for each cell in the worksheet . a convenient data management system for organizing worksheet data . impressive speed in the recalculation and display of data . a unique user interface which allows you to use both cursor keys and the mouse in selecting commands . a Lotus 1-2-3 compatible environment which allows you to transfer files back and forth between 1-2-3 and LDW POWER . ability to work with GDOS, Metafiles, and laser printers Whether you are an experienced user of electronic spreadsheets or a novice, you will find the LDW POWER Spreadsheet an efficient application for working with all kinds of data. If you are an experienced user, you will appreciate its close compatibility with Lotus 1-2-3, its many and varied commands, functions, and options, and its impressive speed. If you are a novice to electronic spreadsheets, you will appreciate the user-friendly GEM environment, the easy to read drop-down menus, and the convenient help feature. ABOUT THIS MANUAL This manual manual is divided into four main parts. The first part explains the basics of the LDW POWER Spreadsheet, describing the LDW POWER screen and the basics of the worksheet itself. The second part deals with the worksheet in more detail, explaining how to move around in the worksheet and how to make and format entries. The Third part defines and gives examples of each and every command and function which can be used in the LDW POWER Spreadsheet. The fourth part is composed of appendices which list helpful information for easy reference. If you are an experienced user of electronic spreadsheets, you might want to skip parts one and two and use parts three and four as reference guides. The descriptions of the spreadsheet commands are arranged in the order that they appear in the worksheet menu bar. If you are a novice user of electronic spreadsheets or a novice to the Atari ST GEM environment, you should read parts one and two especially carefully along with parts three and four. SETTING THINGS UP The first thing you'll want to do in getting started with your LDW POWER Spreadsheet is to make back up copies of both program diskettes in order to safeguard against any mishaps which may occur with the originals. We strongly recommend that you use your back up copies as the working diskettes and place the originals in a safe place. Since the LDW POWER Spreadsheet diskettes are not copy protected, you will have no problem in making back up copies. If you have only one disk drive, you should first copy the LDW.HLP file onto your work disk (with your worksheet files) and then load the LDW spreadsheet from the program disk. While you are using the spreadsheet, you can use the work disk to save worksheets and to access the help feature. To copy the LDW POWER Spreadsheet program onto your hard disk, you simply need to copy the files LDWPOWER.PRG and LDW.HLP over to your hard disk. Both files should be copied to the same directory. There is no need to install the LDW POWER Spreadsheet. Consult your user's manual for your hard disk for instructions on how to copy. The LDW POWER Spreadsheet program will run on the Atari 520 and 1040 ST and MEGA ST on both monochrome and color monitors. LDW POWER can support all printers that work with the Atari 520/1040 ST and MEGA ST. It may be necessary to use certain control codes with your printer in order to create special printing effects. Consult the user's manual for your printer for details. To load the LDW POWER Spreadsheet program, place the LDW POWER diskette in your disk drive and turn on your computer system. When the file window appears, double click on LDWPOWER.PRG. Your computer will load the LDW POWER Spreadsheet program. TOUR OF THE LDW POWER SCREEN When you first load the LDW POWER Spreadsheet, the main screen will appear. This screen is the principle medium by which you will manipulate data in your worksheet and consists of two main parts: the control panel and the worksheet window. The control panel occupies the upper portion of the screen directly above the worksheet window. The control panel is made up of four parts: the menu bar, the row of buttons rand indicators, the status line, and the input line. The menu bar at the top of your screen is composed of nine drop-down menus which contain the commands which help make your spreadsheet such a powerful tool. These menus include: Worksheet, Range, Copy-Move, File, Print, Graph, Data, Macro, Quit. Each of these menus will be explained in detail in the Commands section of this manual. The row of buttons and indicators is located directly under the menu bar. They are used to select certain worksheet features or to indicate certain conditions. The eight buttons on the left side of the screen can be selected with the mouse. These buttons include: CMD, MODE, OK, CALC, SCRL, END, NOTE, and HELP. The five indicators on the right of the screen are initially hidden from view and appear only when a certain condition is invoked. As their name implies, they are indicators only and cannot be selected. These indicators include: STEP, OVR, MREC/PLAY, PROT, and CIRC. These buttons and indicators will be explained in detail in the Control Panel chapter. Directly under the row of buttons and indicators is the status line. The status line displays information pertaining to cells in the worksheet (see explanation of cell in description of the worksheet window). This information includes the cell location coordinates and a listing of the contents of the selected cell. It will also list any format or style changes made to the cell, the width of the cell, if it has been changed from the global width, and the actual cell contents (label, number, or formula) that has been entered into the cell. The input line is identified by the (>) symbol and is used for entering and editing cell entries and entering cell locations. A small tick mark on the input line indicates the width of the selected cell. this mark appears only when you are entering or editing cell contents. The remaining portion of the screen consists of the worksheet window (a standard GEM window). The top line of the worksheet window is the title bar, which will display the name of the current worksheet. Near the upper left corner of the window is the home box. When the home box is selected, the cell pointer will automatically be returned to the cell addressed A1 (the first cell in the worksheet). For more information as to how to manipulate the worksheet window, (such as scrolling or resizing the window) refer to your Atari ST owner's manual. The worksheet you will use in the LDW POWER Spreadsheet program is much like the columnar pad used by accountants. The accountant's worksheet is a large table, with grids, on which you can enter information, store data, perform calculations, and much more. The worksheet in LDW POWER is an electronic worksheet which stores the information in the computer's memory and is equipped with commands and information in the worksheet. The worksheet in LDW POWER is 256 columns wide and 8192 rows deep. The portion of the worksheet shown on the screen at any time is, therefore, a very small part of the full worksheet. Each row is represented, in ascending order, by a number (from 1 up to 8192), and each column is represented, in alphabetical order, by a letter or pair of letters (from A up to IV). The rectangle formed by the intersection of a row and a column is called a cell, and is identified by the letter of the intersecting column paired with the number of the intersecting row. For example, the cell formed from the intersection of column E and row 9 has the cell address: E9. The cell is the most fundamental part of the worksheet, for it is where you enter your information. Each cell can hold a single piece of information (an entry) which can be a value, label, or formula. A value is a numeric or quantitative entry. A label is usually a text entry used as a description of other cells. A formula is usually an entry which refers to other cells to arrive at an end result. Cell entries can be formatted in several different styles. This formatting is done by formatting the actual cell in which the entry is made. The formatting commands are located within the menu bar. The following are a few examples of cell formats. They are by no means the only formats available in LDW POWER. For a complete listing of available formats, see the chapter on Cell Formats. An entry can be aligned to the left or right of the cell, or it can be centered. A value can be displayed in fixed point format, in scientific notation, in monetary format, or in a percent format. An entry can be displayed in plain style, in bold style, in underline style, or in bold and underline style. The cell which you are working with at any specific time will be highlighted (shaded black) to indicate that it is the current or active cell. This highlighting is referred to as the cell pointer, because it indicates which cell is active. When a cell is active, it is "open" for you to enter data into it or to edit the data already in the cell. THE CONTROL PANEL The control panel consists of the portion of the screen above the worksheet window. As the name implies, this area is used to control various aspects of the actual worksheet. From the control panel, you may select commands, enter and edit data, and receive information regarding various aspects of your worksheet. The control panel is made up of four different parts: the menu bar, the row of buttons and indicators, the status line, and the input line. The Menu Bar The menu bar is located at the top of the screen and contains nine drop-down menus which hold the commands you can use in your spreadsheet. These menus include: Worksheet, Range, Copy-Move, File, Print, Graph, Data, Macro, and Quit. The Worksheet menu contains commands which are used to manipulate the worksheet as a whole. For example, these commands may be used to insert rows or columns, format the entire worksheet, erase the entire worksheet, or open another worksheet window. The Range menu offers commands which manipulate only specific rectangular blocks (cell ranges) within the worksheet. Once you have specified a range of cells (see Worksheet Window chapter), you can use these commands to format, erase, or assign a name to that range. The Copy-Move menu contains commands which are used to either copy or move specific cell ranges to different areas of the worksheet. The File menu holds commands which work with worksheet files. From these commands, you can choose to retrieve or save files, manipulate and exchange parts of different worksheets among different files, or erase saved files. The Print menu contains commands that prepare your worksheet for printing. These commands allow you to ready a worksheet or part of a worksheet for printing to the printer or to the file. The Graph menu offers a list of commands which allow you to create graphs from the data entered in your worksheet. From these commands, you can choose what type of graph to create, what data to use from the worksheet, and what customized features to add to the graph. The Data menu contains commands used in creating a data management system for the data in the worksheet. These commands allow you to create data tables for complex data analysis, search through your worksheet for data meeting specific requirements, or sort your data in a more organized manner. The Macro menu offers commands which allow you to create macros within your worksheet. Macros can be thought of as miniature computer programs which allow you to perform several operations automatically within the worksheet. You can use the macro commands to "record" actual keystrokes and execute those operations automatically. The Quit menu allows you to quit the LDW POWER Spreadsheet program and return to the operating system of the computer. Selecting Menu Items Commands can be accessed by use of the mouse or the keyboard. When you move the mouse to the desired menu, the menu will automatically drop down revealing a list of submenus. These submenus can then be opened by selecting the submenu and clicking the left mouse button once. Menus can also be selected by pressing the slash (/) key or clicking on its equivalent, the CMD button. When the slash (/) key is pressed or the CMD button is selected with the mouse, the control panel will change and will display the "Lotus style" command menu on one line and descriptions of the selected menu items on the line below it. You may select a menu item by typing the first capitalized letter of the desired item or by moving the menu pointer to the appropriate item with the cursor keys and pressing Return. Since submenus can vary in size, this "nested" arrangement of commands can be quite extensive. You can cancel a selected command or return to the previous menu level by pressing the Esc key or by selecting the ESC button at the left side of the menu. Once a command has been executed, or you leave the menu bar, the control panel will return to its original state. A unique feature of the LDW POWER user interface is the fact that the keyboard and the mouse can be used interchangeably to select menus. Whether you use the mouse, slash (/) key, or CMD button to access the menu bar, you can select submenus and commands using the mouse or keyboard or both. For example, if you access the Worksheet menu with the mouse and select on the Global submenu, the submenu for Global will be displayed on the control panel. You can select choices from this submenu by using the mouse or by using the keyboard cursor keys or by typing in the first capitalized letter of the desired submenu choice. Buttons and Indicators The buttons and indicators are located directly below the menu bar. The buttons allow you to manipulate certain features of your worksheet, while the indicators inform you of certain conditions in the worksheet. The eight buttons on the left side of the screen can be selected with the mouse. The five indicators on the right side of the screen are hidden and only appear when certain conditions exist. The indicators cannot be selected. BUTTONS The eight buttons include: CMD, mode, OK, CALC, SCRL, END, NOTE, and HELP. CMD [slash (/) key equivalent]: As mentioned previously in the menu bar description, the CMD button is used to access the menu bar. Mode: The mode button is used to indicate the present mode of operation in the worksheet. At any given time, the worksheet may switch between different modes of operation: the READY mode, the EDIT mode, and the POINT mode. It is set to default for the READY mode. When the button shows READY, data can be entered into cells in the worksheet. In essence, the worksheet is ready to accept data or commands. When the button shows EDIT, the worksheet is in EDIT mode, which allows you to edit the contents of the selected cell. In EDIT mode, the contents of the selected cell will appear on the input line, and a prompt will be positioned at the end of the entry. You may make any changes t the entry, simply by positioning the prompt within the entry and typing the desired characters. After you have made the desired changes, press the Return key or click on the OK button on the control panel. Whenever you select a cell or cell range (using the mouse), in response to a command prompt, the POINT mode will be enabled. When the button displays POINT, LDW POWER is indicating that you are pointing out a cell or cell range. Perhaps the most valuable advantage that the POINT mode offers is the convenience it provides in entering ranges for formulas and functions. Instead of having to type in range coordinates for the given operation, you can simply use the mouse to point out the range. Once you have selected the cell or cell range and press Return or select the OK button, the mode button will show READY again. As well as serving as a mode indicator for the entire worksheet, the mode button is also used to identify the mode of an active cell. When you first begin to enter data into a cell, the mode button will display either VALUE or LABEL depending on the type of entry that is being made. In EDIT mode, you can determine what type of data you're editing by clicking on the EDIT button. The button will change to LABEL or VALUE depending on the nature of the entry being edited. Once the cell entry is confirmed, the button will return to the READY position. OK [Return key equivalent]: The OK button is used to confirm a cell entry or a cell range (in the exact same way the Return key is used). When you make a cell entry, and are satisfied with the contents, you can confirm that entry by clicking on the right mouse button, pressing the Return or arrow key, or clicking on the OK button. You may also confirm a cell range with the OK button by clicking on it after selecting your range. When you are using a command, and a cell range is required, the OK button will be highlighted, indicating that the selected range needs to be confirmed. CALC [F9 key equivalent]: The usefulness of the CALC button can only be appreciated after you understand the workings of formulas and functions. Formulas and functions operate in the worksheet by using the contents of other cells as their arguments. In other words, they refer to other cells to obtain the values or strings needed to return a result. Therefore, when any of those cells are altered or when any new entries are made, the formulas and functions may be affected. In the automatic recalculation mode (the default mode), all relevant formulas or functions are recalculated. The CALC button is used mainly when the worksheet is in manual recalculation mode. When selected, it recalculates all formulas and functions in the worksheet that may be affected by any cell changes. Selecting the CALC button will ensure that the results from all your formulas will reflect the most recent values on the worksheet. The CALC button can also come in handy for solving "quick" formulas or functions on the input line (sort of like a mini calculator). When you just want to figure out some equation without disturbing the worksheet, simply type in the equation or function and click on the CALC button (DO NOT press the Return key). The result will appear on the input line only and will not show up in the worksheet. To erase the result from the input line, press the Esc key. The worksheet will be placed back into the READY mode and will be ready for normal operations. Here's an example: Suppose you want to add the values contained in cells E1, E2, and E3. First, enter some values in each of the three cells. When the worksheet is in READY mode, type in a plus sign (+) followed by the address E1. Then, type in another plus sign. Now, use the mouse to move the cell pointer to cell E2 and click once on the left mouse button. You'll notice that the address E2 appears on the input line (as if you had typed it in directly). Type in another plus sign and then the address E3. Don't press the Return key, or the formula will be entered into the worksheet at the location of the cell pointer. Instead, click on the CALC button or press the F9 key. The answer to the equation will appear on the input line in place of the formula. Press the Esc key to return to the READY mode. SCRL [Undo key equivalent]: The SCRL button is used to place the worksheet into the scroll mode in which the worksheet window may be scrolled one row or column at a time. When it is selected, the SCRL button will be highlighted, and the cursor keys can be used to scroll over one column at a time (left and right arrow keys). When the scroll mode is not enabled, the cell pointer must be located at the edge of the worksheet window in order to scroll the worksheet over one column or row. The scroll mode allows you to scroll regardless of the position of the cell pointer on the worksheet. END [Shift/Home combination equivalent]: The END button is used in conjunction with the arrow keys to facilitate movement of the cell pointer around the worksheet. When the END button is selected, the cell pointer will move in the direction of the arrow key to the next boundary between a blank cell and a cell that contains an entry. The left and right arrow keys will move the cell pointer among boundaries within the same row, while the up and down arrow keys will move the cell pointer among boundaries within the same column. If the cell pointer is positioned at the last cell in either a row or column that contains data, use of the END button/arrow key combination will send the cell pointer to the last cell in the worksheet for that row or column. After each move is performed, the END button will automatically switch itself off. For each move then, you must select the END button and an appropriate arrow key. NOTE [F6 key equivalent]: The NOTE button allows you to write a note regarding the selected cell. Such a note can be useful for describing or commenting on the particular cell or cell contents. When the NOTE button is selected, the NOTE window will appear and cover part of the control panel. There are two lines available on which you may write your note. After you're finished, you can elect to keep the note, delete the note, or quit without writing a note, by selecting the KEEP, DELETE, or QUIT buttons respectively. Whenever you position the cell pointer over that particular cell and select the NOTE button, your note for that cell will appear. HELP [F1 key and Help key equivalent]: The HELP button allows you to draw upon the LDW POWER help feature. If during the operation of LDW POWER, you are unsure of some aspect of the worksheet or command menu, you can select the HELP button to receive on-screen assistance. When the HELP button is selected, the HELP dialog window will appear on screen. You can then choose the appropriate category in which you would like to receive help. INDICATORS The five indicators include: STEP, OVR, MREC/PLAY, PROT, and CIRC. STEP: The STEP indicator is invoked when the worksheet is in single step mode or macro execution. A macro, which is a recorded series of keystrokes, is normally executed automatically with the keystrokes quickly executed one after another. When the worksheet is in single step mode however, the macro will only execute one keystroke at a time to allow you to view each step as it happens. During this single-step operation, the STEP indicator will appear to inform you that the macro being executed is indeed in single step mode. Refer to the description of Macros for more details. OVR: The OVR indicator appears only in the Edit mode and indicates that any characters entered during editing will overwrite any existing characters beneath or to the right of the edit cursor. When the OVR indicator is not enabled during the Edit mode, any characters entered during editing are inserted at the position of the edit cursor. To invoke the OVR indicator while in Edit mode, press the Insert key. To disable the OVR indicator, press the Insert key a second time. MREC/PLAY: The MREC and PLAY indicators are macro indicators. The MREC indicator signals that a macro is being executed. When you select Macro from the menu bar and begin a macro recording, the MREC indicator will be enabled. It will only be disabled when you end or abort the macro recording. When you have defined a macro and select the Macro Play command to execute the macro, the PLAY indicator will appear on screen during the execution of the macro. As soon as the macro is finished, the PLAY indicator will disappear from the screen. PROT: The PROT indicator shows that the Global protection for the worksheet is enabled. When the Worksheet Global Protection command is invoked, every cell in the worksheet is restricted from accepting an entry. When you try to make a cell entry under Global protection, an error message will appear to inform you that the selected cell is protected. CIRC: The CIRC indicator signals that a circular reference has been found in the worksheet. A circular reference occurs when a cell contains formula that either directly or indirectly refers to the cell itself. When LDW POWER is in automatic recalculation mode, the CIRC indicator will be highlighted immediately after a circular reference is made in a formula. In manual recalculation mode, the CIRC indicator will not be highlighted until you recalculate the worksheet using either the CALC button or F9 key. The majority of circular references are the result of an input error. Therefore, LDW POWER provides you with an easy way to find circular references. The Worksheet Status command will display a window which tells you the location of an existing circular reference. By using this feature, you can easily pinpoint the source of the error and thereby correct it. Status Line Directly under the buttons and indicators is the status line. It provides useful information pertaining to the individual cells in the worksheet. Included in this information is the cell address of the selected cell. The cell address is always the first thing displayed on the status line and is followed by a colon. Following the cell address there can be the cell format, if the format is different from the global worksheet format. The cell format is displayed in an abbreviated code which relates to the type of formatting that has been selected. For example, if you format a cell to have a currency format with three decimal places, the status line would display the following format information for that cell: (C3), where the C stands for currency and the 3 stands for three places after the decimal. The code for numeric formatting is made up of the first letter of the formatting command chosen for the cell (F for Fixed; C for Currency; etc...) along with the number of decimal places chosen to follow the decimal all enclosed in parentheses. The code for style formatting is made up of the first letter of the style format enclosed in brackets. Following the cell format is the contents of the cell itself. If the cell contains a label, the label will be displayed preceded by either an apostrophe ('), a quotation mark ("), or a caret symbol (^) to indicate that the label is either left, right, or center justified. If the cell contains a value, the value will be displayed without any trailing zeros after the decimal. Note that the actual numeric value, as it was entered from the keyboard, and not the formatted version from the cell will be displayed. If the cell contains a formula, the actual formula itself and not the result of the formula shown in the cell will be displayed. Besides showing the status of the active cell, the status line can also be used for directing the cell pointer to any cell on the worksheet. If you click on the cell address on the status line, while in READY mode, a message will appear on the input line: Enter cell to go to: (address). You can type in any cell address, and the cell pointer will move to that cell address. This feature is equivalent to the F5 key (see Moving Around the Worksheet). Input Line Directly under the status line is the last part of the control panel, the input line. The input line is marked by the (>) symbol and contains a cursor which moves as characters are entered. There is also a small tick mark on the bottom of the input line which indicates the width of the selected cell. This tick mark is useful in showing you how much room you have to enter data into the particular cell (the tick mark only appears when the worksheet is in LABEL or VALUE mode). You may make a label entry that is longer than the selected cell, however its appearance on the worksheet may be affected. If there are empty cells which follow the selected cell, then the label will overflow into those cells. If however, the following cells are filled, the label will be truncated or cut off to fill the nearest empty cell. If the entry is a numeric value, and is too long for the cell, it will be displayed as a series of asterisks (*) in the cell. The input line is really a kind of all-purpose line. On it, you can enter cell data, edit cell data, enter cell locations, or enter cell ranges. In READY mode, the input line is used for making cell entries. In EDIT mode, the same input line is used to edit cell entries. The contents of the selected cell appear on the input line, and you can move the edit cursor to make any necessary changes. In POINT mode, a message will appear on the input line prompting you to enter a cell or cell range. You can type the cell or cell range directly on the input line. MOVING AROUND THE WORKSHEET In order for a spreadsheet to be an effective tool, it must allow you to perform fundamental actions on a worksheet in an easy and convenient manner. One of these fundamental actions is to be able to move around the worksheet with minimal effort. Since the worksheet contains 256 columns and 8192 rows, the ability to move around quickly and easily can obviously be appreciated. LDW POWER offers several methods which allow you to move to all different parts of the worksheet. These methods include use of the scroll bars, the SCRL and END buttons, the HOME icon, the mouse, and the keyboard. Scroll Bars On the right and bottom edge of the worksheet window are the scroll bars, which can be used for quick and easy movement from the beginning to the end of the worksheet. They are used in the same manner as scroll bars on most windows in the Atari GEM environment. By dragging the white boxes, or sliders, you can move the window to expose different parts of the worksheet. Dragging the slider on the horizontal scroll bar will move the worksheet window horizontally, exposing different columns. Dragging the slider on the vertical scroll bar will move the worksheet window vertically, exposing different rows. Clicking on the arrows at the ends of the scroll bars allow for slower scrolling. If you hold down the Shift key while clicking on the arrow, the window will scroll one page at a time. When you use the scroll bars, the worksheet will move on the screen, but the active cell will not change, even if it goes off the screen. Refer to your Atari ST owner's manual for more information on the scroll bars. SCRL Button [Undo key equivalent] The SCRL button (described earlier in the description of the Buttons and Indicators) located in the control panel places the worksheet in scroll mode and can be used with the keyboard cursor keys to scroll the worksheet one row or column at a time. When you select the SCRL button, it will be highlighted, indicating that you can use the cursor keys (arrows) to scroll one column or row at a time. END Button [Shift + Home] The END button (described earlier in Buttons and Indicators) located in the control panel is used in conjunction with the keyboard cursor keys to move the cell pointer to the next cell boundary between a blank cell and a cell containing an entry. Use of the END button can save keystrokes, for the cell pointer will automatically "jump" over cells that do not fit the cell boundary criteria. For example, if you have a row of ten consecutive cells (all containing data), the eleventh cell is empty, and the cell pointer is positioned at the first of those cells, you can use the END button and the right arrow key to move the cell pointer directly to the tenth cell in the row. If you use the right arrow key alone, you would have to press it nine times in order to move to the tenth cell. The advantage of using the END button in moving around the worksheet becomes obvious when you are working with large amounts of data. HOME Icon [Home key equivalent] The HOME icon located in the upper left corner of the worksheet can be used to move the cell pointer to the first cell in the worksheet (cell A1) from any cell in the worksheet. Selecting the HOME icon will bring the A1 cell back into view in the worksheet window, if it was previously out of view, and position the cell pointer on the A1 cell. END + HOME Sequence There are certain situations in which you may want to move to the farthest cell of the worksheet located at the intersection of the farthest column to the right with the farthest row down in which an entry can be found. LDW POWER will move the cell pointer to this location in two simple steps. When in READY or POINT move, select the END button on the control panel. It will be highlighted. Click on the HOME icon, and the window will one to and display the cell which contains the farthest entry in the worksheet. This cell also becomes the active cell. Moving with the Mouse The mouse can be used to move the cell pointer around the worksheet window. Move the mouse cursor to any cell in the worksheet window and click. The cell you selected will be highlighted and will become the active cell. Note that you can only move the cell pointer to cells that are currently displayed in the worksheet window. If you wish to move the cell pointer to a cell that is not currently displayed, you can use the scroll bars to move the worksheet window to the desired area of the worksheet and then use the mouse to select the desired cell. The Keyboard MOVING ONE CELL AT A TIME The arrow keys on the keyboard can also be used to move around the worksheet. When you press any arrow key, the cell pointer will move one row or column in the direction of the arrow that is pressed. When the cell pointer reaches the last visible cell of a row or column and the arrow key is pressed in the direction of the cells that are not visible in the window, the worksheet will automatically scroll one row or column at a time. THE F5 [GoTo] KEY The F5 [GoTo] key on your keyboard is a special key for moving around the worksheet. The F5 key allows you to move the cell pointer to any cell location in the worksheet in a single step. When you press the F5 key, a message will appear on the input line: Enter cell to go to: (address). Enter the address of the cell you would like to go to and press Return. If the cell you specified is located on a part of the worksheet that is not displayed in the worksheet window, the worksheet window will move to that part of the worksheet which contains the cell. MOVING THE WINDOW BY PAGES The worksheet in your LDW POWER Spreadsheet program is divided into screenfuls of pages. The typical page is 14 rows high and 8 columns wide. There are three important keystrokes which can be used for moving pages around the worksheet: Tab, Shift+Tab, and Shift+(arrow). The Tab key will move the window to the first column of the next page. Each time you press the Tab key, the window will move horizontally to reveal the next page. The columns will change, but the rows will remain the same. The Shift+Tab combination will move the window back to the first column of the previous page. Pressing the Shift key simultaneously with any of the arrow keys will move the window to another page in the direction of the arrow. The up and down arrows will move the window to the first row of the next or previous page. The right arrow will move the window to the first column of the next page, while the left arrow will move the window to the first column of the previous page. RANGE BASICS In working with the worksheet and LDW POWER commands and function, you will frequently be required to select a cell or range of cells. Whether you are issuing a command to copy a certain range or to format a certain range, you will need to know how to indicate the range that you want to use. Because of the compatibility of the GEM and Lotus interface in LDW POWER, selecting a range is a familiar task for both ST and IBM users. A range of cells is a rectangular block consisting of one or more cells. The range is defined by either pair of diagonally opposed corner cells. The cells in either pair are referred to as the anchor cell and the free cell. The anchor cell is the cell from which you begin the range definition, while the free cell is the cell in which you end the definition. The names refer to the fact that the free cell can be moved (to either expand or contract the range), while the anchor cell is fixed. Selecting a Range LDW POWER allows you to select a range in many different ways, by doing the following: 1. Type in the cell addresses of the anchor and free cells of the range. 2. Define the range using the mouse pointer or cursor keys. 3. Refer to the range using a range name. USING THE KEYBOARD You can indicate a range simply by typing in the cell coordinates of any two diagonally opposed corner cells in the range. The coordinates must be separated by at least one period and should not have any spaces between them. When you are selecting a single cell (which is the smallest range possible), you can just type in the coordinates of that cell. Press the Return key or click on the OK button to confirm the range. There are also some special keys that you can use when the worksheet is in POINT mode to facilitate range definition. If you press the period (.) key when the range is not anchored, the active cell will be anchored. You can then use the cursor keys to move the cell pointer and define the range. If you press the period key after a range is defined, the anchor will move (in a clockwise direction) to the next corner cell. Accordingly, the free cell will also move to the diagonally opposite corner of the new anchor. If you are being prompted by a command for a range, and the cell is not yet anchored, pressing the Esc key will return you to the previous level in the command. If the cell has already been anchored, pressing the Esc key will contract the range all the way back to the anchor cell and will remove the anchor. Whether or not the cell is anchored, pressing the Backspace key will erase the range and return the cell pointer to the current cell. USING THE MOUSE The GEM interface in LDW POWER allows you to use the mouse to select a range as well. Defining a range with the mouse is similar to using the keyboard in that you must use the mouse to point to the anchor cell and then drag the mouse to the free cell. To select a single cell, you can just point to the cell and click. The cell pointer will indicate the active cell. To select a range, position the cell pointer on the anchor cell; hold down the left mouse button and drag the cell pointer across all of the cells you would like to include in your range. All of the selected cells will become highlighted as the cursor is dragged across them. If you are selecting a range, and the cell pointer reaches the edge of the screen, the worksheet will automatically scroll in the direction you are selecting. If you press the Shift key while scrolling, the worksheet will scroll a full page. When you are satisfied with the range, release the mouse button. The free cell will be marked by the cell pointer. To undo a selected range, you can click the ESC button on the screen, or press the Esc key, or simply use the mouse to define a new range. When the range is highlighted and just sitting there in the worksheet, it can still be altered very easily. Suppose that you've just defined a range, but you want to expand it to include more cells. Simply move the mouse pointer to the cell that you would like to designate as the new free cell and click once. The range will automatically expand to include the designated cell. You can also use the cursor keys in tandem with the mouse to alter the range. Just press the appropriate arrow for the direction that you want to expand or contract the range, and the range will change by one row or column for each press of the arrow. You'll notice that as you move the position of the free cell, the position of the anchor cell will not change. Because the anchor cell is fixed in its location, it will always be the "axis" around which the range will revolve. This fact holds true whether you are using the mouse or the cursor keys. One of the advantages of using the mouse in selecting ranges is that you can pre-select ranges before issuing a command. That is, you can select a range, then issue the command, and the command will automatically use the selected range. This way, you don't have to wait for the prompt from the command to enter a range. USING RANGE NAMES The third way to designate a range is through the use of range names. These names are simply an alternative to referring to ranges by their coordinates. For example, instead of calling a range A1..B10, you could give it a more descriptive name like SALES. You could then use that name whenever you wanted to refer to the range A1..B10. Range names are created by the Range Name Create command. You should refer to the Range Name commands for details. ENTERING AND EDITING CELL CONTENTS There are three types of information which can be entered into LDW POWER worksheets: labels, numbers, and formulas. A label is a text entry. A number is a numeric entry. A formula is an entry which refers to other cells to reach a result. Together, numbers and formulas are regarded as values within the worksheet. You may not combine numbers and labels in the same cell. If you attempt to do so, you will get an error message. To enter information into any cell, select the cell (make it active) and type in the entry. You will notice that what you are typing will appear on the input line. When you are done typing, press Return, click on the OK button, click on the right mouse button, or press one of the arrow keys to confirm the entry. When you press Return or click on the OK button or press the right mouse button to enter the information, the cell you are working in will remain the active cell. When you press an arrow key to enter the data, the cell adjacent to the one you are working with, in the direction of the arrow pressed, will become the active cell. In other words, if you press the down arrow after typing in your entry, your entry will appear in the selected cell, and the cell pointer will move to the cell directly below it. Similarly, if you use the mouse cursor to click (with the left mouse button) on a cell other than the selected cell, the entry will appear in the selected cell, and the cell pointer will move to the cell indicated by the mouse cursor. Sometimes, when you find yourself making a whole series of entries in a column or row, it can be more convenient to just press the Return key after each entry instead of the vertical or horizontal arrow keys. LDW POWER allows you to do this, because it remembers the direction of the last arrow pressed and automatically moves the cell pointer in that direction when you press Return. Note, that LDW POWER will only remember the last cell pointer movement used to confirm the cell entry. For example, if you're making entries in 10 consecutive cells of the same column, and you confirm the first entry with the down arrow key, you can use the Return key to confirm the nine remaining entries. After you have made the tenth entry, the cell pointer automatically moves down to the cell immediately below that tenth cell. You move the cell pointer to the right and make another entry. The cell pointer will remain on the cell and will not automatically move over one cell to the right. If you want it to automatically move to the right, you'll have to make an entry and press the right arrow key and make an entry in the next cell. Now, when you press Return, the cell pointer will move over right one cell. If you select a cell which already contains an entry, the new entry that you type in will replace the one contained in the cell. Once the new entry is entered, there is no way to retrieve the old entry. You must re-type the old entry. You can look in the input line to see exactly what is being entered into the cell. When you press Return or an arrow key, the input line is cleared, and the entry is displayed in the worksheet cell. If you are in the middle of making an entry, and decide to cancel the entry, you can use the Esc key or the ESC button to remove the entry from the input line and to return the worksheet to the READY mode. There are four things that happen when you press Return or one of the arrow keys to complete an entry: 1. LDW POWER checks the entry for errors. If everything in the entry is correct (following the rules of the worksheet), it is stored in the cell. If there is an error, the worksheet will beep and be placed in EDIT mode. The cell entry will be displayed on the input line with the cursor positioned at the point that the error occurred. You can correct the error on the input line and try to enter the entry into the cell again. 2. If the automatic recalculation mode is enabled, all formulas in the worksheet will be recalculated. 3. The screen is updated to include the new entry and the new cell pointer (if an arrow key was pressed). 4. LDW POWER returns to the READY mode to indicate that the worksheet is ready for another entry. Making Cell Entries This section will describe how to enter the three specific types of data: labels, numbers, and formulas. ENTERING LABELS Any entry into the worksheet that does not start with one of the following characters: 0-9, +, -, ., /, (, @, #, $, is considered a label by LDW POWER. Labels can be up to 240 characters long. As soon as you type in the first character of the label, the READY mode will change to the LABEL mode to indicate that the worksheet is accepting a label. To enter a label, type in the entry and press Return, or one of the arrow keys. There are four very important characters which are used when entering labels. These characters are used as label prefixes which tell LDW POWER how to handle the label within the cell. These prefixes are: Prefix Label format in cell ' (apostrophe) left-aligned " (quote mark) right-aligned ^ (caret) centered \ (backslash) repeating the string after the backslash Labels can be entered into the worksheet without a prefix. In this case, LDW POWER will automatically include the prefix set by the Worksheet Global Default command. If you want to enter a label which starts with a number, such as a street address, you must enter a label prefix. If you don't include the prefix, LDW POWER will think that you're trying to mix numbers and labels in the same entry and will not allow the entry. When you add a prefix to a label, it is automatically saved in the cell with the entry. You can view the prefix of any active cell in the status line, but the prefix does not appear in the cell itself. ENTERING NUMBERS There are certain characters which tell LDW POWER that you are entering a number. These characters are: 0 1 2 3 4 5 6 7 8 9 + - . $ When entering numbers into the worksheet, you must follow a few rules: 1. A number must begin with one of the special characters noted above. 2. You may enter a number which is followed by a % sign to indicate percent. 3. You may not have more than one decimal point in any number. 4. When entering a number, do not insert any commas or spaces. Commas and other formatting characters can be added using formatting commands. 5. A number may end in scientific notation, for example, 1.452E+12. 6. Number entries cannot contain spaces, commas, or alphabetical characters, except E, which is used for scientific notation. ENTERING FORMULAS Formulas are entries which manipulate other variables or cells in order to reach an end result. Think of a simple mathematical formula: 2+2=4 Here, the formula derives its meaning from manipulating (adding) two numbers in order to reach an end result (a sum). Formulas in LDW POWER go beyond this simple example, for they can manipulate actual numbers (as in the formula above) or strings, or they can manipulate the contents of certain cells by referring to their cell addresses. For example, an LDW POWER formula can take the form: +6/2 in which the formula evaluates the division of six by two, or: +C4*A5 in which the formula evaluates the value of the contents of cell C4 multiplied by the contents of cell A5. Formulas can also incorporate the functions built into LDW POWER. For example: @SUM(A1..A5) This formula uses the @SUM function to sum all the values in cells A1 through A5. @LOWER(B5) This string formula uses the @LOWER function to convert the label in cell C5 to lowercase. Like other entries in the worksheet, formulas appear in the input line as you enter them, and like number entries, formulas cannot contain spaces. Formulas may also be entered by using the keyboard or by pointing with the mouse. Take the following formula as an example: +A1*A2*A3*A4*A5 Having to retype each cell address can be both tedious and inconvenient. Therefore, LDW POWER allows you to enter each address into the formula by pointing to the particular cell. For this example, you would type in the plus (+) sign, point to cell A! with the mouse or cursor keys, type in the multiplication symbol (*), point to cell A2, and so on until the formula is completed. Note, that you can always use this pointing method in lieu of typing in the addresses from the keyboard whether you are entering a single cell, a series of cells, a range, or a group of ranges. Formulas in LDW POWER, as stated before, can use mathematical operators, LDW POWER functions, or a combination of both. Since the functions will be explained in depth later in this manual, we will confine ourselves here to a description of the operators. The Operators The operators used by LDW POWER formulas fall under three main categories: mathematical, relational, and logical. There is also an operator that performs string concatenation that will be explained briefly. Mathematical Operators There are seven mathematical operators: (+) addition (-) subtraction (*) multiplication (/) division (^) exponentiation (+) positive (-) negative The addition sign (+) tells the worksheet to add the items directly before and directly after the addition sign. The subtraction sigh (-) tells the worksheet to subtract the item directly after the subtraction sigh from the item directly before the sign. The asterisk (*) tells the worksheet to multiply the two arguments before and after it. The slash (/) results in the division of the item before the slash by the item after the slash. The caret (^) raises a given number to a power indicated by the user. The positive (+) and negative (-) signs are used to designate a given number as either positive or negative. LDW POWER follows a specific order in using the operators for calculations. It is helpful to know this order, when you start using more complex formulas. When calculating formulas, LDW POWER will process exponentiation first. The negative (-) and positive value (+) signs are evaluated next. Multiplication and division are processed next, and addition and subtraction are calculated last. When the formula contains more than one operator on the same level of priority (for example: a [*] and a [/} in the same formula), may override the order of the operators by using parentheses. LDW POWER will calculate all items in parentheses before anything else in the formula. Note, that when you use parentheses within parentheses, the operations enclosed in the innermost set of parentheses will be performed first. Relational Operators The relational operators are used to compare two arguments within a formula in order to determine if the arguments satisfy certain conditions. These operators include: Operator Meaning = Equal < Less Than <= Less Than or Equal To > Greater Than >= Greater Than or Equal To <> Not Equal LDW POWER evaluates these operators from left to right in the formula. Relational operators have lower precedence than all of the mathematical operators but higher precedence than the logical operators. These operators are usually used with the @IF function. Logical Operators Logical operators serve to "connect" two or more conditional statements in order to set up more comprehensive conditions and are usually used with the @IF conditional command. They are: #NOT# #AND# #OR# #NOT# The #NOT# operator does not really connect two conditions. Rather, it allows you to set up a "negative" condition. For example, the conditional statement: @IF(#NOT#C5>100,"Bad","Good") tests the contents of cell C5 to see if it is not greater than 100. If it isn't greater, the function returns the result "Bad"; if it is greater, the function returns the result "Good". The same result you would obtain using the statements: @IF(C5<=100,Bad","Good") or @IF(C5>100,"Good","Bad") #AND# The #AND# operator joins two conditional statements to form a compound condition. In a compound conditional statement, both conditions must be true in order for the entire statement to be true. Therefore, if either condition is false, the entire statement is false. For example, the compound conditional statement: @IF(@SUM(A1..A5)>55#AND#@AVG(A1..A5)<15,"true","false") stipulates that the sum of all values in the range A1..A5 must exceed 55 AND the average of those values must be less than 15 in order for the function to return the result "true". Therefore, if either the sum is less than or equal to 55 or the average is greater than or equal to 15, the function will return the result "false". #OR# The #OR# operator joins two conditional statements to form a less restrictive compound condition than that formed by the #AND# operator. With the #OR# operator, only one of the conditions in the statement must be true in order for the entire statement to be true. For example, the function: @IF(@MAX(D5..D9)<@MIN(B3..B10)#OR#B2>D4,"True","False") will return the result "True" if either the largest value in the range D5..D9 is less than the smallest value in the range B3..B10 or the value in cell B2 is greater than the value in cell D4. "False" will only be returned when both conditions are false. The logical operators are evaluated after relational operators. String Concatenation LDW POWER offers the (&) operator which allows you to easily concatenate strings located in different cells. For example, suppose cell A7 contains the label "LDW"; B9 contains " POWER"; and C3 contains " Spreadsheet". If you enter the formula +A7&B9&C3 in cell E1, the concatenated string "LDW POWER Spreadsheet" will be returned in cell E1. Note, that if your try to concatenate literal strings, they must be enclosed in quotation marks. Note also, that the (&) operator can only be used to concatenate labels. If values are used, an error will result. Cell Addresses in Formulas As mentioned before, formulas can use both actual numbers and cell addresses in calculating an end result. In fact, much of the versatility and power of LDW POWER comes from the fact that formulas can be used to link, compare, and combine the cell contents of many different cells in the worksheet. The advantage of using cell addresses in a formula is the flexibility that comes in changing arguments for the formula. For example, if your formula adds the contents of two cells, and you want to change the values that are being added, you can go directly to one of the referenced cells and enter a new value. The formula will automatically be recalculated (if the worksheet is in auto recalculation mode) using the new value. You can enter cell addresses in formulas either by typing in the cell address from the keyboard or by pointing to the cell with the mouse. If you use the mouse, you will have to type in a value character or operator and point to the appropriate cell. You can continue to type in operators and point to cells to complete the formula. ABSOLUTE AND RELATIVE CELL ADDRESSES When a cell address is used in a formula, the formula is, in effect, making a cell reference, because it is referring to other cells or ranges of cells from which to draw values. There are three kinds of cell references which can occur within a formula: absolute reference, relative reference, and mixed reference. A relative cell reference is a cell reference that is dependent on the cell location of the formula. That is to say, the referenced cell's position on the worksheet is relative to the position of the formula. For example, if your formula (+A2*4) is located in cell B3, and it makes a reference to cell A2, it is not really referring to the cell address A2, but rather to the cell that is over one column to the left and one row up in the worksheet. When you copy or move a formula using relative referencing, the addresses of the cells in the formula will be adjusted automatically to fit the new location. For example, if the above formula were moved to cell D4, it would no longer refer to cell A2 but rather to cell C3 (cell C3 is one column to the left of and one row up from cell D4). Absolute references are cell references that are independent of the cell location of the formula. If a formula makes an absolute cell reference, it is calling upon a fixed cell address. For example, if a formula makes an absolute reference to cell A1, it is specifically referring to the contents located in cell A1. When you copy or move a formula using absolute referencing, the referenced cell addresses will not change. Absolute cell references are designated within a formula by placing a dollar sign ($) immediately in front of both the column name and row number of the appropriate cell address. MIXED CELL REFERENCE Sometimes, you will need to reference a cell address that is both relative and absolute. Take the following formula as an example in cell B2: $A2+B$1 If you wanted to be able to use the same formula for each cell of the table, you would need to use mixed references. A mixed reference is one in which either the column is absolute and the row is relative or the column is relative and the row is absolute. In the formula above, the cell reference $A2 makes an absolute reference to column A and a relative reference to row 2. Translated, it says: "look for the contents in column A of the present row". This mixed reference allows thqz formula to always refer to the items in column A of the current row no matter where the formula is in the table. The cell reference B$1 makes a relative reference to column B and an absolute reference to row 1. Translated, it says: "Look for the contents in row 1 of the present column". It can be seen then, that mixed references add greater flexibility in using formulas. Cell addresses: Relative Absolute Mixed C5 $C$5 $C5 or C$5 Instead of designating references from the keyboard, you can use the F4 key to designate addresses. As an example, consider the formula: +G$2*$H$4. Begin by typing in the plus (+) sign and then point to cell G2. At this point, press the F4 key. You'll notice that dollar signs ($) are automatically inserted in front of both the column letter and row number. If you press F4 again, the dollar signs will only appear in front of the row number thus creating a mixed address that is fixed to the particular row. Press F4 again, and the dollar signs will only appear in front of the column letter thus creating a mixed address that is fixed to the particular column. Pressing F4 once more will remove all dollar signs from the cell address and will turn it back into a relative address. This sequence will repeat indefinitely as long as you keep pressing the F4 key. Cycle through the sequence until the first part of the formula looks like this: +G$2. Now, enter the rest of the formula: *H4. Use the F4 key once again to designate cell H4 as an absolute address *$H$4. Note, that the F4 key can be sued on any cell or range addresses when the worksheet is in the EDIT, VALUE or POINT mode. Editing Cell Entries LDW POWER offers convenient editing options which allow you to edit a cell entry without retyping the complete entry. All three types of cell entries; labels, numbers, and formulas; can be edited in a similar fashion. You can edit a cell entry, while you enter it into the cell, or you can edit a cell entry that has already been entered and accepted into a cell. To begin editing data, you will first want to place the worksheet into EDIT mode. You can switch to EDIT mode by clicking on the mode button on the control panel, by pressing the F2 key, or by double clicking on the cell that you wish to edit. If you want to edit an entry while it is still being entered (and the entry is displayed on the input line), you can click on the entry on the input line to invoke the EDIT mode. The highlighted EDIT button will appear on the control panel and the contents of the selected cell will appear on the input line. Some keys will take on different functions in the EDIT mode as compared to the READY mode (see table). You can then use the keyboard and mouse to make the appropriate changes to then entry directly on the input line. Action of keys in EDIT mode: Key Action right arrow Moves the cursor one position to the left left arrow Moves the cursor one position to the right Tab Moves the cursor 5 characters to the right Shift+right arrow Moves the cursor 5 characters to the right Shift+Tab Moves the cursor 5 characters to the left Shift+left arrow Moves the cursor 5 characters to the left Home Moves the cursor to the first character in the entry Shift+Home [End] Moves the cursor to the last character in the entry Backspace Deletes the character just to the left of the cursor Insert Toggles between INSERT and OVERTYPE modes Delete Deletes the character above the cursor Esc Clears the edit line and/or takes you out of EDIT mode CELL FORMATS Several of the menu commands in LDW POWER offer formatting options with which you can customize your worksheet. Since these options perform the exact same tasks in each case, they will be explained once. These options determine the way entries are displayed in their cells and can affect an area as small as a single cell or as large as the entire worksheet depending on the command they are used with. Some of these commands will ask you to enter the number of decimal places you would like displayed. When you specify the number, LDW POWER will automatically round off the numbers entered in the cells. The commands which offer these formatting options are: Range Format, Worksheet Column Format, and Worksheet Global Format. The Range Format command has priority over the Worksheet Column Format command which, in turn, has priority over the Worksheet Global Format. That is, a format option issued with the Range Format command will override any format option issued with either the Worksheet Column Format or Worksheet Global Format command. A format option issued with the Worksheet Column Format command will only override format options issued with the Worksheet Global Format command. Fixed Format The Fixed format displays values with a fixed number of decimal places. You may display up to 15 decimal places. When you select the Fixed format, a message will appear in the edit line asking you to enter the number of decimal places you would like displayed in each cell. Enter the number and press Return or click OK. Scientific Format The Scientific format displays values in scientific (exponential) notation. All values are represented to the powers of 10. An example of scientific notation for the number 1552 is: 1.55E+03 where the format is set to two decimal places. The E used in scientific notation stands for "10 raised to the power of". When you select the Scientific format, a message will appear in the edit line asking you to enter the number of decimal places you would like displayed. Enter the number and press Return or click OK. Currency Format This format option displays values in a monetary format. The default monetary format in LDW POWER displays a ($) at the beginning of the value and commas separating every third integer (i.e., hundreds, thousands, millions, ...etc.). When the Currency format is used, negative values are displayed in parentheses. You can use the Worksheet Global Default Other International Currency command to change the $ sign to any currency sign you want and to display it as either a prefix or suffix to the value. Comma (,) Format The Comma option, displays numbers with commas inserted between every third integer (i.e., hundred, thousands, millions). This option also displays negative numbers in parentheses and allows you to determine the number of decimal places you would like to display. General Format The General format is the default format setting. Values entered under the General format will usually be displayed the way they are entered, unless they are too long for the given cell. The worksheet will display such numbers in one of three ways. If the integer portion of the value will fit into the cell, the spreadsheet will simply truncate enough of the decimal portion to allow the value to fit into the cell. If the integer portion will not fit, the value will be displayed in scientific notation. If the width of the cell is too narrow to accommodate scientific notation, the value will be displayed in the cell as a series of asterisks. +/- Format The +/- format allows you to create simple bar graphs in the cells of your worksheet through the use of a series of plus and minus signs. Positive numbers are assigned a series of plus signs equivalent to the value in the cell. Negative numbers are assigned a series of minus signs equivalent to the value in the cell (i.e., the number 3 is displayed in the cell as +++). A zero is represented as a period. One important thing to remember when using the +/- format is that numbers that require more plus or minus signs than the cell width can accommodate will be displayed as a series of asterisks. Percent Format When the Percent format is selected, specified values int he worksheet will be converted to percentages and will be displayed with a percent sign (%). For example, the number 1 will be converted to 100%; the number 0.5 will be converted to 50%; the number 2 will be converted to 200%; and so on. Date Format The Date options can display values either in one of five date formats or in one of four time formats depending on the type of entry being formatted. The Date formats are used to format the dates specified by the @DATE, @DATEVALUE, and @NOW functions. Formatting for these functions is essential, because they return all dates as serial values. For example, @DATE(88,12,25), which represents the date December 25, 1988, would appear as 32502; refer to the @DATE function for more information. When the Date format is selected, a horizontal menu of the five date formats will appear (followed by the Time option). The formats are: Format Form Example 1 DD-MMM-YY 25-Dec-88 2 DD-MMM 25-Dec 3 MMM-YY Dec-88 4 (Long Intn'l) MM/DD/YY 12/25/88 5 (Short Intn'l) MM/DD 12/25 Note, that the Long and Short International date formats can be changed using the Worksheet Global Default Other International Date command. The same type of formatting procedure can be applied to time values. As with dates, time values are represented in the worksheet as serial values and must be formatted into a familiar form (refer to the @TIME and @TIMEVALUE functions for more information). When you select the Time option from the Date menu, a horizontal menu of the four time formats will appear. The time formats are: Format Form Example 1 HH:MM:SS AM/PM 12:25:30 AM 2 HH:MM AM/PM 12:25 AM 3 (Long Intn'l) HH:MM:SS 12:25:30 4 (Short Intn'l) HH:MM 12:25 Note, that the Long and Short International time formats can be changed using the Worksheet Global Default Other International Time command. Text Format The Text format will display the actual formulas or functions contained in cells rather than the results of those formulas or functions (which is the default display). Note that formulas or functions which ar longer than the cell width will only be partially displayed. Anything longer than the cell width will be cut off from view. Hidden Format You can use the Hidden option to hide the values and labels contained in the worksheet. Not only will existing entries be hidden, but also will any subsequent entries. That is, if you make an entry after selecting the Hidden option, the entry will be recorded in the worksheet but will not be displayed. To re-display the hidden entries, simply use on of the other formats. (End of Part 1 ... Now load Part 2 !) Part 2 of the extremely large LDW Power Spreadsheet Doc..... COMMANDS WORKSHEET MENU The commands in your Worksheet menu are used to adjust and control many different aspects of your worksheet. Most of the commands affect every cell in the worksheet, allowing you to control many cells with convenience and ease. The following section of your manual takes you step by step through the Worksheet menu, explaining each of the commands and how they are used. Explanations appear in the order that the commands are displayed in the menu. Worksheet Global The Worksheet Global menu contains all the commands necessary to adjust the settings for the entire worksheet. Once you execute a command from the Worksheet Global menu, every cell in the worksheet will be affected by that command. The commands in the Global menu allow you to change the default settings for the worksheet which are put into place when the worksheet is loaded. Then Worksheet Global is selected, a horizontal menu will appear displaying seven separate menus. These menus are: Format, Label-Prefix, Column-Width, Recalculation, Protection, Default, and Zero. WORKSHEET GLOBAL FORMAT The commands in the Worksheet Global Format menu are exactly the same as those described under the Cell Formats. Remember, however, that the Global Format options will affect every cell in the worksheet. Refer to the Cell Formats section for a description of the format options. You can override the Global formatting in certain cells by formatting those cells individually (see Range Format commands). WORKSHEET GLOBAL LABEL-PREFIX The Worksheet Global Label-Prefix menu contains options which allow you to change the default label-prefix for all cells in the worksheet. As explained previously in the manual, there are four available prefixes: ' (align-left), " (align-right), ^ (align-center), and \ (repeat). Worksheet Global Label-Prefix includes three commands which allow you to set all cells in the worksheet to automatically align entries to the left, center or right. Note, that these commands will not affect existing entries in the worksheet but will only apply to entries made after the command is issued. WORKSHEET GLOBAL COLUMN-WIDTH The Global Column-Width command allows you to change the default column width for all of the columns in the worksheet. The default global column width is nine characters wide. You may select a column width from 2 characters up to a column width of 72 characters. When you select the Worksheet Global Column-Width command, a message will appear in the edit line asking you to enter the column width. Enter a number between 2 and 72 for the width and press Return or click OK. Note, that this command will not effect columns that have been changed either by the Worksheet column Set-Width command or that have been altered by the column header dragging method. WORKSHEET GLOBAL RECALCULATION Much of the power of a spreadsheet lies in its ability to process formulas and functions. Often, these formulas and functions refer to the contents of other cells in the worksheet. When those contents change, all formulas are automatically recalculated (in the default auto-recalculation mode). LDW POWER provides you with several commands which let you control the process of recalculation. These commands include manual and automatic recalculation, order of recalculation, and the number of times recalculation occurs. When you select Recalculation from the Worksheet Global menu, a horizontal menu will appear with the available commands. The first three commands determine the order in which the spreadsheet performs recalculation. The final three commands determine when the recalculation will occur. Recalculation Order Natural Performs recalculation starting with the most basic cell and proceeding to the last cell to be affected by recalculation. When the Natural command is selected, calculation begins with the cell which all the other cells depend on. It will not recalculate any cells until it recalculates the cells that they are dependent upon. Columnwise Performs recalculation, column by column, through the worksheet. When the Columnwise command is selected, recalculation begins with cell A1, and proceeds across the worksheet, one column at a time. Rowwise Performs recalculation, row by row, through the worksheet. When the Rowwise  option is selected, recalculation begins with cell A1 and proceeds down the worksheet, one row at a time. Recalculation Mode Automatic LDW POWER automatically recalculates affected formulas in the worksheet every time an entry is made. In the Automatic Recalculation mode (the default), relevant formulas are recalculated every time an entry is made which may change the results of those formulas. Note that only those formulas affected by any new entries are recalculated. LDW POWER saves time by not recalculating all the formulas in the worksheet. Manual LDW POWER will only recalculate the worksheet when you tell it to do so. The benefit of manual recalculation is that you do not have to wait for the worksheet to recalculate every time you make an entry. When you are in manual mode, and a change has occurred in the worksheet, the CALC button will be highlighted to indicate that yuo are in manual mode. When you want to recalculate in the Manual mode, select the CALC button or press [F9]. Iteration The iteration option can be used to consecutively recalculate all the formulas in the worksheet a specified number of times. Iteration is a procedure that is usually used when circular references are involved. Therefore, in order to explain the purpose of iterative recalculation, circular references must first be explained. A circular reference occurs when a cell contains a formula that refers to the cell itself. For example, if cell C6 contains the formula [+C4+C6], a circular reference will occur, because the formula refers directly to cell C6. This type of reference is unsolvable, because each time that a value is calculated for the formula in cell C6, the value of cell C6 changes. Suppose cell C4 contains the value 10. When you enter the above formula into cell C6, it will return the result 10, and cell C6 will contain the value 10. The second calculation will return the result 20 (C4=10;C6=10;C4+C6=20), and so on. This process would go around in a circle, and a "true" answer would never be found. Whenever a circular reference is made in the worksheet, and the worksheet is recalculated, the CIRC indicator will be displayed on the control panel and will remain there, until the reference is corrected or removed. Some circular references though, are solvable, and it is these references that employ the use of iterative recalculation. Take the following circular reference as an example: A1 = $10000; B2 = A1-A3; C3 = 10%*B2 You can use the Worksheet Global Recalculation Iteration command to "solve" this circular reference, or at least approximate a solution as close as possible. When you select this command, a prompt will ask for the number of iterations to perform (the maximum number of iterations is 50). Note, that the default iteration count is 1. When you change that count, the new number will become the default for all further recalculations for the current session. WORKSHEET GLOBAL PROTECTION One of the most common errors that you can make when using a worksheet is to accidentally overwrite or delete an important entry. Accordingly, LDW POWER provides you with a global protection feature which allows you to protect the entire worksheet from accidental changes or deletions. Once you activate Worksheet Global Protection, you cannot make any changes to the worksheet, including entries, changes or deletions. Since you can do little more than move around the worksheet, this protection might, at first, seem silly. However, you can use the Range Unprotect command to unprotect a specific range or specific ranges. This way, you can work on a given range of cells, while still keeping the rest of the worksheet protected. If you want to re-protect cells or cell ranges within those unprotected ranges, you can use the Range Protect command (the Range Protect command will only have an effect when the Global Protection is enabled). Note, that when you use the Range Unprotect command to unprotect an empty cell, a space in memory is used. Therefore, you might be better off by disabling the Global Protection command rather than trying to unprotect a very large range of empty cells (see the Range Protect and Unprotect commands for more information). The Worksheet Global Protection menu contains two commands: Enable and Disable. Select Enable to turn on the protection. The PROT indicator will be highlighted in the control panel to indicate that protection is on. As a default, all cells in the worksheet are protected against changes when Global Protection is enabled. If you try to make any changes to the worksheet when protection is on, an error tone will sound, and a message will appear warning you that cell protection is on. Select Disable to turn Global Protection off. WORKSHEET GLOBAL DEFAULT The Worksheet Global Default menu contains commands related to the default settings for the printer, file directory, and others. All default settings are active for the current session, and can be saved in the "default settings" file (LDW.DNF) using the Worksheet Global Default Update command. Worksheet Global Default Printer The Worksheet Global Printer menu contains commands which allow you to define and adjust the printer default and interface settings. Interface The Interface option is used to tell LDW POWER which printer interface it should use. The interface is the port (connector) on your computer where you connect your printer. Your computer has two printer interfaces: a parallel interface and a serial interface. Refer to your printer manual to determine which type of interface you will use. Most printers use the parallel interface, so that is the default interface in LDW POWER. When you select the Interface option, a horizontal menu will appear with the Interface commands. To select a parallel interface, choose the number 1 from the menu. To select a serial interface, choose the number 2. Once you have made your selection, you will be returned to the Worksheet Global Default Printer menu. Auto-LF The Auto-LF command is used to control whether LDW POWER sends a line feed to the printer when a carriage return is issued at the end of each line of the document being printed. LDW POWER has an Auto-LF default setting of Yes. This means that line feed will be sent to the printer with every carriage return. The Auto-LF setting you choose is dependent on your printer. Some printers will automatically put in a line feed with every carriage return. Other printers will just issue a carriage return with no line feed. If your printer automatically issues a line feed, and you have the Auto-LF option set to yes, your printout will be double spaced. If you want single spacing, you should turn off the Auto-LF command. Select No to turn off the Auto-LF command or Yes to turn it on. Margins The next four commands in the menu; Left, Right, Top, and Bottom; all refer to the default margins LDW POWER will leave in your document. Each command will ask you to enter a number for the particular margin. Remember, that the numbers you choose are default settings, and that every document you print during the current session will have the margins you specify, until you change them. To set margins for just one document, refer to the Print Options command. Left The Left command allows you to specify the character width of the left margin. Select Left, and a message will appear in the edit line asking you to enter the number of characters from the left edge of the paper you would like the printer to start printing from. You may enter a number from 0 (to start printing at the very edge of the paper) to 240 (for 240 blank spaces). If you enter a number over 240, you will get an error message. Type in the number and press Return. Note, that you should never set the left margin to be larger than the right margin. Right The Right command allows you to specify the character width of the line. When you select the Right command, a message will appear in the edit line asking you to enter the right margin. You may enter a number from 0 (no characters per line) to 240 characters per line. If you enter a number over 240, you will get an error message. Unlike the Left command, which tells LDW POWER how far from the edge of the paper the text should begin, the Right command tells LDW POWER how many characters a line of text can have. For example, if you enter the number 80 for the right margin, and the left margin is the default 4 spaces, no more than 76 characters will be printed on any line. The right margin can be any number between 0 and 240, but it should never be smaller than the left margin setting, or you will not be able to print anything. You must also make sure that the right margin will fit into the boundaries of the paper you are using and the set-up string you have specified. Top The Top command allows you to specify how far down from the top of the paper you would like the paper to start printing. Select Top and a message will appear in the edit line asking you to enter the number of blank lines from the top of the page LDW POWER should leave. Enter a number between 0 and 32. If you enter a number over 32, an error message will appear. LDW POWER will automatically leave three blank lines at the top of a page for the page header. Therefore, any blank lines you specify for the top margin are in addition to these three blank lines. Bottom The Bottom command allows you to specify how far up from the bottom of the page you would like the printer to stop printing. Select Bottom, and a message will appear in the edit line asking you to enter the number of blank lines from the bottom of the page LDW POWER should leave. Enter a number between 0 and 32. If you enter a number over 32, an error message will appear. LDW POWER will automatically leave three blank lines at the bottom of a page for the page footer. Therefore, any blank lines you specify for the bottom margin are in addition to these three blank lines. Pg-Length The Pg-Length command allows you to change the default page length setting. The default page length is 66 lines, but you can use this command to change the page length from 1 line per page up to 100. To execute this command, select Pg-Length from the menu. When the message appears in the edit line asking you to enter the default lines per page, enter a number between 1 and 100. If you enter a 0 or a number greater than 100, you will get an error message. The Pg-Length command from the Worksheet Global Default menu will change the default page length for the current session with LDW POWER. If you just want to change the page length for one document, use the Pg-Length command from the Print menu. Wait The Wait command allows you to stop printing after each page and resume when you are ready. This is very useful when you are printing on individual sheets of paper rather than on continuous-feed paper. The default setting for the Wait command is No. When No is the active setting, LDW POWER will continuously print a document without pausing. Page break spaces will automatically be entered, and there will be no time to manually feed pages. To use the Wait command, select Yes from the Worksheet Global Default Printer Wait menu. When this command is selected, LDW POWER will stop and beep at the end of each printed page. You can then manually feed pages and arrange them as you would like them. When you are ready to print a page, press any key, and LDW POWER will resume printing. Setup Setup strings are special character sequences which tell your printer how to print characters in your document. Using setup strings, found in your printer owner's manual, you can instruct your printer to print LDW POWER worksheets with different character or line spacings or with special printing effects (such as bold type). The default setup string is an empty string. That means that the default setting for your printer will determine the default printing format. If you specify a setup string for a worksheet and save it with the worksheet, that setup string will become the default for that particular worksheet (see the Print Options Setup for more information). When you change the default setup string, the new string becomes the default for all printouts for the current session with LDW POWER. If you just want to change the setup string for one printout, use the Print Options Setup command. If you want to save this default setting for all future use with LDW POWER, issue the Global Default Update command from the Worksheet menu. To define a default setup string, select the Setup option. A message will appear in the edit line asking you to enter a default setup string. Enter the string (refer to Print Options Setup for more information on setup strings) and press the Return key. Once you have entered a proper setup string, you will be prompted for two character sequences to represent bold and underlined style in the printout. These sequences should follow the same form as the setup string. Enter the sequences (or press the Return key to accept the default forms of the sequences). Your setup string and the two style sequences will be saved as the defaults. Quit The Quit command in the Printer menu will return you to the Default command menu. Worksheet Global Default Directory The Worksheet Global Default Directory command allows you to change the default directory. The default directory is the directory from which LDW POWER will load the AUTO.LDW file and will look for the worksheet files to retrieve or save (unless you have selected a different directory using the File Selector or the File Directory command). The default directory, like other default settings, can be made "permanent". To do so, you have to save the default settings using the Worksheet Global Default Update command. When you select Worksheet Global Default Directory, a message will appear in the edit line asking you to enter the default directory. Press Esc or Backspace to delete the initial directory and type in the new directory. Worksheet Global Default Status The Status command lets you check the status of all the worksheet defaults. When you select Status, the Status dialog box will overlay the worksheet. Press Return or click on OK to exit from the Status dialog box. Worksheet Global Default Update The Update option will save all Global Default settings to a special "default settings" file called LDW.CNF. Whenever LDW POWER is loaded, it will look for this file in the current directory. If you change some of the Global Default settings during a session and do not save them with the Update option, those changes will not appear the next time you load LDW POWER. Worksheet Global Default Other The Other menu contains commands which deal with numeric settings of the worksheet. These include dates, times and the order in which numbers are displayed for Data Sort commands. When you select Other, a horizontal menu will appear with two items: International and Sort. International The International menu, when selected, will reveal a horizontal menu of commands which control the default numeric formats. The following will explain each command and how it works. International Punctuation There are three ways that LDW POWER will use punctuation marks for formatting purposes. In the LDW POWER default setting, periods are used as decimal points in numbers (10.7), commas are used to separate every third digit in numbers (1,000,000), and are also used to separate the arguments in functions (@SUM(B1,12,C4)). The Punctuation command allows you to change the punctuation marks to fit your needs. When you select Punctuation, a horizontal menu of the available punctuation settings will appear. International Currency The Worksheet Global Default Other International Currency command allows you to change the default currency string which appears in currency formats. The default string is a $ preceding the number. You can change this to any string you choose of up to 15 characters long. When you select this command, a message will appear in the edit line asking you to enter the string you would like displayed as the currency indicator. Backspace or delete to remove the present string and enter your own. When you enter the character, another menu will appear. This menu contains two items: Prefix and Suffix. Select Prefix if you would like the currency symbol displayed before the value or Suffix, if you would like the currency symbol displayed after the value. International Date The commands in this menu allow you to control the display of long and short international dates (formats 4 and 5 in the Worksheet Global Format Date menu). These four commands are: Format Form Example A MM/DD/YY 12/25/88 B DD/MM/YY 25/12/88 C DD.MM.YY 25.12.88 D YY-MM-DD 88-12-25 When any of these commands are issued, all long and short international dates will be adjusted according to the selected format. Note, that short international dates will be formatted according to the styles listed above, except they will not display figures for the year. International Time The commands in the Worksheet Global Default Other International Time menu allow you to set the display of the long and short international time settings (formats 3 and 4 in the Worksheet Global Format Date Time menu). These four commands are: Format Form Example A HH:MM:SS 12:25:59 B HH.MM.SS 12.25.59 C HH,MM,SS 12,25,59 D HHhMMmSSs 12h25m59s When any of these commands are issued, all long and short international times will be adjusted according to the selected format. Note, that short international times will be formatted according to the styles listed above, except they will not display figures for the seconds. International Quit This command will return you to the Worksheet Global Default menu. Sort The Sort menu contains commands which determine the order of precedence of characters in a short (see the Data Sort command). These commands can be used together. For example, you can issue the Same command to treat both upper and lower case letters the same, while issuing the First command to give number precedence over letters. The following section explains each of these commands. Sort Different The Different command tells LDW POWER to treat lower case and upper case letters differently when performing a sort. Upper case letters will precede lower case letters in an ascending sort and will follow lower case letters in a descending sort. Sort Same The Same command instructs LDW POWER to treat upper and lower case letters the same in a sort. Sort First The First command tells LDW POWER that numbers are given precedence over letters in a sort. This means that entries which begin with numbers will appear before entries which begin with letters. Sort Last This command tells LDW POWER that letter take precedence over numbers in a sort. This means that entries that begin with letters will appear before any entries which begin with numbers. Worksheet Global Default Quit This command will return the worksheet to the READY mode. Worksheet Global Zero The commands in this menu hide the display of all cells in the worksheet that have a total numeric value of zero. Select Yes to enable this setting or No to disable it. Cells that contain formulas or functions that return total numeric values of zero are affected by this command as well. Worksheet Insert The Worksheet Insert menu contains two commands, Row and Column, which allow you to insert one or more blank rows or columns anywhere in the worksheet. When you select either of these commands, a message will appear in the edit line asking you to indicate where in the worksheet you want to insert the row or column. Enter a range with same number of rows or columns that you wish to insert (i.e., if you want to insert three rows, enter a range that is three rows long). Columns will be inserted directly to the left of the designated range, while rows will be inserted directly above the designated range. Worksheet Delete The Worksheet Delete menu has two commands, Row and Column, which allow you to delete one or more rows or columns from your worksheet. When you select either of these two commands, a message will appear in the edit line asking you to enter the range you would like to delete. Enter the appropriate range, highlighting the row(s) or column(s) you would like to delete, and press Return. Worksheet Column The Worksheet Column menu contains commands which affect the display of columns in any given worksheet window. The Worksheet Column commands affect only the active worksheet window, therefore, the column display may be different among different windows. When the Column menu is selected, a horizontal menu displaying the four commands will appear. WORKSHEET COLUMN SET-WIDTH The Worksheet Column Set-Width command will change the column width for a selected column or range of columns from the current width to a column width that is anywhere from 2 characters wide to 72 characters wide. When you select the command, a message will appear in the edit line asking you to enter the desired column width. Type in the number and press Return or click OK. The column or columns you selected will adjust to the desired width. Note that you can also set the column width by moving the mouse pointer to the right border of the column label box at the top of any column, and dragging the column the desired width. WORKSHEET COLUMN RESET-WIDTH The Worksheet Column Reset-Width command returns the selected column or columns to the default width. WORKSHEET COLUMN FORMAT The commands in the Worksheet Column Format menu will format a specific column or range of columns. These formatting options are identical to those in the Cell Formats section, so you can refer to that section for details. There is one option that deserves mention here, however, and that is the Reset option. This option will change the format of the cell(s) in the range to the setting that has been defined under the Worksheet Global Format command. Note, that all formats issued under the Worksheet Column Format command take priority over formats issued with the Worksheet Global Format command but do not take priority over formats issued with the Range Format command. WORKSHEET COLUMN HIDE The Worksheet Column Hide command prevents a specified column or columns from being displayed on the screen and suppresses the printing of the specified column(s). To hide columns, select the Worksheet Column Hide command and specify the column(s) you would like to hide. Note, that the hidden columns become visible when the worksheet is in the POINT mode. WORKSHEET COLUMN DISPLAY The Worksheet Column Display command re-displays the specified hidden columns. Select this command and enter the appropriate column(s). Worksheet Erase The Worksheet Erase command will erase everything in the worksheet. When you select this command, an alert will appear in the center of your screen warning that erasing will destroy all your work. Click on PROCEED to erase the worksheet or click on CANCEL to return to the worksheet. Remember that once you erase a worksheet that has not been saved, there is no way to retrieve the information that was erased. Worksheet Titles The Worksheet Titles menu contains commands which allow you to freeze a few specified rows or columns (titles) on the worksheet. This means that as you scroll down or across the worksheet, the specified row(s) or column(s) will remain on the screen. This feature is often helpful when you have row or column headers which you want to remain visible. To select the row(s) or column(s) y0u would like to freeze, move the cell pointer to the cell directly below the rows you would like to freeze and/or to the right of the columns you would like to freeze and issue the command. Once you have frozen a title to the screen, the area that has been frozen is distinguishable by the dark line separating it from the rest of the worksheet. To move the cell pointer into the frozen rows or columns, you must place the worksheet in POINT mode. You can then use the cursor keys or the mouse to move into the frozen area(s). Note, that if you have several windows open, the Title commands only affect the active window. If you want to freeze titles in the other windows, you must make each window active and issue the Title commands. WORKSHEET TITLES BOTH The worksheet Titles Both command allows you to freeze both rows and columns of the worksheet. WORKSHEET TITLES HORIZONTAL The Worksheet Titles Horizontal command allows you to freeze only rows of the worksheet. WORKSHEET TITLES VERTICAL The Worksheet Titles Vertical command allows you to freeze only columns of the worksheet. WORKSHEET TITLES CLEAR The Worksheet Titles Clear command will "unfreeze" any titles that have been frozen to the screen. When you select the Clear command, the lines marking the frozen rows or columns will disappear. Worksheet Window The Worksheet Window menu allows you to manipulate up to four separate worksheet windows at one time and to see different parts of the worksheet simultaneously. This can be very useful when you want to see different parts of the worksheet without constantly having to use the cell pointer to move around. When you open up a new window, it will become the active window. WORKSHEET WINDOW OPEN The Worksheet Window Open command opens a window. When working on a worksheet, you can open up to three additional windows at a time. WORKSHEET WINDOW CLOSE The Worksheet Window Close command can be used to close the active window when you have more than one window open. If you select the Close command with only one window open, LDW POWER will return an error message, because you must always have at least one window open. WORKSHEET WINDOW GRID The Worksheet Window Grid command will either show or hide the grid displayed on the worksheet. If the grid is visible and the command is selected, the grid will disappear from the worksheet. If the grid is hidden and the Worksheet Window Grid command is selected, the grid will appear. Note, that this command only affects the active window. WORKSHEET WINDOW VIEW The Worksheet Window View command can be used to condense the worksheet window display on a monochrome monitor. If you select the Worksheet View command while using a color monitor, nothing will happen. Selecting the View command condenses the total number of rows in the worksheet window, so that you can see up to 29 rows instead of the default of 15. To return the display to normal viewing, re-select the command. You can use the Worksheet Window View command to condense the display in any open window. That is, if you have four windows open, you can condense the display in all four windows. You must, however, set the view command separately for each window that you want to condense. WORKSHEET WINDOW TOP The commands in this menu will make a given worksheet window the active window and will display it on top of all other windows. If the window is not already on the screen, these commands will open it and bring it to the top. The commands A, B, C, and D refer to the four different windows that may be accessed. Select the window to be affected and press Return. You may also make one of the open windows active simply by clicking on it. Worksheet Status When the Worksheet Status command is selected, a large dialog box appears displaying the free memory space available on the worksheet along with the status of the current default settings of the worksheet. The first line of the status box shows the amount of memory that is currently free, the total memory available on the worksheet and a percentage of the amount of memory that is currently free. The next sections display the default settings for recalculation and cell display. The bottom lines of the status box indicate if there is any circular reference in the worksheet, where it is, and whether global protection is active or inactive. Note that the circular reference indicator will give the location of only one circular reference at a time. Once the circular reference displayed is removed, the status box will display another circular reference, if one exists. This cycle will continue until all circular references have been removed. Worksheet Page The Worksheet Page command allows you to insert manual page breaks into your worksheet. To issue this command, move the cell pointer to the cell directly below where you would like the page break inserted and to the first column of the area to be printed, and select the Worksheet Page command. A new row will be inserted with a special page-break symbol (::) to indicate where the page break will occur when you print your worksheet. To remove page breaks, either erase the cell which contains the page break (see the Range Erase command) or simply type a new entry over the page break. RANGE MENU The Range menu contains commands which deal with ranges of cells specified by the user. With Range commands, you must select a specified cell or range of cells which will be affected by the command you issue. You may select the range before issuing a Range command (in which case the command will automatically refer to the pre-selected range), or after issuing a Range command (in which case the command will prompt you to enter a range). Range commands almost always deal directly with the actual cell that is being affected and not the cell entry. For example, you can use a range command to format a cell to display two decimal places in a fixed format. Therefore, any changes made to the cells through the Range commands are independent of any entries that may be made into those cells. The following section of your manual takes you through the Range menu explaining each of these commands and how to use them. Range Format The Range Format menu contains commands which can be used to format a specified cell or range of cells in the worksheet. When Range Format commands are issued, they will override existing global and column formats. These format commands are exactly the same as those described under Cell Formats except for the fact that the Range Format commands apply to specific cell ranges. Refer to the section on Cell Formats for the format options. Range Style The Range Style menu contains three commands which affect the way cell entries are displayed and printed. Select the appropriate command and enter the desired range at the prompt. The Range Style commands are: Bold: Displays characters in bold lettering. Underlined: Displays characters with underline symbols. Reset: Returns the characters to plain font. Range Layout The Range Layout menu contains three commands that control the alignment of entries in their cells. Select the appropriate Range Layout command and enter the desired range. Note, that this is the only Range command and does not affect the cell format itself but rather the entry within the cell. That is, you cannot format a cell to automatically align any entry that may be placed in it. You must wait until an entry is made in the cell and then perform the Range Layout command on that entry. Note, that the Range Layout command will not only affect the alignment of the entry but also affect the label prefix of the entry. The Range Layout commands: Left: Displays labels or values aligned to the left side of the cell. Right: Displays labels or values aligned to the right side of the cell. Center: Displays labels or values centered in the cell. Range Erase The Range Erase command deletes the entries from the specified cell or range of cells. Select the Erase option and enter the address of the cell or range of cells you would like to erase, and the entries will be removed from the selected cells. Note, that this command also deletes cell formats assigned by the Range Format commands. Range Name LDW POWER allows you to name cell ranges. You can then use these names as you would use range addresses in formulas, functions and commands. After a name has been given to a range of cells, LDW POWER will refer to the name, and not the range coordinates. Naming ranges can help in clarifying worksheets and making formulas easier to follow and understand. For example, the range name SALES is much more descriptive and easier to understand than the range coordinates A5..A10. The Range Name commands are: Create, Delete, Labels, Reset, Table. The following is a description of each command and how it is used. RANGE NAME CREATE The Range Name Create command is used to create or modify the name of a range. When you create a range name for the first range in the worksheet, a message will appear in the edit line asking you to enter a name. Type in the name you would like to give the selected range. The name you choose can be up to 15 characters long and can include almost any character, but the name must begin with a letter. Also, names must not be valid cell or range addresses, numbers, operators or function names. Thereafter, a menu of all existing range names will appear (in alphabetical order) when you invoke the Range Name Create command. You may enter a new name for the next range, or you can use one of the existing names. To enter a new name, just type it in. After you have entered the name, a second message will appear in the edit line asking you to enter the range you would like to name. Enter the range and press Return or click OK. The selected range will now be assigned the name you entered. You can give a range more than one name. If a range has more than one name, either name can be used to refer to that range. You cannot use the same name more than once, however. If you try to use a name that has already been used, LDW POWER will re-issue the name to the new range that is specified, and the original range will no longer be a named range under that name. RANGE NAME DELETE The Range Name Delete command is used to delete range names. When you select this command, a message will appear in the edit line asking you to enter the name you would like to delete. A list of all of the range names in the worksheet will also appear. Select the name you would like to delete and press Return. It is important to note that deleting a range name does not delete the range itself. LDW POWER will simple change the range name to the range coordinates in functions and formulas. RANGE NAME LABELS The Range Name Labels command offers a convenient way to name ranges in the worksheet. This command uses labels located in a range of the worksheet to name the cells which are adjacent to that label range. For example, suppose you have a label in the worksheet in cell C5. You can use that label to name the cell adjacent to that label, such as cell D5. Using labels on the worksheet to name cells will also help you keep track of the locations of the cells. To use this command, you must first enter the labels into the worksheet. The labels should be arranged in the cells of one row or column. The Range Name Labels menu contains four commands: Right, Down, Left and Up. These commands tell LDW POWER which cells to name. For example, if you select Left, LDW POWER will name the cells to the left of the range of labels you select. Once the direction has been entered, the edit line will appear with a prompt asking you to enter the range of labels to be used as names. Enter the correct range and press Return. RANGE NAME RESET The Range Name Reset command erases all named ranges from the worksheet. RANGE NAME TABLE The Range Name Table command will create a table in the worksheet which contains all of the names of the named ranges. This list can be useful when you have a lot of named ranges and need to keep track of them. When you select the Range Name Table command, a prompt will appear asking you to enter the range where you would like the table to appear. Select the correct range. A listing of all of the range names will appear in the first column with their corresponding range coordinates in the second column. Range Justify The Range Justify command allows you to break up long labels into a series of shorter labels. If you have a lone label that stretches across the screen out of view of the window, you can specify a shorter range in which you would like the label to appear. The label will then be broken up and placed in the specified range. Note, that sections of the label will be broken up according to the spacing between sections. Therefore, if the label has no spaces, it will not be broken up. When you issue the Range Justify command, a message will appear in the edit line asking for the range in which you would like the label to appear. Enter the proper range. The label will appear in the specified range, broken up into a series of smaller labels. Range Protect The Range Protect command works directly with the Worksheet Global Protection command to allow you to protect your worksheet from accidental changes. Usually, when you are using a worksheet filled with important data, you will use Worksheet Global Protection to protect every cell in the worksheet to be sure that all the data is protected. You can then use the Range Unprotect command to unprotect specific cell ranges that you want to work on. If there are certain cell ranges within those unprotected ranges that you wish to re-protect, you can use the Range Protect command to protect those cells. When you select the Range Protect command, a message will appear in the edit line asking you to enter the range you would like to protect. Enter the proper range. The entered range cannot be altered until the Unprotect command is issued or Global Protections is disabled. If you try to alter a protected cell, you will get a message that the cell is protected. Range Unprotect The Range Unprotect command will unprotect a range of cells that are protected by the Global Protection command. When Global Protection is activated, all cells in the worksheet are protected and cannot be altered. The Range Unprotect commands allows you to unprotect a given range of cells and change or even delete the entries in the given range. The range specified can be as large as you choose, but for each cell that is unprotected, a space in memory is used up. Therefore, if you try to unprotect the entire worksheet using the Range Unprotect command (rather than disabling the Global Protection), you will waste the available memory. When the Unprotect command is selected, a message will appear asking you to enter the range to unprotect. Enter the appropriate range and press Return or click OK. You may now edit the unprotected cells. Range Input The Range Input command is used in conjunction with Range Protect, and Range Unprotect. Suppose you have the following situation: a range is unprotected by the Range Unprotect command; certain cells within that unprotected range are protected by the Range Protect command. You can use the Range Input command to move the cell pointer more easily within that unprotected range. Since some of the cells in the unprotected range are protected, you will want to skip over them, as you are entering data. The Range Input command automatically moves the cell pointer over any protected cells within the unprotected range, allowing you to move to only the unprotected cells in that range. This command can be useful for creating fill-in forms, because you can enter data only in certain locations. When you select the Range Input command, a message will appear asking you to enter the Input range. Enter the appropriate range (which will usually be the entire range that was unprotected by the Range Unprotect command) and press Return or click OK. The mode button will display INPUT to indicate that the worksheet is in the INPUT mode. To return to the READY mode, press the Return or Esc key. Range Value You can use the Range Value command to copy the values returned by formulas in one range into another range. For example, if you have a formula in cell B1, you can use the Range Value command to copy the value returned by that formula into any cell in the worksheet. Remember that when you use the Range Value command, the formulas are not affected or moved in any way, and only the returned values and not the formulas themselves are copied into another range. Labels and numbers are copied directly. After you select the Range Value command, enter the appropriate FROM and TO ranges for the copying procedure. See the Copy-Move command for the rules that apply when copying ranges of cells. Range Transpose The Range Transpose command allows you to copy the entries from a specified range of columns into a specified range of rows or to copy the entries from a specified range of rows into a specified range of columns. This means that the Transpose command will flip horizontal entries vertically or vertical entries horizontally. You may also transpose ranges which are larger than one column or row. When you select the Range Transpose command, a message will appear in the edit line asking you to enter the range to copy from. Enter the range you would like to transpose. A second message will appear in the edit line asking you to enter the range you would like to copy to. Enter the range you would like the entries in the first specified range of cells transposed into. The destination range may not overlap the cells from the source range. COPYING AND MOVING ENTRIES The Copy-Move menu allows you to copy or move selected cells or ranges of cells anywhere in the worksheet. This is one of the most basic features of any spreadsheet and also one of the most important. Copy Command The Copy command allows you to copy the contents of any cell or range of cells in the worksheet to any other cell or range of cells in the worksheet. As with all other commands in LDW POWER, you may pre-select the range you want to copy or wait until the prompt appears to enter the cell or range of cells you want to copy. When you select the Copy range command, a prompt will appear in the edit line asking you to enter the range you want to copy from. This is the range which contains the entries which you want to copy to another location in the worksheet. Enter the correct range or accept the range which appears in the edit line. A second prompt will appear asking you to enter the range you want to copy to. Enter the range in which you would like the copied entries to appear. When you press Return or click OK, the copied entries will appear in both the original cells and the new range of cells you specified. You may issue the copy command as many times as you wish, and you may copy any given range as often as necessary. If you copy a cell or range of cells into any cells which already contain entries, the newly copied entries will overwrite the original entries. If you are copying a range into another range of the same size, and the destination range overlaps the original range, LDW POWER will not allow you to perform the copy. The possibilities for copying are: FROM range TO range Example Single cell Single cell A1 to D5 Single cell Multiple cells A1 to F7..H9 Single row Single row D1..H1 to F3..F3 Single column Single column B1..B7 to K1..K7 Single row Multiple rows D1..H1 to F3..G10 Single column Multiple columns B1..B7 to F1..H2 Rectangular range Rectangular range B7..F9 to K7..O9 Single cell to single cell: copies the contents of a single cell into the destination cell. Single cell to multiple cells: copies the contents of a single cell into each cell of the destination range. Single cell to single row: copies the contents of a single row into the destination row. You can designate the destination row by selecting the entire row or by selecting the leftmost cell in that row. Unlike the single cell to multiple cells copy, the source row will only be copied once into the destination row (even if the destination row is longer than the source row). Single column to single column: copies the contents of a single column into the destination column. You can designate the destination column be selecting the entire column or by selecting the top cell in that column. Like the single row to single row copy, the source column will only be copied once into the destination column. Single row to multiple rows: copies the contents of a single row into each row of the destination range. You can designate the destination rows by selecting the entire range or by selecting the leftmost cell in each row. Single column to multiple columns: copies the contents of a single column into each column of the destination range. You can designate the destination columns by selecting the entire range or by selecting the top cell in each column. Rectangular range to rectangular range: copies the contents of a range into the destination range. You can designate the destination range by selecting the entire range or by selecting the upper left cell in the range. All the rules concerning copying that have been explained apply to any type of cell entry (label, number, formula). Copying formulas, however, can be a little more complicated than copying simple labels or numbers, because of the presence of cell references. A formula or function can have three different types of cell references: relative, absolute, and mixed. When formulas are copied, they will be affected according to the kind of reference they contain. When a formula or function containing a relative cell reference is copied, the copy will refer to different cells than the original. This result is due to the fact that relative references do not refer to fixed cell locations but instead refer to locations relative to the location of the formula or function containing the reference. Therefore, when you are copying or moving a range containing a formula or function, it is important to remember to include all cells that are referenced by that formula or function. Example: A10: @sum(A1..A9) /Copy From A10 To B20..C20 B20 will contain @sum(B11..B19) C20 @sum(C11..C19) When a formula or function containing an absolute cell reference is copied, the copy will refer to the exact same cell as the original. This result is due to the fact that absolute references always refer to a specific fixed cell location. When a formula or function containing a mixed cell reference is copied, the copy will refer to different rows or columns depending on the nature of the mixed reference. A10: @sum(A$1..A$9); /Copy Form A10 To B20..C20 B20 will contain @sum(B$1..B$9) C20 @sum(C$1..C$9) Move Command The Move command works very similarly to the Copy command. The difference is that the Move command will actually move the contents of the selected cells to the specified destination leaving the original cells empty. When you select the Move command, a prompt will appear in the edit line asking you to enter the range of cells you would like to move from. Enter the range and press Return or click OK. If you pre-select a range of cells before issuing the move command, the FROM prompt will not appear and the spreadsheet will assume that the pre-selected range of cells contains the entries which you would like to move. A second prompt will now appear asking you to enter the range you would like to move to. Enter the TO range and press Return or OK. The entries which were originally in the first range you entered will now be moved to the new range of cells. The move command can be issued as many times as necessary. If the destination range already contains entries, these entries will be overwritten by the new entries. Care must be taken in selecting a destination range for the Move command. If the destination range overlaps either the anchor or free cell of an existing range, the existing range will no longer be defined within the worksheet. Also, when you are moving cells which contain formulas, the complete formula will be moved, not just the value which appears in the cell. When moving formulas, the same rules apply to relative, mixed and absolute references as in the Copy command. FILE MANAGEMENT The File menu includes commands for saving and retrieving entire worksheets, selected portions of worksheets or combining information from different worksheets. There is even a command which allows you to import information from other programs. The following chapter takes you through the File menu and explains each command in the order in which it appears in the menu. File Selector Box Most of the commands in the File menu require you to specify a file from the files available on the disk. Therefore, when you select these commands, a file selector box will be displayed with a browser of current files saved under LDW POWER. You will be using these file selector boxes to select the files for various File commands. At the top of each file selector box is the directory path of the displayed files (e.g., A:\*.LDW). Directly below the directory path is a browser displaying all of the files available in the directory. The browser can show up to nine files on the screen at any one time. If the disk contains more than nine files, you can use the scroll bar to scroll through the available files. To the right of the browser you will see the word Selection: with the cursor positioned after the colon. The name of the file which you are going to be working with will appear below Selection:. Finally, there are two buttons at the bottom right of the file selector box. Select the OK button when you want to enter your file to the worksheet (the enlarged border around around the OK button signifies that it is the default button and can be automatically selected by pressing the Return key). The second button, Cancel, is used to return you to the worksheet and cancel any file commands. If you wish to select a file which already exists in the directory, click on the name of the file in the browser. It will appear in the file selection box below the word Selection:. If you wish to specify a file which does not exist in the direction, simply type in the name. To change the files displayed in the browser to those from another directory, move the cursor to the area below the directory indicator where the file extension is displayed and click the mouse. The cursor will appear at the end of the extension. Backspace to delete the existing extension and/or directory, and enter the extension and/or directory you would like displayed. Click anywhere within the browser, and it will be adjusted to display a listing of all files saved with the selected extension in the selected directory. When LDW POWER is loaded into memory, it sets the default directory. All default settings for the worksheet are stored in a default file called LDW.CNF. When the program is loaded, it automatically looks in this default file to get the settings for the session. You can designate a different directory as the default by using the Worksheet Global Default Directory command to change the directory and the Worksheet Global Default Update command to save the change. The File Directory command and the file selector box can be used to change the directory during the current session. Any directory changes made with the File Directory command are cancelled, however, once the current session ends. File Retrieve The File Retrieve command is used to call up previously saved worksheets to memory. When you select this command, the file selector box will appear on the screen. Enter the name of the file you would like to retrieve and press Return. The file you select will become the active worksheet. When issuing the File Retrieve command, it is important to remember that once you select OK, the previously active worksheet will be removed from memory, and the worksheet you are selecting will appear. Because of this fact, it is important to remember to save the active worksheet before issuing the Retrieve command. Automatic-Load Worksheet If you have a worksheet which you constantly use, LDW POWER provides you with a file name which allows you to automatically load that worksheet every time you load the program. This is the auto-load worksheet. If you would like to designate a specific worksheet as the auto-load worksheet, save it to the default directory with the file name AUTO.LDW. Every time you load the spreadsheet, the file named AUTO.LDW will automatically be loaded. You may only have one AUTO.LDW file for each directory. If you want to run a different auto-load worksheet than the one in the current default directory, you must use the Worksheet Global Default Directory command to change the default directory and the Worksheet Global Default Update command to save the change. When you load the spreadsheet, the new auto-load worksheet from the new default directory will automatically be called up. File Save The File Save command allows you to move the active worksheet from RAM (random access memory) to permanent storage on a disk. When you select the Save command, the file selector box will appear on the screen, and the file browser will contain a listing of all of the currently saved files. SAVING WITH A NEW NAME When saving a worksheet for the first time, you will have to enter a new file name. If you enter an existing file name, the worksheet you are saving will overwrite the existing file. Type in the name below the word Selection: If you don't add an extension, the extension LDW will automatically be added. If you enter an extension other than LDW, the file will appear in the browser for the corresponding extension. When you have entered the name (which can be no more than eight characters long), click on the OK button to confirm it. SAVING WITH AN EXISTING NAME LDW POWER also allows you to re-save previously saved worksheets after you have updated them. To save an existing worksheet, move the cursor to the name of the worksheet in the browser, click and then select OK. Because you are overwriting an existing file, a protection message will appear warning you that the existing file will be overwritten. When you select OK to enter the file name, the message box appears with two options: Cancel and Replace. When you click Replace, the active worksheet will overwrite the existing file. When you click Cancel, you will return to the active worksheet without having saved it. NAMING FILES There are a few restrictions which must be noted regarding the names you choose for your files. As mentioned before, the name you choose can be no more than eight characters long. You can also specify a three character file name extension. If you do not, LDW POWER will automatically add the LDW extension. If you create your own extension the file name will not appear in the browser under the *.LDW directory. To view the file, you must change the browser to the appropriate extension. Also, when you create your own extension, you must type in the extension along with the file name every time you want to re-save the worksheet. File names can include all letters of the alphabet (upper and lower case), the numbers 0 through 9, and the underline character. DISK-FULL ERROR When you are saving your worksheets to a floppy disk, you will occasionally try to save to a disk which is full. When this happens, LDW POWER will beep, and an error message will appear telling you that the disk is full. There are several things you can do in this situation. One way to get around this problem is to remove the full disk from the drive and replace it with a disk which has space. Then, you can save the worksheet to the new disk. A second solution is to use the File Erase command to erase unnecessary files from the disk in order to free up some disk space. If a single diskette does not have the memory capacity to save the entire worksheet, you can use the File eXtract command (which will be explained further in this chapter) to save different parts of the worksheet on two different disks. You can then use the File Combine command (which will also be explained) to combine the sections at a later time. File Password LDW POWER provides you with a feature which allows you to protect your worksheets, so that they can only be retrieved by you or someone authorized to open your worksheets. The Password option in the File menu provides you with this protection by allowing you to assign a password to your worksheets before you save them. LDW POWER will not allow you to retrieve a protected file, until the correct password is given. To assign a password to a worksheet, select the File Password command from the File menu. A message will appear in the edit line asking you to enter a password. Type in your password (which can be up to 15 characters long) and press Return (or click OK). When the password is entered, a second message will appear in the edit line asking you to verify the password. Re-type your password exactly as you entered it the first time and press Return. If the passwords match, protection for the worksheet will be turned on, otherwise an error message will appear telling you that the passwords do not match. You must then re-issue the Password command to assign a password the the worksheet. Note, that when you are entering a password, the letters do not appear on the screen. Instead, block characters appear. This feature is used to prevent other people from being able to retrieve your files after accidentally seeing your password on the screen. Also, note that passwords do not distinguish between upper and lower case letters. Once a password has been assigned to a worksheet, the Password option will have a check mark next to it to indicate that the protection is on. If you change your mind and want to turn the Password feature off, simply re-select the option. To permanently turn on the protection for the worksheet, you must save the protected worksheet to the disk. When the protected worksheet has been saved to the disk, the password must be given in order to retrieve the file. Note, that once a password has been permanently assigned to a worksheet, that password protection can never be removed. It is, therefore, very important that you do not forget the password, or you will not be able to access your worksheet. When you attempt to retrieve a file which has been saved with a password, a message will appear in the edit line asking you to enter the password. If you enter the correct password, the file you are retrieving will be called to memory, otherwise a message will appear telling you it is a bad password. You must then either issue a correct password or press Esc to return to the worksheet you were working on before issuing the Retrieve command. File Combine The File Combine command allows you to combine the contents of selected files into the active worksheet. You can also use the Combine command to transfer information from one worksheet to another. To invoke this command, select Combine from the File menu. A horizontal menu with three options will appear: Copy, Add, and Subtract. These options determine the way the selected worksheet files will interact with the active worksheet. Select the command which will combine the worksheet files in the manner which best fits your needs (refer to the following pages for a detailed description of each of these three options). Once you select an option, a second horizontal menu will appear. This menu will contain two items: Entire-File and Named/Specified-Range. These options are used to tell LDW POWER what portion of the worksheet files should be combined with the active worksheet. Regardless of which option you choose, LDW POWER will combine the contents of the worksheet file with the active worksheet beginning at the current cell. ENTIRE-FILE The Entire-File options tells LDW POWER to combine the contents of the entire worksheet file with the active worksheet. When you select this option, the file selector box will appear on the screen. Select the worksheet file you would like to copy and click OK. The worksheet file you selected will be combined with the active worksheet. NAMED/SPECIFIED-RANGE The Named/Specified-Range option tells LDW POWER to combine the contents of a specified range of the worksheet file with the contents of the active worksheet. When you select this command, a message will appear in the edit line asking you to enter the range name or range coordinates you would like to select from the file. The range you enter can be as large or small as you choose. Enter the range and press Return or click OK. After the range is entered, the file selector box will appear on the screen. Select the worksheet file you would like to copy and click OK (clicking on Cancel will return you to the second horizontal option menu). COPY The File Combine Copy command will copy the contents of a worksheet file into the cells of the active worksheet. This option is particularly helpful in transferring information from one file to another. If the cells of the active worksheet contain any information, the information in the worksheet file cells will overwrite the existing information contained in the active worksheet cells of the same address. If the worksheet file contains blank cells, the Copy option will not overwrite existing information in the corresponding cells of the active worksheet. The Copy option will adjust the cell references for formulas which are copied into a file to reflect the new location of the formulas. Even formulas containing absolute references will be adjusted (the absolute references will be treated as relative references). ADD The Add option allows you to add the values contained in the worksheet file with the values contained in the corresponding cells of the active worksheet. This option can be helpful for summarizing the figures on a number of different worksheets. The Add option is different than the Copy option in that it doesn't copy the contents of a worksheet file directly into the cells of the active worksheet. Instead, the Add option will add together values. The Add option will treat values and labels differently. Labels whicha re contained in the active worksheet when the Add option is invoked will not be affected by the labels, values or formulas in the worksheet file. The Add option will not combine labels from the worksheet file into the active worksheet even if there are blank cells in the active worksheet. Any type of entry which returns a value in the worksheet file, however, will be added to values in corresponding cells in the active worksheet. When the worksheet file contains functions or formulas, the Add option will take the value returned by the function or formula and place it in an existing blank cell of the active worksheet or add it to an existing value in the active worksheet. If the cell of the active worksheet already contains a formula, that formula will not be affected by the Add command. If, however, cells within the active worksheet which are referred to by a formula or function are altered by the Add option, then that formula or function will be affected. The manner in which the Add option deals with formulas and labels requires that the two worksheets have very similar layouts. If the layouts differ, the results may not be the ones expected. When using the Add option, it is often a good idea to have a "Standard" worksheet into which the files you want to add can be imported. A standard worksheet is a worksheet in which the layout is pre-designed to fit the information which is to be imported into it. When you have a standard worksheet, you don't run the risk of damaging the information in any of your worksheets, and the layout is pre- set to avoid adding formulas with labels, etc. SUBTRACT The Subtract option is very similar to the Add option. The only difference is that the Subtract option subtracts the values contained in the cells of the worksheet file from the values contained in the active worksheet. The manner in which the Subtract option processes the two worksheets is identical to the Add option. Refer to the Add option section of this manual to see how the Subtract option works. File eXtract The File eXtract command allows you to extract a range from any worksheet and save it into a separate file. The range can be as small as a single cell or as large as the entire worksheet. You can use this command to break up files which are too large, or to separate information which you would like to combine with other files. When you select the eXtract command, a horizontal menu with two options will appear. These options are Formulas or Values. The option you choose will determine the manner in which LDW POWER treats the formulas in your worksheets. See the sections of this chapter titled Formulas and Values for an explanation of how these options work. Once you select an option, the file selector box will appear for you to enter the name of the file under which you would like to save the extract range. The file selector box will contain a listing of the existing files. Type in the file name to create a new file or select an existing one. If you select a file which already exists, the information you are extracting and saving to the disk will overwrite the existing information. Click on OK to confirm the file name. Click on Cancel to return to the horizontal menu. After you enter a file name, a message will appear in the edit line asking you to enter the range you would like to extract. Enter the appropriate cell addresses or range name. Once you press Return (or click OK), the range you selected will be saved into the specified file. Note, that the extracted file will not save any information concerning graphs or windows from the original worksheet. FORMULAS Selecting Formulas from the menu will cause LDW POWER to save the formulas in the specified range as actual entries and not simply the values returned by those formulas. There is one thing to be aware of when saving the formulas. If all of the cells which are referenced by the formulas are not included in the specified range, the results returned by the formulas will be different than the original results. When you combine an extracted range (with such external referencing formulas), LDW POWER will adjust the formulas to calculate according to their present position in the combined worksheet. VALUES Selecting the Values option will save the current results returned by the formulas in the extract range rather than the actual formulas themselves. There are a couple of important points to keep in mind when using the Values option with the eXtract command. The first point is that there is no connection between the extract file and the original worksheet after the command has been issued. Therefore, the values that are saved where a formulas had been in the original worksheet will not be affected if a change is made to the formula in the original worksheet. The second point is that LDW POWER does not recalculate the worksheet before the contents of the extract range are saved into the extract file. If the worksheet has not been recalculated, the range saved may not contain the most current values. File Erase The File Erase menu contains commands which allow you to erase unwanted files from your disk. When you select the Erase menu, a horizontal menu with the following options will appear: Worksheet, Print, Graph, and Other. The item you select will determine the type of file which is erased. The Worksheet option will erase worksheet files with an LDW extension. The Print option will erase print files with an LDP extension, and the Graph option will erase graph files with a GEM extension. When you select any of these three items, a file selector box containing a list of all the files saved under the designated extension will be displayed (i.e., if you select Print, the file selector box will display all files with an LDP extension). The Other option will display a file selector box containing all files saved with all extensions. You can then scroll through the dialog box displayed by the option you selected, choose a file, and click OK to enter the file to be erased. After you have selected a file, a message box will appear with Yes/No choices; giving you a chance to change your mind about erasing the file. Select yes to erase the file, No to return to the current worksheet. File List The File List menu allows you to view a list of all of the files you have saved. When you select the File List menu, a horizontal menu will appear with the following options: Worksheet, Print, Graph, and Other. When you select the Worksheet option, a listing of all worksheet files saved with a LDW extension will be displayed. When you select the Print option, a listing of all print files saved with an LDP extension will be displayed. When you select the Graph option, a listing of all graph files saved with a GEM extension will be displayed. Finally, when you select the Other option, a listing of all files that have been saved with any extension will be displayed. With LDW POWER, you can use the mouse or cursor keys to highlight the different entries in the listing. At the top of the listing, the date and time the highlighted file was last saved, along with the size of the file will be displayed. File Import The File Import command is used to bring the contents of an ASCII file into a worksheet. This command allows you to import files which contain both text and values into a worksheet. You can then use LDW POWER to format, calculate, and manipulate the information from those imported files. When you select the File Import command, a horizontal menu will appear with the following options: Text and Numbers. These items will tell LDW POWER how to import the files you select. After you have selected either the Text or Numbers option, the file selector box, displaying a browser of all files saved with an LDP extension, will appear. Select the file that you want to import and click OK. After you click OK, the file selector box will disappear and the import file will be brought into the active worksheet at the location of the cell pointer. One important fact to remember is that the File Import command does not erase the contents of the entire current worksheet. Instead, the imported ASCII file will be brought in and inserted in your active worksheet. To be certain not to overwrite any existing information in the active worksheet, you should make sure to have plenty of blank cells below and to the right of the cell pointer when importing a file. The File Import command is often used with a blank worksheet in order to avoid any overwriting errors. TEXT If you select Text, the file you select will be imported as a series of long labels. Each individual line of the file you are importing will be brought into a single cell as a long label. The entire file will be inserted in the column of the worksheet where the cell pointer is positioned. When you import information with the Text option, the spaces that were in the original ASCII file will remain. This will make it appear as if there are many entries in different cells. Moving the cell pointer around the worksheet will prove otherwise. The long label is contained in a single cell. To split up the long labels brought into the active worksheet, you can use the Parse command in the Data menu (see the Data Parse command for a full explanation on how to break up the imported text). NUMBERS When you use the File Import Numbers command, the data in the import file will automatically be divided into values and labels and entered into separate cells of the active worksheet. All numbers in the import file will be brought into the worksheet as values. All text that is enclosed in quotation marks will be imported as labels. Any text which is not enclosed in quotation marks will not be imported. Successive numbers and labels from the same line of the import file are placed in successive columns of the same row in the active worksheet. Entries from the next line are placed in the next row of the active worksheet, and so on. When importing an ASCII file using the Numbers option, make sure that the numbers in the import file are not formatted strangely. If they are formatted in a way that LDW POWER cannot recognize, they may be altered unexpectedly during importing. LDW POWER will recognize decimal points and percentage signs and will not alter the numbers when it comes across these characters. File Directory The File Directory command and the file sector box allow you to change the directory for the current session with LDW POWER. When you select the File Directory command, a message will appear in the edit line asking you to enter the directory you would like as the default. Enter the directory and press Return or click OK. If you enter a directory which does not exist, an error message will appear on the screen. Note, that any changes made for the directory will only be apparent during the current session. The next time LDW POWER is loaded, the default directory that will be called up will be the default directory that has been defined under the Worksheet Global Default Directory command and saved under the Worksheet Global Default Update command into the LDW.CNF default file. PRINT MENU This chapter covers some subjects you will need to be familiar with in order to print your worksheet, including printer set-up, standard print settings and the Print commands. You should familiarize yourself with these features whether you plan to print your worksheet to the printer or to the file. You can then use them to format and arrange your printouts to fit your specific needs. In the most basic terms, printing a document involves: selecting the Print Range command; selecting the range of cells in the worksheet to be printed; and selecting the Go option. The Print menu contains the commands you will use to print your documents, whether you print them to a printer or to a file. The commands are identical for both situations. The only difference is that the document printed to the printer will end up as a hard copy printout, while the document printed to the file will end up an ASCII file in your directory. This ASCII file can then be read by word processors or other applications which read ASCII files. Since the commands for printing to a printer and printing to a file are the same, they will only be explained once. Keep in mind then, that the following descriptions of the Print commands can apply to either Printer or File. Print Range The Print Range command will specify the range of cells you would like printed. This range must always be specified when a document is printed, or else an error message will appear when you attempt to print. LDW POWER will remember the previous print range used in the worksheet and will treat it as the default range for subsequent printings. You can simply accept this range or select a new one. When you save the worksheet, the most recent print range will be saved with it and will be treated as the default at the beginning of the next session. When you select the Print Range command, a message will appear in the edit line asking you to enter a print range. You can select the range before you issue the Print Range command, or you can enter the range in the edit line by pressing the Esc key and selecting or entering the range you would like printed. Press Return or click OK. Remember, that selecting the Shift and Home keys will bring the cell pointer to the last cell in the worksheet which contains an entry. If you select the anchor cell and use the Shift/Home combination, all cells which might contain an entry will be highlighted. Print Line The Print Line command will send a single line feed to the printer causing the paper in the printer to advance one line. Print Page The Print Page command will scroll the paper in the printer one full page. If the paper is aligned with the top of the page, it will be scrolled to the top of the next page. Print Options The Print Options menu contains numerous commands which allow you to format your printouts. These commands allow you to change the margins of a report, create a page header and footer, adjust the page length, and format the printout in other ways. PRINT OPTIONS HEADER/FOOTER The Print Options Header command allows you to create a header at the top of every page of your report, while the Print Options Footer command allows you to create a footer at the bottom of every page. Headers and footers are lines of text which can be used to describe a particular document (i.e., a title), to number pages, or to give the date of the document. To create a header or footer in your document, select the appropriate Print Option command, and type in the header or footer for your printout. Press Return to confirm the header or footer. Headers and footers can be up to 240 characters long; however, the setting of your left and right margins will determine the actual number of characters which can be printed. Refer to the Print Options Margin Left and Print Options Margins Right commands for more details. LDW POWER will always reserve three lines at the top and bottom of a page (in addition to the top and bottom margins) for headers and footers whether they are used or not. When you issue a page header, it will appear at the top line of the page with two blank lines below it. When you issue a page footer, it will appear on the bottom line of the page with two blank lines above it. Aligning Headers and Footers The default alignment for headers and footers is left-justified, but LDW POWER provides you with a special character (|) which allows you to control the alignment of your headers and footers. You can use this special character on your headers or footers to break them up, to center them, or to right-justify them. A header or footer can be broken up into two or three separate sections. If you want three sections, simply insert the special character (|) between the parts that you want separated. LDW POWER will left-justify the first section, center the second, and right justify the third. You can also split headings or footers into two parts. If, for example, you would like to have a footer with the page number on the left side and the company name on the right side, enter the footer #||LDW next to the footer prompt in the edit line. In this example the page number will be left-justified, and the company name will be right-justified. Page Number and Dates LDW POWER allows you to sequentially number your pages and include the current TOS date in your page headers and footers. Enter a (#) character in the edit line to instruct LDW POWER to automatically number your pages or an (@) character to instruct LDW POWER to automatically date your pages. Deleting Page Headers and Footers To delete either page headers or footers, re-issue the appropriate command. When the prompt appears in the edit line, press Esc, and then press Return, and the page header or footer will be deleted. (End of Part 2... Now load Part 3!) Part 3 of the LDW Power Spreadsheet Docs..... PRINT OPTIONS MARGINS The Print Options Margins menu contains four commands which allow you to adjust the margins on your printout. These commands are: Left, Right, Top, and Bottom. Print Options Margins Left/Right The Print Options Margins Left and Print Options Margins Right commands allow you to specify the character width of the left and right margins. Select either Left or Right, and a message will appear in the edit line asking you to enter the width of the margin. You may enter a number from 0 (to start printing at the very edge of the paper) to 240 (for 240 blank spaces). If you enter a number over 240, you will get an error message. Type in the number and press Return. Unlike the Print Options Margins Left command which tells LDW POWER how far from the left edge of the paper the text should begin, the Print Options Margins Right command tells LDW POWER how many characters a line of text can have. For example, if you enter the number 80 for the right margin and the number 4 for the left margin, no more than 76 characters will be printed on any line. The right margin can be any number between 0 and 240, but it should never be smaller than the left margin setting, or you will not be able to print anything. You must also make sure that the right margin will fit into the boundaries of the paper you are using and the set-up string you have specified. Print Options Margins Top The Print Options Margins Top command allows you to specify how far down from the top of the paper you would like the printer to start printing. Select Top, and a message will appear in the edit line asking you to enter the number of blank lines from the top of the page LDW POWER should leave. Enter a number between 0 and 32. If you enter a number over 32, an error message will appear. LDW POWER will automatically leave three blank lines at the top of a page for the page header. Therefore, any blank lines you indicate are in addition to these three. Print Options Margins Bottom The Print Options Margins Bottom command allows you to specify how far up from the bottom of the page you would like the printer to stop printing. Select Bottom and a message will appear in the edit line asking you to enter the number of blank lines from the bottom of the page LDW POWER should leave. Enter a number between 0 and 32. If you enter a number over 32, an error message will appear. LDW POWER will automatically leave three blank lines at the bottom of a page for the page footer. Therefore, any blank lines you indicate are in addition to these three. PRINT OPTIONS BORDERS The commands in the Print Options Borders menu allow you to include a few designated rows and columns in every page of your printout. These commands are very useful for worksheets which have row or column headers, because they allow you to print those headers on every page. The Print Options Borders menu contains two commands: Columns and Rows. To create either column or row borders, you must select the appropriate command and enter the appropriate border range at the prompt. Column ranges must contain at least one cell in each of the columns you would like to print as a border, and Row ranges must contain at least one cell in each of the rows you would like to print as a border. Once you define the columns or rows to be printed on each page, you must adjust the print range to exclude these rows and columns. If you do not adjust the print range, the rows and columns you specified as borders will be printed twice. Clearing Borders To clear borders previously set with the Print Options Borders command, select the Border command from the Print Clear menu. PRINT OPTIONS SETUP Setup strings are special character sequences which tell your printer how to print characters in your document. Using setup strings, found in your printer owner's manual, you can instruct your printer to print LDW POWER worksheets with different character or line spacing or with special printing effects (such as bold type). The default setup string is a blank string. That means that the default setting for your printer will determine the default printing format. If you specify a setup string for a worksheet and save it with the worksheet, that setup string will become the default for that particular worksheet. A setup string must conform to the following format: \### (a backslash immediately followed by three single digit numbers). Here's an example: Suppose that you want to print your document with 1/8" line spacing, and your printer's special code for this spacing is [ESC] 0. In order to tell LDW POWER to send this message to the printer, you must translate it into the proper setup string (\###). First, you have to convert the [ESC] into ASCII code (ESC is represented as 027 in ASCII). You can either convert the 0 to ASCII or leave it as it is. The LDW POWER setup string then, would end up as: \0270 or \027\048. It's important to remember that the backslash must immediately be followed by three single digit numbers. Once you have entered a proper setup string, you will be prompted for character sequences that will be sent to the printer whenever bold or underlined text is printed. These sequences should contain printer's codes (e.g., \027G) for bold and underlines styles, and should follow the same form as the setup string explained above. If you do not wish to change the default forms for these sequences, press the Return key. PRINT OPTIONS PG-LENGTH The Print Options Pg-Length command allows you to change the page length setting from its default of 66 lines. You can use this command to change the page length from 1 line per page up to 100. Note, that the lines per page spoken of here refer to the total number of printable lines on the page and not to the number of lines actually printed. Therefore, you may have a page that has a capacity of 66 printable lines with only 60 actual printed lines. When using the Print Options Pg-Length command, keep the total number of printable lines per page in mind. When you select this command, a message will appear in the edit line asking you to enter the default lines per page. Enter a number between 10 and 100. If you enter a number less than 10 or a number greater than 100, you will get an error message. The Print Options Pg-Length command will change the page length for the current session only. If you want to change the default page length, issue the Worksheet Global Default Pg-Length command. If you save the page length setting with the worksheet (using the Worksheet Global Default Update command), that setting will become the default for that worksheet. PRINT OPTIONS OTHER The Print Options Other menu contains commands which allow you to determine the way LDW POWER will print the contents of the worksheet. These commands are: As-Displayed, Cell-Formula, Formatted, Unformatted. Print Options Other As-Displayed The Print Options Other As-Displayed command will print the contents of the worksheet exactly as they appear on the screen. Print Options Other Cell-Formula The Print Options Other Cell-Formula command will print a listing of the contents of the cells in the worksheet. The actual formulas contained in the cells will be printed rather than the results returned by those formulas. Print Options Other Unformatted The Print Options Other Unformatted command allows you to print out an unformatted report. This command tells LDW POWER to ignore any page formats when it prints out your document at the top line of the page and continue without page breaks until the end of the page. Print Options Other Formatted The Print Options Other Formatted command re-enables the formatting for the page. PRINT OPTIONS QUIT Select the Print Options Quit command to return from the Print Options menu to the Print menu. Print Clear The Print Clear menu contains commands which will reset some or all of the print settings which have been defined with other commands from the Printer menu. The commands under the Print Clear menu are: All, Range, Borders, and Format. PRINT CLEAR ALL The Print Clear All command will return all of the Print settings to their default settings. When you select this command, any headers, footers, or borders will be deleted, and margins, page length settings and setup strings will be returned to their defaults. PRINT CLEAR RANGE Select Print Clear Range to reset the print range. Remember, that LDW POWER remembers the most previous print range and will treat it as the default range, until it is changed or cleared. PRINT CLEAR BORDERS Select Print Clear Borders to delete all border settings which have been designated. When you clear the borders, remember to adjust the print range to include the rows and columns which had been designated as borders. PRINT CLEAR FORMAT Selecting the Print Clear Format command will reset the margins, page length, and setup string settings. Print Align The Print Align command is used to tell LDW POWER that the paper in the printer is positioned at the top of the page. You will need to issue this command every time you move or reset the paper in the printer. Set the paper in the printer to the top of the page (or wherever you would like to begin printing). When the paper is correctly aligned, select the Print Align command. Print Go Selecting the Print Go command will cause LDW POWER to send the print file to a buffer in memory. When you select the Go option, it will appear that nothing is happening. This is not the case. The print file is simply waiting in the buffer to be printed. When you quit from the Print menu (by selecting the Print Quit command or by pressing the Esc key), the actual printing will occur. Note, that a separate buffer file will be created each time the Go option is selected. Therefore, if you select Go three times, your document will be printed three times. Print Quit Selecting the Print Quit command will cause LDW POWER to move the print file from the buffer to the printer, close the print file, and return the worksheet to the READY mode. Print File As was mentioned before, printing to the printer and printing to the file are similar situations. There are some notable points to keep in mind however, when printing the file. The Print File command allows you to print a worksheet, or a portion of it, into an ASCII text file. The ASCII file you create can then be sued by word processors or other programs that read text files. When you use the Print File command, each page (including margins, headers, footer, and borders) will be saved in the ASCII file in the form of text line separated by line feed or carriage return characters. When you select the Print File command, a file selector box will appear. Type in the name of the file you would like saved as an ASCII file (or select an existing name). If you do not specify an extension, LDW POWER will automatically add an LDP extension. Confirm the name by clicking on the OK box or pressing Return. GRAPHS LDW POWER provides you with graphic capabilities which make it easy for you to create graphs from the data in your worksheet. In a few simple steps, you can have beautifully formatted graphic representations of any numerical data entered in your worksheet. Some of the important features which LDW POWER provides include: . Five different graphic representations: Line graphs, Bar graphs, XY graphs, Stacked bar graphs and Pie charts. . Multiple data sets: You can display up to six different data sets on a graph with each set differentiated by color, pattern or symbol. . Graph labeling: LDW POWER offers several title options which allow you to label axes and points on the axes; label data points; and create legends. . Automatic scaling: LDW POWER lets you "fit" your data into any graph by controlling the numerical distances along the axis. . Immediate access: The graphs you create can be viewed instantly from the READY mode in the worksheet simply by pressing the F10 [graph] key. You can also update an existing graph simply by clicking on the graph window. . Saving with the worksheet: LDW POWER allows you to save your graphs to the worksheet thus enabling you to create different graphs that can be compared and enhanced at any time. . Graph printing: You can print graphs directly from the worksheet, and do not need a separate graph printing program. Although the different types of graphs often require different kinds of data, there is some information which is essential to every graph. The following is a brief explanation of the information which is necessary to create a basic graph: 1. The type of graph you are creating is an essential element. Use the Graph Type menu to create one of the five graphs available with LDW POWER. 2. The values to be graphed must be specified using a given range in the worksheet. Use the data range commands from the graph menu to enter the range(s) of numbers to be used in your graphs. Remember, that the range specified from the worksheet relates tot he cell addresses and not the current contents of the cells. Therefore, if you were to change the contents of the cells in the specified range, the corresponding graph would change as well. 3. Viewing the graph is done by selecting the Graph View command or by pressing the F10 [Graph] key while in the ready mode in the worksheet. When Graph View is selected, a separate window containing the graph will be displayed on top of the worksheet. To return to the worksheet from a graph, press any key. 4. You can save a graph with the worksheet in one of two ways. If yuo have only one graph, it will automatically be saved when you save the worksheet. If you wish to save more than one graph, you must use the Graph Name Create command to name each graph. When you save the worksheet, the graphs will be saved with it. The final portion of this chapter takes you through the Graph menu and explains each command in the order that it appears in the menu. Graph Type When creating a graph, the most basic decision you must make is deciding what king of graph you want to display. Different graphs will emphasize different aspects of your data, so you want to be sure to choose the appropriate type for your needs. You can use the commands in the Graph Type menu to select a suitable graph. When you select Graph Type, a horizontal menu with five commands will appear. These commands are: Line, Bar, XY, Stacked-Bar, and Pie. Select the type of graph you would like to create. Remember, that you can switch the type of graph you are creating at any time by selecting a different Graph Type command. You must make sure, however, that the data entered must be appropriate for the type of graph you select. The Line graph is set as the default graph type and will display each data item in the specified range as a symbol. A line connects each symbol in the range. When your graph contains more than one range, each range is displayed as a different symbol. If you have a color monitor, the lines and symbols for each range will be displayed in different colors. The Bar graph displays given values in the specified range as individual bars on the graph. This type of graph is usually used to compare values at a given point in time. When more than one data range is specified, the bars for each range will appear side by side. If you have a color monitor, the bars will appear in different colors. If you have a monochrome monitor, the bars will be differentiated with different patterns. There are six available data ranges in LDW POWER, and each range is assigned a different pattern. The XY graph is dependent on two data ranges. Unlike the other four graphic displays available with LDW POWER, the XY graph needs at least two ranges specified by the user, a range for the X-axis and a range for the Y-axis, to determine where the points on the graph will be plotted. In this way, XY graphs allow you to plot the relationship between two related values. The X-range, which is the range of values assigned to the bottom or X-axis, will remain constant in all graphs until it is changed with the X-range command. The X-axis determines the horizontal position of each point. There are six available data ranges for the Y-axis. Therefore, you can have up to six differing data points on any given XY graph. The Y-range determines the values plotted for the Y-axis or the vertical positions of the points. The XY graph is displayed in the same manner as a line graph, with different points (symbols) plotted on the graph connected with a line. The Stacked-Bar graph displays different data sets as bars that are placed one on top of the other. Each bar is represented by a different pattern. The size of the individual stack is dependent of the number and values of the other bars in the stack. LDW POWER places the bars in alphabetical data range order. In other words, the A-range will be placed on the bottom, followed by the B-range, the C-range, etc. If you plot all negative numbers on a stacked bar graph, the graph will be displayed upside down. You cannot combine positive and negative numbers in a stacked-bar graph. The Pie chart displays a round graph which can accommodate only one data range (the A-range). Pie charts will display all of the values in a given range as proportional segments of the circle. The top or left most segment corresponds to the first value in a range and proceeds counter-clockwise around the circle. A legend, indicating the percentages of each segment, will automatically be displayed along with the chart. Note, that pie graphs will not accept negative numbers. Ranges The next seven items in the Graph menu are commands which name the ranges of data to be plotted on the graph. These commands are: X-range, A--range, B- range, C-range, D-range, E-range, and F-range. To name a range, select the appropriate command. A message will appear in the edit line asking you to "enter data range". Select the appropriate range and press Return. The range of cells you selected will now be assigned to the appropriate data range. The current cell values will appear in the graph when you view it. Note, that if you change the values in the cells, the graph will change as well. The Graph X-range command has two different purposes, depending on the graph that you are creating. When you are creating an XY graph, the X-range is used to represent a series of values which will be plotted along the horizontal axis, but you can also use the X-range to label graphs. When the X-range is specified for line, bar, and stacked-bar graphs, it is used to label the X-axis. When the X-range is used with pie graphs, it is used to label the segments of the circle. The range entered for the X-range in this instance may be either values or labels. The remaining range commands, A-range through F-range, allow you to enter up to six data sets to be plotted on your graph. Each range corresponds to a different symbol or pattern to be displayed on your graph. Graph Reset The Graph Reset menu contains commands which allow you to reset the current data ranges. If you are creating numerous graphs with different ranges, it is a wise idea to save your current graph before using any of the Reset commands. There are nine commands under the Reset menu: Graph, X, A, B, C, D, E, F, and Quit. The Graph command allows you to cancel all of the current data ranges. The next seven commands: X-range, A-range, B-range, C-range, D- range, E-range, and F-range, allow you to cancel a specific data range. Select the appropriate command and the specified data range(s) will be cancelled. After you issue a command under Reset, you will remain in the Reset menu. Select the Quit command to exit to the worksheet. Graph View Selecting the Graph View command will display the current graph with all of the settings you have chosen. To quickly display a graph at any time from the worksheet, hit the F10 [Graph] key on your keyboard. The graph or pie chart displaying the current settings will appear. Graph Save The Graph Save menu allows you to manipulate the current graph and save it into a graph file or send it directly to the printer. GRAPH SAVE METAFILE Select Metafile to save your graph to a metafile. A metafile is a special file format in GEM which can save the graph and allow it to be ready by another application. With the metafile, you can transfer graphs created in LDW POWER to true graphics applications. You will need to install GDOS into your operating system as well as the appropriate metafile driver. GRAPH SAVE PRINTER You can use Graph Save Printer along with GDOS to print your graphs in a high-quality graphics mode. This feature is especially important when you're working with the Atari Laser Printer. Note, that you will need to install GDOS into your operating system along with the appropriate printer driver. GRAPH SAVE DUMP The Graph Save Dump command can be used to "dump" the graph, exactly as it looks on the screen, to the printer. The quality of the graphics, in this case, will be less refined due to the limitations in resolution on the screen. You can adjust the size of the printed graph by sizing the graph window on the screen. GRAPH SAVE X/Y FACTOR The X and Y Factor can be used to adjust the size of the printed graph in the high quality Printer mode. When Printer is used to print the graph, the printed graph will take up the entire page. If you want to shrink its size, use the X and Y scale factors to reduce the graph accordingly. GRAPH SAVE QUIT Use the Graph Save Quit command to return to the Graph menu. Graph Options The Graph Options menu contains a number of commands which allow you to enhance your graphs to make them as visually pleasing and understandable as possible. The commands are: Legend, Format, Titles, Grid, Scale, Color, B&W, Data-Labels, and Quit. GRAPH OPTIONS LEGEND Legends are used to explain what each range in the graph represents. Legends can be added to line, bar, stacked-bar, and XY graphs. Pie charts are the only type of graph for which you cannot create legends, because they can only have one range. Each legend will match its corresponding data range in either color, symbol, or pattern. To create a legend, select the Graph Options Legend menu. When it is selected, a horizontal menu with the Legend commands will appear. The items refer directly to the range you are naming and are labeled: A, B, C, D, E, and F. Once a command is selected, the following message will appear in the edit line: Enter legend. You can enter the legend name in one of two ways. Either type in the name directly, or type in a cell reference in order to use a label already in the worksheet. The cell reference must begin with a backslash (\) followed immediately by the cell address (i.e., \C4). Note that you can only use names that are no more than 15 characters long. The symbol or pattern that corresponds to the range you named will appear in the legend with the name next to it. You should continue to name all the ranges in your graph to create a complete legend. To remove a previously named legend, select the letter that corresponds to the legend you would like to remove and press the Esc key followed by the Return key. GRAPH OPTIONS FORMAT The Graph Options Format command is used to format the display of Line and XY graphs. LDW POWER gives you the choice of displaying symbols, lines, or both on the graph. Therefore, data points can be represented as symbols, as the intersection of connecting lines, or as both. To change the format of a Line or XY graph, select the Format command. A horizontal menu with the format commands will appear. The Graph option allows you to format all ranges in the graph. If you add ranges to the graph, they will appear in the format you set with the Graph command. The A through F options allow you to format a specified range in the graph. When you select these options, a second horizontal menu with four more options will appear. These options are: Lines, Symbols, Both, and Neither. The Graph Options Format Graph Lines command will display the specified range(s) as a series of data points connected by lines. The Graph Options Format Graph Symbols command will display the specified range(s) with a symbol positioned at each data point of the range. The Graph Options Format Graph Both command will display a graph of plotted symbols connected by lines. Finally, the Graph Options Format Graph Neither command is used when data labels are used in the graph. In this case, the data labels appear at the data points on the graph. Select the appropriate command to format your graph. You will then be returned to the previous format commands where you can select the Quit command to return to the Graph menu. GRAPH OPTIONS TITLES The Graph Options Titles menu allows you to add titles to your graph to help explain what the graph depicts. There are four titles which you can use: a title at the top of the graph, a sub-title below the title, and titles to name the X and Y axes. When you select the Graph Options Titles menu, a horizontal menu containing four commands will appear. These commands are: First, Second, X-Axis, and Y-Axis. The Graph Options Titles First command is used to create the overall graph title to be placed at the top of the graph. When you select the First option, a prompt will appear in the edit line asking you to enter the graph title. You can either type in the title of the graph directly, or type in a cell reference in order to use a label already in the worksheet. If you use the latter, you must begin the reference with a backslash (\) immediately followed by the cell address (i.e., \C4). Note, that in either case, the title can be no more than 39 characters long. The Graph Options Title Second command is used to create a sub-title for your graph. Select the Second option, and enter your sub-title or a cell reference next to the prompt in the edit line. The sub-title you entered will appear in the graph directly below the title. The X-Axis and Y-Axis commands are used to label the X and Y-axes. Select the appropriate command; enter the label or cell reference; and press Return. The Graph Options menu will re-appear on the screen. When you view your graph, the X-axis label will appear under the X-axis, while the Y-axis label (which can only be 32 characters long) will appear vertically next to the Y-axis. GRAPH OPTIONS GRID The Graph Options Grid menu allows you to create a grid of horizontal lines, vertical lines, or both across your graph. The Grid menu, a horizontal menu displaying the Grid options, will appear. Select Horizontal to create horizontal lines in your graph. Select Vertical to create vertical lines in your graph. The Both option will create a grid across your graph. The Clear command will delete any previous grid lines created in your graph, while the Average command will display average and standard deviation lines. GRAPH OPTIONS SCALE The Graph Options Scale menu is used to format the Y-axis of line, bar, XY, and stacked-bar graphs. It can also be used to format the X-axis of XY graphs. When you select this menu, a horizontal menu with three commands will be displayed. These commands are: Y-Scale, X-Scale, and Skip. The Y- Scale command allows you to format the Y-axis of all graphs except for pie charts. The X-Scale command allows you to format the X-axis on XY graphs. When you select the X-Scale or Y-Scale commands, a second horizontal menu appears, displaying: Automatic, Manual, Lower, Upper, Format, Indicator, and Quit. The Automatic command is the default and automatically sets the X and Y scales. The Manual command corresponds directly with the Lower and Upper commands. The Manual command turns off the automatic scaling and allows you to set the upper and lower limits of your Y-axis (or X-axis in the case of XY graphs). You must first select Manual and then use the Lower and Upper commands to specify the limits. Once you select Manual, you must select a lower and upper limit, or your graph may look very strange. There are a few basic guidelines that should be followed when setting upper and lower limits. The upper limit should not be smaller than the lower limit, otherwise the graph may not be displayed accurately. The lower limit can be a negative number to accommodate for any negative values in the graph. Both the upper and lower limits can be negative if all the values in the graph are negative. LDW POWER will use the upper limit you enter as a guideline and not as a strict limit, and may try to round up in order to create more even divisions on the Y-axis. FORMAT The Graph Options Scale Format menu allows you to format the numbers along the Y-axis (or X-axis). When you select this menu, a menu similar to the Range Format menu will appear. Choose the format you would like, enter the number or decimal places and press Return. You may notice that when you display your graph, that the numbers along the X and Y axes have been scaled down. This format is the default display and is used in order to save room and to make the axes look more uniform. LDW POWER will scale the values on the axis based on the highest value on the Y- axis is 1000, LDW POWER will adjust all the values on the Y-axis using 1000 as the base scale (i.e., 500 would be represented as 0.5). The scale indicator Thousands would be displayed along the Y-axis to indicate the scale for that axis. The Indicator option allows you to turn the scale indicators on or off. Select the Indicator command, and Select Yes to display the scale indicator or No to remove it. Selecting the Quit option from your X and Y Scale commands returns you to the Graph Options menu. SKIP The Skip option is very useful when your graph contains a lot of data points along the X-axis. When you are labeling the tick marks on the X-axis using the Graph X command, and you have a lot of data points, the labels may not fit. The Skip command allows you to label some of the tick marks while skipping others (in constant increments). To skip labels, select the Skip command and enter the number of points you would like to skip between each label. When you view the graph, the labels will appear spaced with the appropriate number of tick marks between each. GRAPH OPTIONS COLOR The Graph Options Color command allows you to display your graphs in color (if you have a color monitor). GRAPH OPTIONS B&W The Graph Options B&W command allows you to display your graphs in black and white. GRAPH OPTIONS DATA-LABELS Data labels are used to identify the data points plotted on a graph. They appear directly in the graph either on the points they identify or next to them. The labels which you will use as data labels in your graph should be entered in a range in your worksheet. Select the Graph Options Data-Labels menu to define your data labels. From the menu, select the command (A - F) which corresponds to the range you would like to label. A prompt will appear asking for the range in the worksheet containing the data labels you would like to use. Select the appropriate range and press Return or click OK. After you select the range of data labels, another menu containing format choices for those labels will appear. You can use these format commands to center the labels, align them to the left or right of, or align them above or below their respective data points. After you select the appropriate format, you will be returned to the A,...,F commands. you can continue to format ranges using the above steps, or you may select Quit to return to the Options menu. GRAPH OPTIONS QUIT The Graph Options Quit command will return you to the Graph menu. Graph Name LDW POWER allows you to create numerous graphs within a single worksheet, but only one graph can be active at a time (i.e., you can only open one graph window at a time). The Name menu contains commands which allow you to name, save and delete graphs. You can, therefore, access different pre- designed graphs at any time by making them active. When you invoke the Name menu, four more commands are displayed. They are: Use, Create, Delete, and Reset. The Graph Name Use command allows you to make a previously named graph active. When you select this command, a menu of all previously named graphs will appear. Move the cursor to the appropriate name and select it. The graph you selected will now be the active graph. Remember that once you make a graph active, any changes you make will affect that graph. You must save the active graph again to retain any changes that were made, while it was active. Remember, that if you issue the Use command while working on a graph that has not been saved, you will lose the graph that is presently active. To save a graph, you must use the Graph Name Create command to name it. When you select this command, a prompt will appear in the edit line asking you to name the active graph. Enter the name and press Return or select OK. The maximum length of the name you can give to your graph is 15 characters. When you save a graph, it is not saved to the disk until you save the worksheet. If you would like to remove a saved graph from the worksheet, invoke the Graph Name Delete command. When you select Delete, a menu with all of the saved graphs will appear. Select the name of the graph you would like to delete, and it will be removed from the worksheet. LDW POWER also provides the Reset command to delete all of the saved graphs from a worksheet. When you select Reset, all graphs in the worksheet except the active graph are immediately deleted. DATABASE MANAGEMENT Database management is one of the strongest capabilities of LDW POWER and can make it easy for you to work with data. The first step to understanding LDW POWER's database management capabilities is to know what a database is. A database is an organized grouping of information which allows you to search for, tabulate, compare, and alter pieces of information. The database in LDW POWER is fully integrated within the the worksheet, allowing you to recognize all the data contained in the worksheet. You may organize all types of information; including telephone numbers, important dates, employee records, sales, etc. LDW POWER's database management capabilities make it possible to quickly retrieve, manipulate and print out selected items from your database. The Basics Before getting into the specifics of database management with your LDW POWER Spreadsheet, it is important to understand some basic concepts for creating databases in your worksheets. . A database in your worksheet must contain at least one column and at least two rows. . Each column within the database contains fields. A field is a cell containing a single unit of information. . The first row of your database should contain field names. Field names can be thought of as column headers which describe the entries contained in the cells (fields) of the same column. . Each row of information (not including the field names) in the database is called a record. Each record of your database is made up of a series of associated fields, arranged consecutively in a single row. These fields all contain data which pertain to the item described by the record. A typical example is a record for an employee with fields describing his name, telephone number, position, and gross sales. Each record occupies a different row, and a database is arranged with its records placed in consecutive rows. Database entries are very similar to other entries in LDW POWER, in that you can enter labels, values, functions and formulas. When you are entering labels which begin with numbers, you should remember to include the label prefix. When you are using functions and formulas in your database, there are a few special rules you must follow. These will be explained later in the chapter. Manipulating the information in your database is just as simple as manipulating any worksheet entries. Editing, formatting, copying, moving and printing the entries in your database are done in the exact same way as it is with any other entry. With LDW POWER, you can quickly locate items in your database, create powerful comparison tables, and much more. The commands to do this are found in the Data menu. The following section of your manual takes you through the Data menu, and explains each of these commands. Data Query The Data Query menu has a number of commands which allow you to locate, extract, and delete records which meet certain criteria. There are four Query commands available: Find, Extract, Unique, and Delete. The Find command will find all records in the database which match the specified criteria and highlight these records. The Extract command will copy the records which meet certain criteria to another portion of the worksheet. The Unique command will copy records which match the specified criteria, but will also filter the records to make sure no two records are duplicated. Finally, the Delete command will delete any records which meet the specified criteria. When you select the Data Query menu, a horizontal menu with all of the available Query commands will appear. The first thing you will need to do to perform a Query is to specify two to three ranges: the Input range, the Criterion range, and the optional Output range. DATA QUERY INPUT The Input range should include every cell in the database which will be considered in the Query, including the field names. When you select Input, a message will appear in the edit line asking you to enter the Input range. DATA QUERY CRITERION The Criterion range specifies certain conditions which tell LDW POWER which records to search for. You must have at least two rows in your Criterion range. The first row consists of one or more field names, all subsequent rows will contain the criteria. The field names in your Criterion range ill tell LDW POWER which fields to look under when testing for the criteria specified. The Query command will look under the specified fields for items which match the criteria specified in the subsequent rows of the Criterion range. The criterion which you place in the rows below the field name can be a label, value, formula or blank cell. When you are trying to look for records which have a specific entry, you will want to use a matching label or value. LDW POWER will then choose only those records which contain the same label or value. If, in your criterion range, you specify more than one field, in the same row, with criteria for each field, a record must satisfy all criteria to be selected in the Query. Blank cells are used as open criteria and will match all records. You can use these open criteria to create "or" conditions in your query to allow you to search for a record that satisfies at least one of two conditions. You can have one row of the Criterion range that contains a criterion in thqz first field and a blank cell in the second field. The next row could contain a blank cell in the first field and a criterion in the second field. When LDW POWER performs the Query, it will accept records with one criterion or the other. SPECIAL MATCH CHARACTER IN CRITERIA There are three special match criteria characters which can be used so that labels need not match exactly to be selected in a Query. These characters are: ?, *, ~. The (?) character will match a single character. For example, l?t would match the words let, lot, and lit. You can use as many (?) characters as you would like. The letters f??t would match foot, felt, or fast. The (*) character will match all characters to the end of a label. For example, pre* would match prefix, present or pretty. In figure below, the criterion range specified would tell LDW POWER to search in the Name fields of the input range for all entries that begin with a K or J. The (~) character tells LDW POWER to accept any label except those that begin with the letter following the tilde. For example, ~Y will not accept any labels which start with a Y. FORMULAS IN CRITERIA If you wish to set up more complex criteria for your database, you can use formulas and functions to set more exacting conditional tests. For example, if you wanted to set a criterion for all employees with salaries over $20,000, you could use the formula: +E3>20000. You can also compare two different fields. For example, the formula +E386. This criterion can be used to select all records for employees that were hired after 1986. Using this criterion range, you can select all employees whose total expenses exceed their salaries and who belong to class A, or all employees whose total expenses exceed their salaries and have been hired after 1986. MULTIPLE CRITERIA LDW POWER allows you to use multiple criteria in performing a query. You can select records which satisfy all of the specified criteria or just one. Where you place your criteria, tells LDW POWER how to make the selection. LDW POWER will allow you to use multiple criteria in two ways. One way involves checking the records to see if they contain all of the specified criteria. This is called an AND query. In an AND query, if a record contains one of the criteria specified but not the others, it is not selected. The second type of query is called an OR query. In this type of query, LDW POWER will check to see if any of the criteria is contained in a record. Any record that contains at least one of the specified criterion is selected. The placement of the criteria in the Criterion range will determine if LDW POWER performs an AND query, an OR query, or both. Criteria which are placed in the same row of the Criterion range are AND criteria. Criteria placed in different rows are OR criteria. You can have a combination AND/OR query by placing two or more criteria in one row and then different criteria in subsequent rows. CREATING YOUR CRITERION RANGE Before performing a query, you will need to create a Criterion range. This range should be located in cells in the worksheet which are not part of the database. In other words, the cell addresses of your Criterion range and your Input range should not overlap. Select the field names you would like to include in your Criterion range and enter them into the cells in the opt row of the range you will select. In the row(s) below the field names enter the criterion you will be using in the column of the corresponding field name. When all of your criteria has been entered, issue the Criterion command and enter the prepared range. OUTPUT RANGE The final range in the Data Query command is optional, depending on the type of Query to be performed. The Output range is used only when you intend to select the Extract or Unique commands for your Query. The first row of your range should consist of field names for all the fields in your database which you would like to be copied. If you only have two field names in your Output range, only two fields of each record will be copied into the range. These field names must be identical to the field names in the Input range in order for the Query to function properly. They can, however, be in any order. You can create the row of field names either by typing them in or issuing the Copy command. The remaining cells of the Output range should be blank. This is where the records will be copied to. When you select the Output option, a message will appear in the edit line asking you to enter the Output range. There are two ways to specify the Output range. You can specify only the range of field names, or you can specify the field names along with a range of blank cells which will hold the output. The size of the selected range will determine the number of records that LDW POWER can extract. If you choose the single row of field names as the range, LDW POWER will be able to extract as many records as there are rows between those field names and the bottom of the worksheet. If you choose to specify a range of blank cells along with the field names, LDW POWER will only be able to extract as many records that can fit within the range. You must be careful not to have any information in the area of the Output range which will be deleted when entries are copied into the Output range. QUERY OPERATION COMMANDS After you have created and entered all of the necessary ranges, you are ready to execute one or more of the Query Operation commands. There are four commands available: Find, Extract, Unique and Delete. DATA QUERY FIND The Data Query Find command allows you to inspect and edit specified records within your input range. When you select this command, LDW POWER will look through the records in your Input range and find all records which match the criteria in your Criterion range and highlight them sequentially (i.e., the first record in your input range which matches the criterion will be highlighted first). When the Data Query Find command is invoked, the worksheet window changes somewhat. The worksheet will be adjusted such that the input range is aligned with the upper left corner of the window (i.e., the left-most column of the input range will be displayed as the left-most column of the window, and the top row of the input range will be displayed as the top-most row in the window). The shaded portions of the scroll bars will not be visible, therefore, you will not be able to scroll to different parts of the worksheet. The arrows located at the ends of the scroll bars, as well as the cursor keys, can be used to move the highlight among the matching fields in the input range. The cell pointer will move directly to matching records, passing over any records which do not match the criteria. If the input range is too wide to fit on the screen, the left and right arrows will also allow you to scroll back and forth across the range. As soon as you issue the Find command, the mode button will display FIND, indicating that the worksheet is in the FIND mode. To edit entries in the FIND mode, click on the mode button to enter the EDIT mode. The mode button will then display EDIT, and editing can be done in the usual manner. When you are satisfied with the changes, press Return to return to the FIND mode. To exit the FIND mode, press Return, Esc or [Ctrl][Undo]. DATA QUERY EXTRACT The Data Query Extract command is used to copy specified fields from records in your database into the Output range. The records which will be copied are those records which match the criteria specified in the Criterion range. After you have defined the three necessary ranges, select Extract to issue the command. The records will be copied into your Output range. You must remember to select an Output range before issuing the Extract command. If the selected range is not big enough, an error message will appear on the screen. Only those records which will fit into the given range will be copied. Note that when the Extract command copies records which contain formulas or functions, only the values returned by the formulas or functions will be copied into the Output range. DATA QUERY UNIQUE The Data Query Unique command is very similar to the Extract command. The difference is that the Unique command will not allow duplicate records to appear in the Output range. This is helpful for big databases when you have numerous criteria. DATA QUERY DELETE The Data Query Delete command will remove records from the database which satisfy the criteria specified in the Criterion range. The remaining records in the database will then be moved up to fill any empty rows created by the deleted records. Once you delete records from the database, they are permanently erased. You cannot retrieve the lost records unless you perform the File Save command prior to issuing the Data Query Delete command. When you issue the Data Query Delete command, a message will appear to ensure that you really want to delete records. Select OK to delete, or Cancel to return to the menu. DATA QUERY RESET LDW POWER will remember all ranges used in the very last Query command. This means that all ranges which were specified will be remembered until you change them. The Data Query Reset command will remove from LDW POWER's memory all information about Data Query ranges and the last command. THE QUERY [F7] KEY LDW POWER has a special function key [F7] which allows you to re-issue the last Data Query command (Find, Extract, Unique or Delete). This can be helpful when you change entries in your database or change the criteria in the Criterion range. DATA QUERY QUIT Selecting the Quit command in the Query menu will return you to the READY mode. NOTES To summarize the Query command, there are three range commands in the Query command. The first two, Input and Criterion, are mandatory. The Third range, Output, is optional. Input - The range you enter which determines what data the Query will be performed on (usually the entire database). Criterion - The range which contains the field names and criteria which determine the records and fields which are selected by the Query. Output - The range into which the records containing the specified criteria will be copied. There are four commands which determine the type of Query performed by LDW POWER Find - Locates records which match the criteria entered in the Criterion range. Extract - Locates and copies into the Output range records which match the criteria entered in the Criterion range. Unique - Locates records which match the criteria entered in the Criterion range and copies only those records that have unique data in the specified fields. Delete - Removes records from the database which meet the specified criteria. Data Fill The Data Fill command will enter a sequentail series of numbers into a specified range. You specify the range, sequence, and scope of the fill by issuing a Start value, a Step value, and a Stop value. This command can be very useful with many of the other commands available to you with LDW POWER. This includes preparing values for the X range of a graph, preparing input values for Data tables, numbering records in a database, and many others. When issuing the Data Fill command, you will need to enter three values: Start, Step, and Stop. The Start value is the number which you would like LDW POWER to place in the first cell of the range. The Step value is the number you would like LDW POWER to increment the Start value by in each consecutive cell of the range. The step value can be positive or negative. Finally, the Stop value is the limiting value for the fill. To issue the Fill command, select it from the Data menu. A prompt will appear in the edit line asking you to enter the Fill range. If there is a previous fill range in the worksheet, it will appear in the edit line. Move the cell pointer to select the range you would like to fill (or type in the range). The prompt Start: will appear in the edit line followed by the default Start value of 0. Enter your Start value. A second prompt will appear in the middle of the edit line asking you to enter a Step value. The default Step value is 1. Enter a Step value. Finally, a third prompt will appear on the right side of the edit line asking you to enter a Stop value. The default Stop value is 8191, one number less than the number of rows in the worksheet. If the Stop value causes less numbers to be issued than the number of cells in the specified range, the range will only be filled to the point of the Stop. As soon as you enter the Stop value, LDW POWER will enter the Fill values into the specified range. Once you issue Start, Step and Stop values for the Fill command, these become the default values in the worksheet until they are changed. Data Table The Data Table command will perform a "what-if" analysis on a series of entries in your worksheet and will create a table containing the results. With the Table command, you can create a table which takes a formula or several formulas containing a reference to one or two input cells, replaces this reference(s) with corresponding values from the table range, and returns a set of the different results. For example, if you wanted to see how different mortgage rates affected the amount of your monthly house payment, you could list a series of interest rates, insert a formula for your monthly payment, and the table would automatically calculate what your monthly payment would be at each given interest rate. You can see then, how valuable a table can be in automating your work, when you're dealing with many possible "what-if" situations. When you select Table from the Data menu, a horizontal menu will appear with three choices. Select 1 if you want to create a one variable table. Select 2 if you want to create a two variable table. Select Reset to cancel the table ranges which have been set if you have previously created a data table in your worksheet. ONE VARIABLE DATA TABLES To create a one variable data table, you will need to set up two things: the table range, and the input cell. The table range is the area in which you will place the formulas to be calculated along with the values to be tested. The values should be lined up in consecutive cells in the left-most column of the range, while the formulas should be placed in consecutive cells along the top row of the range. Notice that the values are lined up in a column to the left and the set of formulas are positioned one row above and one column to the right of the column containing values. The calculated results will appear next to each tested value and under the formula which was calculated. These formulas can be as simple or as complex as you choose and can include functions. The input cell is the cell which contents are temporarily replaced by the values for the time that the formulas are being calculated. At the end of the calculations, the contents of the input cell are restored. This cell can be located anywhere in the worksheet except within the table range. You may want to label the input cell in order to identify it. For a one variable table, select 1 from the Table menu. A message will appear in the edit line asking you to enter the table range. Select the range, which must contain the column of values and row of formulas. Your range should contain a number of blank cells to accommodate the results of the data table calculations. A second message will appear in the edit line asking you to enter the Input cell. Choose an appropriate cell and enter its cell address. The cell address you enter should be the same address which is referred to in the formula(s). As soon as you enter the address, LDW POWER will complete the data table by calculating the formula with each value and then placing the result in the cell which is the intersection of the column containing the formula and the row containing the value. The ranges and cell addresses you specify will become the defaults for the Data Table command until you change them. TWO VARIABLE DATA TABLES LDW POWER allows you to create data table sin which two variable can be substituted into a formula. However, unlike one variable data tables, two variable data tables can only process one formula each time the Data Table command is issued. To create a two variable data table, the first thing you must do is set up your table range. The table range should include the values for Input Cell 1 in the left most column and the values for Input Cell 2 in the top row. The formula containing references to both Input Cells should be entered into the upper left cell of the range. Select 2 from the Data Table menu. A message will appear in the edit line asking you to enter the table range. Enter the range that begins with the cell that contains the formula and includes all of the horizontal and vertical values. LDW POWER will fill in all of the cells of the range when you issue the Data Table command. When the range has been entered, a second message will appear in the edit line asking you to enter Input Cell 1. The Input Cell can be any cell in the worksheet that is not in the Table range. Enter the cell address for Input Cell 1. You will get another message in the control panel asking you to enter Input Cell number 2. Enter the address for Input Cell 2. As soon as the second input address is entered, the data table will be filled with results of the calculations. The ranges and cell addresses you specify will become the defaults for the Table command until you change them. THE TABLE [F8] KEY LDW POWER provides a special key which will allow you to recalculate a pre- defined table with the press of the key. This Table key is the [F8] key on your keyboard. When you press this key, LDW POWER will automatically recalculate the last range and input cell(s) which were defined. You may change the value(s) and formula(s) in the range and quickly create a new table with the press of the [F8] key. The Table key will work for both one and two variable data tables. SUMMING UP DATA TABLES To create one variable table: - Choose the input cell outside of the table range. - Set up the column of values on the left side of the table range. - Set up the row of formulas/functions one row up and one column over from the column of values. - After selecting Data Table, specify the table range and Input Cell. To create two variable table: - Choose the input cells for both the first and second value set outside of the table range. - Place the formula/function in the upper left corner of the table range. - Set up the first value set in the left-most column of the table range below the formula. - After selecting Data Table, specify the table range and Input Cells. Data Sort The commands in the Data Sort menu allow you to sort the information in your database to fit your particular needs. With these commands, you can arrange and re-arrange your database according to category, name, date, value, or any other field. The commands will work with both text and numbers and can make it easier to locate or compare entries based on a particular subject. LDW POWER will let you sort items in either ascending or descending order. When you select Data Sort, a horizontal menu of the Sort commands will appear. The first thing you must do when issuing the Sort command is to specify the sort range. This is done by issuing Data-Range from the menu. When you select Data-Range, a message will appear in the edit line asking you to enter the Data-Range. The range you enter should include all the data which you want sorted but should exclude all record or field names. After you have entered the range, you must tell LDW POWER what criteria to sort the database by. The field on which you will be sorting the records is called the key field. LDW POWER provides you with both a Primary-Key command and Secondary-Key command. The Primary-Key is the field you will specify which you would like to base the sort on. For example, if you have a database of employee records, and you would like to display employee names in alphabetical order, the Name field would be the primary key for the sort. The Secondary-Key is a command which will allow you to sort duplicate items in the Primary-Key based on a second field. For example, if you have employees with the same name in your primary sort, you can use a secondary sort to place these employees in an order dependent on the date they were hired. Once a data range has been entered, select Primary-Key. A message will appear in the edit line asking you to enter the Primary sort key. Select any cell from the column which contains the field you wish to use as your key. When the sort key is entered, a second message will appear in the edit line asking you to enter the sort order (A or D). Choosing A will sort in ascending order (smallest to largest). You then have the option of entering a Secondary-Key using the same method as entering a Primary-Key. To set the order of placement of characters in the sort (numbers or letters first), use the Worksheet Global Default Other Sort menu (see Worksheet Menu). When you have entered the Primary-Key and Secondary-Key (if desired), you are ready to execute the Sort. Select Go from the menu and the sort will be executed. Once you have defined the range, keys, and sort order for a sort, LDW POWER will remember them. If you alter entries in the database, you can quickly re-sort them by selecting Go from the Data menu. If you don't want LDW POWER to automatically select the previously defined Sort formats, you can issue the Reset command to clear the previous settings from LDW POWER's memory. Note that a space before or after entries in your database may affect the way in which LDW POWER sorts them. SUMMING UP THE DATA SORT COMMAND: - Enter a data range. This range should include the data to be sorted but exclude the field names. - Select a field for the Primary-Key to base the sort on and choose the order of the sort. - Select a field for a Secondary-Key. This is an optional selection dependent on whether you want LDW POWER to run a secondary sort. - Select Go to run the sort. Data Distribution The Data Distribution command will compute a frequency distribution for a given set of values. A frequency distribution is a table which shows how may values from a given group fall within a certain range. For example, you can use a frequency distribution to determine how many employees are between the ages of 40 and 65; how many are between the ages of 35 and 40; how many are under the ages of 35; and so on. The first thing you must do before issuing the Distribution command, is enter the range of values you want to group in the frequency distribution into a single column of the worksheet. In our example above, this would be the ages of all employees. This is called the Values range. You then need to enter a range of entries in ascending order which will tell LDW POWER what frequency range to use for grouping the Values range. In our example above, this would be the age divisions: 35, 40, 60. This range is called the Bin range and must contain values listed in ascending order. The values you enter in the Bin range are the points at which you would like LDW POWER to divide the values in the Values range. In other words, all employee ages less than or equal to 35 will constitute one group. Note that neither of these ranges may contain labels. The frequency distribution table created with the Data Distribution command will contain a listing of how many values in the Values range lie between the distribution points of the Bin range. All values below the lowest value in the Bin range will constitute one distribution range, and all values above the highest value will also constitute one distribution range. When you select the Distribution command, a message will appear in the edit line asking you to enter the Values range. Select the range from your worksheet which contains the values you want to group. When this range is entered, a second message will appear asking you to enter the Bin range. As soon as the range is entered, the distribution table will be calculated and placed in the cells adjacent to the bin range. Data Parse The Data Parse command lets you break up long labels into a series of small labels or values. This command can be extremely helpful when you use the File Import Text command to bring along labels into your worksheet (see section in the File management chapter on the File Import command). When you select Parse from the Data menu, a horizontal menu of the Parse commands will appear. The first three of these commands will organize how you would like the divided set of labels displayed and where you would like them placed. The next command will Reset the three previous commands. The Go command will run the Parse, and the Quit command will return you to the READY mode. DATA PARSE FORMAT-LINE The Data Parse Format-Line command is used to tell LDW POWER how to break up the labels. When you are parsing, you will need to create different format lines for each label that contains different types of information. The Format-Line command will tell LDW POWER where to break up the label and the format with which the new entries should appear in their respective cells. To create the format line, position the cell pointer in the cell that contains the label you will be parsing, and the select the Data Parse Format-Line Create command. A new row with a format line will be inserted directly above the label. The format line will be exactly as long as the label you are parsing. It is divided into sections which are comprised of a variety of characters. These characters tell LDW POWER where to break up the label and the format for each new entry. The following is a table of each character and what it means. Symbol Meaning L Beginning of a label V Beginning of a value D Beginning of a date T Beginning of a time S Beginning of a skip > Character symbol * Blank space The first four of these symbols will mark the beginning of a label. The > and * are the most common symbols and will be used to fill up the spaces where characters and blank spaces go. The S symbol is the skip symbol and tells LDW POWER to eliminate the characters in the long label which are positioned where the S is positioned in the format line. When you first issue the Create command, LDW POWER will guess where you would like the breaks, the kind of entry you are creating, and where spaces should be inserted. Most of the time, LDW POWER's guess will be correct. However, in case it is incorrect, you can use the Data Parse Format-Line Edit command to change the format line in order to break up your labels exactly as you would like them. To edit your format line, position the cell pointer on the cell which contains the format line and select the Edit command. If you try to select Edit and the cell pointer is not positioned on the cell containing the format line, and error message will appear. When Edit is selected, the Edit box will overlay the control panel and part of the worksheet window. Inside the Edit box, the format line, the actual label, and the cell address of the label are displayed. In the lower right corner of the Edit box you will see an ESC button, an OK button, an Up arrow and a Down arrow. Editing is done within the Edit box in the same manner as it is done in your worksheet. When you first enter the Edit box, the cursor will be positioned over the first symbol of the format line. You can use the left and right arrow keys on the keyboard, or the mouse, to move the cursor across the format line. The Backspace and Delete can be used to remove characters. When you enter the Edit box, it will be in overwrite mode, so any symbols you type in will overwrite existing symbols. You can use the Insert key to turn the overwrite mode off and on. If you try to enter any characters which are not format symbols, the worksheet will beep. You can use the Up and Down arrows in the Edit box or the cursor keys, to move up and down over all labels to be parsed. When you are finished editing your format line, click on the OK button to enter the changes. If you do not want to enter the changes, click the ESC button or press the Esc key. INPUT-COLUMN After you have finished creating and editing your format line, you will need to tell LDW POWER which labels you would like to parse. This is done with the Data Parse Input-Column command. Select the range which contains the format line and labels (in the same column as the format line) you want to parse. OUTPUT-RANGE The Data Parse Output-Range command tells LDW POWER where in the worksheet to put the divided labels. Select the command and specify the upper left corner of a blank range to accept the parsed data. RUNNING THE PARSE: GO You are now ready to parse your label. This is done by selecting the Data Parse Go command. RESET LDW POWER will remember the last Input-Column and Output-Range addresses that were entered. The Reset command will erase these addresses from LDW POWER's memory so that you can easily designate new addresses to fit your next parse. QUIT The Quit option will return you to the READY mode. Database Statistical Functions LDW POWER provides you with special database statistical functions which allow you to perform statistical calculations within your database. These functions are designed specifically to use entries in your database as their arguments. There are seven such functions, and their form is very similar to the other functions available with LDW POWER. The seven database statistical functions are: @DCOUNT, @DSUM, @DAVG, @DVAR, @DSTD, @DMAX, and @DMIN. Each of these functions have the same form: @Function name(Input Range, Offset Column, Criterion Range) The first argument, Input Range, is exactly the same type of range as used in the Data Query commands. The second argument, Offset Column, tells LDW POWER which column of data in the database to use for calculation. Begin counting at zero for the column in the farthest left area of the input range. The final argument, Criterion, is exactly the same type of range as used in the Data Query commands, and it specifies which records are to be selected. For example, function @DSUM in the figure below will sum E3 and E5 (E is 3rd column in B..E range) and return 418,790. @DCOUNT - The @DCOUNT function counts the cells in the Offset Column that are non-blank and match the criteria specified in the Criterion Range. @DSUM - The @DSUM function calculates the sum of the values in the Offset Column that match the criteria specified in the Criterion Range. @DAVG - The @DAVG function computes the average of the entries in the Offset Column that match the criteria specified in the Criterion Range. @DVAR - The @DVAR function calculates the variance of the entries in the Offset Column that match the criteria specified in the Criterion Range. @DSTD - The @DSTD function computes the standard deviation of the entries in the Offset Column that match the criteria specified in the Criterion Range. @DMAX - The @DMAX function returns the maximum or largest value in the Offset Column that match the criteria specified in the Criterion Range. @DMIN - The @DMIN function returns the minimum or smallest value in the Offset Column that match the criteria specified in the Criterion Range. SUMMARY NOTES Database commands in LDW POWER Spreadsheet allow you to keep large amounts of information in an orderly manner in your worksheets. You can then use the commands available to manipulate the entries in your worksheets. Data Fill - The Data Fill command will enter a group of numbers, which are spaced sequentially, into a specified range of the worksheet. You must set the Start, Step, and Stop values to use this command. Data Table - The Data Table command will substitute values into a given formula or formulas and create a table of the returned values. There are two types of Data Tables available with LDW POWER: one variable and two variable. One variable tables allow you to use one variable on as many formulas as you choose. Two variable tables require two variables to be entered which can be used on a single formula. Data Sort - The Data Sort command will sort the information in the database in ascending or descending order using any field you choose. Data Query - The Data Query command will located information in the database using criteria entered by the user. The Data Query menu contains four commands which determine what is done with the records retrieved by the Query: Find, Extract, Unique, and Delete. The Find command will locate the records. The Extract command will copy the records into a specified portion of the worksheet. The Unique command will copy the records and also screen them to make sure no duplicates occur. The Delete command will delete the records. Data Distribution - The Data Distribution command will compute a frequency distribution for a set of values. Data Parse - The Data Parse command will break up long labels into smaller cell entries which are formatted to the specifications of the user. These smaller entries are then placed in a range of the worksheet. Database Statistical Functions - LDW POWER provides you with seven statistical functions created for use within your database. These functions allow you to perform calculations within your database. MACROS Macros are powerful and convenient tools which help to automate some of the operations within your worksheet. A macro can be thought of as a miniature program which instructs LDW POWER to execute a pre-determined series of keystrokes. The instructions of this "program" are the actual keystrokes themselves. For example, if you wanted to format every cell in the worksheet with a two digit fixed decimal display, you would type in the following macro: /wgff2~ If you look at it closely, you will see that this macro is nothing more than a collection of the actual keystrokes that would be used to format the worksheet manually. If you were formatting the worksheet manually, you would press the slash (/) key first in order to access the command menu. Then, you'd press the (w) key to open the Worksheet menu and then the (g) key to open the Worksheet Global menu. Once in the Worksheet Global menu, you would press the (f) key for Worksheet Global Format and then (f) again to choose the Fixed command. Finally, you'd enter the number 2 in order to display 2 fixed decimal places and would then hit Return which is represented by the tilde (~) in a macro. These are the exact keystrokes contained in the macro above. The only difference is, that the macro can be saved in the worksheet and executed automatically at any time, while the manual method would require that you enter each keystroke by hand every time you wanted to perform the operation. The power of macros then, is obvious especially when you're dealing with repetitive operations which require many keystrokes. LDW POWER offers a macro language compatible with LOTUS 1-2-3 version 1.1 that allows you to define macros and call them up any time with just a keystroke. LDW POWER also features a macro recorder which actually records your keystrokes as you enter them, thus freeing you from having to deal with the complexities of macro definition syntax. A series of /x macro commands is included to offer more advanced macro capabilities. The number of macros is limited only by the amount of available memory. Whether you use the macro language or the macro recorder to create a macro, you must assign it a name (up to 15 characters long) in order to run it. All macro names must be preceded by a backslash (\). You can create a macro name using the Range Name Create command or using the macro recorder which adds the backslash automatically. To call up the macro from the keyboard, you would press the Alt key simultaneously with the first letter in the name of the macro. If more than one macro name starts with the same letter, LDW POWER will only call up the macro that was defined first. You will have to use the mAcro Play command to call up any others. The following section of the chapter will describe how to define, store, and name a macro. Because differences exist between the two methods, the macro language and macro recorder will be explained separately. Creating a Macro USING THE MACRO RECORDER Creating macros with the macro recorder is simply a matter of turning the recorder on, and typing the desired keystrokes. You may use both the keyboard and mouse to enter various keystroke commands. For example, both may be used to select a range of the worksheet. Because the macro recorder "records" every keystroke, you can enter the special keys (such as Return or Esc) directly from the keyboard. To turn the recorder on, select the Macro Record command or press [Alt][F3]. The MREC indicator will appear on the control panel to indicate that a macro is being recorded. Then, just type in the keystrokes as if you were manually issuing the commands. If, at any time during the macro definition, you wish to cancel the macro, select the mAcro Abort command or press [Alt][F6]. When you are finished defining the macro, select the mAcro End command or press [Alt][F4] to turn off the recorder. The MREC indicator will disappear from the control panel. When you end a macro definition, a prompt will appear on the edit line asking for the cell in which the macro is to be stored. Enter an appropriate cell and press Return. Another prompt will appear asking for the macro name. Enter a name for the macro (no more than 14 characters) and press Return. The macro recorder will add the backslash (\) automatically. The macro is now defined and can be called up by its name. USING MACRO LANGUAGE A macro must be entered into the worksheet as a label and cannot contain cells with values. You can place the entire macro (up to 240 characters) into a single cell, but the more keystrokes you have in one cell, the harder it is to read and debug the macro. Therefore, if your macro is long, you might be better off splitting it up into a series of smaller labels. LDW POWER will look in the first cell of the macro and execute all the keystrokes contained in it and will then move down to the cell immediately below to see if there are any keystrokes to be found. If there are, those instructions will be carried out, and LDW POWER will move down to the next cell. This process will continue until LDW POWER comes across an empty cell or the special quit command (/XQ), or encounters an error. The macros, therefore, must be arranged in consecutive cells of a column in order to work properly. It's important to remember to always include a label prefix when entering a macro. If you try to enter the slash (/) character at the beginning of a macro without first entering a label prefix, you will automatically access the command menu (LDW POWER interprets the slash as a manual command to open the command menu). If the slash is preceded by a label prefix (', "", or ^), however, it will simply be treated as a label. The label prefix is ignored during macro execution. You must create a macro in the active worksheet in order to run it. LDW POWER allows you to transfer macros from one worksheet to another by means of the File Combine command. Refer to the chapter on the File menu for more information. Macros use a set of symbols which function as the "special keys" such as Esc, Return, or F5[Goto] which cannot be inserted into a label. The following is a list of the symbols used in macros and the keys they represent. Key Symbol Key Symbol down arrow {Down} Del {Delete} or {Del} up arrow {Up} Ins {Insert} left arrow {Left} Return ~ right arrow {Right} [Edit] F2 {Edit} Home {Home} [Name] F3 {Name} Home+Shift {End} [Abs] F4 {Abs} up arrow+Shift {PgUp} [Goto] F5 {Goto} dn arrow+Shift {PgDn} [Notes] F6 {Notes} Tab+Shift {BigLeft} or {BTab} [Query] F7 {Query} Tab {BigRight} or {Tab} [Table] F8 {Table} Esc {Escape} or {Esc} [Calc] F9 {Calc} Backspace {BackSpace} or {BS} [Graph] F10 {Graph} Undo {ScrLock} Undo+Control {Break} Note, that the braces "{" and "}" should be included in the symbols shown above. Naming Macros In order to run a macro, you must first name it. The name will tell LDW POWER where to find the macro in the worksheet, so the macro must always be named at the first cell which contains it. Macro names should be preceded by a backslash (\). When assigning a name to a macro, select the first cell of the macro and issue the Range Name Create command. Enter the name (preceded by a backslash) you would like to give the macro and press Return. Because the name is so important to macro operation, it might be a good idea to include the name of the macro on the worksheet. You can use the Range Name Label Right command to name macros using labels adjacent to those macros. That way, the labels would serve as reminders as to the names of the particular macros. See the chapter on the Range commands for more information. Aside from these macros, every worksheet can contain one macro name \0 (zero). This is the auto-execute macro, which is automatically run every time the worksheet is loaded. Note, that the \0 macro will only be run when the worksheet is loaded and that you cannot call it up at any other time. If you would like to be able to execute it at other times in the worksheet, you can simply assign it a second macro name and use this second name to play it. LDW POWER allows you to create macros that can call up other macros by means of macro subroutines. The macros contained in these subroutines do not have to be named, because they are referred to by their cell locations. See the /XC command for more details. Running Macros To run a macro, use the mAcro Play command or simply press the Alt key simultaneously with the first letter of the appropriate macro name. When you select the mAcro Play command, a menu of all defined macro names will be displayed. Select the appropriate macro name, and the chosen macro will run. Once you begin execution of a macro, the screen will reveal that the worksheet is quickly executing all of the commands and keystrokes in the macro. The PLAY indicator in the control panel will be displayed to indicate that a macro is running. Once the macro has finished, the PLAY indicator will disappear. Stopping Macros A macro will stop its execution under any one of the four following conditions: 1. LDW POWER runs across a blank cell. If you want the macro to run without interruption, you should go back and eliminate the blank cell. 2. An error exists in the macro. In this case, LDW POWER will beep, and an error message will appear on screen. 3. The macro contains the special quit command (/XQ). 4. The [Control] and [Undo] keys are pressed during macro execution. Pressing [Control][Undo] will not undo the effects of the macro. It merely stops if from executing any further. You can pause a macro in the middle of execution by pressing [Alt][F1] or [Alt][F2]. To restart the macro, press either combination of second time. The Step Mode The main purpose of macros is to quickly execute a series of commands or keystrokes to save you time and effort. For this reason, macros normally execute with great speed. There may be times, however, when you need to see the operations of a macro, one step at a time (especially when you are trying to debug a macro). LDW POWER allows you to slow the macro using the step mode. To enter the step mode, press either [Alt][F1] or [Alt][F2] before you run the macro or during macro execution. When you are in the step mode, the STEP indicator is on, and you can press any key to execute each step of the macro. To exit the step mode and resume normal execution of the macro, press either [Alt][F1] or [Alt][F2]. Errors in Macros If a macro is not entered into the worksheet correctly, an error results. There are numerous types of errors which can be made. However, most errors are caused by one of three common mistakes: omission of the proper characters, an invalid response to a prompt, or the misspelling or incorrect form of one of the LDW POWER special commands. Usually, when any of these errors occur, LDW POWER will beep, and the macro will stop executing. A message will appear on the screen telling you that an error has occurred. Click OK or press Return. The worksheet will re- appear. The macro will have been executed up until the point where the error was detected. You must now go back over the macro to determine what error occurred. Editing macros is identical to editing any other label in the worksheet. Interactive Macros LDW POWER provides you with a special symbol {?} to allow for interaction with the user during execution of a macro. When a macro is running and comes across a {?} symbol, the macro will pause for input from the user. You can enter as much information (including keystrokes and commands) as necessary and the worksheet will process the information in the normal worksheet fashion. When you are ready to resume running the macro, press Return, and the macro will continue where it left off. Because the Return is not considered part of the macro and will only start the macro running again, you will often want to include a tilde (~) after {?} to press Return in the macro. The following macro command sequences are often used to resume execution of the macro when using the {?}: {?}~ {?}{Right} {?}{PgDn} /X Commands LDW POWER provides you with a special set of /X commands which allow you to perform operations from within the macro that are more complex than basic worksheet commands and involve more that just a mere keystroke. One very important thing to remember when using /X commands is that since they create "programs" within the worksheet, bugs are often the result when an unanticipated reaction is made to one of the commands. Below is a summary of the LDW POWER /X commands. The summary lists all /X commands available and a brief description of what each command does. Directly following the summary is a more detailed description of each of these commands and how they work. Summary of the LDW POWER /X commands: Command Function /XI(Condition)~ Results in an if-then comparison. /XG(Location)~ Goes to a location for remaining keystrokes. /XC(Location)~ Calls a subroutine. /XR Returns from a subroutine. /XQ Quits macro execution. /XM(Location)~ Displays and processes a user-defined menu. /XL(Message)~(Location)~ Displays a message in the control panel, accepts a label entry from the keyboard, and places this entry in a cell. /XN(Message)~(Location)~ Displays a message in the control panel, accepts a number entry from the keyboard, and places this entry in a cell. /XWF Freezes the worksheet window during macro execution. /XWN Cancels the /XWF command. /XPF Freezes the control panel during macro execution. /XPN Cancels the /XPF command. /XI(Condition)~ The /XI(Condition)~ command is a programming command which tests a condition and determines if it is TRUE (has a non-zero value) or FALSE (has a zero value). If the condition is TRUE, the macro will execute the remaining keystrokes and commands in the cell. If the condition is FALSE, the macro will move on to the next cell and execute the keystrokes and commands which begin there. You must always remember to include the tilde (~) at the end of the command. /XG(Location)~ The /XG(Location)~ command causes the macro to go to the cell(s) specified by (Location) and continue reading the keystrokes and commands from that cell (or the upper left corner of the range). The (Location) specified can be a cell address, a range or a range name. You must always remember to include the tilde (~) at the end of the command. /XC(Location)~ The /XC(Location)~ command causes the macro to call a specified subroutine in a specific (Location). The (Location) specified can be a cell address, a range address, or a range name. Note, that you only need to specify a location in order to run the macro subroutine. Therefore, you do not need to name the macros in your subroutines in order to run them. To return from the subroutine to the macro, see /XR command below. It is essential to remember to include the tilde (~) at the end of the command. /XR The /XR command causes the macro to return from a subroutine to the original cells in the macro, directly following the /XC(Location)~ command, and continue execution of the macro. If an /XR command is encountered and no /XC(Location)~ command was issued, an error results. LDW POWER allows you to nest subroutines up to 32 levels. /XQ The /XQ command stops macro execution at the point where it is encountered and returns the worksheet to READY mode. /XM(Location)~ The /XM(Location)~ command allows you to create your own custom menus in the control panel of the worksheet window. These menus are displayed exactly like the command menus for the worksheet except for the fact that they contain commands that have been customized by you. The (Location) specified in the macro refers to the range in which you will set up the menu. This range must be set up properly in order for the macro to work. The first row of the range should contain the names of the commands to appear in your menu. The second row should contain descriptions of the commands in the first row. For example, if the command in the first row is Clear, the description in the second row should be something like: Clears a range of cells. This type of descriptive phrase is found throughout the standard command menu in LDW POWER. The third row should contain the actual part of the macro which will be executed when you choose the command specified in the first row. In the case of the example above, the third row would contain keystrokes which would clear a specified range of cells. When the macro is run, and you choose a command from your customized menu, LDW POWER will go to the range in which you defined the menu and look in the cells directly below the chosen command to get instructions to execute. Therefore, the command name, the command description, and the keystroke instructions should all be placed in consecutive cells of the same column. Once you have a menu created in any range of the worksheet, you are ready to use the /XM(Location)~ command. The argument (Location) included in this command must refer to the upper leftmost cell of the range which contains the menu. You must also be sure to include the closing tilde (~) in order to make the command perform. When the macro comes across the /XM command, your menu will appear in the control panel. When the menu first appears on the screen, the first option will always be highlighted. You can then use the mouse or the arrow keys to move the highlighting to the appropriate command. When you select one of the commands, the macro will proceed to process the instructions specified for that command. When using the /XM(Location)~ command, there are a few rules which must be followed. The commands must be placed in adjacent cells of the first row in the range. If the cells contain values or have blank cells between the entries, LDW POWER will beep, and an error message will appear on the screen. The descriptions in the second row must either be labels, or the second row can contain blank cells. Menus created with the /XM command can contain up to eight commands. Any commands created beyond that number will ignored by LDW POWER. When the commands are displayed in the control panel, LDW POWER will automatically leave one space between each command. If the combined total of the characters in the commands and these spaces add up to more than 72 characters, LDW POWER will beep, and an error message will appear. You can stop the execution of any of your customized commands by pressing the Esc key. When you press Esc, your custom menu will disappear from the control panel, and you will exit the /XM(Location)~ instruction. If there are any further keystroke instructions which follow the /XM(Location)~ in the macro, LDW POWER will process them. /XL(Message)~(Location)~ and /XN(Message)~(Location)~ The /XL(Message)~(Location)~ command and the /XN(message)~(Location)~ command are very similar in that they both allow you to ask for and receive input from the user during execution of a macro. The only difference between these two commands is the type of input they will accept. The /XL command will only accept labels, while the /XN command will only accept numeric input. The /XL or /XN comes first in the sequence and is followed by the (Message)~ prompt which tells the user the type of input desired. The (Location)~ argument then tells LDW POWER where to store the input from the user. The message argument must be a string and can be no more than 72 characters long. If the message contains a semicolon, a colon, a comma, or a closing brace, these characters must be enclosed in quotation marks. The (Location) argument can be a cell address, a range address, or a range name. You must always remember to include both tildes (~) in these commands for them to work properly. These commands function by first displaying a message in the edit line. LDW POWER will pause and wait for the user input. Depending on the command, the user must then enter a label or a value. LDW POWER will store the entry in the cell specified by the (Location) argument. If a range was entered as the location, the entry will be stored in the upper left cell of the range. In the case of the /XN command, the entry made by the user may be a literal value or a formula or function that returns a value result. If anything else is entered, LDW POWER will beep, and an error message will appear. When using the /XL command, the label entered may not be more than 240 characters long. If the entry is longer than that limit, LDW POWER will beep and return an error message. Also, if you enter a value with the /XL command, LDW POWER will convert it to a label before it enters the label into the location cell. /XWF The /XWF command allows you to freeze the worksheet window. Freezing the worksheet window is useful both for increasing the speed of macro execution and for suppressing the bothersome flickering that occurs during macro execution. The worksheet will remain frozen until one of two things happen: the macro ends or the macro encounters a /XWN command (see below). /XWN The /XWN command will reverse the effects of the /XWF command and will unfreeze the worksheet window. /WPF The /XPF command will freeze the control panel which will remain frozen until one of two things happens: the macro ends or the macro encounters a /XPN command (see below). /XPN The /XPN command will reverse the effects of the /XPF command and will unfreeze the control panel. Saving and Retrieving Macros Macros can be saved in two ways. Whenever you save a worksheet, the macros contained within that worksheet are automatically saved. When you call up a worksheet, all macros that were included in that worksheet are retrieved as well. Macros can also be saved independently from the worksheet by way of the File eXtract command. When the dialog box appears, enter the file name and select OK or press Return. Enter the range which contains the macro you would like to save. To retrieve a macro that has been saved with the File eXtract command, you can use the File Combine command. Refer to the chapter on File management for further information. Additional Notes on Macros 1. If the first character in your macro is the slash (/), remember to precede it with a label prefix or else LDW POWER will interpret it as a direct command to access the command menu. 2. You must remember to include Return character (~) in your macros. 3. You must always name the first cell (upper left corner of a range) in a macro. The name must include a (\) character followed by a name. If you try to run a macro which has not been named, LDW POWER will beep and an error message will appear. 4. The macro will start by executing the keystrokes contained in the first cell, and will then go to the cell directly below looking for the next set of instructions. As soon as a macro encounters a blank cell, it will stop. 5. When entering a numeric label in a macro, you must enter two label prefixes, because LDW POWER will ignore the first prefix. 6. When revising an existing macro, you don't need to rename it. EXITING LDW POWER To exit LDW POWER, select Quit from the command menu. If the worksheet has been altered in any way, an alert will appear on the screen to give you a chance to save the worksheet. Directly below this message will be displayed three buttons: Save, OK, and Cancel. SAVE Selecting the Save button will save your current worksheet before the program is ended. When you select the Save button, a file selector will appear displaying a list of the filenames of currently saved files. You can select an appropriate file to overwrite, or you can enter a new file name under which to save the worksheet. Press the Return key to save the worksheet under the chosen filename. OK Clicking on the OK button will cause you to exit the program without saving the worksheet. Select this button when you have already performed a final save using the File Save command or when you do not want to save the current worksheet. CANCEL Clicking on the Cancel button will remove the Quit message box from the screen and return you to the current worksheet. The Cancel button is provided in the event that you accidentally select the Quit command or you change your mind about exiting the program. The Cancel button has a dark outline to indicate that it is the default button and is, therefore, automatically selected when you press the Return key. FUNCTIONS THE FORM OF FUNCTIONS The LDW POWER Spreadsheet provides over 80 built-in functions which allow you to do everything from calculating depreciation expense to determining the length of a label. A function can be thought of as a compact, specialized tool that can be used to perform an operation that would otherwise be cumbersome or impossible to do with the keyboard. This chapter contains a listing of all those available functions along with a description and example of each. A function operates by using an argument or arguments in order to return a result. For example, the function @SUM(A1,A2,A3) uses the cells A1, A2, and A3 as its arguments in order to sum the values contained in those three cells. Such arguments are similarly used in string functions. For example, @LEFT("Test",2) uses the string "Test" and the number 2 in order to extract the first two characters ("Te") from the string. There are some functions though, that do not use arguments. For example, the function @ERR simply returns the value ERR. Functions like this are most commonly used in other, more complex function or simply to mark a cell. Every function, however, begins with the (@) sign and usually follows the form: @Function Name(argument 1, argument 2,...,argument n) The function name can be entered in lower or upper case or a combination of the two, but the (@) symbol must always be the first character. The argument(s) must always be enclosed by parentheses, and individual arguments should be separated with commas (unless the punctuation settings have been changed with the Worksheet Global Default Other International Punctuation commands). As mentioned before, the argument defines what the function evaluates. Some functions require a specific number of arguments, while others allow a variable number of arguments. Arguments may take the form of a cell or range reference, a range name, a literal value or string, a formula or a function. You may incorporate any combination of these alternatives in a given function. For example, the function @SUM(2,F4,Sales,@MAX(C1..C5)) will sum the value 2, and value contained in cell F4, the values in the range named Sales, and the largest value found in the range C1..C5. References, formulas, and functions can all be used as arguments as long as they return appropriate results. For example, a cell reference used as a string argument should refer to a cell containing a label, and a formula used as a mathematical argument should return a value of some sort. In the case of string arguments, they must be enclosed in quotation marks in order for the function to perform properly. (End of Part 3... Now load Part 4!) Part 4 of the LDW Power Spreadsheet Docs..... SUMMARY OF FUNCTIONS The following is an example of each function available in the LDW POWER Spreadsheet. The summary is divided into six general categories to make it easier to locate a given function. A more detailed description will follow in the Alphabetical Function Reference. Mathematical Functions @ABS(x) absolute value @ACOS(x) arc cosine @ASIN(x) arc sine @ATAN(x)  2-quadrant arc tangent @ATAN2(x,y) 4-quadrant arc tangent @COS(x) cosine @EXP(x) exponential @INT(x) integer portion @LN(x) log base e @LOG(x) log base 10 @MOD(x,y) x mod y @PI the constant pi (3.1416...) @RAND random number between 0 and 1 @ROUND(num,decimal) rounds a number to specified number of decimal places @SIN(x) sine @SQRT(x) square root @TAN(x) tangent String Functions @CHAR(ASCII code) converts an ASCII code to its corresponding character @CODE(string) converts the first character in the string into ASCII code @CLEAN(string) strips special control characters from a specified string @EXACT(string1,string2) compares one string to another @FIND(substring,string,x) locates a substring within another string @LEFT(string,# of chars) extracts a substring from another string starting at the beginning of the string @LENGTH(string) returns the number of characters in the string @LOWER(string) converts all characters to lowercase @MID(string,starting point,# of characters) extracts a string from anywhere within another string @N(range) the number in the upper left cell of the range @PROPER(string) capitalizes the first letter in separate words in a string @REPEAT(string, # of repeats) repeats a specified string @REPLACE(string1,starting point, # of characters,string2) replaces one group of characters with another @RIGHT(string,# of characters) extracts a substring from another string at the end of the string @S(range) the label in the upper left cell of the range @STRING(value,decimals) converts the value into a number string @TRIM(string) trims leading and trailing blanks from a string @UPPER(string) converts all characters to uppercase @VALUE(number string) converts the number string into a value Logical Functions @FALSE the value 0 (FALSE) @IF(condition,t,f) the argument t if condition is TRUE; the argument f if condition is FALSE @ISERR(x) the value 1 (TRUE) if argument x has the value ERR @ISNA(x) the value 1 (TRUE) if argument x has the value NA (Not Available) @ISNUMBER(x) the value 1 (TRUE) if argument x is a number; the value 0 (FALSE) if x is not a number @ISSTRING(x) the value 1 (TRUE) if argument x is a label; the value 0 (FALSE) if x is not a label @TRUE the value 1 (TRUE) Special Functions @@(cell) indirect cell reference @CELL(code,cell) information about a cell @CELLPOINTER(code) information about the active cell @CHOOSE(z,I0,I1,I2...In) zth argument in the list returned @COLS(range) the number of columns in a range @ERR the value ERR (error) @EXTERNAL(filename,range) contents of a cell in another worksheet @HLOOKUP(lookupvalue,range,column) horizontal table lookup @INDEX(range,column,row) two dimensional table lookup @NA the value NA (Not Available) @ROWS(range) the number of rows in a range @VLOOKUP(lookupvalue,range,row) vertical table lookup Financial Functions @CTERM(interest rate,final amount,initial amount) number of periods required to compound an investment @DDB(initial cost,scrap value,lifespan,current period) double declining balance depreciation @FV(pmt,int,term) Future value @IRR(guess,range) internal rate of return @NPV(rate,range) net present value @PMT(pmt,int,term) payment @PV(pmt,int,term) present value @RATE(final amount,starting amount,term) interest rate required to compound an investment @SLN(initial cost,scrap value,lifespan) straight line depreciation @SYD(initial cost,scrap value,lifespan,current period) sum of the years' digits depreciation @TERM(payment amount,interest rate,final amount) number of periods required to compound a series of equal payments to a final amount Date and Time Functions @DATE(year,month,day) returns serial value of date (e.g., 1=01-Jan-1900) @DATEVALUE(date string) converts date label into serial date value @DAY(date) the day from a serial date value @HOUR(time value) the hours from a serial time value @MINUTE(time value) the minutes from a serial time value @MONTH(date) the month from a serial data value @NOW the current serial date and serial time values @SECOND(time value) the seconds from a serial time value @TIME(hours,minutes,seconds) serial time value @TIMEVALUE(time label) converts a time label into a serial time value @TODAY today's serial date value @YEAR(date) the year from a serial date value Statistical Functions @AVG(range) average of all numeric items in a range @COUNT(range) count of all non-blank items in a range @MAX(range) maximum of all numeric items in a range @MIN(range) minimum of all numeric items in a range @STD(range) standard deviation of all numeric items in a range @SUM(range) sum of all numeric items in a range @VAR(range) variance of all numeric items in a range ALPHABETICAL FUNCTION REFERENCE @@(cell) The @@ function can be used to indirectly reference a cell or range. The cell argument must be a cell address or name of a cell which contains the address or name of another cell. Example: @@(C3) = 'Expenses (where cell C3 contains the label D1, and and cell D1 contains the label 'Expenses) @ABS(x) The @ABS function returns the absolute value of the argument (x). Examples: @ABS(-75) = 75 @ABS(B3) = 22 (where cell B3 contains the value 22) @ACOS(x) The @ACOS function returns the arc cosine of the value argument (x). The arc cosine is the angle in radians whose cosine is the argument. The value of the argument must be between -1 and +1, or a value of ERR is returned. The value of the @ACOS function will always be between 0 and pi. Examples: @ACOS(.5) = 1.047198 @ACOS(C6) = 1.266104 (where the value in cell C6 is 0.3) @ASIN(x) The @ASIN function returns the arc sine of the value argument (x). The arc sine is the angle in radians whose sine is the argument. The value of the argument must be between -1 and +1, or a value of ERR is returned. The value of the @ASIN function will always be a number between -pi/2 and +pi/2. Examples: @ASIN(1) = 1.570796 @ASIN(B2) = -1.57080 (where the value in cell B2 is -1) @ATAN(x) The @ATAN function returns the arc tangent of the value argument. The arc tangent is the angle in radians whose tangent is the argument. The value of the @ATAN function will always be a number between -pi/2 and pi/2. Examples: @ATAN(.5) = 0.463648 @ATAN(D7) = 0.785398 (where the value in cell D7 is 1) @ATAN2(x,y) The @ATAN2 function calculates a four-quadrant arctangent. The function returns the arc tangent of the argument (y) divided by the argument (x). The arc tangent is the angle in radians whose tangent is y/x. The main difference from the @ATAN function is that the @ATAN2 function looks at x and y separately, so it returns the value for all 4 quadrants from -pi to pi. A value of ERR is return if x=0 and y=0. Examples: @ATAN2(2,-2) = -0.78540 @ATAN2(B5,F7) = 0.785398 (where the value in cell B5 is 3, and the value in F7 is 3) @AVG(range) The @AVG function calculates the average of a group of values specified in the range. The average is the sum of a group of values divided by the number of values in the group. if all cells in the range are empty, a value of ERR is returned. However, cells containing labels are evaluated as the value 0 and are included in the calculation. Examples: @AVG(B3..B6) = 8 (where B3 = 15, B4 = 10, B5 = 7, B6 = 'the) @CELL(code,cell) The @CELL function return information regarding the cell specified by the cell argument. Although the function will only return information regarding one cell, the cell argument will be displayed as a range address. If you use range coordinates or a range name, the function will return information concerning the upper left cell in that range. The code argument is a special string that will tell the function what type of information to return. Below is a list of the available codes: Code Type of information returned "address" the absolute address of the cell "col" the column letter of the cell "contents" the contents of the cell "format" the format code of the cell "prefix" the label prefix of the cell "protect" the protection status of the cell "row" the row number of the cell "type" the kind of entry in the cell "width" the width of the cell Note, that the code must be enclosed in quotation marks. Example: @CELL("row",A1) = 1 @CELLPOINTER(code) The @CELLPOINTER function returns information concerning the currently active cell (the cell in which the cell pointer is located) at the time that the function is calculated. The code argument is exactly the same as that listed for the @CELL function. Refer to the @CELL function for details. Example: @CELLPOINTER("contents") = "test" (where the current cell contains the string "test") @CHAR(ASCII code) The @CHAR function returns the character that corresponds to the ASCII code argument. Example: @CHAR(40) = "(" @CHOOSE(z,item 0, item 1, item 2,...,item n) The @CHOOSE function will evaluate the first argument, z, and convert it into an integer, x, and will return the item which occupies the xth position in the argument list. If z is not between 0 and n, a value of ERR is returned. The arguments (item 0,....,item n) are the label or values that will be returned depending on the value of z. This list may be as large or as small as you choose, but there must be at least two arguments entered. Example: @CHOOSE(2,6,B9,"Income",A3) = "Income" @CLEAN(string) The @CLEAN function deletes any special control characters that may be present with the string. Example: @CLEAN(B5) will strip away any special control characters that may be present in cell B5. @COLS(range) The @COLS function returns the number of columns contained within the specified range. Examples: @COLS(A1..F10) = 6 @COLS(net Income) = 5 (where the range name Net Income contains 5 columns) @COS(x) The @COS function returns the cosine of the value argument. The cosine is the angle of the argument in radians. Examples: @COS(2) = -0.41615 @COS(C2) = 0.877583 (where the value of cell C2 is .5) @CODE(string) The @CODE function returns the ASCII code for the first character in the string. Examples: @CODE("Z") = 90 @CODE("7") = 52 @COUNT(range) The @COUNT function returns the number of non-blank cells in the specified range. If the range contains only empty cells, @COUNT will return a 0. Example: @COUNT(B1...B5) = 5 (where all cells contain entries) @CTERM(interest rate,final amount,initial amount) The @CTERM function calculates the number of periods it would take to compound an initial investment amount to some final amount given a fixed interest rate. Example: @CTERM(F1,F2,F3) = 17.65494 (where cell F1 = .20; F2 = 2500; F3 = 100) In this example, the number of periods required to compound a $100 investment into $2500 at 20% is 17.65 periods. @DATE(year,month,day) The @DATE function returns a serial value for the date specified by the arguments. The arguments must relate to actual day and month values, i.e., December would have the value of 12. The year arguments must have a value between 0 (standing for 1900) and 199 (standing for 2099). If improper arguments are entered, a value of ERR is returned. Examples: @DATE(87,12,25) = 32136 @DATE(0,1,1) = 1 You can use the Format Date commands to display these serial values in a more familiar form. @DATEVALUE(date string) The @DATEVALUE function converts a date string into a serial date value. The date string argument must be in the form of one of the LDW POWER date formats. Example: @DATEVALUE(D4) = 23919 (where cell D4 contains the date label '26-Jun-65) @DAY(date) The @DAY function returns the day of the month from the given date. The value returned from this function will always be a number between 1 and 31. The argument must be a number between 1 (the serial number for January 1, 1900) and 73050 (the serial number for December 31, 2099). Examples: @DAY(7000) = 1 @DAY(7050) = 20 @DDB(initial cost,scrap value,lifespan,current period) The @DDB function calculates the depreciation expense of an asset using the accelerated depreciation method of double declining balance. That is, the depreciation expense charged to the asset will be higher in earlier periods of its lifespan and will decrease as the asset grows older. The first three arguments are exactly the same as those for the @SLN function. The current period argument specifies which period the function should calculate the depreciation expense for. Example: @DDB(G1,G2,G3,G4) = 1114.286 (where G1 = 7800; G2 = 560; G3 = 14; and G4 = 1) In this example, the depreciation expense during the first period for an asset with an initial cost of $7800; a scrap value of %560; and a lifespan of 14 years is $1114.29. @ERR The @ERR function is often used with the @IF function in a conditional test and returns the result ERR in the worksheet depending on the result of the test. The @ERR function does not use an argument. Example: @ERR=ERR @IF(D5<6.75,@ERR,A10) @EXP(x) The @EXP function returns the value of e raised to the power of the argument (x). A value of ERR is returned if the argument is greater than 87. Examples: @EXP(10) = 22026.47 @EXP(D5) = 5.18E+21 (where the value in cell D5 is 50) @EXACT(first string,second string) The @EXACT function compares two strings in an effort to find an exact match. If the two strings match exactly, the function returns a 1 (true). If there is not an exact match, the function returns a 0 (false). Examples: @EXACT("xyz",B10) = 1 (where cell B10 contains "xyz") @EXACT("ABC","ABc") = 0 @EXACT("XYZ",D1)=ERR (where cell D1 is empty or contains a value) @EXTERNAL(filename,range) The @EXTERNAL function can be used to link two different worksheets by allowing you to reference a cell or range from another worksheet in order to return a value or string. The filename argument must include the appropriate extension (and directory, if the directory is different from that of the active worksheet) and must be specified in quotes. The range argument may be either a cell address or a range name, but must be enclosed in quotes. In the case of a range name, the value in the upper left corner cell will be returned. Examples: @EXTERNAL("Finance.LDW","C5) = "Sales" @EXTERNAL("B:\Expenses.LDW","payables") = 1500 @FALSE The @FALSE function returns the value of 0. This function accepts no argument. Example: @FALSE=0 @IF(B4=N8,@TRUE,@FALSE) @FIND(substring,string,x) The @FIND function will return the location of a substring within another string. The substring argument specifies the substring that LDW POWER should look for, while the string argument specifies the string in which LDW POWER should look. The x argument tells LDW POWER where to begin looking within the string. If you specify 0 as the x argument, LDW POWER will begin its search at the beginning of the string. Example: @FIND("win",D5,0) = 7 (where D5 contains "The brown cow") @FV(payment,interest,term) The @FV function calculates the future value of an investment. The arguments for this function are the payment per period, the interest rate, and the term or number of periods that the investment will run. The formula used to calculate the value returned by @FV is: FV = payment * (1+interest)term interest Examples: @FV(5000,.15,12) = 145008.337 @FV(D1,D2,D3) = 145008.337 (where D1 contains 5000; D2 contains .15; and D3 contains 12) @HLOOKUP(lookup value,range,row) The @HLOOKUP function performs a horizontal table lookup by comparing the lookup value against the key values in the first row of the range in order to locate a specific result in that range. If the key values are numbers, they should be arranged in ascending order with no duplicates. In order to locate a specific result in that range. The first argument, lookup value, is used as a comparison value against the key values in the range. The second argument, range, determines the range of values that will be searched. The third argument, row, tells the function in which row of the range to get the return value. Note, that the first row in the range is assigned the number 0, the second is assigned 1, and so on. The function begins by comparing the lookup value (numeric or string) with each value in the key value row. If the lookup value is numeric, the function will look until it finds a key value greater than the lookup value. It then, looks in the column immediately preceding this key value and in the appropriate row for the result. If the lookup value is a string, the function will look until it finds a key value that matches the lookup value and will look in the same column for the result. If there is no corresponding string in the key value row, the function will return the value ERR. Example: @HLOOKUP("Atari",B1..D4,2) = 23 (where is the lookup value; B1..D4 is the range; and 23 was found in the 2nd row @HOUR(time value) The @HOUR function returns the hour from the decimal portion of the argument. Example: @HOUR(.208345) = 5 (the value .208345 is equal to the time 5:00:01) @IF(conditional test,true,false) The @IF function uses the first argument, conditional test, to determine if the return value will be the second argument, true, or the third argument, false. LDW POWER allows you to use either values or labels as the true and false values that are returned by the conditional test. Examples: @IF(B1>E4,A1*.9,A1/9) @IF(C10=C11,@TRUE,@FALSE) @INDEX(range,column,row) The @INDEX function returns the contents of a cell within a range based on the column and row offsets in the range. The range argument specifies the range of cells in which the function should look. The column argument specifies which column in the range the function should look, while the row argument specifies which row in the range the function should look. The desired cell is derived from the intersection of the row and column. Example: @INDEX(B2..D4,2,1) = 2,1 (where B2..D4 is the range; 2 is the column; 1 is the row; where 2,1 was found. @INT(x) The @INT function returns the integer value of the argument (x). Examples: @INT(17.345) = 17 @INT(C3) = -7 (where cell C3 contains the value -7.68) @IRR(guess,range) The @IRR function calculates an approximate internal rate of return for an investment. The internal rate of return is defined as that rate which makes the present value of cash inflows equal to the cash outflow at the start. In other words, the rate which makes the net present value of cash flows equal to zero. The @IRR function uses the first argument, which is your approximation of the return percentage and the second argument, which is the range of cash flows for the investment being analyzed, and performs a series of iterations to return a value. The return value is a percentage which will be an approximation of the internal rate of return for the investment entered. The first value in the range must be negative in order to represent the initial cash outflow. The rest of the values should represent the cash inflows that follow the initial investment. If the guess argument is a number so far from the actual rate of return that more than 20 iterations are required to return a value, an ERR message will be displayed. Also, there are some cases where the internal rate of return may have more than one value. In this case, different initial guesses will yield different return values. Example: @IRR(B2,C2..C6) = 18.27% (where B2 is 10%; and range C1 = -400, C2 = 100, C3 = 100, C4 = 150, C5 = 300) @ISERR(x) The @ISERR function returns the value 1 (true) if the argument being tested returns the value ERR. The value 0 (false) is returned if the argument does not return ERR. If the argument entered is not a formula or a function, the value ERR is returned. Examples: @ISERR(7/1) = 0 @ISERR(7/0) = 1 @ISNA(x) The @ISNA function returns the value 1 (true) if the value of the argument is NA. The value 0 (false) is returned for all other arguments. (See @NA function.) Example: @ISNA(A1) = 1 if the value of cell A1 is NA; 0 if the value of cell A1 is not NA @ISNUMBER(x) The @ISNUMBER function tests its argument (x) to see whether (x) returns a value. If a value is found, the function returns the value 1 (true), otherwise it returns a 0 (false). If a blank cell is tested, the function will return a 1. Example: @ISNUMBER(B9) = 1 (if cell B9 contains a value) @ISSTRING(x) The @ISSTRING function tests its argument (x) to see whether (x) contains or returns a label. If a label or empty string is found, the function returns the value 1 (true), otherwise it returns a 0 (false). If an empty cell is tested, the function will return a 0. Example: @ISSTRING(G2) = 1 (where the cell G2 contains a label) @LEFT(string,number of characters) The @LEFT function extracts a specified number of characters from a given string. The function will begin at the left edge of the string. If the number of characters specified is greater than the total number of characters in the string, the function will return the entire string. Example: @ LEFT(A4,4) = "comp" (where cell A4 contains "computer") @LENGTH(string) The @LENGTH function returns the total number of characters (including blank spaces) in the string argument. Examples: @LENGTH("power") = 5 @LENGTH(G2) = 5 (where cell G2 contains the string "power") @LN(x) The @LN function returns the natural (base e) logarithm of the argument. A value of ERR is returned if the argument is zero or a negative number. Examples: @LN(10) = 2.302585 @LN(C1) = 1.609438 (where the value of cell C1 is 5) @LOG(x) The @LOG function returns the base 10 logarithm if the argument. A value of ERR is returned if the argument is zero or negative. Examples: @LOG(10) = 1 @LOG(B4) = 0 (where the value of cell B4 is 1) @LOWER(string) The @LOWER function converts the string argument to lowercase. Examples: @LOWER("David") = "david" @LOWER(H9) = "ldw power" (where cell H9 contains the string "LDW POWER") @MAX(range) The @MAX function returns the largest value from the argument range. The argument may be a selected range or a list of cell addresses. Empty cells ar ignored, however, if the range only contains empty cells, a value of ERR is returned. Examples: @MAX(2,5,1) = 5 @MAX(B3..B10,-2) = -2 (where the range B3..B10 contains only empty cells) @MID(string,starting point,number of characters) The @MID function extracts a substring from a specified string beginning from any point in the string. The starting point argument tells LDW POWER at what position to start extracting the substring, while the number of characters argument tells LDW POWER how many characters to extract. Note, that the first character in the string occupies position 0, the second character occupies position 1, and so on. Example: @MID("automobile",2,3) = "tom" @MIN(range) The @MIN function returns the lowest value in the argument range. The argument may be a selected range or list of cell addresses. Empty cells are ignored, however, if the range only contains empty cells, a value of ERR is returned. Examples: @MIN(45,74,15) = 15 @MIN(C3..D5,2) = 2 (where range C3..D5 contains only empty cells) @MIN(Sales) = 15 @MINUTE(time value) The @MINUTE function returns the minutes from the decimal portion of an argument. Examples: @MINUTE(D4) = 23 (where the cell D4 contains .015972) @MINUTE(.521354) = 30 @MOD(dividend,divisor) The @MOD function returns the modulus (remainder) of the division of one value by another. The formula for the value returned by @MOD is: dividend - (divisor*@INT(dividend/divisor)) The value of ERR is returned if the divisor is given a value of 0. Examples: @MOD(67,3) = 1 @MOD(B2,5) = 2 (where the value in cell B2 = 12) @MONTH(date) The @MONTH function returns a number for the month in a given date. The date argument must be entered as a serial value, otherwise an error will result. Examples: @MONTH(1000) = 9 @MONTH(B3) = 5 (where cell B3 contains the date value 32274 which stands for 5/22/88) @N(range) The @N function returns the contents of the upper-left cell of the specified range. If that cell contains a value or anything that returns a value, the function will return that value. If the cell is blank or contains a label or anything that returns a label, the function will return the value 0. Example: @N(C5..E10) = 4 (where cell C5 contains the value 4) @NA The @NA function returns the value NA (Not Available). Any functions or formulas that refer to a cell containing the NA value will also return the value NA. The @NA function does not accept an argument. Examples: @NA=NA @IF(F9/F8