|AÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ»ÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» |Aº |6Personal Calc|A ºÍÍÍÍÍÍÍÍÍÍÍÍÍ ^1Menu Documentation |AÍÍÍÍÍÍÍÍÍÍÍÍͺ |6Personal Calc|A º |AÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ^Cby ^CDoug Harrison Menu Documentation ================== This section discusses Personal Calc commands. For more information on selecting commands, see the Getting Started file. If you're just starting out, you may want to simply press F1 to bring up the menu bar, then use the cursor keys to highlight the command you want. Shortcuts are available for many commands, and these are listed on the pull-down menus and in the Personal Calc Menu Reference Sheet file. File ---- The File Commands let you save and retrieve worksheets. File New File New erases the current worksheet from memory and the restores default worksheet settings. If the worksheet has been altered somehow, you will first be asked whether or not you wish to save it. If you don't save your work at this point or cancel the operation, it will be lost forever, so be careful! File Open File Open loads a worksheet from disk, erasing the current worksheet (Personal Calc supports editing only one worksheet at a time). As with File New, you will have a chance to save the current worksheet if it has changed since the last save or has never been saved. Use the File Selector dialog box to move around the DOS drive/directory system and to select the file you wish to open. File Open is for loading PCW files (native Personal Calc files with the extension, PCW); to load a Lotus 123 file, see File Import/Export below, and for complete details, see Lotus 123 Files and Personal Calc at the end of this file. File Save File Save saves the current worksheet to disk, under the name with which it was loaded, without presenting the File Selector. You may overwrite the existing file, or automatically rename the previous version with a BAK extension. If the worksheet is brand new and has not been previously saved, File Save functions like File Save As. File Save As File Save As displays the File Selector and lets you save the current worksheet under a different name (or give it a name if it has not been saved previously). File Merge File Merge loads a range of cells from a worksheet on disk created with the File Save Range command. File Merge neither clears the current worksheet nor alters the worksheet configuration settings. File Merge pulls in the range of cells from the worksheet file, beginning at the location of the cell pointer in the current worksheet (overwriting this area of the current worksheet). Cell references in formulas are adjusted as if the range had been moved from its location in the original worksheet to its location in the current one. File Save Range File Save Range saves a range of cells to a disk file without saving the worksheet defaults, printer settings, and so forth. Only the cell information is saved. Use the File Merge function to retrieve a file saved using File Save Range. File Import/Export File Import/Export's sub-menu lets you load or save a file formatted according to the Lotus 123 Release 1 (WKS) or 2 (WK1) specification. In general PC provides adequate conversion and from these formats, but there are some limitations which are described in detail in the Lotus 1-2-3 Files and Personal Calc section at the end of this file. File Print File Print displays the Print dialog box for entering printing options (enter printer control codes with the Options Printer command). PC allows two title lines, which will be printed on the first page, and header and footer lines, which are printed on each page. These lines use the following system of control codes: Code Action ---- ------ &l Left-justify what follows &c Center what follows &r Right-justify what follows &d Current Date &t Current Time &f File name &p Page number && Print '&' For example, a header such as "&lp. &p&c&f&r&d, which PC will expand to something like the following: p. 1 filename 1/1/90. You may also specify if PC should print row numbers and column names, display formulas as text or values, use condensed print, and print in Draft or Final print. (Some features will only work if you correctly set the control codes using Options Printer). To change the print margins, select the Set Margins button in the File Print dialog box. The Set Margins dialog box lets you set left, right, top, and bottom margins and lines per page. Most printers use 66 lines/page (although some laser printers use 60, with a mandatory 3-line top and bottom margin). The left and right margins define the number of characters of white space on either side of the page (at 10 cpi, 1/2 inch would be 5 characters), while the top and bottom margins control the number of lines of white space separating the worksheet data from the page borders (at 6 lpi, 1/2 inch would be 3 lines). You must specify a range to be printed. If you selected a range before invoking File Print, that range specification will appear in the "Range" editable field. Otherwise, the range containing all the spreadsheet data appears in this field. You may enter a named range, if you prefer. File Save as Text File Save as Text essentially prints a worksheet to a disk file, which you may print from the DOS command line or load into a word processor. All of the File Print options are available except Draft vs. Final. File DOS Shell If there is enough free memory, File DOS Shell invokes a copy of COMMAND.COM, so that you may use the DOS command line without permanently leaving PC. (It is still a good idea to save your work before invoking File DOS Shell, just in case.) Type EXIT at the DOS prompt to return to PC with your worksheet intact. PC first searches for COMMAND.COM using the SHELL and then the PATH environmental variables (see the DOS manual for details). File Quit File Quit exits Personal Calc, returning you to DOS. PC will ask if you want to cancel or save if the current worksheet has changed since the last save. Edit ---- The Edit commands let you rearrange cells on the worksheet. The commands are all range-oriented, and if no range is already selected, PC considers the active cell a one-cell range. Edit Copy Edit Copy copies ranges from one location to another. Select the source range to copy, enter Copy mode, select the destination range for pasting (in most cases, simply move the cell pointer to the upper-left corner of the destination), and press Enter (or choose Edit Paste). Since PC remains in Copy mode after pasting the range, you can copy the range to several locations. You may also make several copies of the source range to a single destination range (if the numbers of rows and columns for the destination are even multiples of those for the source). PC displays the sizes of both ranges in the cell reference area. To exit Copy mode, press Esc. Edit Cut Edit Cut is similar to Edit Copy, but Edit Cut is for moving a range. In Cut mode, and as with Copy mode, you move the cell pointer to the upper-left corner of the destination range and press Enter (or choose Edit Paste) to complete the operation. Edit Cut only allows one Paste, and no multiple copies within a destination. Edit Paste Edit Paste completes Edit Copy and Cut, and is only available in Cut or Copy mode. Pressing Enter is the same as selecting Edit Paste. Adjusting Cell References Whenever you copy or move a range containing formulas, or move a range that contains cells that are referenced by formulas, it is necessary for PC to make certain adjustments to cell references. For example, you may have 10 cells in column B that refer to the cells immediately to their left. So in cell B1, you enter =A1+1. Instead of entering this formula manually in cells B2-B10, you would place the cell pointer in cell B1, choose Edit Copy, select the range B2:B10 (or B1:B10), and choose Edit Paste. PC would copy the formula in B1 through the range, such that B2 would contain =A2+1, B3 would contain =A3+1, and so on. This saves time and reduces errors. When you move a range, it is often desirable for cell references to point to the same cells after the move. Cells outside the source range referring to cells inside the source must be adjusted to refer to the destination range. Conversely, cells inside the source range that refer to cells outside the source range should continue to point to those cells; they should not be adjusted as with Edit Copy. Absolute and partially-absolute cell and range references defeat these rules for copy operations only, since there is no distinction between relative and absolute cell addressing for move operations. Edit Insert Edit Insert inserts rows and columns into the worksheet, shifting cells either down or right, respectively. Edit Insert assumes partial insertion, which is a major difference which 1-2-3 users should note; you may insert whole rows and columns or confine the insertion to a certain range of cells. To insert one row of cells at row 10 limited to columns B-E, select the range B10:E10, then choose Edit Insert, and select Shift Cells Down. To have inserted two rows over the same range, you would have extended the range to B10:E11. Inserting columns is like inserting rows, except that column widths are appropriately adjusted. The ability to partially Edit Insert is important to understand, since many spreadsheet programs do not provide partial operation; they limit insertions to entire rows and columns. If you are used to this limitation, remember that with Personal Calc you must extend the selection to encompass the entire row or column to insert whole rows or columns (use Ctrl-Space and Shift-Space). PC will not allow you to shift cells containing data beyond the worksheet borders; this protects against data loss. For example, if cell A999 contains a value, PC will not allow insertion of a row in column A. Edit Delete Edit Delete is similar to Edit Insert. It deletes the cells involved in the selection, shifting the remaining cells up or to the left. Since both Edit Insert and Edit Delete are really range move operations, cell references are adjusted as described above for range moves. Format ------ The Format Menu options control the appearance of the worksheet and cells. These options all operate on a worksheet range, and if no range is selected, they consider the active cell a one-cell range. Except for Column Width and global operations, these functions allocate memory for empty cells within the range. Thus, the size of the range you can format is limited by available memory. To make a global change in the worksheet format (changing the default format), select the entire worksheet with Ctrl-Shift-Space. This is a special case; no new cells will be created; only existing cells will have their formats altered. Format Column Width Columns widths range from 2 to 72 characters. Enter the desired width into the editable field and select OK. Format Justification Format Justification displays a submenu allowing you to choose among left, center, and right justification for text cells. Numeric and formula cells may only be right-justified and are not affected by this function. Cells which are left-justified may "spill over" into adjacent cells to the right (providing they are empty). If a centered or right-justified cell contains text longer than the column width, the text display will appear truncated at the cell margin. Format Number Format Number invokes a submenu from which you choose formats for numeric and formula cells. General format displays numbers in integer form unless there is a fractional portion, in which case it eliminates trailing zeros. General format uses scientific notation when necessary, and if a number still will not fit in the display area of the cell, PC will fill the cell with pound signs (#). (In fact, PC displays pound signs whenever the number will not display properly in the selected number or date/time format.) All number formats other than General let you set the Precision (the number of digits to follow the decimal point). For all number formats other than General, holding the Shift key as you exit the menu with Enter will invoke the Precision dialog box (otherwise, if you want to alter the precision, you have to invoke the Precision function separately). Number formats other than General round to the specified precision, but use the full 14-digits of precision for calculations. Only General format suppresses trailing zeros. All formats, including General and Date/Time, append a blank space to the number, and numbers and formulas may only be right-justified. Fixed format lets you specify between 0 and 14 digits to follow the decimal point. For example, Fixed 4 displays 0 as 0.0000 (always four digits following the decimal point). Scientific format is like Fixed, except that numbers are displayed in scientific notation (as a number between 0 and 10, and a power of ten). For example, Scientific 3 would show 123456 as 1.235E+005. Currency format displays commas separating thousands, prefixes a dollar sign, and displays negative numbers in parentheses. Comma format is identical to the Currency format, except that it omits the dollar sign. Percent format multiplies the number by 100 for display purposes only and appends a percent character. Thus, a cell containing .1 would display as 10%; as expected, .1 would be used in calculations. Format Date/Time PC provides nine Date/Time formats, which are intended to display the serial numbers used by the date/time functions. Format Date/Time invokes a submenu showing the formats. The symbols used are as follows: Symbol Meaning d day number m month number mmm month abbreviation yy last two digits of year h hour mm minutes ss seconds A/P,AM/PM When used, am/pm notation. Otherwise military time is used. Format Precision Format Precision lets you specify between 0 and 14 digits to follow the decimal point (for display purposes only) for all number formats except General. See Format Number for more on this function. Format Hide Format Hide lets you hide cells from view and unhide cells which are hidden. Although hidden cells aren't displayed in the worksheet, their contents appear in the formula bar and may be edited as with unhidden cells. Format Protect Format Protect lets you protect cells from being edited; functions like Edit Paste that would affect protected cells, will only execute after PC has informed you and given you the option to cancel or proceed. Range ----- Range Data Fill Range Data Fill is useful for filling a range of cells with a series of equally-incremented numbers, including dates. First highlight a range (remember to press Enter, if needed). Select Range Data Fill (or Ctrl-F), choose the fill direction (right or down), indicate the type (number or date code), and if needed, mark the date unit (see below). Enter the starting value and the increment value; PC adds the increment to a running total (which begins with the start value) and places these values in the cells. For a date series, enter the starting date in "m/d/yy" format and an integer increment. Set the fill type to Date, and choose one of the Date Units: Day, Weekday, Month, or Year. PC will then fill the range with a set of serial date values. For example, to fill a range with dates beginning 1/1/90 with two-week intervals, enter 1/1/90 into the start field, 2 into the increment field, select Date as the type, and Weekday as the Date Unit. Select OK, and PC will fill the range. Use a date format to display the numbers as dates. Range Sort Use Range Sort to sort a range on a single key by rows or columns. The key cell indicates the row or column that will be used for comparisons. Enter the range (if you have not already), choose Ascending or Descending) and By Row or By Column, the select OK to perform the sort. Cell references are not adjusted during sorting, and the worksheet is NOT recalculated, even if Automatic is in effect (this is the only time you will see the "Calc" indicator in Automatic mode). Press F9 to recalculate the worksheet. Range Define Name PC lets you define up to one hundred named cell and range references. You may use these names in formulas, where they behave exactly like regular cell references (including adjustment for copying/moving). You may also enter them into any dialog box field that requires a range specification. Names may be made up of alphanumeric characters and the underscore; they may not begin with a number or be the same as function names or cell addresses. To enter a name, select Range Define Name from the menu. Next enter the text of the name (for example, SALES_89) and enter the reference into the "Refers to:" field. The reference may be a single cell or a range, and may use absolute reference notation (in fact, that is the default). Select OK; if the name and reference are valid, they disappear from the editable fields and appear in the list. From that point on, the formula bar uses the name for references in formulas that match it. Range Create List Range Create List helps you document your worksheets. It creates a two-column vertical list at the cell pointer location (and in the column to the right), showing range names and their references. Range Goto Use Range Goto to quickly move to any cell in the worksheet. If names are defined, you may select one from the scrollable list in the Range Goto dialog box. If you goto a range, then PC will select the range and place the cell pointer in its upper left cell. Select the Home button to return to cell A1. Options ------- Options Calculate This function recalculates the worksheet. Options Automatic Calculation This function toggles Automatic Calculation on and off. Options Natural Order This function toggles Natural Order calculation on and off. When disabled, PC uses row by row calculation. Options Freeze Titles This function makes it possible to keep row and column headers on the screen while scrolling through a large spreadsheet. You may freeze only the columns at and above the cursor, only the rows at and above the cursor, or both. To freeze both, for example, place the cursor at the lower-left intersection of the row and column headers (a row above and a column to the left of the upper- left of the data area) and press Ctrl-T (or Alt-O T), then B for both and Enter to confirm. Options Statistics Options Statistics displays a dialog box containing several pieces of important information: the file name of the current worksheet (without the extension), the number of allocated cells, number of cells of each class (values, labels and formulas), the address of a circular cell reference (if any, and only if Natural Order is enabled) and the amount of free memory. Exit the dialog box by pressing Esc. Options Printer Options Printer displays a dialog box for entering printer control codes, the number of characters per line in normal and condensed print, and the PC port to which the printer is attached. Refer to you printer manual for the control codes; enter them as decimal numbers in the range 0-255 separated by commas. Initialize is the string sent to the printer before every print job; Draft turns on draft quality mode; Final turns on final quality mode; and Condensed turns on condensed print mode. For condensed print to work properly, you must enter the correct number of characters per line in the Chars/line Condensed field. Make sure the value for Normal print is correct as well. After entering these values, you may exit the dialog box by pressing OK and do some test printing. Once you are satisfied that everything's correct, you can save a file called PRINTER.INF by choosing the Save button in the Options Printer dialog box. PC will read this file upon subsequent sessions, restoring these parameters to the values you saved. Options Worksheet Options Worksheet lets you save your current configuration to a file called PC.CFG, which PC reads file at startup. PC uses this file to set the configuration for the following: calculation modes, printer titles, header/footer, margins, draft/final, row/column headings, condensed print, default format and justification, and default column width. Help ---- The Help menu contains a variety of topics for online reference. In order to provide help, PC must be able to access the Help file, so make sure PC.HLP is located in the same directory as PC.EXE. Use PageUp and PageDown to view the Help file. Lotus 123 files and Personal Calc --------------------------------- To load a 123 file, select File Import/Export. A pop-up menu will appear. Click on Load WKS/WK1 and choose a file with the file selector. The process of saving Lotus files is similar. PC was never intended to be a Lotus 123 clone; therefore, not every Lotus concept is supported. Print ranges, sort ranges, regression ranges, and other concepts have no parallels in PC, and are ignored when reading Lotus files. PC does not include them when writing Lotus files, either. Here's a list of what PC does support: automatic recalculation flag, recalculation order mode (PC does not support column by, column recalculation, so this is limited to natural and row by row), named cell (123 Release 2 only) and range references, 123 window 1 definition (includes frozen rows/columns, cell pointer position, etc. 123's second window isn't supported), column widths, default format, most 123 formats are converted into equivalent PC formats, cell protection and hiding, blank, numeric, label, and formula cells, 123 macros are useless to PC; however, they are just labels and will thus be imported and exported without change. When writing a Release 1 file, PC converts any single-cell named references to equivalent range references, since 123 Release 1 only supports named ranges. 123 doesn't support absolute named references (at least in the actual definition), so this attribute is lost when writing 123 files. Also, when recreating formulas for display, 123 matches named references to explicit references, disregarding any absolute attributes of the explicit references. PC takes this distinction seriously, so 123 named references may not match formulas imported into PC. (123 named reference ABCD = A1 does not match $A1+1 when imported into PC). This has no effect on formula evaluation, moving cells, and so forth. 123 allows you to specify a range in which the first range endpoint points to a cell above or to the left of the second one. PC doesn't allow this, as it can lead to very creative errors, so PC will reverse the endpoints of such references. This applies to formulas as well. This has no effect on formula evaluation. PC is forgiving when translation errors occur; instead of crashing, it will display an alert indicating the cell in which the error occurred, and you will have the option to abort or continue. Two errors are possible: trying to load a cell outside PC's boundaries, and formula translation error. When PC is unable to load or save a 123 formula, the cell is converted to a either a numeric or label cell whose value is the current formula value. Labels are created for 123 Release 2 formulas returning string values. The actual formula information is lost. String constants in 123 Release 2 formulas will always cause a translation error. PC supports the following 123 functions: All mathematical operators (+, -, <=, etc.) 123's ^ operator is converted to PC's POW function @ROUND, @MOD, @ABS, @INT, @ROWS, @COLS @SQR, @EXP, @LN, @LOG @SIN, @COS, @TAN, @ASIN, @ACOS, @ATAN, @ATAN2, @PI @SUM, @AVG, @VAR, @STD, @COUNT, @MAX, @MIN, @RAND @IF, #AND#, #OR#, #NOT#, @TRUE, @FALSE, @ISNA, @ISERR @NA, @ERR @VLOOKUP, @HLOOKUP, @INDEX, @CHOOSE @PV, @FV, @TERM, @PMT, @RATE @DATE, @TIME, @YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECOND, @TODAY, @NOW When working with 123 files, you need to remember that some PC functions use a different number of arguments, a different convention in sign or magnitude, or the arguments occur in a different order. In general, PC completely accounts for these functions, but under some scenarios, there may be side effects: #AND#, #OR# 123 uses these in expressions like A1<2#AND#A2>A1; PC would convert this to AND(A1<2,A2>A1). However, 1#AND#2#AND#3 would be converted to AND(1,AND(2,3)). The "inner AND" isn't necessary, and you might want to change it by hand. For safety's sake, PC will enclose the native AND and OR functions in parentheses when writing a 123 file. This is necessary because these are not functions in 123; they are operators. To appreciate the distinction, consider the PC formula AND(1,2,3)+1. Without placing #AND# in parentheses, it would be translated to 1#AND#2#AND#3+1 in 123. The last #AND# clause is 3+1, which completely alters the meaning of the formula. The proper translation is (1#AND#2#AND#3)+1. Also, AND(1,OR(2,3),4) will be correctly converted to (1#AND#(2#OR#3)#AND#4). Exponentiation PC performs this task through the POW function, while 123 defines the ^^ operator. PC correctly converts functions such as POW(A1+2,A2+3) to (A1+2)^^(A2+3). However, 123 has a rather serious fault in that it assigns the unary minus (negation) operator a lower precedence than the exponentiation operator. This means that the 123 function -2^^4 evaluates to -16; in other words, the minus sign is not part of the number 2 but rather is an operation to be applied to the expression 2^^4. This is a particularly difficult problem to completely account for; fortunately, it is limited to writing 123 files. If you are using the minus sign to negate the first argument of POW, and this argument is a number, enclose the minus sign and number in parentheses. That is, instead of writing POW(-2,4), write POW((-2),4). PC will place parentheses around the argument if it is an expression, such that POW(-A1,4) becomes (-A1)^^4. @STD, @VAR 123 computes population standard deviation and variance, while PC provides a choice between sample and population statistics. PC will convert the 123 functions to SDEVP and VARP, but it will report an error if you try to save the sample functions (SDEV, VAR) to a 123 file. Financial functions 123's financial functions take fewer arguments than PC's; moreover, 123 does not follow the convention designating cash inflows as positive numbers and outflows as negative. PC adds the proper arguments and adjusts the sign of an appropriate argument when necessary. The net effect is to preserve the sign and magnitude of the function value. For example, the payment argument in @PV needs to have its sign changed. If it's a number or cell reference, PC will simply change its sign. However, if it's an expression like "A2+3", PC will change it to "-(A2+3)." Notice that PC correctly enclosed the expression in parentheses before making the argument negative. When you save the file, PC will change the sign again. All 123 financial functions supported by PC are converted to equivalent PC functions when importing a 123 file. However, the reverse is not necessarily true. PC differentiates between ordinary annuities and annuities due. 123 only knows about ordinary annuities, so if you are actually using annuities due, the distinction is lost upon writing a 123 file. Furthermore, @PV takes only 3 arguments in 123, omitting the future value argument supported by PC. Thus, if the value of this argument is anything but 0 in PC, the function will mean something different in 123. Finally, @RATE in 123 is limited to compound interest calculations. PC's RATE function, however, fully supports annuity calculations in addition to simple interest. Thus, any PC RATE function dealing with annuities will mean something different in 123, as the additional information is lost in the translation. You won't be warned of these errors, so please guard against them. Lookup Functions The index arguments for these functions all suffer an "off by one" error. Under Lotus, index begins at 0, whereas under PC, index begins at 1. Thus when reading a 123 file, PC needs to add one to the index argument. If this argument is a number, it will simply be incremented by one. However, if it's anything else, PC will create a new expression of the form "old-expression+1". Similar transformations are made when writing 123 files. After multiple read- write cycles, you might find yourself with an index argument that looks like "A1+1-1+1-1...". Watch out for this; it doesn't hurt anything, but it is confusing. One final thing to keep in mind is that apparently not all 123 Release 2 functions are supported by Release 1. Since the Lotus file format documentation omits this information, PC does no checking while writing Release 1 files for functions that Release 1 may not support. So, keep this in mind if you are using a Release 1 type spreadsheet; use only those functions that your spreadsheet supports.