XESS Spreadsheet - Version 5.0

Overview of XESS Spreadsheet

The XESS spreadsheet application is designed specifically for users who want familiar spreadsheet functionality and ease-of-use right on their X Windows platform without any emulators! By using the intuitive XESS user interface, you can easily access its powerful computational, reporting, and graphing tools.

You need only a few keystrokes or mouse clicks to analyze your data and then present the results with graphs ranging from simple line plots to 3D surfaces. You can create attractive reports just as easily. And you can just as easily publish them as web pages.

The XESS spreadsheet provides a flexible and comfortable visual environment in which to handle simple or complex calculations and data manipulations and anything in between. It calculates a full range of mathematical, statistical, matrix and string functions all with the ease of a familiar spreadsheet format with point-and-click screen displays and pull-down menus.

XESS 5.0 is fully upward compatible from XESS Version 4 (Xess4). References to XESS throughout this document refer to any version of the XESS spreadsheet. Things which are applicable to a specific version of the XESS spreadsheet or its environment are qualified as such.

The XESS product line includes spreadsheet products for most Linux, UNIX, OpenVMS, and Windows NT systems. Optional products provide additional capabilities, including a developer's SDK for tightly integrating with other applications, the xsBasic Macro Add-in facility, and industry-specific applications.

Computational Functions

Other features of XESS calculations include:

• lots of room for your data and calculations XESS supports up to 10 million rows, 4096 columns, and 4095 sheets.
• constraint expressions that indicate when a cell value is outside predetermined or calculated boundaries.
• goal-seeking to calculate the value of an independent variable needed to achieve a desired objective.
• sorting data rows in ascending or descending order, numerically or alphabetically.
• powerful Find and Replace tools that quickly identify occurrences of data that meet specified criteria and optionally replace with different values.
• broad range of mathematical, statistical, and financial functions.
• extracting data based on certain criteria to create internal data aggregates.

Supported File Types

XESS directly creates print files in two formats for printing later or incorporating into other documents: ASCII or PostScript. This gives you the freedom to use the format best suited for your application. Filters are provided which support print preview and printing to non-PostScript printers (e.g. PCL).

The PostScript output format gives you flexibility of producing WYSIWYG output, with the printed output matching the chosen font styles and sizes, graphs, and images on the display. The PostScript Files created by the XESS Print operation are widely accepted into other documents.

You can also produce output in LaTeX or HTML formats for use in other applications or for display using browser technology. You can create area, bar, stacked bar, line, scatter, X-Y, polar, histogram, pie, high-low, box, control, contour and surface graphs of the data in your spreadsheets, with user-selected formats, colors, scaling, axis characteristics, labeling, and annotation.

Using XESS

XESS was designed in accordance with Open Software Foundation, Inc. style guidelines for OSF/Motif software applications. Therefore, the look and feel of XESS should be familiar and intuitive to anyone who has used OSF/Motif applications before. In fact, OSF/Motif conventions such as pull-down menus, scroll bars, list boxes, pop-up dialog boxes, buttons to click on and off, and multiple windows should look familiar to anyone who has used a windows type program, including those that operate on Apple or IBM-compatible personal computers.

XESS is designed to be easy to learn and easy to use:

• Operations are grouped by function in clear, concise, pull-down menus. Making a menu selection is as easy as clicking a selection or pressing a single key or key combination on your keyboard.
• Frequently used pull-down or pop-up menus (e.g. the Cell Formats list box) can be separated from the activation menu button so that they remain on-screen until you explicitly close the window or exit the application.
• The look and feel of operations is consistent throughout the program. When you see how one dialog box works, you understand how all dialog boxes work. The interface is as invisible as possible; all you need to concentrate on is the work to be done, not how to get it done.
• If XESS needs more information to carry out an operation, it prompts you with messages, pop-up dialog boxes, and list boxes. At every step, it is clear what you need to do to complete the task.
• If you make a mistake, XESS tells you. Whereas some spreadsheets beep unintelligible warnings, XESS gives error messages in descriptive terms that actually help you solve the problem.
• An Undo feature lets you restore the sheet and recover from mistakes. Multiple levels of Undo are supported.
• A Redo feature lets you undo the last undo.
• Help for XESS operations and functions is only a keystroke away including a comprehensive Help Search facility.

Major Features of XESS

Accessing Workbooks

• Create or open any number of XESS workbooks in a given session, limited only by the room on your screen and your system's memory. Of course, you can open, save and rename existing XESS workbooks.
• Open native workbook/spreadsheet files from Excel and Lotus and import data from text files arranged in tabular format or with values separated by commas or user-specified delimiters.
• Dynamically link XESS workbook cells to cells in other XESS workbooks (saved on disk or opened concurrently). You can even establish links in an XESS workbook which point to specific cells in Excel and Lotus files residing on disk.
• Use any of six convenient methods for moving around the spreadsheet: the keyboard, the mouse, the scroll bars, the sheet tabs, and the Go To and Find operations.
• Use keyboard and one-character mnemonics to access frequently used menu selections, streamlining your work.
• Create multiple views of your workbook data, each in a separate window. Move intuitively among the views (windows) while performing spreadsheet operations such as selections.
• Lock title rows and columns that stay in view as you scroll through a large spreadsheet or between sheets in a workbook.

Manipulating the Workbook

• Copy and move formulas and values anywhere within the workbook with automatic formula adjustment, and easily cut/copy/paste data to and from other X Windows programs visible on your display as well as within the workbook.
• Select cells and ranges with simple mouse or keyboard actions, or with the Select Range and Select All operations.
• Clear the contents of a cell, range of cells or the entire active sheet with a few simple actions. Delete rows and columns at any time, including the data they contain. All formulas automatically adjust accordingly.
• Assign and use names rather than cell coordinates for cells and ranges. Once assigned, you can use these names anywhere a traditional cell reference is accepted.
• Undo multiple levels of any of the above with a single keystroke or mouse click. Redo the last Undo. Interacting with Other Applications
• Import, export, and paste data to and from other XESS workbooks, transferring data and formatting.
• Import formatted data from other programs, such as spreadsheet applications, tabular text reports, and user-defined delimited files. Export data to other programs in ASCII, XLS, WKS, WK1, WK3, LaTeX, PostScript, HTML tables, tab-separated value, comma-separated value formats, or user-defined delimiter.
• Establish dynamic (live) links with specific cells in Excel and Lotus workbook/spreadsheet files saved on disk such that each linked cell value is automatically retrieved when the XESS workbook is first opened and with each XESS calculation.

Functions and Tools

• Use the Go To operation to position to a specific point in the workbook or use the Find operation to locate cells containing desired values or meeting specific criteria.
• Use the Replace operation to selectively or globally modify cell contents in a workbook which meet specified criteria.
• Sort rows of data in ascending or descending order, naming the primary column on which to sort, and if necessary, naming secondary columns to use when any of the values are equivalent.
• Use the Transpose tool to transpose blocks of data (switch rows to columns) with various options.
• Assign or remove cell protection to cells and ranges. When enabled for the workbook, you may select from three levels of cell protection relaxed (min) to rigid (max) which prohibits any user-made changes to the contents of protected cells. (Cells can still be changed upon recalculation.) Completely remove cell protection easily with the Unprotect operation.

Defining the Appearance of the Spreadsheet and Display

• Set defaults for language, currency, decimal point, and thousands separator from your workstation's Locale setting.
• Select default characteristics that affect the entire workbook, such as the size and position of the spreadsheet, display colors, cell format and fonts, and column width.
• Assign different characteristics to selected sheets and cells or ranges, such as bold type for the totals row, a date format for a column of dates, or a wider column to accommodate large values.
• Completely hide rows and columns.
• Assign colors, patterns, and font characteristics (point size, family, etc.) to specific cells or cell ranges on a sheet in a workbook.
• Change the alignment and orientation of cell contents at will: left-justified, right-justified, centered, or center-across-selection horizontally, vertically, up, down.
• You can also select from many cell formats, including scientific notation, U.S. and International currency, date/time formats and others.
• Change grid line colors or remove altogether.
• Add Cell Notes and Cell Borders to specific cells or cell ranges on a sheet in a workbook.
• Include external graphical images in a wide variety of formats in your XESS spreadsheet or use the Overlay Editor to create graphical overlays which can be anchored to a location on the display or to a cell.
• Tailor the display using Display Options to further customize the XESS appearance such as removing row/column buttons.
• Set your own personal preferences for session and workbook defaults.

XESS Calculations and Functions

• Take advantage of natural, double-precision calculation, constraint-checking, and user-definable recalculation options.
• Use the sum button on the tool bar to insert quick calculations.
• Add criteria to calculations to check for valid results (constraint checking).
• Support all the arithmetic, conditional, and boolean operators available in the C programming language.
• Provide a wide variety of mathematical, statistical, conditional, string, and logical functions, designed specifically for scientific and engineering needs.
• Use special matrix functions that permit matrix, vector and Fourier transform operations.
• Perform iterative calculations.
• Perform "goal-seeking", deriving the value of an independent variable required to reach a stated objective for another cell.

Creating Graphs and Reports

• Create print files in ASCII and PostScript formats, to be printed later or imported into other programs that recognize these formats. Filters convert the PostScript output for printing on non-PostScript printers (e.g. PCL, HPGL).
• Use numerous page layout options to create nicely formatted reports and preview before printing.
• Create area, bar, stacked bar, line, scatter, X-Y, polar, pie, histogram, high-low, box, control, contour, and surface graphs of the data in your spreadsheets. You can customize the graph to suit your application, defining such options as axis labeling and scaling, colors, line and marker patterns, titles, legends, and annotation.

How to Get Help

• on-line help, which displays a concise summary of specific functions at the touch of a button,
• descriptive error messages, which provide information to help you correct the error.

Using On-Line Help

• Help>Index is a complete help system with a multi-level list of topics, "hot" links to related topics, and a keyword search facility.
• Help buttons in each dialog link provide information on the function and options of the dialog.
• Tips are displayed in pop-up windows when the mouse cursor is placed over a toolbar button.
• Help on gives information about the elements of the display windows.

To select a help topic:

1. Select Index... from the Help menu. XESS displays the XESS Help dialog. Click on a topic in the Help Topics list that is shown with a page icon to view the help on that topic. When you click on a topic shown with a closed-book icon, the "book is opened" and topics within that book are shown and the closed-book icon is replaced with an opened-book icon. Clicking on this icon "closes the book".
2. In the XESS Help dialog box you will be able to read about the topic you selected. If necessary, use the scroll bars to view all the information about the topic.
3. Any keywords or phrases that are highlighted in the help text are links to other on-line help topics. Simply click on these keywords to go directly to the corresponding help topic. To return back to where your were, click the Back button.
4. Use the Search option of the Help dialog to locate topics which relate to the keywords you specify. Words that must be found in the topic should be preceded by a plus (+) character; those that must not, by a minus (-). The result of the search operation is a list of topics displayed in the Search Help File dialog. Click on these topics to view the related help. The topics that best match your query are displayed first in the list.
5. Click Cancel to dismiss the XESS Help dialog box

Through the Help menu, you can get information on:

• general system functions and menus,
• the current window,
• special keys and mnemonics,
• all the subjects available in the Help index, and
• using the Help facility.

The Help dialog can be resized to display more of the help at one time. The relative size of the help area versus the help topics area can be adjusted by dragging the indicator between the scroll bars. You can also get context sensitive help while performing an operation that uses a dialog box.

To get help from a dialog box:

1. Select an XESS operation, for example, select Save As from the File menu.
2. XESS displays the Open Sheet dialog box.
3. Click Help. XESS displays the Help dialog box. The dialog box contains information about opening workbooks. Use the scroll bars to view all the information.
4. Click Cancel to dismiss the XESS Help dialog box and resume the Save operation.

The help file is formatted as HTML which makes it possible to access on-line help outside XESS with a standard HTML browser such as that provided with Netscape.

Help is also available using the Help on feature or the [?] toolbar icon. First click Help on or [?], and then click on the feature of interest. XESS then displays a pop-up description of the feature. These pop-up features, tips, are automatically displayed when the cursor is over a toolbar icon.

Error Messages

For example:

The message:
       Error:  Goal Seek - variable cell must be a constant
means:
       The independent cell to be changed during Goal Seek 
       must not contain a formula.

XESS displays error messages on the Message Line at the bottom of the primary spreadsheet display. A full list of XESS error messages and values and what they mean is contained in Appendix C of the User's Guide.

Primary Spreadsheet Window

Components

• menu bar
• toolbar
• edit line
• spreadsheet data area
• select all button
• row and column borders
• scroll bars
• sheet tabs and sheet tab scroll buttons
• message line

You can control which of these are actually being displayed by using the Display Options dialog from the Options menu.

There is a primary XESS display for each open workbook.

Main Menu Bar

If you open multiple workbooks within the same XESS session, each workbook will be attached to its own main menu.

• The File menu contains operations that read or write files to disk or other devices, link external graphical images to specific cells, and print.
• The Edit menu contains operations that modify cells in the spreadsheet.
• The Search menu contains operations for locating cells based on their values, and for replacing values.
• The View menu contains operations associated with navigation and creating new views.
• The Format menu has operations for changing the cell format and appearance for a cell or a group of cells and Cell Notes.
• The Tools menu includes special functions such as Sort, Transpose, Goal Seek, and Overlay Editor.
• The Options menu contains operations that define default options for spreadsheet functions, appearance, and locale settings.
• The Graph menu contains operations for defining the appearance of a graphic display of spreadsheet data and displaying a graph.
• The Help menu gives you access to on-line help.

Toolbar

• New
• Open
• Save
• Print
• Cut to Clipboard
• Copy to Clipboard
• Paste from Clipboard
• Undo
• Redo
• Cell Format Type
• Cell Decimal Places
• Summation
• Bold toggle
• Italic toggle
• Underline toggle
• Left alignment
• Center alignment
• Right alignment
• Line graph
• Bar graph
• Overlay Editor
• Help on...

To display or remove the toolbar:

1. Select Display Options from the Options menu to display the dialog box.
2. Click the Toolbar check button on to display the toolbar or off to remove.
3. Click Apply or OK to complete the operation.

Edit Line

• viewing the full contents of the current cell, and
• entering and editing cell contents

Data Area

For sheets larger than the window, XESS shows only part of the sheet on screen at a time. To access other areas, you can scroll through the sheet using the scroll bars or the arrow keys on your keyboard. All components of the Data Area may be selectively removed from the display using the Display Options dialog.

XESS spreadsheets are a computerized version of a ledger pad, with information stored in a row-by-column table of cells. A group of sheets which work together and are saved in a single disk file is called a workbook. The cells in a sheet may contain several types of information:

Text

Text entries are useful for labeling columns and rows, for including comments about data values being calculated, and for managing textual data such as lists of names and addresses. A text string may be up to 4095 bytes. XESS accepts any character defined for your locale. By default, this is the ISO 8859-1 (Latin-1) character set.

Numbers

XESS treats numeric entries as constants, which you may change by editing the cell but which are not changed when the workbook is recalculated.

Dates and Times

XESS accepts date and time entries in several formats and displays these in the format you choose. You can perform calculations with dates and times. XESS supports both 1900 and 1904 date systems for compatibility with other spreadsheet products.

Formulas

Formulas are the backbone of the spreadsheet, defining and calculating mathematical relationships between elements of the spreadsheet. XESS formulas can calculate with numbers, text, logical values, cell references, and other formulas.

Graphs

Graphs created in XESS can be stored directly in a cell in the sheet. Once stored in the sheet, standard operations can be used to change its cell location.

Images

Images created outside XESS may be included in the body of a sheet by establishing a link in the cell where you want the image to be displayed. Several image formats are supported. Refer to File->Link for more details.

A Cell Note can also be associated with each cell, but is not considered as the value of the cell. A cell with a cell note is displayed with a small dot in its upper right corner. When the mouse cursor is positioned over the cell, the note is displayed in a pop-up window.

Overlay Objects can be drawn and displayed on top of the Data Area. As an option, they can be positioned relative to a cell and will scroll in the display area along with the associated cell.

Column Border

Row Border

Select All Button

Sheet Border and Sheet Tabs

Sheet Tab Scroll Buttons

Message Line

Multiple Views

Each secondary window can be sized, navigated, and manipulated independently using the main menu bar along with standard scrolling and window management facilities.

To create a secondary data window:

1. Select Create New View from the View menu.

1. Select Cancel from the view window.

Display Options Dialog

Working with the Keyboard and the Mouse

Using the Mouse

To move the mouse pointer (which usually appears on screen as an arrow), move the mouse across your desktop or mouse pad. To move the mouse pointer further than your mouse area allows, lift the mouse off the surface and reposition it.

The mouse has two or three buttons. Most XESS operations require only the left button (MB1). The right mouse button (MB3) is used to activate a short-cut dialog. On a three-button mouse, the middle button (MB2) is the paste operation. On a two-button mouse, you simulate MB2 by simultaneously clicking MB1 and MB3.

Mouse Actions

Point

Move the mouse pointer to a specific area of the screen.

Click

Quickly press and release the left button.

Double-click

Quickly press and release the left button twice.

Hold down

Hold down the left button while you perform some action, such as moving the mouse.

Drag

Press the left button and hold it down while you move the pointer.

Release

Release the left button after dragging.

Select

Hold down the left left mouse button and drag it over the area to be selected.

Paste

Click the middle mouse button to copy a selection to a new location.

Using the Keyboard

XESS provides two faster methods for many operations:

• keyboard accelerators execute menu selections with simple key combinations, whether or not the menu is displayed.

  A keyboard accelerator is a key or key combination that invokes a menu selection without displaying the menu. Not every menu selection has a keyboard accelerator. Those that do are marked with the accelerator key or key combination on the menu.

• mnemonics execute menu selections with a single keystroke while that menu is displayed.

  A mnemonic is a single character that provides a shortcut for making selections from the keyboard. Mnemonics are usually the first character of a command and are always marked on the menu with an underscore. While a menu is displayed, simply press the mnemonic character.

Keyboard Accelerators

Many keyboard accelerators are formed by holding down the Ctrl key or the Meta key while pressing another key. This type of action is documented as Meta + key or Ctrl + key where key is the name of the key that is pressed. Depending on the system, the [Meta] key on your keyboard may be labeled [ALT], [Compose], [Meta], or be represented with a diamond.

The definition of keyboard accelerators can be changed by modifying the XESS resources. The ones documented below are the defaults.

XESS Keyboard Accelerators and special function keys are shown in the table below:

Delete

Clear

Ctrl + a

Select All

Ctrl + b

Bold Font

Ctrl + c

Insert Column

Ctrl + d

Redo

Ctrl + f

Copy Formula

Ctrl + g

Go To

Ctrl + i

Italic Font

Ctrl + m

Move

Ctrl + n

Normal Font

Ctrl + o

Open Sheet

Ctrl + r

Insert Row

Ctrl + s

Save

Ctrl + t

Insert Sheet

Ctrl + u

Undo

Ctrl + v

Copy Value

Ctrl + C

Delete Column

Ctrl + F

Format Range

Ctrl + I

Bold-Italic Font

Ctrl + R

Delete Row

Ctrl + T

Delete Sheet

Ctrl + V

Create new view

Ctrl + Space

Select

Ctrl + Meta + Space

Reselect

Meta + a

Automatic Alignment

Meta + c

Center Alignment

Meta + i

Center-over-selection Alignment

Meta + l

Left Alignment

Meta + p

Print Sheet

Meta + q

Quit

Meta + C

Close Sheet

Meta + N

New Sheet

Meta + R

Right Alignment

F1

Help

F2

Edit Cell

F3

Replace

Meta + F3

Replace Next Cell

F4

Toggle absolute/relative cell reference

F6

Find

F7

Find Next

F8

Find Previous

F9

Recalculate

F10

Shift focus to the menu bar

F11

Cancel Edit

Esc

Cancel Edit

Left Arrow

Move left one cell

Right Arrow

Move right one cell

Up Arrow

Move up one cell

Down Arrow

Move down one cell

Ctrl + Left Arrow

Page left

Ctrl + Right Arrow

Page right

Ctrl + Up Arrow,

Page up

Ctrl + Down Arrow

Page down

Next Page/Page Down

Page down

Prev Page/Page Up

Page up

Home

Top of sheet -- cell A1

End

Last non-empty row

Ctrl + End

Right-most non-empty column

Shift + any motion

Select while moving cursor

Meta + arrow key

Advance to next "break" in data in the specified direction

Mnemonics

To display a pull-down menu using mnemonics:

1. Press [F10] to shift keyboard focus to the menu bar (the File option will be highlighted by a square).
2. Type the underlined letter in the option you wish to select.
3. Repeat for each level of the menu structure until the final selection is made.

1. Press [Meta] and the underlined letter at the same time.

Mnemonics are a keyboard alternative to using the mouse or cursor to access menus. For tear-off menus, mnemonics can be used when the menu has focus.

How to Use Menus

When secondary data windows (Multiple Views) are displayed within the same workbook, the menu bar is not attached to each window; however, any operation invoked through the menu bar is applied to the window(s) containing the current cell.

To view a pull-down menu from the menu bar:

1. Move the mouse pointer to the desired menu title.
2. Press the left mouse button.

1. Press [F10] on the keyboard.
2. Use the arrow keys to move the highlight to the desired menu title.

1. Press [F10].
2. Type the underlined character in the menu selection you desire.

To select a menu action:

1. Display the pull-down menu from the menu bar.
2. Move the highlight to the desired menu selection.
3. Release the mouse button.

Menu selections can result in any of three actions:

• A menu selection can directly activate an operation such as Edit Cell, Select All, or Exit.
• A menu selection can lead to a cascading submenu that contains additional related operations or options. For instance, the Copy selection has a submenu that contains the selections Copy Formulas and Copy Values. The Font Style selection has a sub-menu that contains the selections Default, Normal, Bold, Italic, and Bold-Italic. Selections that have sub-menus are always marked with a right-arrow.
• A menu selection can display a pop-up dialog box for you to specify further information about the menu selection. For instance, the Go To selection from the View menu displays a dialog box for you to specify the destination of the Go To operation.

To display a submenu:

1. Display a pull-down main menu from the menu bar.
2. Move the highlight to a menu selection with an arrow next to it.
3. Move the mouse to the right to display the submenu.

1. Pressing the left mouse button, move the highlight to the desired selection.
2. Release the mouse button.

To avoid making a menu selection when you have displayed a menu, drag the pointer off the menu and release the mouse button. Keyboard users press the [Esc] or [F11] key.

Mouse Button 3 Popup Menu

To invoke the short-cut menu:

1. Select the cells to be effected.
2. Hold down MB3 and traverse as needed to the desired operation and release.

To disable MB3 popup, change the resource *popupMenu in the Xess5 resource file to False.

If you are in the middle of editing data in a cell and click MB3, some operations will be greyed out.

Tear-off Menus

When you are through using a tear-off menu, it may be dismissed. On the Motif Window Manager (mwm), to cancel (close) a tear-off menu, double-click the top left button of the tear-off window frame.

How to Use Dialog Boxes

• check buttons square buttons that are clicked on and off to activate any number of selections that are not mutually exclusive.
• option buttons rectangular button containing the current state of an option menu followed by a bar indicator; when clicked it displays the option menu.
• list boxes boxes that present lists of information or choices, with scroll bars that work just like the scroll bars on the main XESS window.
• slider bars that can be moved to change a parameter setting. Similar to a scroll bar.
• data entry lines or boxes lines and boxes that contain information you type, such as a file name or cell location.
• push buttons buttons that you click to activate your selection or cancel an operation, such as Start, OK, Apply, or Cancel. A Help button provides on-screen help describing dialog box functions.

Check Button

1. Move the mouse pointer to the desired selection.
2. Click the left button. The button will appear in reverse video or a different color, depending on your monitor.

Keyboard users: Press the space bar to display button options or change on/off state.

Option Button

1. Move the mouse pointer to the desired button.
2. Click the left button to display the option menu. While holding down the left mouse button move the pointer to the desired selection and release.

Slider Bar

1. Move the mouse pointer onto the slider bar.
2. Drag the mouse to the right or left until the desired parameter is displayed above the slider.

Data Entry Line or Box

1. Move the mouse pointer to the desired location on the line.
2. Click the left button. The vertical bar cursor will blink, indicating that XESS is ready to accept text in that space.
3. Use the keyboard and numeric keypad to enter the information.

OK and Apply Buttons

1. Move the mouse pointer to the OK button at the bottom of the dialog box.
2. Click the left mouse button. By clicking OK you activate your choices and dismiss the dialog box.

1. Move the mouse pointer to the Apply button at the bottom of the dialog box.
2. Click the left mouse button.

By clicking Apply you activate your choices and keep the dialog box open. With the Apply button, you are able to quickly readjust or add more settings without having to reopen the dialog box.

Any applied spreadsheet action which is destructive to the data can be reversed using Undo. Ten (10) levels of Undo are supported by default. You can reverse the most recent Undo with Redo.

Cancel Button

1. Move the mouse pointer to the Cancel button at the bottom of the dialog box.
2. Click the left mouse button.

Cancel does not undo changes that have already been applied.

Reset Button

Help Button

File Selection Dialog

The File Selection dialog box displays an alphabetical list of all the files in the selected directory whose names meet the criteria in your Filter. The current directory name is displayed at the top of the Directories list. If the lists are large, all the names may not fit in the boxes.

To move the selection highlight, use the up and down arrow keys, or move the mouse pointer to the desired name and click.

To scroll through the list of file names, click the up and down arrows along the right side of the list box, or drag the scroll box.

To jump to a specific alphabetical section of the list, type the first character(s) of the name of the file you are looking for in front of the asterisk (*). The dialog box then starts the display at the first file name starting with those characters.

To select a different directory, double-click on the directory name in the Directories list. XESS then displays the names of files in that directory in the Files list. Double-click on the second file in the Directories list change to the next-higher directory level.

To select a workbook, double-click on the workbook name, or click the name once and then click OK to complete the operation.

To cancel the operation after viewing the dialog box, click Cancel.

Window Frame and the Window Manager

You can have several windows open at once, move from window to window, change the size and position of windows on screen, and shuffle the order in which windows overlap one another. If several windows are open at once, all of them are active, but keyboard and mouse actions can address only one current window. In X Window System terminology, that window has the input focus.

The window frame and functions are not controlled by XESS but rather by your system's window manager program. The exact method for manipulating windows depends on the window manager program you are using. XESS should work with any X11 compliant window manager.

The following instructions assume that you are using the OSF/Motif Window Manager (mwm) from the Open Software Foundation, Inc. Other window managers have different window frames and use different mouse and keyboard sequences to perform comparable actions.

OSF/Motif Window Manager windows usually contain the following functional elements:

• a window menu icon, located in the top left corner of the window frame
• a minimize icon, located in the top right corner of the window frame, that reduces a normal-sized window to an icon
• a maximize icon, located in the top right corner of the window frame, that enlarges a normal-sized window to fill the computer screen or reduces a full-screen window to normal size
• a resize border, located at the borders, that permits you to change the dimensions of the window

The Window Manager provides several options for manipulating windows with the keyboard, mouse and built-in shortcuts.

Current Window

1. Move the mouse pointer into the window, or
2. Move the mouse pointer into the window and click the left mouse button.

Window Sizing

1. Move the mouse pointer to the larger box icon at the upper far right of the window frame.
2. Click the left mouse button.

1. Click the window menu icon at the top left of the window frame.
2. Select Maximize from the window menu.

To return a maximized spreadsheet to normal size:

1. Move the mouse pointer to the maximize button at the upper right corner of the window frame.
2. Click the left mouse button.

1. Click the window menu icon at the top left of the window frame.
2. Select Restore from the window menu.

To change the dimensions of the window:

1. Position the mouse pointer in the window border. The pointer will change shape.
2. Press and hold the left mouse button. Drag the mouse until the fine cross-lines indicate the desired dimension.
3. Release the mouse button.

1. Click on the window menu icon at the top left of the window frame.
2. Select Size from the window menu.
3. Press the left mouse button and drag the mouse until the cross-lines indicate the desired dimension.
4. Release the mouse button.

Iconifying

1. Move the mouse pointer to the smaller box icon at the upper right of the window frame.
2. Click the left mouse button.

1. Click the window menu icon at the top left of the window frame.
2. Select Minimize from the window menu.

To restore a spreadsheet that has been turned into an icon:

1. Move the mouse pointer to the icon.
2. Click the left mouse button.
3. Select Restore.

1. Move the mouse pointer to the icon.
2. Double click the left mouse button.

Moving a Window

1. Move the mouse pointer to the title bar.
2. Press the mouse button. While holding the mouse button down, move the mouse to reposition the window.
3. Release the mouse button.

1. Click the window menu icon at the top left of the window frame.
2. Select Move from the window menu.
3. Press the left mouse button and drag the mouse until the cross-lines indicate the desired position.
4. Release the mouse button.

To move the current window behind another window:

1. Click the window menu icon at the top left of the window frame.
2. Select Lower from the window menu.

1. Display the File menu from the menu bar.
2. Select Quit from the File menu.

Keyboard shortcuts can be used to perform these functions. The keystrokes are marked beside the selection on the menus.

User Preferences Dialog

Smart Text Entry

	1-919-942-7801
	561-042-563

Smart Date Entry

and

Smart Time Entry

Enable In Cell Editor

Enable Multiple Workbooks

Remember Cursor Position

Disable Bell

Editor Colors Match Cell

Use @ for Functions

Use ! for Sheet Names

Enable Visual Bell

Enable Toolbar Animation

Normal Cursor

Working Cursor

Scrolling Speed

How XESS Handles Errors

Calculation Errors

For example:

If you try to invert a 3 x 2 matrix, XESS highlights the cell where the formula was entered, and displays the message, "Error - @INVERT, matrix must be square" in the cell containing the invalid formula. In addition, the message, "Cell xx:Error - @INVERT must be square" appears on the Message Line.

Syntax Errors

XESS will not allow a syntactically incorrect expression to be entered.

For example:

If you enter the formula @DATE(1/10), XESS displays the message, "ERROR: not enough arguments to function", on the Message Line and the cursor is positioned to the right of the 0 on the Edit Line. You must correct or erase the invalid expression.

Operational Errors

For example:

If you enter an invalid cell address in a dialog box which requires that data to continue, XESS displays the XESS Message dialog box which contains the message, ERROR: - illegal range specification. You must press Cancel to remove the box and continue.

Accessing Workbooks

Building a New Workbook

If you are already working in a spreadsheet workbook and want to erase all cells and start again with an empty workbook:

1. Display the File menu from the menu bar.
2. Select Close from the File menu.
3. Display the File menu again and then select New.

If you are working in a workbook and want to begin working in a different workbook:

1. Display the File menu from the menu bar.
2. Select Open... and XESS will display the Open Sheet dialog box for selecting the name of the workbook you wish to use.

If you have set User Preferences to single-workbook mode and have made changes to the current workbook, XESS will first ask if you want to save the changes before loading the new workbook. In multiple-workbook mode, the new workbook is displayed in a new primary window.

Loading an Existing Workbook

The .xs5 file format consists entirely of printable characters. This makes it easy for you to copy XESS files from one system to another regardless of the operating system or file structure. It also means that XESS files can be mailed among systems without additional processing.

XESS can also access spreadsheets and workbooks in several other popular formats: .xs, .xs3, and .xs4 sheets compatible with previous versions of XESS; and .wk1, .wks, .wk3 (wk3/wk4), and .xls sheets compatible with most other spreadsheet systems.

You can get an existing workbook, view it, change it, recalculate it, and then save it (overwriting the original version) or exit without affecting the original version. You can load and use workbooks from other spreadsheet programs or load data from text files in a variety of formats.

To load an existing XESS, WK*, or XLS workbook:

1. Select Open... from the File menu. XESS displays the Open Sheet dialog box.
2. Select the type of spreadsheet file you wish to read: XS5, XS4, XS3, XS, WK1, WKS, WK3/WK4, or XLS.
3. Click the Selection entry box and type the name of the file you wish to use, or select a file by highlighting its name in the Files list using the mouse.

Adjacent to the Files list is the Directories list. The files shown in the Files list are those files found in the directory whose name is highlighted in the Directories list.

The dialog box displays an alphabetical list of all files whose names meet the criteria in your File Filter. If the list is large, all the document names may not fit in the box.

• To move the selection highlight, use the up and down arrow keys, or move the mouse pointer to the desired name and click.
• To scroll through the list of document names, click the up and down arrows along the right side of the list box or drag the scroll box.
• To jump to a specific alphabetical section of the list, type the first character(s) of the name of the document you are looking for in front of the asterisk (*). The dialog box then displays the first file name beginning with these characters.
• To select a file, double-click on the file name, or click the name once and then click the OK button. XESS opens the workbook with the same window size and position that were in effect when the file was last saved.
• To select a different directory, double-click on the directory name in the Directories list. XESS then displays the names of files in that directory in the Files list. (The .. entry allows you to change to the next highest level of the directory structures.)
• To avoid opening a file after viewing the dialog box, click Cancel.

If you do not find the file name you are looking for, check the File Format and the File Filter in the dialog box to make sure you are searching in the correct disk or disk directory and for the desired spreadsheet type.

The File Filter

XESS changes the default filter to match the file format you requested. For example, if you set File Format to WKS, the File Filter appears as *.wks and the Files list includes all files with names ending in .wks.

On Linux and UNIX systems, the default extension allows filenames in upper, lower, or mixed case, similar to the following: .[Xx][Ss]5. On OpenVMS and Windows NT, the same extension is displayed as .xs5 but is not case sensitive.

You can change the file filter to display only certain groups of files. This is especially useful when you have many files on the disk and the list is cumbersome to search. For example, you can change the file filter to display:

• files in a specified directory or sub-directory,
• files whose names have a character match to designated characters, or
• files with a specified extension.

One way to change the file filter is to double-click on a different directory. Alternatively, you can change the file filter by doing the following:

1. While the dialog box is displayed, click on the text of the File Filter. XESS displays a blinking cursor in the File Filter area.
2. Using the keyboard, type the desired file filter. You can use the asterisk (*) as a wildcard. For instance, the file filter *.xs5 tells XESS to display files with the .xs5 extension, no matter what characters precede the extension. The file filter test*.xs tells XESS to display all files whose names begin with test and end with the .xs extension.
3. Press [Enter] or click on the Filter button. XESS displays file names that meet the criteria specified in the new file filter.

Loading a Workbook in a Different Format

Note that there are differences between spreadsheet programs, so you may have to modify certain formulas or cells to get the spreadsheet to work properly in XESS. You can load most spreadsheets without any difficulty, but those with complex interactions that use macros or certain functions may require some modification.

When loading an XLS or WK* file into XESS, formulas which contain functions that exist in the source spreadsheet product but not in XESS will generate a standard formula error indicating that the function does not exist. Formulas containing ambiguous function references are converted to text strings in XESS for manual intervention.

In addition, with the Import operation, you can load tabular data from text files in several formats which include comma-separated values (CSV), tab-separated values (TSV), user-defined delimiter, and HTML.

Saving the Workbook

To save a workbook that has not yet been named:

1. Select Save As from the File menu. XESS displays the Save Sheet dialog box.
2. Select the type of spreadsheet file you wish to create: XS5, XS4, XS3, WK1, WK3/WK4, WKS, XLS, or XLS V4.
3. Enter the name of your file in the Selection area of the dialog. Alternatively, use the Files display and select the file name there.
4. Click OK or press [Enter]. To cancel the operation, click Cancel.

To save a workbook that has already been named:

1. Display the File menu from the menu bar.
2. Select Save from the File menu. XESS saves the sheet using the current name and displays a message on the Message Line.

The Save and Save As operations do not remove your workbook from memory, so you can continue to make changes to it after saving. You should, in fact, save your work periodically during a session to minimize the risk of losing work if the computer system is interrupted for any reason.

If you Save the current workbook, it will have the same file name as the file already on your disk. The old version of this file is renamed to have a tilde character (~) at the end of the name before the workbook is saved with the file name.

Saving a Workbook in a Different Format

When saving an XLS or WK* file from XESS, formula which contain functions that exist in XESS but not in Excel or Lotus are converted to text strings when XESS creates the XLS or WK* file.

In addition, with the Export operation, you can save tabular data into text files in several formats which include comma-separated values (CSV), tab-separated values (TSV), user-defined separators, LaTeX (TEX), and Hypertext Markup Language (HTML).

Naming the Workbook File

You should use names that are descriptive or mnemonic, so you can easily identify the file from a directory listing, possibly weeks or months after you have last used the file. If you have several files that are similar, you can differentiate between them by adding dates or code numbers, for example, MFG_391 or TEST91.

Follow these conventions in assigning file names:

• File names follow the conventions for the host file system. They can usually be at least eight characters long and can consist of both letters and numbers. (Some systems support names as long as 127 characters.)
• Be careful when using upper-case and lower-case letters. Some systems are case sensitive; others, case insensitive. For example, under some systems (e.g. OpenVMS, Windows NT), "USRTEST.XS5" is considered identical to "usrtest.xs5". On other systems, including UNIX and its derivatives, the previous names represent different files.
• Do not use spaces in a file name. You can use an underline character (_) or hyphen (-) to simulate spaces, for example, ekg-200 or EKG_200.
• Unless you have a specific reason to change the file extension (transferring the file to a different program, for example), let XESS assign the default file extension.

Copying, Moving, Renaming, or Deleting Files

Opening Multiple Workbooks

Ending an XESS Session

To save the changed version, click the Save button. XESS saves the new version (possibly overwriting a previous version) and ends the session.

To discard your changes and preserve the workbook on disk, click Discard. XESS discards the version on screen and ends the session.

To cancel the Exit operation, click the Cancel button. XESS returns you to the active workspace without saving or discarding anything.

Moving Around the Spreadsheet

• the mouse or the keyboard arrow keys to move the cell cursor,
• the data scroll bars to scroll to another view of the sheet,
• the sheet tabs to select a different sheet in the workbook,
• the End key to go to the last row used in the current column of the current sheet,
• the Go To operation to move to a designated cell address,
• the Find operation to move to a cell with a specified content,
• the Create View operation to permit multiple windows in the same workbook, or
• the Top and Bottom operations for moving within a view.

Moving with the Keyboard

Because the spreadsheet window can show only part of a large sheet, you may need to scroll through the sheet to view other sections. The arrow keys can be used to scroll to the remote areas of the sheet as described below.

To move the view to the right:

1. Hold down the right arrow key and watch the sheet scroll to the right.
2. Release the arrow key once you reached the desired view area.

Similarly, use the down, up, and left arrow keys to move the view other areas of the sheet.

The Ctrl key plus the arrow keys allow you to move the cell cursor around the sheet a page at a time. Hold down the Ctrl key while using the arrow keys to move an entire window (page) in any direction. Your keyboard may also have specific keys for moving a page at a time. These are usually labeled PageUp and PageDown or PrevScreen and LastScreen.

The Meta key plus the arrow keys advance the cursor to the next "break" in the current row or column. (On many keyboards, Meta is labeled ALT.) For example, Meta+Down advances to the last entry in the current column -- the one with an empty cell below it. A subsequent Meta+ Down might take you to the absolute last row available.

Other keys may be available on your keyboard which help you move around the workbook:

Ctrl + g

Go To

Ctrl + V

Create new view

F6

Find

F7

Find Next

F8

Find Previous

Left Arrow

Move left one cell

Right Arrow

Move right one cell

Up Arrow

Move up one cell

Down Arrow

Move down one cell

Ctrl + Left Arrow

Page left

Ctrl + Right Arrow

Page right

Ctrl + Up Arrow,

Page up

Ctrl + Down Arrow

Page down

Next Page/Page Down

Page down

Prev Page/Page Up

Page up

Home

Top of sheet -- cell A1

End

Last non-empty row

Ctrl + End

Right-most non-empty column

Meta + arrow key

Advance to next "break" in data in the specified direction

Moving with the Mouse

To move the view to the right:

1. First position the cell cursor somewhere in the rightmost column that is displayed.
2. Press the left mouse button and drag the mouse (still holding down the left mouse button) a little to the right and watch the sheet scroll to the right.
3. Release the left mouse button once you reach the desired view area.

To move the view down:

1. First position the cell cursor somewhere in the last displayed row of the sheet.
2. Press the left mouse button and drag the mouse (still holding down the left mouse button) a little towards the bottom and watch the sheet scroll down.
3. Release the left mouse button once you reach the desired view area.

Similarly, follow the steps above to scroll towards the left and top of the sheet.

Moving with the Scroll Bars

• the size of your sheet view relative to the total area used by the sheet, and
• the current position of the sheet view in relation to the total area used by the sheet.

Each view has its own scroll bars. If the slider fills the entire scroll bar, then the current view displays the entire sheet.

To move the view left or right one column at a time:

1. Move the mouse pointer to the arrow at the left or right of the horizontal scroll bar.
2. Click the left mouse button.

To move the view up or down one row at a time:

1. Move the mouse pointer to the arrow at the top or bottom of the vertical scroll bar.
2. Click the left mouse button.

To move the view one page at a time:

1. Move the mouse pointer to an empty area at either end of the scroll bar.
2. Click the left mouse button.

To move the view anywhere in the sheet:

1. Move the mouse pointer into the slider.
2. Press the left mouse button and drag the slider until the view reaches the desired location.
3. Release the mouse button.

Moving with the Goto Dialog

To go to a designated cell:

1. Select Go To... from the View menu. XESS displays the Goto Cell dialog box.
2. In the Enter Cell Address entry box, type the address of the cell to which the cursor should move. Named ranges may be used for the cell address.
3. Click OK to carry out the command and dismiss the dialog box. Click Apply to carry out the command and leave the dialog box open. Click Cancel to cancel the operation.

If the cell is not currently visible, XESS changes the view so that the requested cell is in the displayed area of the sheet.

The Goto Cell dialog can reference sheet names and named ranges. This allows you to move the cell cursor to a cell anywhere on any sheet in the workbook.

Moving with the Find Tool

For example, you can search for cells whose values are greater than 100 and less than 150, or for cells whose values are less than the average of the three preceding cells. This facility is extremely useful for spotting patterns in large, complex data sets.

You can tell XESS which part of the workbook to search, whether to search by row or by column, and whether to search for text characters or numeric values.

To use the Find tool:

1. Select Find... from the Search menu or press the [F6] key. XESS displays the Find dialog box.
2. In the Find dialog box, make the following entries to carry out the Find operation:

   Search Range

   Specify the range of cells in which XESS should search, by entering two corner coordinates (e.g. A1..Z55). The default range is the entire sheet. If a range was selected when you invoked the Find operation, the default is this selected range.

   Target

   Specify the string or value as it would appear formatted in the cell. To find a numeric value that meets certain criteria, enter an expression in Target. For example, to find values less than 20, specify the Filter as #<20 and do a numeric search. The Target criteria are explained in detail below.

3. By default, XESS searches row by row. Change the selection box to Search by Column to start searching column by column.
4. Select Text Comparison, Regular Expression, or Numeric Comparison to indicate whether the Find operation does character comparisons, regular expressions, or evaluates numeric and logical expressions. These are described in detail in following sections.
5. For Text Comparison and Regular Expressions searching:
   1. Use the Ignore case toggle button to determine whether or not upper/lower case is taken into consideration with the Find operation. If you wish to find both upper and lower case occurrences of alphabetic characters, click the toggle button on to Ignore case. Otherwise, if this toggle button is off, the Find operation will be case sensitive.
   2. Use the Ignore accents toggle button to determine whether or not accents used in the extended ISO 8859 character pages are taken into consideration with the Find operation. If you wish to find both unaccented and accented occurrences of the same alphabetic character, click the toggle button on to Ignore accents. Otherwise, if this toggle button is off, the Find operation will differentiate between accented and unaccented characters.
   3. Use the Match whole word to require that parts of words do not satisfy the search.
6. To start the search, select Apply or OK. Select Find Next ([F7]) and Find Previous ([F8]) on the Search menu to find additional cells meeting the search requirements.

The initial settings for the Ignore case and Ignore accents toggles are determined by the respective collating settings for case and accents in the Workbook Defaults dialog. Once you override either setting from the Find dialog, it will be remembered with subsequent Find operations within the same session.

Find Next

To find the next occurrence of the value or string being searched, select Find Next button from the Search menu. Alternatively, press F7 key to repeat the search. The Find operation (described above) must precede the Find Next operation.

Find Previous

To find the previous value that was searched, select Find Previous from the Search menu. Alternatively, press F8 key to search the previous value.

Target Criteria

To find a character string, type the string inside the Target entry box as it would appear when formatted in the cell. For example:

                Doe, John

To find a character string that matches a pattern, select Regular Expression and enter the desired pattern in the Target area. For example:

                A[B-Z]

This finds a cell containing an A followed by another letter other than an A.

To find a numeric value, either a constant or a calculated value, type the value exactly as it would appear formatted in the cell. For example:

                $1,000,000

To find numeric values that meet certain criteria, enter the target with a constraint expression. # is used to represent the current cell being examined. Select Numeric Comparison. For example:

• To find values less than 20, specify the target as:

  	 #<20
  
  

• To find values greater than or equal to 50, specify the target as:

  	#<=50
  
  

• To find cells whose values are less than 600 and whose neighboring cells in the same column have values less than 500, specify the target as: #<600 && #{0,-1}<500 && #{0,1}<500
• To find the cell containing the date August 22, 1991, specify the target as:

  	#==@DATE(1991,8,22)
  
  

How XESS Evaluates Target Criteria

• XESS begins the search at the top left corner of the selected range. Subsequent searches with Find Next and Find Previous start at the current cell.
• You can use regular expressions to define text (non-numeric) searches. Regular expressions specify a set of strings of characters. In a regular expression, a character normally matches with the same character in the matching string. A few characters have special meanings:

  .

  A period matches any character (e.g. A.C matches "ABC" or "AXC").

  ^

  Matches only at the beginning of a field (e.g. ^ABC matches "ABC" but not "AABC").

  $

  Matches only at the end of a field (e.g. ABC$ matches "AABC" but not "ABCD").

  [x-y]

  Matches any character between the character x and the character y (e.g. [0-5]$ finds fields ending in the characters 0 through 5).

  [xyz]

  Matches any character in the list xyz (e.g. [05]$ finds fields ending in either a 0 or a 5). This form and the one above can be combined. [27-9]$ will find fields ending with the characters 2, 7, 8, or 9.

  \

  Uses the next character as its actual value rather than with special meaning (e.g. \\, \$ ).

  \<

  Match the beginning of a word.

  \>

  Match the end of a word.

  *

  Match 0 or more of the expression to the left.

  +

  Match 1 or more of the expression to the left.

  \( ... \)

  Match the expression with the delimiters. Useful when you want to add a closure operator to the right of it.

  These methods can be combined. Thus, the filter [brc]at finds "bat," "rat" and "cat."
• By default, XESS looks for your search string in any part of a cell entry. If you search for the word "Test," XESS locates cells containing the strings "Test1," "Test2," "TestFinal," and so on.
• When conducting a text search, XESS formats the cell entry before searching. For example, XESS would recognize the dollar sign ($) and commas in numbers formatted in the Dollars format, rather than the generic manner in which they were entered.

Creating Multiple Views

Secondary windows do not include a menu bar. However, any menu or operation selected from the main menu on the primary window is applied to any secondary window which has the current or selected cell(s) in view.

To create a secondary window of the spreadsheet data:

1. Select Create New View from the View menu.

1. Click Cancel on the window to be removed.

Setting and Clearing View Titles

• The Lock View Titles option on the View menu establishes rows at the top of the sheet for column headings and columns on the left as row headings. The title rows scroll with the view from left to right but remain in the same position at the top of the sheet as you scroll up and down.

  The title columns scroll with the view as you move up and down but remain in the same position at the left of the sheet as you scroll right and left.

  Row and column titles must be entered before the view titles are locked. After view titles are set, the cursor no longer moves into the title area.

• The Unlock View Titles option removes title status from all existing title rows and columns.

Entering Data

This section describes how to enter data in your spreadsheet and how to enable cell protection. For more information about editing cells, moving and copying data, and manipulating spreadsheets, refer to Manipulating the Spreadsheet. For more information about using formulas to calculate values, refer to Calculations.

About Cells

A cell is the intersection of a row and column within a sheet in a workbook. Cells can contain numeric or text constants, dates and time, formulas, images, and graphs. Constants and calculated values are visible on screen, but the formulas that produce those values are generally invisible.

Formulas are viewed and changed directly in the cell if in-cell edit is enabled or in the Edit Line otherwise; they appear only when the cell is highlighted. The current cell is typically outlined in red in the body of the sheet; in addition the row and column button of the current cell is a different color from the other row/column buttons. This is the cell that will be affected by your next action.

Each cell is identified by an address that contains the column and row coordinates of a specific sheet in the workbook. For instance, the address of the first cell in the workbook is typically Sheet1!A1; the cell in column D, row 4 of Sheet 10 has the cell address Sheet10!D4. With 4096 columns, 10,000,000 rows, and 4095 sheets an XESS workbook may contain more 167 trillion cells. However, the actual size of your workbook is limited by the memory capacity of your computer system, which is far less than the theoretical size of the workbook.

The amount of information in each cell and the nature of the entries themselves determine how many cell entries you can make before you run out of memory. There is a good reason, though, for XESS to provide a spreadsheet grid larger than you can ever fill; it gives you the flexibility to develop long, narrow sheets or wide ones with few rows.

The actual size of a given workbook can be determined with the Workbook Statistics Tool.

Cell Ranges

Ranges can also be be 3-dimensional by referencing cells on more than one sheet in a workbook. For example, Sheet1!A1..Sheet10!A1 creates a rectangular group which cuts through the third dimension of a workbook.

You can reference ranges in other workbooks by including the filename of the other workbook in the range specification. Thus,

        =@SUM("OtherSheets.xs5"::A.A1..C.A10) 

Input Mode and Edit Mode

Input Mode

Input Mode allows you to enter data into a blank cell or retype the contents of a cell.

To enter input mode, press any alphanumeric key on your keyboard. You can then enter text, numeric values or formulas. XESS stays in input mode until you press [Enter], move the cell cursor to another cell location by using the up or down arrow keys, or click the accept or reject indicators left of the edit line.

Edit Mode

Edit Mode allows you to modify the contents of a cell without retyping the entire contents. Use the mouse or the arrow keys to position the cell cursor to the cell to be edited.

To enter edit mode, either use the mouse to position the cursor in the current cell contents in the Edit Line, choose Edit Cell from the Edit menu, or use the [F2] key. You may edit either in the Edit Line or within the cell itself.

Use the [Delete] or [Backspace] key to erase and enter the correct entry.

Use the left and right arrow keys to move within the cell.

After making your changes, click the accept or reject buttons, press [Enter], or use the up or down arrow keys to complete the edit. You can cancel Edit Mode by pressing the [esc] key or by clicking the reject button.

In both input mode and edit mode, data entered from the keyboard appears in the line in the Edit Line of the main view of the current sheet. When you complete the entry with [Enter] or its equivalent, XESS inserts the new value into the current cell. In input mode, you can also use any arrow key to complete the entry and move one cell in the direction of the arrow.

You can change the current cell by:

• pressing an arrow key to complete an entry in input mode. The cursor moves one cell in the arrow direction.
• pressing the up or down arrow key to complete an entry in edit mode. XESS accepts the entry and moves the cursor one cell in the arrow direction.

  Cell entries often look different on the input/edit lines than they do in the cells. For example, if a cell contains a formula that produces a value, the input/edit lines show the formula, not the value. If a cell is formatted for scientific notation or a special date format, it appears in the cell in its special format but appears on the input/edit lines just as you typed it.

• In input mode, any input beginning with + - . , @ = or a digit 0 - 9 is assumed to be a formula, unless the cell is already formatted to be text, a date, or time. Otherwise the entry is assumed to be text. In edit mode, XESS does not differentiate between formula and text entries except that formula always begin with the = character.
• In input mode, an entry is completed by pressing [Enter] or any arrow key. In edit mode, the left and right arrow keys move the position of the character cursor within the current value for the cell.
• In input mode, you cannot use the arrow keys to move the text cursor. In edit mode, you can use the arrow keys to move over characters without deleting them. In either mode you can use the mouse to reposition the text cursor. Characters can then be inserted or deleted at the text cursor.
• In input mode, cell and range references are appended to the characters already entered. In edit mode, cell and range references are inserted into the string at the cursor location.

When you are modifying the Edit Line, various editing options are usually available:

left arrow

Move the character cursor a single character.

right arrow

Move the character cursor a single character.

Ctrl + left

Moves the character cursor a word left.

Ctrl + right

Moves the character cursor a word right.

Meta + left

Moves to the beginning of the line.

Meta + right

Moves to the end of the line.

Shift

With one of the above, selects characters to be deleted or replaced.

Cell Contents

Text Entries

Text entries are useful for labeling columns and rows, for including comments about data values being calculated, and for using XESS to manage textual information, such as names, addresses or whatever your application may require.

Numeric Values

If a cell entry begins with a digit from 0 - 9, XESS treats the entry as a numeric entry. XESS also recognizes the following symbols as indicators of numeric entries: + - . (and , in some locales)

You can format numeric values to be displayed in several ways, including fixed formats, scientific notation, currency, fractions, and hex.

Dates and Times

XESS provides special, built-in features for displaying date entries in the format you choose. Date and time entries begin with the the slash (/). Example date and time formats include:

 
    24-Oct-99
    24-Oct
    10/24
    4-Jul-1776
    11/23/1963
    24.10.91
    2001-10-24
    14:27

Dates and times can also be entered without the initial slash. In this mode, both the day and year must be entered or the cell must already be formatted as date.

Formulas

Formulas establish and calculate mathematical relationships between spreadsheet elements. XESS formulas can calculate with numbers, text, logical values, cell references, and other formulas. For example, you can easily calculate the sum of a series of cells, the total of values in a column, or the absolute value of another cell entry.

Graphs

Graphs can be inserted into cells. The anchor cell becomes the upper left corner of the embedded graph. Graph objects in cells can be moved with the Copy Formulas operation.

Entering Text

XESS treats text and numeric entries differently, so you differentiate between text and numeric values when entering cell contents.

To enter most text into a cell, just type the text string as you want it to appear. If the text you wish to enter would normally be considered a formula or a date, then you must precede the actual text with the ' (apostrophe) character.

If you make an entry that is not text but which returns an error when treated as a formula, XESS inserts an error message but remains in input mode.

Character Sets

Treating Numeric Entries as Text

If Smart Text Entry is set in the User Preferences dialog, then cells formatted as Text assume that their input will also be text strings (and not numbers or formulas), unless the initial character entered is:

	=	number or formula
	/	date or time
	//g 	graph
	//r 	repeating character
	\ 	escaped character
	'	string

	01754

Repeating Characters

             Type:  //r-

This feature can be useful for drawing lines to demarcate areas of your spreadsheet when printed. Another way is to fill a cell with hyphens and plus signs:

	Type:  //r-+

However, if you wish to enter a character string that begins with a slash (/), you must be sure to begin the entry with the special text-indicator characters ' (apostrophe).

Entering Text Wider than One Cell

If you change the alignment of the data in the cell, the text will overflow or be truncated as needed on the left, the right, or both. You can format the entire column with the Snap Column Width to Contents option to adjust the column width to contain the entire string.

Text Wrapping

To edit an entry that is displayed over more than one cell, position the cell cursor to the cell actually containing the value. XESS displays the full entry in the Edit Line. Press [F2].

XESS lets you change the width of columns to accommodate entries up to 255 characters long.

Entering Special Characters

Entering Numeric Values

• To enter a positive number, use the number keys to type the number, with or without a + indicator. If you do not type a plus (+), XESS assumes the number is positive.
• To enter a negative number, type a minus sign (-) and the number. Do not use parentheses to indicate negatives. However, if you change the numeric format to Dollars or Comma, XESS displays negative numbers in parentheses.
• Do not use spaces, commas, or currency symbols when entering numbers. If you wish, you can display these items with the value by choosing the appropriate cell format or by creating a custom format. In locales which use comma rather than period as the decimal point, commas may be used as a decimal point when entering numbers. In these locales, semi-colon (;) is used in formulas to separate items in a list, rather than comma (,).
• Be careful not to substitute a lower case L for the numeral 1 or an upper case O for the numeral 0.
• You can use scientific notation to enter a number, with the convention that the letter e separates the fraction from the base 10 exponent. 1.23e3 is the equivalent of 1230.

Entering Numeric Values Wider than the Cell

Changing the Numeric Format

• To change the default formatting for the entire sheet, use the Cell Defaults dialog box, available from the Options menu on the menu bar.
• To change formatting for a selected cell or range of cells, use the Cell Format dialog box from the Format menu.
• To change formatting for a selected cell or range of cells, use the format icon button on the toolbar.
• To change formatting for a selected cell or range of cells, select the cell(s) to be affected and press MB3 (the right mouse button) to display the short-cut popup menu and select Cell Format.

XESS Formats

Scientific

Displays the number in scientific notation (exponent form) with N significant digits.

Fixed

Displays the number using a fixed number of decimal places, specified by N.

General

Displays the number in fixed format or scientific notation, whichever fits. Trailing zeros are not displayed.

Dollars

Displays the number with a leading $ sign and with comma delimiters, as in $1,000,000. Negative values are displayed in parentheses.

Currency

Displays the number using the relevant currency settings from the workstation's Locale.

Custom

Displays the number using the combination of specification from the Custom dialog which allows you to override all settings defined by Locale.

Comma

Displays the number with comma delimiters, as in 1,000,000. Negative values are displayed in parentheses.

Percent

Display the number as a percentage, multiplying it by 100. For example, the value .1 is displayed as 10.00%.

Fraction

Displays the non-integer part of a number as a fraction instead of decimal.

MM/DD/YY

Displays the integer portion of a date/time value in the format 08/01/99.

DD-MMM-YY

Displays the integer portion of a date/time value as a Gregorian date, in the format 08-Aug-91.

MMM-YY

Displays the integer portion of a date/time value in the format Aug-99.

DD-MMM

Displays the integer portion of a date/time value in the format 08-Aug.

YY-MM-DD

Displays the integer portion of a date/time value in the format 99-08-01.

YYYY-MM-DD

Displays the integer portion of a date/time value in the format 2000-01-01.

DD.MM.YY

Displays the integer portion of a date/time value in the format 01.08.99.

HH:MM

Displays the fractional portion of a date/time value in the format 06:15.

HH:MM:SS

Displays the fractional portion of a date/time value in the format 06:15:30.

Hex

Displays the integer portion of the number in base 16 with a leading 0x indicator. For example, the value 30.5 would be displayed as 0x1e.

Logic

Displays 0 as 0, displays 1 as 1, and displays all other values as ? .

Hidden

Cell contents are not displayed.

Text

Displays the characters which were entered to create the cell rather than the resulting value of the cell.

When Default is chosen, the Decimal Places value is taken from the Cell Defaults dialog, not the Cell Format dialog.

The @TEXT function provides a higher-level of user control of formatting.

Entering Dates and Times

If Smart Date Entry or Smart Time Entry is not enabled, then for XESS to interpret the entry as a date or time, you must begin all date and time entries by typing a slash (/) before the desired value. Type a / followed by the date or time in any of the date/time formats. The dates and times entered with this method are also converted to the equivalent references to @DATE and @TIME.

Date/Time            Entered as      Generates

19 July 1990         /07/19/90       @DATE(1990,7,19)
27 September 1991    /27-sep-91      @DATE(1991,9,27)
01 August 1999       /01-Aug         @DATE(1999,8,1)
01 Aug 2003          /Aug-2003       @DATE(2003,8,1)
21 May 1621          /1621-05-21     @DATE(1621,5,21)
21 May 1621          /21.05.1621     @DATE(1621,5,21)
12:30                /12:30          @TIME(12,30,0)

Using Date Functions to Enter Dates

XESS internally stores date and time information using the same convention as other popular spreadsheet programs:

Dates

are represented as an integer equal to the number of days before or since December 30, 1899 (or January 1, 1904 in the 1904 date system).

Times

are represented as fractions of a day, starting at midnight. For example, 6:00 am is stored as .25 (a quarter of a 24-hour day).

You can use date values in calculations. For example, subtracting 05-Oct-1999 from 10-Oct-1999 yields a value of 5 days.

Entering the Current Date or Time

Entering Sequences of Values Quickly

Entering Formulas

	=A1+B2-C4/(E1+E2)

Once entered in a cell, formulas are hidden behind the scenes, performing their work in the background and displaying only the result of their calculation. To view the formula in a cell, move the cell cursor to the cell. XESS displays the formula in the Edit Line.

You can edit the formula or values in cells by selecting the Edit Cell option from the Edit menu or by positioning into the cell contents in the Edit Line with the mouse.

Point-and-Click Entry

If you type a non-formula cell value intended for the current cell and then click the mouse in a different cell, the data will be accepted into the current cell before the current cell is moved to the new mouse location. If you type/edit a formula, and click the mouse on different cell, XESS will treat this as true formula point/click mode and adjust the formula in the edit line with the cell address.

Built-in Functions

Entering Graphs

To embed a graph in the sheet using the menus:

1. Position the cursor cell where you want the top left corner of the graph to be.
2. Select Insert in Sheet from the Graph menu.
3. Select the desired graph from the Choose Graph dialog box.
4. Click OK or Apply to complete the operation.

To embed a graph in the sheet from the edit line:

1. Position the cell cursor in the cell where you want the top left corner of the graph to be.
2. Move the mouse pointer to the edit line and type the string //gn where n is the number assigned to the graph when you first saved it in the Graph Editor. (One way to determine the graph number is to select Edit Graph from the Graph menu).
3. Press [Enter] or click the accept button in the Edit Line to complete the entry.

Entering Images

To insert a link to an image into a cell:

1. Place the cursor on the cell to contain the image.
2. Select Link from the File menu.
3. In the Link Image dialog, select the image type and filename. Press OK.

Cell Notes

To enter a note in a cell or to modify an existing note:

1. Place the cell cursor in the desired cell.
2. Choose Cell Note... from the Format menu.
3. Enter or edit the text of the note in the dialog box.
4. Click OK.

To delete a note from a cell:

1. Place the cell cursor in the desired cell.
2. Choose Delete Cell Note from the Edit menu.

Cell notes are saved in the spreadsheet file. Cell notes can be copied along with other cell values and attributes. This is controlled by the Copy Options dialog.

Protecting Cells from Modification

XESS provides a global setting Cell Protection in the Workbook Defaults dialog which controls whether or not protection is enabled and, if so, the level of cell protection being enforced for the entire workbook:

• Off
• Max
• Mid
• Min

Off means that protection is disable or not enforced. Max does not allow any modification, manipulation, or movement of protected cells by the user, except through recalculation. The Mid and Min settings relax the stringency of the protection checking. For example, clearing an unprotected range within a protected workbook is allowed in both. The Min setting also allows relocation and reordering of data in protected workbooks.

Like color, font, format, etc. each cell has a protection attribute: Default, Protected, or Unprotected. The protection attribute of cells or ranges are set via the Tools->Cell Protection menu. It may be set to:

• Default
• Unprotect
• Protect

The default value of the protection attribute is defined by the Protection field in Cell Defaults dialog. Cell protection is controlled by three operations:

• Cell Protection in the Workbook Defaults dialog box turns cell protection on and specifies the level of protection to be enforced for cells that have been designated as protected.
• The Protection option menu in the Cell Defaults dialog box applies the selected protection option to all cells in the sheet.
• The Cell Protection option from the Tools menu protects, unprotects, or assigns the cell protection default to selected cells, overriding the setting in Cell Defaults. However, cell protection must first be enabled in the Sheet Defaults dialog box for this setting to have any effect.

To enable or disable cell protection for the entire workbook:

1. Select Workbook Defaults from the Options menu to display the Workbook Defaults dialog box.
2. Select the type of Cell Protection desired.
3. Apply your selection by clicking OK or Apply.

To set default cell protection for the entire workbook:

1. Select Cell Defaults from the Options menu to display the Cell Defaults dialog box.
2. Move the pointer to the Protection option button. While holding down the left mouse button to display the options, point to desired option and release.
3. Move the pointer to the Recalc Mode option button. While holding down the left mouse button to display the options point to desired option and release.

To apply cell protection to a selected cell or range:

1. Select the cell or range to be protected.
2. Select Cell Protection from the Tools menu.
3. Select Protect from the cascade menu.

To remove cell protection from a selected cell or range:

1. Select the cell or range of cells for which protection should be removed.
2. Select Cell Protection from the Tools menu.
3. Select Unprotect from the cascade menu.

To apply the default cell protection to a selected cell or range:

1. Select the cell or range to be protected.
2. Select Cell Protection from the Tools menu.
3. Select Default from the cascade menu.

Cells are protected by default. If Cell Protection is on, then all cells will be protected until changed.

Correcting Entry Errors

The Undo option is context-sensitive and the menu reflects which operation is to be removed. For example, if the last operation were Copy Formulas the menu would read Undo Copy. To Undo the Copy Formulas operation, select Undo Copy from the Edit menu.

If you accidentally Undo too many levels, use the Redo operation to redo the operation that was undone.

By default, you can undo 10 previous operations. This is can be changed in the resource file.

Manipulating Data and the Edit Menu

Undo

Undoes the last operation performed on the workbook. The Undo operation is context-sensitive and removes the most recent operation.

Redo

Performs again the operation that was just canceled with Undo.

Edit Cell

Switches into Edit Mode so that you can edit the contents of the current cell.

Cut to Clipboard

Copies the selected cells to the Clipboard and removes them from the sheet.

Copy to Clipboard

Copies the selected cells to the Clipboard.

Paste from Clipboard

Copies the Clipboard to the cells beginning at the cell cursor.

Select All

Highlights (selects) the entire sheet for use by the next operation.

Select...

Highlights a range of cells to be affected by a subsequent operation.

Reselect

Selects the most recent selection.

Named Range...

Defines and manipulates named ranges.

Move

Moves a selected cell or range of cells (including all data values, formulas, formatting and cell references) to a designated location, overwriting existing data in the destination range.

Copy

Duplicates the contents of a range of cells and writes them one or more times into a specified area of the workbook.

Clear

Erases all data values and formulas in a selected cell or range of cells. The cells and their formatting are retained.

Insert

Inserts a row, a column, or a page break into the sheet at the present position of the cursor or inserts a sheet before the current sheet.

Delete

Removes rows, columns, page breaks, or sheets from the workbook. When a row or column of cells is deleted, they no longer exist and other cells shift up and left to replace them.

Mouse Button 3 (MB3) Popup Menu

If you happen to click MB3 during an edit operation, some selections will be greyed out.

You may disable the MB3 functionality by setting the resource, *popupMenu, in the Xess5 resource file to False.

Undo

The Undo operation allows you to undo the most recent operations performed on the workbook. Undo is context-sensitive. For example, if the last operation were Copy Formulas, the menu would read Undo Copy.

If there is not a command that can be undone, the Undo option will appear dim on the Edit menu and you can not select it.

Note: If Undo results in restoring cells to the empty state, some of their cell attributes may not be restored.

Redo

Selecting Cells and Ranges

Before you do an operation that manipulates cell contents, you must select which cell(s) you wish to affect. For example:

• To clear the contents of cells A1 through A5, you must first select the range A1 through A5. When you initiate the Clear operation from the Edit menu. XESS clears the contents of each cell in the range.
• To copy the values of cells A1 through E5, you must first select the range A1 through E5. XESS prompts you to specify the destination.

XESS offers five options for selecting cells:

• using the mouse to highlight the desired cell(s),
• using the select button to select all cells in the sheet.
• using the Select... operation to specify the cell(s) by address,
• using the Select All option or
• using the shift key with the arrow keys.

In each case, XESS highlights the selection. If no range or cell is selected, most XESS operations apply to the current cell.

Most operations affect all the cells in the selected range (for example: Clear, Cell Color, or Copy). Certain operations restrict the ranges that they use. For example, if you have 10 rows of numbers each in columns A and B and select all of both columns (using the column buttons) to define a graph, the graph operation will only use the 10 data points and ignore the thousands of empty cells.

Selecting Ranges Using the Mouse

1. Move the mouse pointer to the desired cell.
2. Click the left mouse button and drag the mouse pointer just outside of the cell boundary, and then back in.
3. Release the mouse button. The selected cell will be highlighted.

If there is not an explicit selection of a cell or range, most operations affect the current cell, the one where the cell cursor is positioned.

To edit the contents of the current cell, use Edit Cell, or place the mouse cursor in the Edit Line and click the left mouse button.

If you wish to move, copy or clear cell contents, click once on the cell, then select the appropriate function from the Edit menu.

To select a range of cells using the mouse:

1. Move the mouse pointer to one corner of the range.
2. Press the left mouse button and drag the mouse (still holding down the left mouse button) to the opposite corner of the range.
3. Release the mouse button. The selected range will be highlighted.

To select a row using the mouse:

1. Move the mouse pointer to the row border. For example, move the mouse pointer to the number of the row you wish to select.
2. Press the left mouse button to select the whole row.
3. Drag the mouse pointer up or down if you wish to select several contiguous rows.
4. Release the mouse button. The selected row(s) will be highlighted.

To select a column using the mouse:

1. Move the mouse pointer to the column border. For example, move the mouse pointer to the letter of the column you wish to select.
2. Press the left mouse button to select the column.
3. Drag the mouse pointer to the left or right if you wish to select several contiguous columns.
4. Release the mouse button. The selected column(s) will be highlighted.

To extend a selection with the mouse:

Use the Shift key with the left mouse button to extend or reduce a selection. The current selection will be adjusted so that the newly-selected cell becomes one of the corners of the selection.

Multiple views of the workbook can be used when extending selections. For example, select the top elements in one view and shift-click the bottom cell in another view. The selection is extended as if it were all in a single view.

To select the entire sheet with the mouse:

Use either the Select All option of the Edit menu or click on the push button at the top of the row numbers and to the left of the column names. This selects the entire sheet.

Selecting Ranges Using the Select Range Dialog

To select a cell using the Select Range dialog:

1. Choose Select... from the Edit menu. XESS displays the Select Range dialog box.
2. In the Selection entry box, type the cell address or range in the dialog box. For example, type in B17. Named ranges can also be used.
3. Apply your selection by clicking OK or Apply or by typing [Enter].

To select a range of cells using the Select operation:

1. Choose Select... from the Edit menu. XESS displays the Select Range dialog box.
2. In the Selection entry box, type the addresses of two cells in opposite corners of the desired range, separated by two periods ( .. ). For example, to select a range from A14 to B27, type A14..B27. Named ranges can also be used.
3. Apply your selection by clicking OK or Apply or by typing [Enter].

To select all cells using the Select All command, choose Select All from the Edit menu. XESS selects the range which covers all cells in the sheet.

If you are working in edit mode or input mode, XESS assumes that Select All is meant to be an insertion into the content of the current cell. Therefore, XESS does not leave the selection highlighted for subsequent action; it inserts the range address into the edit line text at the current cursor position.

Selecting Ranges Using the Keyboard

1. Using the arrow keys, move the cell cursor to the desired cell.
2. Hold the Shift key and move the cursor to an adjacent cell and back to the desired cell. The desired cell will be highlighted.

If you wish to edit the contents of the cell, press the accelerator key for Edit Cell (usually [F2]) to enter edit mode. You can then use the keyboard to change cell contents.

To select a range of cells using the keyboard:

1. Using the arrow keys, move the cursor to one corner of the range.
2. Hold down the Shift key.
3. Use the arrow keys to move the cursor to the diagonally opposite corner of the range.
4. If this is the selection of a destination for a move or copy, type [Enter] to complete the operation.

To select all cells using the keyboard, press Ctrl+a. This is the accelerator for Select All.

To enter a selection into the Edit Line: Press [Enter] after making the selection with the arrow keys and Shift.

Deselecting Cells and Ranges

To cancel a selection using the mouse, click the left mouse button on any cell.

To cancel a selection using the keyboard, move the cursor without depressing the Shift key. XESS will de-select the current cell or range.

Note: Any motion such as the arrow keys (without Shift), scrolling, Goto, Top, and Bottom also cancels the selection.

Reselecting a Range of Cells

Specifying Cells and Ranges in Dialog Boxes

To specify a cell in a dialog box:

1. Enter the address of the cell. For example: A1.

1. Use the mouse to point to the cell, and then click the left mouse button (This moves the cell cursor.). Now position the mouse over the dialog entry and press the middle mouse button (MB2). XESS pastes the cell name into the dialog as a relative reference. To paste an absolute reference, hold the Ctrl key when you press the middle mouse button.

To specify a range in a dialog box:

1. Type the addresses of opposite corners of the range, separated by two periods ( .. ). For example: A1..E5.

1. Use the mouse or keyboard to highlight the range of cells. Now position the mouse over the dialog entry and click the middle mouse button (MB2). XESS pastes the cell range into the dialog as a relative reference. To paste it as an absolute reference, hold the Ctrl key when you click the middle mouse button.

If you have previously assigned a name to a cell or cell range using the Named Range dialog, you can specify cells and ranges by this name rather than by pointing or by coordinates.

Named Ranges

The following guidelines apply to named ranges:

• XESS differentiates between upper and lower-case letters in range names. For example, XESS would consider "Range1" and "range1" to be different names.
• Names must begin with an uppercase or lower-case alphabetic character. The rest of the name may include any combination of alphabetic characters, numeric characters, dollar signs($), and underscores (_) . If the name does not satisfy this rule, it must be enclosed within single quotes (').
• When an area containing a named range or cell is moved, XESS automatically updates the definition of the name to reflect the new location.
• Named cells and ranges can have relative or absolute addresses. When cells containing references to named cells with absolute addresses are moved, the references are not updated.
• When cells containing a reference to a named cell or range are copied, XESS converts the reference to an appropriately translated standard reference.
• If you redefine a name, all instances of that name in the workbook are updated as soon as the workbook is recalculated.
• If you remove a name definition, all references to that name in the workbook are converted to appropriate standard references.
• Named ranges can reference three-dimensional ranges by qualifying the upper left cell with the front sheet name and qualifying the bottom right cell with the back sheet name.

To name a cell or range:

1. Select the cell or range to be named.
2. Select Named Range... from the Edit menu. XESS displays the Named Range dialog box.
3. In the Selection entry box, the current cell or range is displayed. Click on the entry box before the equal (=) sign and enter a name.
4. Click Set or press [Enter]. Click Cancel to dismiss the dialog box.

To clear the name from a cell or range:

1. Select Named Range... from the Edit menu. XESS displays the Named Range dialog box.
2. Click on an entry in the Named Range List box. XESS displays your choice in the Selection entry box.
3. Click Clear.

Copying Data

The most common ways to copy data are:

• With the mouse
• Through the Clipboard
• With the Copy menu
• With the Transpose tool

You can also copy between workbooks using either the mouse or the Clipboard.

Copying with the Mouse

Selection-Paste can be used within the current sheet, between XESS workbooks, and between XESS and other applications. If XESS is unable to exchange cell information with the other application, the current values are passed as text.

Selection-Paste is the standard convention for most X applications including terminal windows, text editors, and utility applications. This makes it easy to do things like selecting a file directory listing in a terminal window and pasting it directly into a sheet where the data might be sorted and graphed.

Using the Clipboard

Items copied to the clipboard may be pasted into several different locations by using repeated paste operations. The clipboard holds its contents until the next cut or copy to the clipboard.

Using the Copy Menu

Copy Formulas

Copies formulas from a selected cell or cell range into a selected cell or range, overwriting existing data in the destination range. XESS changes cell references to reflect the new location, unless the cell references are absolute (marked with a dollar sign, $), which indicates that they should not change.

Copy Values

Copies values from an active cell or cell range into a designated cell or range, overwriting existing data in the destination range. When copying cells that contain formulas, XESS copies the present value of the cell, not the formula, and enters it into the destination cell or range as a constant.

• If you specify one source cell and one destination cell, XESS copies the original and places the contents in the destination cell. The original cell is unchanged.
• If you specify one source cell and more than one destination cell, XESS duplicates the original cell in each cell of the target range.
• If you specify a range of cells to copy and specify an equivalent destination range (one with an equivalent number of rows and columns), XESS copies each cell in the origin range into its counterpart cell in the destination range.
• If you specify a source range that spans only one row but a destination range that spans several rows, XESS repeats the data in each row. (Note: The source and destination ranges must have the same number of columns.)
• If you specify a source range that spans only one column but a destination range that spans several columns, XESS repeats the data in each column. (Note: The source and destination ranges must have the same number of rows.)
• If you specify a destination range that contains fewer cells than the source range, XESS returns the message illegal source/destination, and nothing is copied.
• If you specify a destination range with a different number of rows and columns from the source range, XESS returns the message illegal combination of source/destination range, and nothing is copied.
• In order for cell attributes to be copied, the cell must not be empty.

Note: You can make multiple copies of cells or cell ranges in one operation, simply by making the destination range longer or wider than the source destination.

Copying Formulas

To create an absolute cell reference -- one that XESS will not translate if the cell contents are copied or moved -- insert a dollar sign ( $ ) before each component of the cell reference (e.g., $A$1). You can use the [F4] key to insert the $ into formulas.

To copy formulas from one cell or range to another:

1. Select a cell or range of cells whose formulas or values are to be copied. For example, select A1 through A10.
2. Select Copy from the Edit menu, MB3 popup, or press Ctrl+f, and display its submenu.
3. Select Copy Formulas from the Copy submenu. XESS displays the Copying formulas from A1..A10, select destination Range/Address... in the Message Line.
4. Using the mouse, click on a cell or select a range to which the formulas should be copied. For example, click on cell E1 to copy the formulas to cells E1 through E10, or select the range E1..G1 to copy three columns of the formulas in cells E1 through G10. XESS copies the formulas and their values from the source range to the destination range, overwriting any existing data.

Alternatively, use the the Select Range dialog box from the Edit menu to select a destination range.

Remember that copying with the mouse is usually an easier operation.

Copy Formulas can be use to copy cells with repeating characters or graphs that are stored in the sheet.

Copying Values

If you have used formulas to build part of your sheet and no longer need the formulas themselves, converting the formulas to values can save memory and reduce recalculation times.

To copy values from one part of the workbook to another:

1. Select a cell or range of cells whose values are to be copied. For example select A1 through A10.
2. Select Copy from the Edit menu, MB3 popup, or press Ctrl+v, and display its submenu.
3. Select Copy Values from the Copy submenu. XESS displays copying values from A1..A10, select destination Range/Address... in the Message Line. Using the mouse, click on a cell or select a range to which the values should be copied. For example, click on cell C1 to copy the values to cells C1 through C10, or select the range E1..G1 to copy three columns of the values in cells E1 through G10. XESS copies the values from the source range to the destination range, overwriting any existing data.

Alternatively, use the the Select Range dialog box from the Edit menu to select a destination range.

Copying Large Blocks of Data

To copy a large block of data using the Select operation:

1. Select Select... from the Edit menu or MB3 popup menu to display the Select Range dialog box.
2. Enter the range you wish to copy and click OK or Apply.
3. Select the desired Copy operation from the Edit menu or MB3 popup menu.
4. To complete the operation, identify the beginning target cell by scrolling to the location and clicking in the cell.

1. Select the Go To... operation from the View menu.
2. Enter the target cell address and click OK or Apply.

1. Select the cell to be copied with the mouse.
2. Select the desired Copy operation from the Edit menu or MB3 popup menu.
3. Select Select... from the Edit menu or MB3 popup menu to display the Select Range dialog.
4. Enter the target range and click OK or Apply. This will cause the target area to be selected only.
5. Press [Enter] to complete the copy operation.

Tailoring Copy Options

Transpose Tool

The Transpose command can copy the values, copy the formulas, or move the formulas. With Transpose>Move and Transpose>Formulas, the cell references are updated appropriately.

Copy Options Dialog

• Copy Formats
• Copy Fonts
• Copy Alignment
• Copy Colors
• Copy Range Names
• Copy Cell Notes
• Copy Patterns
• Copy Borders

1. Display "Copy Options..." dialog box from the Options menu.
2. Click on the check box to activate/deactivate one or more options.
3. Click OK (or press Return) to accept settings and dismiss dialog. Click APPLY to accept settings while retaining dialog. Click CANCEL to dismiss the dialog box.

Moving Data

To move data in a cell or range of cells:

1. Select a cell or range of cells to be moved. For example, select A1 through A10.
2. Select Move from the Edit menu, MB3 popup, or press Ctrl+m. XESS displays moving from A1..A10, select destination Range/Address... in the Message Line. Using the mouse, click on the cell to which the values should be moved.

For example, click on cell A17 to move the contents to cells A17 through A26. XESS clears the original cell location and overwrites any existing data in the destination range with values and formulas from the source. Alternatively, use the the Select Range dialog box from the Edit menu to select a destination cell.

Cells can also be moved by doing a Cut to Clipboard followed by a Paste from Clipboard at the appropriate destination. Effects of Moving Cells

• When you move a cell that is referenced by a formula, XESS automatically updates the formula for the new location, even if the cell reference is absolute.
• When you move a cell in a range that is referenced by a formula, the formula still references the original range. However, if you move a corner cell of the range, XESS extends or contracts the range to match the new location.
• If you move a cell range that has been named, or one referenced in a formula, XESS automatically updates the definition of the range name or the formula reference to reflect the new location.
• If you move part of a cell range that has been named or referenced in a formula, it can cause problems. XESS updates range names and references only when you move one or more corner cells of the range. If you move cells in the interior of the range, XESS will not change the range name or any references to it.

Note: If you make a mistake when copying or moving data, you can use Undo from the Edit menu to restore the workbook to its state prior to the copy or move operation.

Copying or Moving Data from one Workbook to Another

You cannot move data from one workbook to another using the Move operation. You can accomplish the same effect, however, with Cut to Clipboard followed by Paste from Clipboard. You can also paste data to and from other X Windows application. For more information, see Interacting with Other Applications.

Removing Data -- Clear, Delete, and Cut

• The Clear operation from the Edit menu or MB3 popup clears all data from a designated cell or range of cells and makes the cells empty.
• The Delete operation from the Edit menu or MB3 deletes the current rows or columns and all data contained in the rows or columns.
• The Cut to Clipboard clears the cells in the selection.

Clearing Cells

1. Select a cell or range of cells to be cleared.
2. Select Clear from the Edit menu, MB3 popup, or press Delete). XESS makes the selected cells be empty.

Deleting Columns and Rows

To delete a row:

1. Use the mouse to select the row(s) you wish to delete. Place the mouse pointer over the number which identifies the row, and click the left mouse button. To select more than one row, place the mouse pointer over the number, drag the mouse (holding down the left mouse button) to the desired ending number, and release the left mouse button.
2. Select Delete from the Edit menu or MB3 popup to display its submenu.
3. Select Delete Row from the Delete submenu. XESS deletes the row(s) and all data contained in that row. XESS re-addresses the remaining rows and rewrites cell references where necessary to be consistent with the new row numbering.

To delete a column:

1. Use the mouse to select the column(s) you wish to delete. Place the mouse pointer over the letter which identifies the column, and click the left mouse button. To select more than one column, place the mouse pointer over the letter, drag the mouse (holding down the left mouse button) to the desired ending letter, and release the left mouse button.
2. Select Delete from the Edit menu or MB3 popup to display its submenu.
3. Select Delete Column, from the Delete submenu. XESS deletes the column(s) and all data contained in that column. XESS re-addresses the remaining columns and rewrites cell references where necessary to be consistent with the new column lettering.

Calculations

How XESS Calculates

XESS supports several recalculation options:

• Mode (Manual or Automatic)
• Method (Foreground or As Needed)
• Iteration Limit
• Constraint Checking

Mode of Recalculation

Method of Recalculation

Foreground

As Needed

Note that As Needed does not support Constraint Checking and Iterative Recalculation options.

Iterative Calculations

In some cases cyclic dependencies are useful in that they can represent iterative calculations, which XESS supports. Iterative calculation is useful when two or more cells mutually depend on each other such that each time they are recalculated, their values become closer and closer to the desired answer.

When the Iteration Limit field in the Recalc Options dialog box is set to a non-zero value and Method is set to Foreground, iterative calculation is enabled. In this mode, XESS will make multiple recalculation passes, still preserving the natural order and minimal recalculation rules described above, until either the iteration limit has been reached or, if constraint checking is enabled, until all constraints are satisfied. The @INIT function helps perform iterative calculations.

Constraint-Checking

Precision and Accuracy

A simple example of the problem with fractions is to enter the value 0.01 into 100 cells and then sum the values and subtract 1. The result is not 0. Rather, it has small value in the 16th decimal place. The reason for this round-off error is that the decimal value .01 cannot be exactly represented as a binary fraction -- just as the value 2/3 cannot be exactly represented as a decimal number.

This behavior is well known and well understood in scientific and statistical computing, but can cause surprise when first using spreadsheet products to work with currencies. This behavior is found in all popular spreadsheet products.

Calculations with logical operators -- ! (logical NOT), && (logical AND), || (logical OR), and ?: (conditional) -- consider a non-zero value to be True and a zero value to be False. Integer operators -- ~ (complement), & (bitwise AND), | (bitwise OR), ^ (bitwise EXCLUSIVE-OR), and % (modulus) convert their operands to 32-bit integers before performing the operation.

Formulas

XESS formulas can calculate with numbers, text, logical values, cell references, and other formulas. For example, you can easily calculate the sum of a series of cells, the total of values in a column, a minimum or maximum value within a range, the rounded result of another formula, or the absolute value of a cell entry. Formulas can express complex interdependencies among cells, and they can define constraints on the calculation, such as limits on acceptable values or specific conditions under which a calculation should take place.

Once entered in a cell, formulas are hidden behind the scenes, perform their work in the background, and display only the result of their calculation. To view the formula in a cell, simply select the cell.

XESS displays the formula in the Edit Line just below the main menu bar. You can edit the formula or values in cells at any time.

XESS also provides a wide variety of functions that perform certain tasks. Functions can be used alone or in conjunction with formulas and other functions. XESS provides many specialized functions in addition to those that are usually found in typical financial spreadsheets.

Formula Syntax

The expression part of XESS formulas look just like algebraic formulas; they contain values and operators that define the relationships between values.

XESS uses the following conventions for formulas:

• A formula must begin with an equal (=) sign. If the first character you type is one of the following, an equal sign is automatically inserted for you: 0 1 2 3 4 5 6 7 8 9 . + - @ =
• A formula may have as many as 4095 bytes. You can enter spaces if you wish, but XESS automatically removes them.

  To change a formula in a cell, move the mouse pointer to the cell and choose the Edit Cell operation from the Edit menu or position the cursor in the formula display in the Edit Line.

  Formula Values

  Formulas can contain any or all of the following types of values:
• Numbers, such as 123, -123, 12.3
• Addresses of single cells, such as A1, D5, Sheet3!Z100
• Addresses of cell ranges such as B12..G29, A1..D5
• Absolute cell references denoted with dollar signs before the fixed coordinate ($A$1, $A1, or A$1), which will not be updated when the referencing cell is moved or copied.
• User-defined cell names or cell range names, such as TOTALS or PROJECT1
• XESS functions, such as @SUM or @RADIANS, with their arguments
• Text surrounded by double quotation marks, such as "The sum is " or "Total"

Formula Operators

XESS Formulas can contain the following operators to define relationship between values.

Operator Precedence      Definition

   %       14           Unary percent
   **      13           Exponentiation
   +       12           Unary  plus
   -       12           Unary minus (negation)
   ~       12           Bitwise complement (integer)
   !       12           Logical not
   *       11           Multiplication
   /       11           Division
   %       11           Remainder (integer)
   +       10           Addition
   -       10           Subtraction
   <<       9           Shift left (integer)
   >>       9           Shift right (integer)
   <        8           Less Than
   >        8           Greater Than
   <=       8           Less Than or Equal
   >=       8           Greater Than or Equal
   ==       7           Equal
   !=       7           Not Equal
   &        6           Bitwise And (integer) or
                        String Concatenation
   ^        5           Bitwise Exclusive Or (integer)
   |        4           Bitwise Or (integer)
   &&       3           Logical And
   ||       2           Logical Or
   ?:       1           Conditional

In formulas with more than one operator, XESS calculates operators in the order of precedence presented above, with highest precedence first. That is, AND/OR/NOT relationships are considered after equal/unequal relationships, and subtraction/ addition are considered after multiplication/division. Operators at the same precedence level are evaluated from left to right.

To change the precedence of operators, enclose highest priority operations in parentheses.

Here are some special notes about XESS operators:

• The operators marked "(integer)" on the table above automatically convert their operands to integers.
• The & operator performs double duty: as a bit-wise "and" if the operands are numbers or as a string concatenation operator joining two strings together if the operands are text.
• The % operator also performs double duty: as the "percent" operator when appended to a number or numeric expression, or as the C-style "modulus" operator when applied between two integer expressions.
• Operators that define equality/inequality relationships (such as == and < ) can be used to compare text strings lexically (alphabetically).
• In comparing mixed strings lexically, XESS considers string operands to be lower than numeric operands.
• The conditional operator returns its second operand if its first operand evaluates "True" (non-zero) and returns its third operand if it evaluates "False," (zero).
• In formulas with conditional operators, the second and third operands may be any type XESS supports, including ranges. For example, the expression

  
  	@SUM(A1?B1..C20:C10..D15)
  
  

  returns the sum of B1..C20 if A1 evaluates to non-zero; otherwise it returns the sum of C10..D15.
• XESS accepts most arithmetic operators used in other spreadsheets, but there are a few differences in syntax and precedence.

Case and Accent Sensitivity in String Comparisons

Empty Cells and Trailing Spaces

Referencing Other Cells in Formulas

• type the row/column coordinates, or address, in the formula.
• use the mouse or keyboard to highlight the cell or range of cells you wish to reference in the formula.
• type the assigned name of a cell or range of cells in the formula.
• use @XVALUE to reference values in other workbooks.
• use workbook-qualified cells and ranges to reference values in other workbooks.

To reference a cell by address:

Type the row and column coordinates of the cell in the formula. For example, to reference Row 5 in Column D, type D5

While entering a formula, move the mouse pointer to the cell to be referenced and click once. In input mode, XESS inserts the cell reference after the last character entered. In edit mode, XESS inserts the cell reference at the cursor location in the Edit Line.

To reference a contiguous group of cells by address:

Type the row and column coordinates of two cells in opposite corners of the block to be referenced, with two periods ( .. ) between the coordinates. For example, to reference the first five columns and the first five rows of the sheet, type A1..E5.

Move the mouse pointer to one corner of the block or range to be referenced. Press the left mouse button and drag the mouse to the opposite corner, then release the mouse button. In input mode,

XESS inserts the cell reference after the last character typed. In edit mode, XESS inserts the cell reference at the cursor location.

To reference a cell or range by name, type the pre-assigned name of the cell or cell block into the formula.

To assign a name to a cell or range of cells, use the Named Range utility in the Edit menu.

Types of Cell References

Relative Reference

Absolute Reference

To specify an absolute cell address, insert a dollar sign ($) before the address coordinate to be fixed, or before both coordinates if the row and column coordinates are to be fixed. For example: $B$2.

To specify all or part of a cell address to be absolute, insert a dollar sign ($) before the address coordinate to remain fixed For example:

• $B$5 makes the complete address absolute.
• $B5 makes the B coordinate absolute, the row coordinate relative
• B$5 makes the column coordinate relative, the 5 absolute.

Cell ranges are also relative, so when you move a cell range, references in formulas within that range are updated to reflect their new location.

To specify an absolute range reference, insert dollar signs ($) before the coordinates in the formula. For example, to make the range A1..D5 absolute, type the reference as $A$1..$D$5.

To specify part of a cell range to be absolute, insert dollar signs only before the coordinates to remain absolute. For example, $A1..$D5 will fix the column coordinates of cell references but adjust the row coordinates to reflect the new location.

You can use the [F4] key when editing a cell to toggle through the addressing modes in a reference.

Indirect Reference

The format is as follows: #{column offset,row offset}

• If you include only one value in the offset, XESS assumes that it is a column offset. For example, the offset reference #{-1} tells XESS to look to the column just left of the current cell.
• The offset values may be constants or expressions. Examples: #{0,-1} refers to the cell above the current cell. #{-2} refers to the cell two columns left of the current cell. #{1} refers to the cell to the right of the current cell. #{0,1} refers to the cell below the current cell.

@CSUM(C4..C100,#{-1} == "Joe") calculates the sum of all the values in the range C4..C100 for which the cell in the column to the left contains the string "Joe."

@CCOUNT(C4..C100,# > #{0,-1}) counts all the cells in the range C4..C100 whose value is greater than the contents of the cell immediately above.

@XVALUE("master.xs5",#) returns the value of the same cell reference in which this function is stored from the sheet indicated.

#{-1}+2 adds 2 to the cell value from the cell to the left.

Sheet Names in References

	Sheet1, Sheet2,...Sheetn

The alternate form of sheet names is the same as column names:

	A,B,C...AMI

The style of names used for new sheets is specified in the Workbook Defaults dialog and is controlled by the *defaultSheetNameFormat resource with values of N (numeric) or A (alphabetic). Sheets can also be given user-defined names. Sheet names are separated from cell specifications by either a period (.) or an exclamation point (!).

The upper left cell in the first sheet is: Sheet1.A1 or A.A1 Sheet1!A1 or A!A1

If an explicit sheet name is not specified, the cell reference is assumed to be on the current sheet. Sheet names (like column names) are case insensitive. Sheet1 is the same as SHEET1 and sheet1.

One or both of the cells which define the boundaries for a range may be qualified with a sheet name. Thus:

	Sheet3.A1..Sheet9.A1 or C.A1..I.A1

	Sheet3!A1..A5 or C.A1..A5

	Sheet1.A1..Sheet10.E10 or A.A1..J.E10

Just as row and column references may be relative or absolute, so can sheet names. Place a dollar sign ($) before the sheet name to prevent it from changing when formulas are are copied from one sheet to another.

Set Sheet Name Dialog

To change the name of a sheet:

1. Select the sheet whose name you want to modify.
2. Choose Set Sheet Name... from the Edit menu. The Set Sheet Name dialog is displayed containing the current sheet name.
3. Edit the sheet name to contain the new name.
4. Click OK. All references to the sheet name are updated.

              "cr�pe"!A5
              "1997 Sales"!B5..J5
              "Sales/Quarter"!B12

Referencing Cells in Other Workbooks

External cell references have the filename of the workbook enclosed in quotes and followed by two colons (::):

     
     "book2.xs5"::Sheet1!A4
     "/home/test/olddata.xls"::Sheet4.C1..C17
     "part-table.xs5"::namedrange

If the referenced workbook is not active, the values of the cells are retrieved from the file on disk. If the referenced workbook file is opened by XESS, then the values dynamically change and the referencing workbook is recalculated as required.

Constraint Expressions

Constraint expressions establish conditions under which a formula operates or boundaries for valid results of the formula. Constraint expressions may be simple equality/inequality relationships, or they can be arbitrary formulas. Any valid XESS expression which returns a numeric value is also a valid constraint expression. However, unlike the expression that defines a cell value, a constraint expression can reference the cell in which it resides, using the symbol #.

For example, the formula

	=A1 + A2 ; #>2 && #<=B5 || #==C7

Constraint expressions are used in several other contexts within XESS, including the Find and Extract Tools, and the conditional statistical functions.

The benefit of constraint expressions is maximized when combined with indirect reference support (#) as illustrated in the above example.

Explicit Dependency

	=@SUM(A1..A20)\D50

This feature is particularly important when you have a constraint expression containing an offset reference that produces a cell reference outside the cell range referenced in a dependent formula. Under these circumstances, Automatic Recalculation would not necessarily be triggered. Take for instance, the example from above:

@CCOUNT(C4..C100,# > #{0,-1}) counts all the cells in the range C4..C100 whose value is greater than the contents of the cell immediately above.

In order for C4 to be evaluated, it must be compared to C3 - which is not part of the explicit range, C4..C100. Without indicating an explicit dependency, C4 would never be evaluated properly. So, in this case, we could indicate the dependency as follows:

       @CCOUNT(C4..C100,# > #{0,-1})\C3..C99

Changing Calculation Options

• Recalculation Mode
• Recalculation Method
• Iteration Limit
• Recalculate on Load
• Constraint Checking

To change or view the calculation options using the Recalc Options dialog box:

1. Select Recalc Options from the Options menu to display the Recalc Options dialog box.
2. Change any of the options (described below).
3. Apply your selection by clicking OK or Apply.

Setting Recalculation Method

As Needed means that recalculation is highly optimized to recompute the minimum number of cells when updating the workbook. This can result in significant performance improvement for certain types of workbooks, particularly on very large workbooks.

As Needed does not support Constraint Checking and Iteration Limits.

Foreground means that minimal recalculation is performed, similar to other spreadsheets.

To change recalculation method:

1. Select Recalc Options from the Options menu. XESS displays the Recalc Options dialog box.
2. Move the pointer to the Recalc Method option button. While holding down the left mouse button to display the options point to the desired option and release.
3. Apply your selection by clicking OK or Apply.

Controlling Automatic Recalculation

Disabling Automatic Recalculation

1. Select Recalc Options from the Options menu. XESS displays the Recalc Options dialog box.
2. Move the pointer to the Recalc Mode option button. While holding down the left mouse button to display the options point to desired option and release.
3. Apply your selection by clicking OK or Apply.

Manual Recalculation

Recalculate on Load

Enabling Constraint-Checking

To turn constraint-checking on:

1. Select Recalc Options from the Options menu. XESS displays the Recalc Options dialog box.
2. Click the Constraint Checking check button on.
3. Apply your selection by clicking OK or Apply.

Setting Iteration Limits

To set iteration limit:

1. Select Recalc Options from the Options menu. XESS displays the Recalc Options dialog box.
2. Move the Iteration Limit slider bar to the desired number.
3. Apply your selection by clicking OK or Apply.

Highlighting Special Calculated Results

Functions

Other functions are used for more specialized purposes such as computing the future value of an investment or the product of multiplying one cell range by another range. Some functions perform calculations that arithmetic operators cannot handle such as text-string manipulations.

XESS functions fall into the following categories:

• Mathematical
• Statistical
• Conditional Statistical
• String
• Logic
• Digital Logic
• Financial
• Date and Time
• Miscellaneous

Using XESS Functions

• Start the function name with the "at" sign (@), so XESS recognizes the string as a function. Function names are abbreviations that indicate what the function does. For instance, @ABS computes absolute value, @ROUND rounds to the specified number of places, and @AVG computes the average of a list of arguments.

  Function names can be entered without the initial @ as long as a left parenthesis immediately follows the function name. The User Preferences dialog controls whether the @ is displayed in formulas.

• After typing the function name, enter arguments enclosed in parentheses. Most functions use one or more arguments to define the task to be performed. For example, the @AVG function averages the value of two or more arguments. The @LENGTH function returns the length of an argument that is a character string.
• Use only the arguments required by the function, in the exact order specified in the function syntax. If you enter other arguments or enter them in the wrong order, XESS will misinterpret their meaning or return an error message.
• All the function names in this section are shown in uppercase letters, but you may enter them in upper or lower-case for your entries.

Arguments

The following chart shows different types of arguments used in XESS functions.

      Argument                 Example
      
      Numeric Value            123
      Address of a cell        A10
      Address of a range       F9..F99
      Name of a cell or range  BUDGET1, where BUDGET1 is the range D9..K9
      String Value             "Quarterly Report"
      Matrix constant          {1,1,2,3,5,8,13,21}

Using Operators with Functions

Explicit Dependencies

          =@CCOUNT(C3..C100,#>#{-1} )

          =@CCOUNT(C3..C100,#>#{-1} )\B3..B10

Matrix and Table Results

This notion of explicit dependencies is important for recalculation. It guarantees that any cell that references B3 will not be recalculated until after cell B2 is recalculated. This ensures that data generated by the formulas is always current.

Functions by Category

Mathematical Functions

XESS also provides a full range of trigonometric functions as well as ones for matrix arithmetic and manipulation. Mathematical functions perform calculations with numeric values as arguments, returning numeric values.

@ABS(X)

The absolute value of X

@ACOS(X)

The arc cosine of X

@ACOSH(X)

The hyperbolic arc cosine of X

@ACOT(X)

The arc cotangent of X

@ACOTH(X)

The hyperbolic arc cotangent of X

@ACSC(X)

The arc cosecant of X

@ACSCH(X)

The hyperbolic arc cosecant of X

@ASEC(X)

The arc secant of X

@ASECH(X)

The hyperbolic arc secant of X

@ASIN(X)

The arc sine of X

@ASINH(X)

The hyperbolic arc sine of X

@ATAN(X)

The 2-quadrant arc tangent of X

@ATAN2(X,Y)

The 4-quadrant arc tangent of Y/X

@ATANH(X)

The hyperbolic arc tangent of X

@BESSELI(X,N)

The Bessel function of order In(x).

@BESSELJ(X,N)

The Bessel function of order Jn(x).

@BESSELK(X,N)

The Bessel function of order Kn(x).

@BESSELY(X,N)

The Bessel function of order Yn(x).

@BETA(Z,W)

The beta function of z and w

@BETAI(A,B,X)

The incomplete beta function

@CEIL(X)

The smallest integer greater than or equal to X

@COMPLEX(A,B[,S])

The complex number defined by the coefficients a and b of the form "a + bi" or "a + bj",

@COS(X)

The cosine of X

@COSH(X)

The hyperbolic cosine of X

@COT(X)

The cotangent of X

@COTH(X)

The hyperbolic cotangent of X

@CSC(X)

The cosecant of X

@CSCH(X)

The hyperbolic cosecant of X

@DEGREES(X)

Converts the angle expressed in radians to degrees (180/p*X)

@DELTA(X,Y)

Returns 1 if numbers are equal

@DET(M)

The determinant of the matrix range M, which must be a square matrix

@DFT(R)

The Discrete Fourier Transform of the range R

@DOT(R1,R2)

The dot product of the vectors R1 and R2

@EIGEN(M)

The eigenvalues of the matrix M

@EVEN(X)

The next larger even integer

@EXP(X)

e raised to the X power

@FACT(N)

The value of N!

@FACTDOUBLE(N)

The value of N * (N-2) * (N-4) ...

@FFT(R)

The Discrete Fourier Transform of the range R using a fast Fourier Transform algorithm

@FLOOR(X)

The largest integer less than or equal to X

@FRAC(X)

The fractional portion of X

@GAMMA(X)

The value of the gamma function evaluated at X

@GCD(X,...)

Greatest Common Divisor of all the numeric values in the argument list

@GRAND(

A 12th-degree binomial approximation to a Gaussian random number with zero mean and unit variance

@IMABS(A)

The absolute value (modulus) of the complex number A.

@IMAGINARY(A)

The imaginary coefficient of the complex number A.

@IMARGUMENT(A)

The argument (or amplitude) of the complex number A.

@IMCONJUGATE(A)

The conjugate of the complex number A.

@IMCOS(A)

The cosine of the complex number A.

@IMDIV(A,B)

The quotient of A divided by B.

@IMEXP(A)

The exponetial of the complex number A.

@IMLN(A)

The natural logarithm of the complex number A.

@IMLOG10(A)

The common logarithm (base 10) of the complex number A.

@IMLOG2(A)

The logarithm base 2 of the complex number A.

@IMPOWER(A, X)

The complex number A raised to the power X.

@IMPRODUCT(...)

The product of the complex arguments

@IMREAL(A)

The real componnent of the complex number A.

@IMSIN(A)

The sine of the complex number A.

@IMSQRT(A)

The square root of the complex number A.

@IMSUB(A, B)

The difference between the complex numbers A and B.

@IMSUM(...)

The sum of the complex arguments

@INT(X)

The integer portion of X

@INVDFT(R)

The inverse of the Discrete Fourier Transform of the range R

@INVERT(M)

The inverse of matrix M

@INVFFT(R)

The inverse of the Discrete Fourier Transform of the range R using a fast Fourier Transform algorithm

@LCM(X,...)

Lowest Common Multiple of all the numeric values in the argument list

@LN(X)

The natural log (base e) of X

@LNGAMMA(X)

The log base e of the gamma function evaluated at X

@LOG(X)

The log base 10 of X (included for compatibility with WKS/WK1)

@LOG10(X)

The log base 10 of X

@LOG2(X)

The log base 2 of X

@MADD(M1,M2)

The result of adding matrix M2 to matrix M1

@MMUL(M1,M2)

The product of multiplying matrix M2 by matrix M1

@MOD(X,Y)

The remainder of X/Y

@MODULUS(X,Y)

The modulus of X/Y

@MSUB(M1,M2)

The result of subtracting matrix M2 from matrix M1

@ODD(X)

The next larger odd integer

@PI

The value of p

@POLY(X,...)

The value of an Nth-degree polynomial in X

@PRODUCT(X,...)

The product of all the numeric values in the argument list.

@RADIANS(X)

Converts the angle expressed in degrees to radians (p/180*X)

@RAND

A uniform random number on the interval 0,1

@ROUND(X,n)

X rounded to n number of decimal places (0 to 15)

@ROUNDDOWN(X,n)

X rounded towards zero at n decimal places (-15 to 15)

@ROUNDUP(X,n)

X rounded away from zero at n decimal places (-15 to 15)

@SEC(X)

The secant of X

@SECH(X)

The hyperbolic secant of X

@SIGMOID(X)

The value of the sigmoid function 1/(1 + exp(-X))

@SIGN(X)

The sign of X as (-1, 0, 1).

@SIN(X)

The sine of X

@SINH(X)

The hyperbolic sine of X

@SQRT(X)

The positive square root of X

@SQRTPI(X)

The positive square root of X*@PI

@SUMPRODUCT(R1,R2)

The dot product of the vectors R1 and R2, where R1 and R2 are of equal dimension

@TAN(X)

The tangent of X

@TANH(X)

The hyperbolic tangent of X

@TRANSPOSE(M)

The transpose of matrix M

@TRUNC(X,N)

Returns the value of X truncated to N decimal places

@VECLEN(...)

The square root of the sum of squares of its arguments

Statistical Functions

Statistical functions return numeric values.

@AVERAGE(...)

The average (arithmetic mean) of its arguments

@AVG(...)

The average (arithmetic mean) of its arguments

@CORR(R1,R2)

Pearson's product-moment correlation coefficient for the paired data in ranges R1 and R2

@COUNT(...)

A count of its non-blank arguments

@F(M,N,F)

The integral of Snedecor's F-distribution with M and N degrees of freedom from minus infinity to F

@ERF(L[,U])

Error function integrated between 0 and L; if U specified, between L and U.

@ERFC(L)

Complementary error function integrated between L and infinity.

@FORECAST(...)

Predicted Y values for given X

@FREQUENCY(R,B)

Returns a frequency distribution for values R with a set of intervals B.

@FTEST(R1,R2)

The significance level (alpha) of the two-sided F-test on the variances of the data specified by ranges R1 and R2

@GMEAN(...)

The geometric mean of its arguments

@HMEAN(...)

The harmonic mean of its arguments

@LARGE(R,N)

The Nth largest value in range R.

@LINCOEF(X,Y)

The least squares coefficients for the straight line fit

@LINFIT(X,Y)

The straight line least squares fit. This function is equivalent to @POLYFIT(X,Y,1)

@LLS(A,Y)

The linear least squares solution X to the over-determined system of equations AX=Y

@MAX(...)

The maximum of its arguments

@MEAN(...)

The average (arithmetic mean) of its arguments

@MEDIAN(...)

The median (middle value) of the range R1.

@MIN(...)

The minimum of its arguments

@MODE(...)

The mode, or most frequently occurring value

@MSQ(...)

The mean of the squares of its arguments

@PERCENTILE(R,N) The

value from the range R which is at the Nth percentile in R.

@PERCENTRANK(R,N)

The percentile rank of the number N among the values in range R.

@PERMUT(S,T)

The number of T objects that can be chosen from the set S, where order is significant.

@PTTEST(R1,R2)

The significance level (alpha) of the two-sided T-test for the paired samples contained in ranges R1 and R2

@QUARTILE(R,Q)

The quartile Q of the data in range R.

@PLS(X,Y,d)

Analyzes the least squares polynomial model

@POLYCOEF(X,Y,d)

The least squares coefficients for the polynomial fit

@POLYFIT(X,Y,d)

The least squares polynomial fit, given a vector of independent variables (X), dependent variables (Y), and the degree (d) of polynomial.

@PURECOUNT(...)

A count of its non-string arguments

@RANK(E,R[,O])

The rank of a numeric argument E in the range R.

@RMS(...)

The root of the mean of squares of its arguments

@SMALL(R,N)

The Nth smallest number in range R.

@SSE(...)

The sum squared error of its arguments. It is equivalent to @VAR(...) * @COUNT(...)

@SSQ(...)

The sum of squares of its arguments

@STD(...)

The population standard deviation (N weighting) of its arguments

@STDS(...)

The sample standard deviation (N-1 weighting) of its arguments

@SUM(...)

The sum of its arguments

@SUMNEGATIVE(...)

The sum of its negative arguments

@SUMPOSITIVE(...)

The sum of its positive arguments

@SUMSQ(...)

The sum of the squares of its arguments

@SUMXMY2(RX,RY)

The sum of the squares of the differences between ranges RX and RY

@SUMX2MY2(RX,RY)

The sum of the differences between the squares of the elements of ranges RX and RY

@SUMXPY2(RX,RY)

The sum of the squares of the addition of elements of ranges RX and RY

@T(N,T)

The integral of Student's T-distribution with N degrees of freedom from minus infinity to T

@TREND(NX,KX,KY) The

y values for new x values given existing x and y values.

@TTEST(R,X)

The significance level (alpha) of the two-sided single population T-test for the population samples contained in range R

@TTEST2EV(R1,R2)

The significance level (alpha) of the two-sided dual population T-test for ranges R1 and R2, where the population variances are equal

@TTEST2UV(R1,R2)

The significance level (alpha) of the two-sided dual population T-test for ranges R1 and R2, where the population variances are not equal

@VAR(...)

The sample variance (N weighting) of its arguments

@VARS(...)

The sample variance (N-1 weighting) of its arguments

@VSUM(...)

The "visual sum" of its arguments, using precision and rounding of formatted cell values

Conditional Statistical Functions

Conditional Statistical Functions return a numeric value.

These functions provide capabilities similar to those of database functions found in other spreadsheet products (e.g. DSUM).

@CAVG(...,C)

Conditional average

@CCOUNT(...,C)

Conditional count

@CMAX(...,C)

Conditional maximum

@CMIN(...,C)

Conditional minimum

@CSTD(...,C)

Conditional sample standard deviation (N weighting)

@CSTDS(...,C)

Conditional sample standard deviation (N-1 weighting)

@CSUM(...,C)

Conditional sum

@CVAR(...,C)

Conditional population variance (N weighting)

@CVARS(...,C)

Conditional population variance (N-1 weighting)

String Functions

String functions return strings or numeric values.

@CHAR(N)

The character represented by the code N

@CLEAN(S)

The string formed by removing all non-printing characters from the string S

@CODE(S)

The integer value of the first byte in string S

@EXACT(S1,S2)

Returns true (1) if string S1 exactly matches string S2, otherwise returns 0

@FIND(S1,S2,N)

The index of the first occurrence of S1 in S2

@FORMAT(F,N,X)

The string formed by formatting the value X using format code F and precision N

@HEXTONUM(S)

The numeric value for the hexadecimal interpretation of S

@LEFT(S,N)

The string composed of the leftmost N characters of S

@LENGTH(S)

The number of characters in S

@LOWER(S)

S converted to lower case

@MID(S,N1,N2)

The string of length N2 that starts at position N1 in S

@NUMTOHEX(X)

The hexadecimal representation of the integer portion of X

@OCCUR(S1,S2)

Returns the number of times string S2 occurs in string S1

@PROPER(S)

The string S with the first letter of each word capitalized

@REGEX(S1,S2)

Returns true (1) if string S1 exactly matches string S2; otherwise returns false (0). Allows "wildcard" comparisons by interpreting S1 as a regular expression.

@REPEAT(S,N)

The string S repeated N times

@REPLACE(S1,N1,N2,S2)

The string formed by replacing the N2 characters starting at position N1 in S1 with string S2

@REVERSE(S)

The reverse order of characters in S.

@RIGHT(S,N)

The string composed of the rightmost N characters of S

@SETSTRING(S,N,F)

Returns a string of lenght N with the string S aligned within (left, right, center)

@STRCAT(...)

The concatenation of all its arguments

@STRING(X,N)

The string representing the numeric value of X, to N decimal places

@STRLEN(...)

The total length of all strings in its arguments

@SUBSTITUTE(S,OLD,NEW,N)

Replaces the Nth occurrence of string OLD in string S with string NEW from beginning and end of the string S

@TRIM(S)

The string formed by removing spaces from beginning and end of the string S

@UPPER(S)

The string S converted to upper case

@VALUE(S)

The numeric value represented by the

@VERIFY(S1,S2)

Returns the number of the first character in S1 not in string S2

Logic Functions

Logic functions return the value 1, 0, or a value.

@FALSE

The logical value 0

@FILEEXISTS(S)

1 if file S can be opened for reading; otherwise 0

@IF(X,T,F)

The value of T if X evaluates to on-zero, or F if X evaluates to zero

@ISERROR(X)

Returns 1 if X contains an error, otherwise 0.

@ISEVEN(X)

1 if integer part of X is even; otherwise 0.

@ISNUMBER(X)

1 if X is a numeric value; otherwise 0

@ISODD(X)

1 if integer part of X is odd; otherwise 0.

@ISSTRING(X)

1 if X is a string value; otherwise 0

@TRUE

The logical value 1

Digital Logic Functions

Digital logic functions return the values 0, 1, or -1 (for unknown). Any argument value whose integer portion is not equal to 0 or 1 is considered unknown. Unknown input values may cause unknown output values. For example, @OR(1,27) returns 1, while @OR(0,27) returns -1.

@AND(...)

0 if any arguments are 0; 1 if all arguments are 1; otherwise -1

@NAND(...)

0 if all arguments are 1; 1 if any arguments are 0; otherwise -1

@NOR(...)

0 if any arguments are 1; 1 if all arguments are 0; otherwise -1

@NOT(X)

0 if X=1; 1 if X=0; otherwise -1

@OR(...)

0 if all arguments are 0; 1 if any arguments are 1; otherwise -1

@XOR(...)

-1 if any of the arguments are not 0 or 1; otherwise 0 if the total number of arguments with the value 1 is even; 1 if the total number of arguments with the value 1 is odd.

Financial Functions

Financial functions are most useful for solving cash flow calculations where you know all but one variable. For example, if you know the present value of an investment, interest rate, and periodic payment, you can use the @FV function to calculate the future value of the investment. If you know the future value and other variables, but need to know the present value, you can use the @PV function.

Many financial functions require specifying a Day Count Basis. A Day Count Basis indicates the way in which the days in a month and the days in a year are to be counted. Most of the financial functions in securities involve 4 different Day Count Basis: 30/360, actual/actual, actual/360 and actual/365.

30/360 Day Count Basis assumes 30-day months and 360-day years (12 months x 30 days). XESS also follows the "End-of-Month" rule which assumes that a security pays interest on the last day of the month and will always make its interest on the last day of the month. Special rules are followed when calculating the days between two dates on 30/360 Day Count Basis.

For example, let Start_Date = D1/M1/Y1, End_Date = D2/M2/Y2.

1. If D1=31, XESS uses 30 for D1.
2. If D2=31, XESS uses 31, unless D1=30 or D1=31. In this case, XESS uses 30.
3. If D1 is the last day of February (D1=28 or 29 in a leap year), XESS uses 30 for D1.
4. If D2 is the last day of February (D2=28 or 29 in a leap year) and D1 is also the last day of February, XESS uses 30 for D2.

Financial functions use the following arguments:

interest rate

The interest rate to be used in the calculations. The rate may be specified as annual, monthly or quarterly, but it must agree with the increment you use for periods. By default the interest rate is an annual rate.

present value

The present value of an investment, representing the amount already received from or committed to an investment. period The number of periods over which the loan, investment or depreciation is to be calculated. The periods may be defined in months, quarters or years, but must agree with the increment used to define interest rate.

future value

The future value of an investment, given a certain present value, interest rate, and number of periods.

cost

The original cost of a depreciable capital asset.

salvage value

The remaining value of a capital asset after the depreciation period has expired. allowable life The allowable life of a depreciable item.

yield

The interest rate that will make the present value of the expected future cash flows equal to the price of the financial instrument.

price

The present value of the expected future cash flows where the discount rate is equal to the yield of the financial instrument.

coupon rate

The annual coupon rate of a security.

frequency

The number of coupon payments in a year.

basis

The day count basis to be used in calculation.

Functions related to fixed income securities usually require special dates as arguments: issue date, settlement date, first coupon date, last coupon date, maturity date of a security. When specified, the following constraints should be followed:

issue > settlement > maturity
issue > first coupon > maturity
issue > last coupon > maturity

@ACCRINT(I,Ft,S,R,P,F[,B])

Accrued interest for a security that pays periodic interest.

@ACCRINTM(I,S,R,P[,B])

Accrued interest for a security that pays interest at maturity

@CTERM(R,FV,PV)

The number of compounding periods for an investment

@COUPDAYBS(S,M,F[,B])

The number of days between the beginning of the coupon period to the settlement date.

@COUPDAYS(S,M,F[,B])

The number of days in the coupon period that the settlement date is in.

@COUPDAYSNC(S,M,F[,B])

The number of days between the settlement date and the next coupon date.

@COUPNCD(S,M,F[,B])

The next coupon date after the settlement date.

@COUPNUM(S,M,F[,B])

The number of coupon payments between the settlement date and maturity date.

@COUPPCD(S,M,F[,B])

The previous (most recent) coupon date before the settlement date.

@CUMIPMT(R,NP,PV,S,E,T)

The cumulative interest on a loan between start period S and end period E.

@CUMPRINC(R,NP,PV,S,E,T)

The cumulative principal paid on a loan between start period S and end period E..

@DB(C,S,L,P[,M])

Fixed-declining depreciation allowance

@DDB(C,S,L,N)

Double-declining depreciation allowance

@DISC(S,M,P,R[,B])

The discount rate for a security.

@DOLLARDE(FD,F)

Converts a dollar amount expressed as a fraction form into a decimal form.

@DOLLARFR(DD,F)

Converts a dollar amount expressed as a decimal form into a fraction form.

@DURATION(S,M,R,Y,F[,B])

The Macauley duration of a security assuming $100 face value.

@EFFECT(NR,NP)

Returns the effective annual interest rate.

@FV(P,R,N)

Future value of an annuity

@FVSCHEDULE(P,S)

The future value of an initial investment after compounding a series of interest rates.

@INTRATE(S,M,I,R[,B])

The interest rate for a fully invested security.

@IPMT(R,P,NP,PV,FV[,T])

The interest payment for a specific period for an investment based on periodic, constant payments and a constant interest rate.

@IRR(G,F)

The internal rate of return on an investment.

@MDURATION(S,M,R,Y,F[,B])

The modified Macauley duration of a security assuming $100 face value.

@MIRR(CF,FR,RR)

The modified internal rate of return for a series of periodic cash flows.

@NOMINAL(ER,NP)

The nominal annual interest rate.

@NPV(R,CF)

The present value of a series of future cash flows at given the rate R and the cash flow CF range

@ODDFPRICE(S,M,I,FC,R,Y,RD,F[,B])

The price per $100 face value of a security with an odd (short or long) first period.

@ODDFYIELD(S,M,I,FC,R,PR,RD,F[,B])

The yield per of a security with an odd (short or long) first period.

@ODDLPRICE(S,M,LC,R,Y,RD,F[,B])

The price per $100 face value of a security with an odd (short or long) last period.

@ODDLYIELD(S,M,LC,R,PR,RD,F[,B])

The yield per of a security with an odd (short or long) first period.

@PMT(PV,R,N)

The periodic payment for a loan

@PPMT(R,P,NP,PV,FV,T)

The payment on the principal for a specific period for an investment based on periodic, constant payments and a constant interest rate.

@PRICE(S,M,R,Y,RD,F[,B])

The price per $100 face value of a security that pays periodic interest

@PRICEDISC(S,M,D,RD[,B])

The price per $100 face value of a discounted security.

@PRICEMAT(S,M,I,R,Y[,B])

The price per $100 face value of a security that pays interest at maturity

@PV(P,R,N)

The present value of an annuity

@RATE(FV,PV,N)

The interest rate required to reach future value FV

@RECEIVED(S,M,I,D,[,B])

The amount received at maturity for a fully vested security

@SLN(C,S,L)

The straight-line depreciation allowance

@SYD(C,S,L,N)

The "sum-of-years-digits" depreciation allowance

@TBILLEQ(S,M,D)

The bond-equivalent yield (BEY) for a Treasury Bill.

@TBILLPRICE(S,M,D)

The price per $100 face value for a Treasury bill.

@TBILLYIELD(S,M,D)

The yield on a Treasury bill.

@TERM(P,R,FV)

The number of payment periods for an investment

@VDB(C,S,L,S,E)

Fixed-declining depreciation allowance between two periods

@XIRR(G,V,D)

Internal rate of return for a series of cash flows with variable intervals

@XNPV(R,V,D)

Returns the net present value for a series of cash flows with variable intervals.

@YIELD(S,M,R,PR,RD,F[,B])

Yield of security paying periodic interest

@YIELDDISC(S,M,PR,RD[,B])

The annual yield for a discounted security

@YIELDMAT(S,M,I,R,PR[,B])

Annual yield of a security which pays interest at security

Date and Time Functions

XESS internally stores date and time information using the same conventions as other popular spreadsheet programs:

Dates

are represented as an integer equal to the number of days before or since December 30, 1899 (or January 1, 1904 for the 1904 date system).

Times

are represented as fractions of a day, starting at midnight. For example, 6:00 AM is stored as .25 (a quarter of a 24-hour day).

@DATE(Y,M,D)

The date value for year Y, month M, and day D

@DATEVALUE(S)

The corresponding date value for a given string S

@DAY(DT)

The day number in the date/time value DT

@DAYS360(S,E)

The number of days between two dates, based on a 30/360 day count system.

@EDATE(S,M)

The date/time value representing number of months (M) before or after start date (S).

@EOMONTH(S,M)

The date/time value representing the last day of the month M months after S, if M is positive, or M months before if M is negative

@HOUR(DT)

The hour value (0-23) of date/time value DT

@MINUTE(DT)

The minute value (0-59) of date/time value DT

@MONTH(DT)

The number of the month in date/time value DT

@NETWORKDAYS(S,E[,H])

The number of whole working days, starting at S and going to E, excluding weekends and holidays.

@NOW

The date/time value of the current system date and time

@SECOND(DT)

The seconds value (0-59) of the date/time value DT

@TIME

The time value for hour H, minute M, and second S

@TIMEVALUE(S)

The corresponding time value for a given string value S

@TODAY

The date value of the current system date

@WEEKDAY(D)

The integer representing the day of the week on which the day D falls. 1 is Sunday, 7 is Saturday.

@WORKDAY(S,D[,H])

The day that is D working days after S, if D is positive, or before S, if D is negative, excluding weekends and all holidays specified as dates in range H

@YEAR(DT)

The year value of date/time value DT

@YEARFRAC(S,E[,B])

The portion of the year represented by the number of days between start date (S) and end date (E).

Miscellaneous Functions

@@(S)

Reference to the cell or range S

@ALARM(X,S)

If X evaluates non-zero, string (S) is evaluated as an expression and displayed in the message line, and the terminal beeps. If X evaluates to zero, S is not evaluated and the return value is zero.

@ANNOTATE(Text,X,Y[,H[,V]])

Generates a dynamic graph annotation text, given the text (Text), coordinates (X,Y) and the alignment values (H,V)

@CELL(S,C)

The value of property S for cell C

@CELLPOINTER(S)

The value of property S for the current cell

@CELLREF(N1,N2)

A reference to the cell in column N1 and row N2

@CHOOSE(N,...)

The Nth argument from the list

@COL(C)

The column address of the cell referenced by C

@COLS(R)

The number of columns in the specified range R

@COORD(SH,C,R,F)

The string cell address equivalent to sheet SH, column C, row R

@DOLLAR(X,N)

Formats the number X using the Dollars format with N decimal places

@ENVIRON(S)

The value of the environmental variable in string S

@ERR

Returns an error indicator

@HLOOKUP(X,R,N)

The value of the cell N rows below the cell in range R that matches the value X

@INDEX(R,C,R[,S])

The element of the range (or array) R indexed by the column C and row R.

@INFO(S)

Returns information item S for current workbook and environment

@INIT(X1,X2)

The first argument on the first recalculation pass and the second argument on all subsequent recalculation passes when XESS is performing iterative calculations

@INTERP2D(R1,R2,N)

The interpolation value for a 2 dimensional vector

@INTERP3D(R,X,Y)

The interpolation value for a 3 dimensional vector.

@MATCH(V,R[,T])

The relative position in range R of value V based on positioning criteria T.

@MESSAGE(X,S)

The string S is displayed on the status line and the terminal beeps, if X evaluates non-zero

@N(R)

The numeric value of the top left cell in range R

@RANGENAME(R)

Returns the name of the first range (alphabetically) containing the cell R

@RANGEREF(N1,N2,N3,N4)

A reference to the range defined by coordinates N1 through N4

@ROMAN(X[,N])

Returns the Roman numeral for the value X and simplification N

@ROW(C)

The row address of the cell referenced by C

@ROWS(R)

The number of rows in the specified range R

@S(R)

The string value of the top left cell in range R

@SALARM(X,S)

Silent alarm. The same as @ALARM with no terminal beep.

@SHEET(R)

The number of the sheet for the top left cell in range R

@SHEETS(R)

The number of sheets in the range R

@TEXT(X,S)

Formats the number X using the format pattern string S

@VLOOKUP(X,C,N)

The value of the cell N columns to the right of the cell in range C that matches the value X

@XHLOOKUP(F,X,R,N)

In the workbook file F, the value of the cell N rows below the cell in range R that matches the value X

@XVALUE(F,R)

In the workbook file F, the value of cell R

@XVLOOKUP(F,X,C,N)

In the workbook file F, the value of the cell N columns to the right of the cell in range C that matches the value X

Functions - Alphabetical Listing

@@

@@ returns a reference to the cell or range represented by S. Also known as an indirect reference.

Example:

@@("B17") = 17, where B17 = 17

@SUM(@@("B2..B7")) = 24, where B2..B7 = 12, 1, 0, 4, and 7

@ABS

@ABS returns the absolute (positive) value of X.

Example:

@ABS(5) = 5

@ABS(-5) = 5

@ABS(C15) = 10, where C15 = -10

@SQRT(@ABS(D4)) = 10, where D4 = -100

@ACCRINT

Examples:

A security has the following terms: 4/1/93 settlement date, 6/1/93 maturity date, 10% semiannual coupon, $1000 par value, 30/360 basis:

@accrint(34060,34121,10%,1000,2,0) = 33.333

@ACCRINTM

@ACCRINTM returns the accrued interest for a security that pays interest at maturity.

Example:

@ACCRINTM(C6,C7,C8.C9,C10) = 39.3442, where C6 = 02/03/1992, C7 = 07/12/1992, C8 = 9%, C9 = 1000 and C10 = 1

@ACOS

@ACOS returns the arc cosine of angle X. The result is the angle (in radians) whose cosine is X and ranges from 0 to p. To convert the result from radians to degrees, use the @DEGREES function.

Examples:

@ACOS(1) = 0

@ACOS(R3) = 1.670963748, where R3 = -0.1

@DEGREES(@ACOS(0)) = 90

@ACOS(-20) = Error - @ACOS, domain is -1 <= x <= 1

@ACOSH

@ACOSH returns the hyperbolic arc cosine of angle X. The result is the angle (in radians) whose hyperbolic cosine is X.

Examples:

@ACOSH(5) = 2.2924317

@ACOSH(F5) = 5.192926, where F5 = 90

@ACOSH(-1) = Error - @ACOSH, domain is x >= 1

@ACOT

@ACOT returns the arc cotangent of X.

Examples:

@ACOT(0) = 1.5707963

@ACOT(20) = 0.049958396

@ACOTH

@ACOTH returns the hyperbolic arc cotangent of X.

Examples:

@ACOTH(40) = 0.02500521

@ACOTH(0) = Error - @ACOTH, domain is x > 1

@ACOTH returns the hyperbolic arc cotangent of X.

Examples:

@ACOTH(40) = 0.02500521

@ACOTH(0) = Error - @ACOTH, domain is x > 1

@ACSC

@ACSC returns the arc cosecant of X.

Examples:

@ACSC(1) = 1.5707963

@ACSC(0.5) = Error - @ACSC, domain is x >= 1

@ACSCH

@ACSCH returns the hyperbolic arc cosecant of X.

Examples:

@ACSCH(1) = 0.88137359

@ACSCH(0) = Error: x must not be zero

@ALARM

@ALARM evaluates expression X. If X evaluates to non-zero, it beeps and returns the result of evaluating string S as an expression. If X evaluates to zero, S is not evaluated and the return value is zero. @ALARM was important because, prior to Version 3, XESS always evaluated both the true and false clauses of @IF. This could cause side effects with remote functions.

Examples:

@ALARM(Pressure > 500, "@REMOTE_FN(Pressure)") If "Pressure" exceeds 500, then the terminal beeps and REMOTE_FN is called with the value of "Pressure".

@AND

@AND returns 1 if all arguments are 1; 0 if any argument is 0; otherwise -1 (unknown).

Examples:

A1=1, B1=1, C1=0, D1=0, E1=1 and A2=24

@AND(A1,B1) = 1

@AND(A1..E1) = 0

@AND(A1..A2) = -1

@ANNOTATE

@ANNOTATE allows you specify a coordinate at which a label (Text) will be displayed in the data area of a 2-dimensional graph, and also how that label will appear in relation to the coordinate. X and Y represent the coordinates of the label on the graph.

H (horizontal alignment)and V (vertical alignment) are optional values that represent the text spacing offset of the label. If V is specified, H must be specified; however, you may specify only H. This is a convenience function for creating strings of the form accepted by the XESS graph annotation mechanism. For more information on annotation see Graphs.

Examples:

@ANNOTATE("Average",5/2,12,1,0.5) = "(2.5,12,1.0,5)Average"

@ANNOTATE("Data Point",5,10)= "(5,10)Data Point"

@ANNOTATE("x^2",6,36,-1/4) = "(6,36,-0.25)x2"

@ASEC

@ASEC returns the arc secant of X.

Examples:

@ASEC(1) = 0

@ASEC(1e+100) = 1.5707963

@ASEC(0) = Error: @ASEC, domain is abs(x) >= 1

@ASECH

@ASECH returns the hyperbolic arc secant of X.

Examples:

@ASECH(0.1) = 2.9932228

@ASECH(1000) = Error: @ASECH, domain is 0 < x < 1

@ASIN

@ASIN returns the arc sine of angle X. The result is the angle (in radians) whose sine is X and ranges from p/2 to -p/2. To convert the result from radians to degrees, use the @DEGREES function.

Examples:

@ASIN(A2) = 1.570796327, where A2 = 1

@DEGREES(@ASIN(V7)) = -11.536959, where V7 = -0.2

@ASIN(30) = Error - @ASIN, domain is -1 <= x <= 1

@ASINH

@ASINH returns the hyperbolic arc sine of angle X. The result is the angle (in radians) whose hyperbolic sine is X.

Examples:

@ASINH(1) = 0.88137359

@ASINH(L7) = -0.60282073, where L7 = -0.64

@ATAN

@ATAN returns the 2-quadrant arc tangent of angle X. The result is the angle (in radians) whose tangent is X and ranges from -p/2 to p/2. To convert radians to degrees, use the @DEGREES function.

Examples:

@ATAN(.5) = 0.463647609

@ATAN(J44) = -0.785398163, where J44 = -1

@DEGREES(@ATAN(U6)) = 87.70939, where U6 = 25

@ATAN2

@ATAN2 returns the 4-quadrant arc tangent of Y/X. At least one of the arguments must be non-zero. The result is the angle (in radians) and ranges between -p and p. To convert the result from radians to degrees, use the @DEGREES function.

Examples:

@ATAN2(1,1) = 0.78539816

@ATAN2(A1,A2) = -0.78539816, where A1 = 10 and A2 = -10

@ATAN2(D3,S5) = 1.5707963, where D3 = 0 and S5 = 1

@ATAN2(0,0) = Error - @ATAN2, one argument must be non-zero

@DEGREES(@ATAN2(1,3)) = 71.565

@ATANH

@ATANH returns the hyperbolic arc tangent of angle X. The result is the angle (in radians) whose hyperbolic tangent is X.

Examples:

@ATANH(0) = 0

@ATANH(J20) = 1.127029, where J20 = 0.81

@ATANH(1) = Error - @ATANH, domain is -1 < x < 1

@AVERAGE

@AVERAGE returns the average (arithmetic mean) of its arguments. Empty cells and strings are ignored.

Examples:

@AVERAGE(A1,B9,J32,F16) = 21.125, where A1 = 12, B9 = 34.5, J32 = 22, F16 = 16

@AVERAGE(A1,A12..H13,111+32) = 135.67, where we don't really care what is at those variables, but which shows that you can use numbers, ranges, and expressions all together as arguments for @AVG.

@AVG

@AVG returns the average (arithmetic mean) of its arguments. Empty cells and strings are ignored. Another name for @AVERAGE.

@BESSELI

Computes the modified Bessel function of integer order In(x).

@BESSELJ

Computes the Bessel function of integer order Jn(x)

@BESSELK

Computes the modified Bessel function of integer order Kn(x).

@BESSELY

Computes the Bessel function of integer order Yn(x), also known as the Neumann function.

@BETA

Returns the Beta function. z and w must be > 0.

@BETAI

Returns the Incomplete Beta function. a and b must be > 0. x must be 0 <= x <= 1.

@CAVG

@CAVG returns the average (arithmetic mean) of those arguments which satisfy the constraint C. Empty cells and strings are ignored.

Examples:

@CAVG(A1..A5,#<6) = 2.333333, where A1..A5 are 2, 2, 3, 6, and 7

@CAVG(E1..E3,#>10) = Error - @CAVG, sample size less than 1, where E1 = 1, E2 = 5, and E3 = 0

@CAVG(B7..E10,#<#{-1}) = 15, where the average of the numbers in range B7..E10 which are less than the value of the cell to their left is 15.

@CCOUNT

@CCOUNT returns a count of its non-empty arguments which satisfy the constraint C. Cells containing strings are counted.

Examples:

@CCOUNT(T2..T6, #==W2) = 1, where T2..T6 are 4, 7, 2, 8, 0 and W2 = 7

@CCOUNT(E1..E3,#>=0) = 3, where E1 = 1, E2 = 5, and E3 = 0

@CCOUNT(C4..C100,#>#{0,-1}) = 68, counts those cells in range C4..C100 whose value is greater than the contents of the cell immediately above

@CEIL

@CEIL returns the smallest integer that is greater than or equal to X.

Examples:

@CEIL(5.5) = 6

@CEIL(E3) = -9, where E3 = -9.99

@CEIL(C17) = 1, where C17 = 1

@CELL

Return many useful things about a cell. Case is ignored in property names. The available property names are:

"Address"

The coordinates of the cell, as a string in A1 notation. For example, @CELL("address",C4) is equal to the string "$C$4". The sheet name is NOT included.

"BackGroundColor"

Index of background color

"Bold"

1 if Bold attribute; 0 otherwise

"BottomBorder"

Index of border style

"BottomBorderColor"

Index of border color

"Col"

The number of the column containing the cell. Equivalent to the @COL(cell) function.

"Contents"

Contents (value) of the cell

"Coord"

The fully-qualified coordinates of the cell, as a string. For example, @CELL("coord",C4) on Sheet1 would be equal to the string "$Sheet1!$C$4".

"FontFace"

Index number of font family

"FontSize"

In points. 0 indicates default value

"FontStyle"

Font Style

0 Standard
1 Italic
2 Bold
3 Bold Italic

"Halign"

Horizontal Alignment

0 Default
1 Left
2 Right
3 Center
4 Automatic

"Italic"

1 if Italic; 0 otherwise

"LeftBorder"

Index of border style

"LeftBorderColor"

Index of border color

"Orientation"

The orientation property

0 Default
1 Horizontal
2 Vertical
3 Up
4 Down

"Pattern"

Background pattern index

"PatternColor"

Index of pattern foreground color

"Protect"

Cell protection attribute

0 Default
1 Off
2 On

"RightBorder"

Index of border style

"RightBorderColor"

Index of border color

"Rotation"

The amount of rotation for letters, in degrees (NOT the same as the orientation). So the cells using the Vertical orientation have the same rotation as cells using the Horizontal rotation, but cells using Up or Down orientation have either +90 or -90 rotation.

"Row"

The number of the row containing the cell. Equivalent to the @ROW(cell) function.

"Sheet"

The number of the sheet containing the cell. Equivalent to the @SHEET(cell) function.

"SheetName"

The name of the sheet containing the cell.

"StrikeThrough"

1 if strikethrough attribute; 0 otherwise.

"TextColor"

Index of color used for cell text.

"TopBorder"

Index of border style.

"TopBorderColor"

Index of border type

"Type"

b empty ("blank")
l label (string constant)
v anything else

"Underline"

Underline attribute

0 Default
1 None
2 Single underline
3 Double underline

"Valign"

Vertical alignment

0 Default
1 Top
2 Bottom
3 Center

"Wrap"

Word-wrap attribute

0 Default
1 Off
2 On

@CELLPOINTER

Returns cell attribute information about the current cell (containing a formula using this function). The values for property are the same as for the @CELL function.

@CELLREF

@CELLREF returns a reference to the cell whose column index is N1 and whose row index is N2. Column indices start with 0, so the column index for column A is 0, B is 1, etc. Row indices are the same as the row number, and therefore start with 1.

Examples:

@CELLREF(3,1) = -80, where D1 = -80

@CELLREF(27,34) = 2400, where AA34 = 2400

@CELLREF(4,8) = 100.00, where E8 = 100.00

@CHAR

@CHAR returns the character represented by the integer code n.

Examples:

@CHAR(55) = "7"

@CHAR(C2) = "C", where C2 = 67

@CHAR(F5) = "�", where F5 = 163

@CHOOSE

@CHOOSE returns the Nth argument from the argument list, starting with an index of 0. N should be less than the number of items in argumentlist - 1.

Examples:

@CHOOSE(2,D1,C22,F5,K1) = 54, where F5= 54

@CHOOSE(1,A9,D23,G12) = "Peaches", where D23 = "Peaches"

@CHOOSE(3,"nuts","rings","bolts","fittings") = "fittings"

@SUM(@CHOOSE(A1>B1,B1..B10,A1..A10)) sums either column A or column B.

@CLEAN

@CLEAN returns the string formed by removing all non-printing characters from the string S.

Examples:

@CLEAN(A1) = "test", where A1=@STRCAT(A2,A3), A2="test" and A3=@CHAR(10)

@CMAX

@CMAX returns the maximum value among its arguments which satisfy the constraint C. Empty cells and cells containing strings are not counted.

Examples:

@CMAX(A1..A6,#<@SUM(A11,A12)) = 56, where A1..A6 are 4, 56, 33, 100, 0, -1; A11=50 and A12=50

@CMAX(S3..S7,#<0) =-1, where S3..S7 = -1, -4, -99, -20, and -1.5

@CMAX(F1..F25,#<D1||#>D2) = 1.99, where the maximum of the values in range F1..F25; are less than D1 or greater than D2.

@CMIN

@CMIN returns the minimum value among its arguments which satisfy the constraint C. Empty cells and cells containing strings are not counted.

Examples:

@CMIN(A1..A6,#<@SUM(A11,A12)) = -1, where A1..A6 are 4, 56, 33, 100, 0, -1 and A11=50 and A12=50

@CMIN(S3..S7,#<0) = -99, where S3..S7 = -1, -4, -99, -20, and -1.5 @=code(CMIN(F1..F25,#<D1||#>D2) = .99, where the minimum of the values in range F1..F25; are less than D1 or greater than D2.

@CODE

@CODE returns the ASCII code for the first character in string S.

Examples:

@CODE("test") = 116

@CODE(H100) = 107, where H100 = "k"

@CODE(M10) = 56, where M10 = "89"

@COL

@COL returns the column index of the cell referenced by C, or the left-most column if C is a range.

Examples:

@COL(A1) = 0

@COL(F10..G19) = 5

@COL = 25, where @COL is in cell Z20.

@COLS

@COLS returns the number of columns in the specified range.

Examples:

@COLS(A3..J17) = 10

@COORD

Returns a string giving a cell specification. Sheet and row are 1-origin and column is 0-origin, to be consistent with other functions such as @CELLREF. The flags argument specifies which elements in the address string are absolute.

For example, the function reference =@COORD(1, 0, 1, flags) would return the following strings for the possible values of flags:

1 - $Sheet1!$A$1
2 - $Sheet1!A$1
3 - $Sheet1!$A1
4 - $Sheet1!A1
5 - Sheet1!$A$1
6 - Sheet1!A$1
7 - Sheet1!$A1
8 - Sheet1!A1

@CORR

@CORR returns Pearson's product-moment correlation coefficient for the paired data in ranges R1 and R2. R1 and R2 must have the same dimensions. The result is between -1 and 1.

@CORR can be used to determine whether pairs of data are statistically related. The closer the correlation is to zero, the less likely that they are related.

Examples:

@CORR(A1..A4,B1..B4) = 0.44213546, where A1..A4 = .5, .98, .22, .1, and B1..B4 = .87, .6, .21, .49.

@CORR(H5..H9,K5..K9) = 0.92384914, where H5..H9 = 14, 35, 37, 0, 33, and K5..K9 = 67, 77, 94, 34, 99.

@CORR(F1..F9,G1..G15) = Error - @CORR, ranges must be same dimensions

@COS

@COS returns the cosine of angle X. The result is between -1 and 1. To convert the argument from degrees to radians, use the @RADIANS function.

Examples:

@COS(-45) = 0.52532199

@COS(K24) = 0.5403023, where K24 = 1

@COS(@RADIANS(30)) = 0.866025

@COSH

@COSH returns the hyperbolic cosine of angle X. The result is greater than or equal to 1.

Examples:

@COSH(1) = 1.543080635

@COSH(FF1) = 3.762195691, where FF2 = -2

@COSH(0) = 1

@COT

@COT returns the cotangent of X.

@COTH

@COTH returns the hyperbolic cotangent of X.

@COUNT

@COUNT returns a count of its non-empty arguments. Cells that contain strings are counted.

Examples:

@COUNT("Tom","Bob","Sue","Mary","Zack","Ann") = 6

@COUNT(D4..D8) = 3, where D4 is empty, D5 = 1, D6 is empty, D7 = 0, and D8 = 5

@COUNT(E5..J10) = 30, E5..J10 contains 36 cells, therefore 6 cells are empty

@COUPDAYBS

@COUPDAYSBS returns the number of days between the beginning of the coupon period and the settlement date.

Examples:

@COUPDAYBS(@DATE(1993,2,15),@DATE(1994,12,20),2,0) = 55

@COUPDAYBS(@DATE(1993,2,15),@DATE(1994,12,20),2,1) = 57

@COUPDAYS

@COUPDAYS returns the number of days in the coupon period that the settlement date is in.

Examples:

@COUPDAYS(@DATE(1993,2,15),@DATE(1994,12,20),2,0) = 180

@COUPDAYS(@DATE(1993,2,15),@DATE(1994,12,20),2,1) = 182

@COUPDAYSNC

@COUPDAYSNC returns the number of days between the settlement date and the next coupon date.

Example:

@COUPDAYSNC(@DATE(1993,2,15),@DATE(1994,12,20),2,0) = 125

@COUPNCD

@COUPNCD returns the next coupon date after the settlement date.

Example:

@COUPNCD(@DATE(1993,2,15),@DATE(1994,12,20),2,0) = 34140 or 20-Jun-1993

@COUPNUM

@COUPNUM returns the number of coupon payments between the settlement date and the maturity date.

Example:

@COUPNUM(@DATE(1993,2,15),@DATE(1994,12,20),2,0) = 4

@COUPPCD

@COUPPCD returns the previous (most recent) coupon date before the settlement date.

Example:

@COUPPCD(@DATE(1993,2,15),@DATE(1994,12,20),2,0) = 33958 or 12/20/1992

@CSC

@CSC returns the cosecant of X.

Examples:

@CSC(0.01) = 100.00167

@CSC(3) = 7.0861674

@CSC(4) = -1.3213487

@CSCH

@CSCH returns the hyperbolic cosecant of X.

Examples:

@CSC(1) = 0.85091813

@CSTD

@CSTD returns the standard deviation (N weighting) of its arguments that satisfy the constraint C. Empty cells and cells containing strings are not counted.

Examples:

@CSTD(A1..A3,#<10) = 2.6246693, where A1..A3 are 1, 2, and 7

@CSTD(Y1..Y5,#>25) = 6.6833126, where Y1..Y5 = 22,24,29,34,45

@CSTDS

@CSTDS returns the sample standard deviation (N-1 weighting) of its arguments which satisfy the constraint C. Empty cells and cells containing strings are not counted.

Examples:

@CSTDS(A1..A3,#<10) = 3.2145503, where A1..A3 are 1, 2, and 7

@CSTDS(Y1..Y5,#>25) = 8.1853528, where Y1..Y5 = 22, 24, 29, 34, and 45

@CSUM

@CSUM returns the sum of its arguments which satisfy the constraint C. Empty cells and text cells are not counted.

Examples:

@CSUM(L1..L3,#<5) = 1.32, where L1 = 1.32, L2 = 5.45, L3 = 8.32

@CSUM(C5..C10,#>=D1/D10) = 150, where C5..C10 are 10, 20, 30, 40, 50, and D1 = 100 and D10 = 10

@CTERM

@CTERM calculates the number of compounding periods required for an investment of PV to reach a value of FV at the given interest rate R.

Examples:

@CTERM(0.085,1500,1000) = 4.97 (years, if the annual interest rate is 8.5%)

@CTERM(B5,D5,C5) = 11, where B5 = 10.5%, D5 = $300,000, and C5 = $100,000

@CTERM(.09,1900,1100) = 6.3

@CUMIPMT

@CUMIPMT returns the cumulative interest paid between S (the start) and E (the end) on a loan. Make sure that you are consistent about the units used for specifying R and N. For Example, for a 4-year loan with 10% annual interest rate, use 10%12 for R and 4*12 for N)

Example:

@CUMIPMT(9.5%/12,30*12,100000,1,12, 0) = 9473.61

@CUMPRINC

@CUMPRINC returns the cumulative principal paid between S (the start) and E (the end) on a loan. Make sure that you are consistent about the units used for specifying R and N. For Example, for a 4-year loan with 10% annual interest rate, use 10%12 for R and 4*12 for N)

Example:

@CUMPRINC(9.5%/12,30*12,100000,1,12, 0) = 616.64

@CVAR

@CVAR returns the population variance (N weighting) of its arguments which satisfy the constraint C. Blank cells and cells containing strings are not included.

Examples:

@CVAR(Y1..Y5,#<25) = 1, where Y1..Y5 = 22, 24, 29, 34, and 45

@CVAR(A1..A3,#<C2*E7) = 6.8888889, where A1..A3 are 1, 2, 7, and C2 = 5 and E7 = 2

@CVARS

@CVARS returns the sample variance (N-1 weighting) of its arguments which satisfy the constraint C. Empty cells and cells containing strings are not included.

Examples:

@CVARS(Y1..Y5,#<25) = 2, where Y1..Y5 = 22, 24, 29, 34, and 45

@CVARS(A1..A3,#<C2*E7) = 10.333333, where A1..A3 are 1, 2, 7, and C2 = 5 and E7 = 2

@DATE

@DATE returns the date value corresponding to year Y, month M, and date D. If Y is between 0 and 99, it is assumed to be in the current century. Otherwise, it represents the year from 100 to 3999.

Examples:

@DATE(66,4,6) = 24203, April 6, 1966

@DATE(366) = Error! wrong number or arguments

@DATEVALUE

@DATEVALUE returns the corresponding date value for a given string S. It interprets the value of S as if it had been entered into a cell.

The year may be given as 2 or 4 digits. A two year digit assumes the current century.

In the above formats, the month (MMM) is case insensitive and may be either the standard 3 letter abbreviation or the full name of the month.

Examples:

@DATEVALUE("3/24/1954") = 19807

@DATEVALUE("3/24/54") = 19807

@DATEVALUE("4-July-1994") = 34519

@DATEVALUE("4-July") = 34519, where the year is omitted, and assuming the year is 1994.

@DATEVALUE("September-94") = 34578

@DATEVALUE("Sep-1994") = 34578

@DATEVALUE("SEP") = 34578, where the year is omitted, and assuming the year is 1994.

@DATEVALUE("06.04.66") = 24203

@DATEVALUE("06.04.1966") = 24203

CAUTION: When the year is defaulted, the value returned by @DATEVALUE will vary depending on the year.

@DAY

@DAY returns the day of the month component of the date/time value DT.

Examples:

@DAY(24203) = 6 (24203 represents April 6, 1966)

@DAY(@TODAY) = 25, where TODAY = January 25, 1994

@DAY(366) = 31 (366 represents December 31, 1900)

@DAYS360

@DAYS360 returns the number of days between two dates, based on a 30/360 Day Count Basis (30-day-per-month, 360-day year.) Please refer to "Financial Functions" in Chapter 7 for more detail on 30//360 day count basis.

Examples:

@DAYS360(@DATE(1992,5,1),@DATE(1992,6,1)) = 30

@DAYS360(@DATE(1993,2,28),@DATE(1993,3,1)) =1

@DAYS360(@DATE(1992,2,28),@DATE(19923,1)) = 3 (1992 is a leap year.)

@DB

@DB returns the fixed-declining real depreciation of an asset for a specified period.

Example:

@DB(40000,100,6,3,2) = 8323.1198

@DDB

@DDB returns the double-declining depreciation allowance given cost C, salvage value S, allowable life L, and N number of depreciation periods.

The double declining balance depreciation allowance for the Pth depreciation period (DDBp) is calculated by the following iteration formula, where BVi is the following iteration formula, where BVi is the book value for the ith depreciation period. BV0 = C for i=1 to P do DDBi = max(2(BVi / L), 0) BVi = max(BVi-1�DDBi, S)

Examples:

@DDB(200,120,25,10) = 9.6

@DDB(F9,F5,10,6) = 524.29, where F5 = $1800.00 and F9 = $8000.00

@DDB(200,220,10,7) = error!, must have "cost" >= "salvage" >= 0

@DEGREES

@DEGREES returns 180/p * X.

Examples:

@DEGREES(0.5) = 28.64789

@DEGREES(@PI) = 90

@DELTA

Returns a 1 if number1 == number2, or 0 if number1 != number2. If number2 is not specified, it is assumed to be 0.

@DET

@DET returns the determinant of the matrix specified by the range M, which must be square.

Examples:

Matrix A1..C3

            A     B     C
       1     1     2     6
       2     3     4     11
       3     4     7     2

@DET(A1..C3) = 37 @DET(A1..B3) = Error - @DET, matrix must be square

@DISC

@DISC returns the discount rate of a security.

Examples:

@DISC(@DATE(1993,1,10), @DATE(1993,7,20),97.375,100,3) = 0.05016

@DISC(@DATE(1993,1,10), @DATE(1993,7,20),97.375,100) = 0.04974

@DFT

@DFT generates the Discrete Fourier Transform of the given range R. R must represent a real vector (either its row or column dimension must be 1), or a complex vector (either its row or column dimension must be 2). The result is a complex vector.

Example:

Vector C1..C4

                 C

        1       1.2
        2       3.4
        3      54.3
        4      0.34

@DFT(C1..C4) =
                59.2       0
               -53.1    3.06
                51.8       0 
               -53.1   -3.06

@DOLLARDE

@DOLLARDE returns the amount in Dollar Decimal form of an amount expressed in fractional form.

Examples:

@DOLLARDE(1.02,8) = 1.025

@DOLLARDE(1.2,8) = 1.25

@DOLLARDE(10.16,32) = 10.5

@DOLLARFR

@DOLLARFR returns the amount in Dollar Fractional form of an amount expressed in decimal form.

Examples:

@DOLLARFR(1.025,8) = 1.02

@DOLLARFR(1.25,8) = 1.2

@DOLLARFR(10.5,32) = 10.16

@DOT

@DOT returns the dot product of two vectors. R1 must have the same number of rows and columns as R2, or R1 and R2 can be one-dimensional vectors of the same length.

Examples:

Matrix 1, A1..B2

               A     B
         1      1    3
         2      2    4

Matrix 2, C1..D2

               C     D
         1      9     5
         2      2     4

@DOT(A1..B2,C1..D2) =44 @DOT(A1..B1,C1..C2) = 15 @DOT(A1..B2,D1..D2) = Error - @DOT, incompatible range dimensions

@DURATION

@DURATION returns the Macauley duration for an assumed par value of $100.

Example:

@DURATION(@DATE(1990,1,1),@DATE(1995,1,1),10%,8%,2,0) = 4.0954

@EDATE

@EDATE returns a date/time value representing the date M months after S if M is positive, and M months previous to S if M is negative. The date returned will be on the same day of the month as S.

Examples:

@EDATE(@DATEVALUE("2/29/1992"),3) = 33753 or 05/29/1992

@EDATE(@DATEVALUE("2/29/1992"),12) = 34028 or 2/28/1993

@EDATE(@DATEVALUE("3/30/1993"),-6) = 33877 or 09/30/1992

@EFFECT

@EFFECT returns the effective annual interest rate.

Example:

@EFFECT(6.75%,5) = 0.06935

@EIGEN

@EIGEN generates the eigenvalues of the matrix M, which must be square and symmetric.

Examples:

Matrix B9..C10

                   B     C
             9      0     1
            10      1     2

@EIGEN(B9..C10) =
                    -0.414
                    2.4142

Matrix  A1..B4
                   A     B
             1    .99     0
             2      3   6.2
             3    4.1   1.1
             4    3.3   6.3

@EIGEN(A1..B4) =  Error - @EIGEN, matrix must be square

@ENVIRON

Returns the value of the environmental variable whose name is in the string S.

@EOMONTH

Returns a date/time value representing the date M months after S, if M is positive, and M months before S, if M is negative. The date returned will be on the last day of the month.

Examples:

@EOMONTH(@DATEVALUE("7/7/1993"),-24)= 33450 or 7/31/1991

@EOMONTH(@DATEVALUE("4/15/1994"),2)= 34515 or 6/30/1994

@ERF

@ERF returns the error function integrated between L and U. If U is omitted, @ERF integrates between 0 and L:202

Examples:

@ERF(1) = 0.8427

@ERF(0.35) = 0.3794

@ERF(0.35,0.89) = 0.4125

@ERFC

@ERFC returns the complementary error function integrated between L and infinity.

Example:

@ERFC(1) = 0.1573

@ERFC(0.35) = 0.6206

@ERR

Returns an error indicator for this cell.

@EVEN

Returns the even number with the next larger absolute value. EVEN(1) = 2, EVEN(-1) = -2.

@EXACT

@EXACT returns 1 if string S1 exactly matches string S2; returns 0 otherwise.

Examples:

@EXACT("computer","computer") = 1

@EXACT("Abc","abc") = 0

@EXP

@EXP returns e raised to the power specified by the argument, where e is the base of the natural log 2.71828183. @EXP is the inverse of the natural log function @LN.

Examples:

@EXP(1) = 2.71828183

@EXP(D30) = 1, where D30 = 0

@LN(@EXP(X4)) = 5.1, where X4 = 5.1

@EXP(710) = Error - @EXP, floating exception

@F

@F returns the integral of Snedecor's F-distribution with M and N degrees of freedom from minus infinity to X.

Examples:

@F(14,35,99) = 1

@F(D3,W2,Y8) = Error - @F, degrees of freedom must be > 0, where D3 = .5, W2 = 0, and Y8 = .99

@F(B1,B2,B3) = 0.77459667, where B1 = 1, B2 = 2, and B3 = 3

@FACT

@FACT returns N! of N. The formula is computed as:

                 N! = N * (N-1)!
                 0! = 1

Examples:

@FACT(4) = 24, because 4! = 4 * 3 * 2 * 1

@FACT(C13) = 3628800, where C13 = 10

@FALSE

@FALSE returns 0.

Examples:

@FALSE = 0

@IF(B17 == 0,@TRUE,@FALSE) = 0, where B17 is not equal to 0

@FFT

@FFT generates the Discrete Fourier Transform of the range R using a Fast Fourier Transform algorithm. The length of the range must be a power of 2. @FFT will produce the same results a @DFT, but it is much faster (N*log(N)) time, as opposed to N2 time).

@FILEEXISTS

@FILEEXISTS returns the value 1 if file S can be opened for reading; otherwise 0.

Examples:

@FILEEXISTS("connections.c") = 1

@FILEEXISTS("Q1EXPENSES96.XS5") = 1

@FILEEXISTS("Q1EXPENSES90.WKS") = 0

@FIND

@FIND returns the index of the first occurrence of string S1 in string S2, starting the search at position N in string S2. Note that the string index always starts with 0.

Examples:

@FIND("bc","abcdefg",0) = 1

@FIND(V1,V2,5) = 9, where V1 = "Functions", and V2 = "Built-in Functions"

@FIND(V1,V2,5) = Error - @FIND, substring not found, where V1 = "Built-in" and V2 = "Functions"

@FLOOR

@FLOOR returns the largest integer less than or equal to X.

Examples:

@FLOOR(5.5) = 5

@FLOOR(E3) = -10, where E3 = -9.99

@FLOOR(C17) = 3, where C17 = 3.45

@FORECAST

@FORECAST returns Y', the predicted value for y for X', given original x and y values X and Y.

Example:

                A           B
        1       20           6
        2       28           7
        3       31           9
        4       38          15
        5       40          21

@FORECAST(30, A1..A5, B1..B5) = 10.60725

@FORMAT

@FORMAT returns the string formed by formatting the numeric value X using the XESS format code F and the precision specified by N.

Examples:

@FORMAT("dollars",2,660406) = "$660,406.00"

@FORMAT("hex",0,660406) = "OxA13B6"

@FORMAT("general",2,660406) = "6.6e+05"

@FRAC

@FRAC returns the fractional portion of X.

Examples:

@FRAC(232.45566) = 0.45566

@FRAC(20) = 0

@FRAC(@PI) = 0.14159265

@FREQUENCY

@FREQUENCY returns a frequency distribution for a set of values R with a set of intervals B

Example:

@FREQUENCY(A1..A8,B1..B2) = 3,2,2 where A1..A8 = 70,79,80,61,83,93,88,97 and B1..B2 = 80,90

@FTEST

@FTEST returns the significance level (alpha) of the two-sided F-test on the variances of the data specified by ranges R1 and R2. Alpha is the probability of error in rejecting the null hypothesis that var[R1]=var[R2].

Examples:

@FTEST(A1..A4,B1..B4) = 0.57065573, where A1..A4 = .5, .98, .22, .1, and B1..B4 = .87, .6, .21, .49

@FTEST(H5..H9,K5..K12) = 0.62049415, where H5..H9 = 14, 35, 37, 0, 33, and K5..K12 = 67, 77, 94, 34, 99, 56, 86, 67

@FV

@FV returns the future value of an annuity given periodic payment P, interest rate R, and N number of periods.

Examples:

@FV(100,0.085,5) = 592.54

@FV(G7,C2,2) = 6240, where C2 = 8% and G7 = $3000.00

@FV(1000,0.075,1.5) = 15.27

@FVSCHEDULE

@FVSCHEDULE returns the future value of an initial principal after compounding by a series of interest rates.

Example:

@FVSCHEDULE(100,A1..A4) = 133.4260, where A1..A4 = 7.5%, 6.6%, 8%, 7%

@GAMMA

@GAMMA returns the value of the Euler's gamma function evaluated for values of X > 0, and less than or equal to approximately 171 (less on a few computers with limited mathematics capabilities). Gamma is a continuous function whose value for integer arguments N is (N�1)! (N�1 factorial).

Examples:

@GAMMA(5) = 24

@GAMMA(1.59) = 0.89243

@GAMMA(L38) = Error - @GAMMA, floating exception, where L8 = 175

@GCD

Returns the greatest common divisor of the numbers in the list.

@GMEAN

@GMEAN returns the geometric mean of its arguments. Empty cells and cells containing strings are not included.

Examples:

@GMEAN(56,49,99) = 64.76491

@GMEAN(F10..F14) = 3.4712517, where F10..F14 = 2, 2, 3, 6, and 7

@GMEAN(U7..V15) = Error - @GMEAN, operand less than or equal to zero, where U13 = 0

@GRAND

@GRAND returns a 12th-degree binomial approximation to a Gaussian random number. A set of these numbers will approximate a normal (bell-shaped) distribution with a mean of 0 and a variance of 1. The number will change with every recalculation.

Examples:

@GRAND = 1.2255531

@HEXTONUM

@HEXTONUM returns the numeric value for the hexadecimal (base 16) interpretation of S. Since XESS can operate directly on hexadecimal numbers, this function is not very useful; it is included primarily to maintain compatibility with other spreadsheets.

Examples:

@HEXTONUM("A") = 10

@HEXTONUM(J10) = 45019, where J10 = "AFDB"

@HLOOKUP

@HLOOKUP returns the value of a cell found by performing a horizontal table lookup.

@HLOOKUP searches the first row (known as the index row) in the range R for the numeric or string value which "matches" X, and returns the value N rows beneath the matching cell.

If X is a string value, an exact match must be found or an error is returned.

If X is a numeric value, the index row must contain numeric values, sorted in ascending order, and the matching column is determined by the following rules:

1. Strings and empty cells in the index row are ignored.
2. If the first value in the index row is greater than X, an error is returned.
3. Searching stops when a numeric value which is greater than or equal to X is found in the index row. If the value found is greater than X, the preceding column is the matching column.
4. If there are no numeric values in the index row which are greater than or equal to X, the last column in R is considered to be the matching column.

Examples:

              A          B        C         D
       1    Lodging    Meals    Airfare   Entertainment
       2     $85.00     $30.00  $698.00    $25.00

@HLOOKUP("Meals",A1..D2,1) = 30

@HLOOKUP("Airfare",A1..D2,1) = Error - @HLOOKUP, lookup failed to produce a match

@HLOOKUP("Airfare",A1..D2,3) = Error - @HLOOKUP, row offset out of range

@HMEAN

@HMEAN returns the harmonic mean of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@HMEAN(F10..F15) = 3.0434783, where F10..F14 = 2, 2, 3, 6, and 7

@HMEAN(C1..C5) = Error - @HMEAN, operand equal to 0, where C4 = 0

@HOUR

@HOUR returns the hours (0 - 23, where 0 represents midnight) component of the date/time value DT.

Examples:

@HOUR(.25) = 6

@HOUR(0) = 0

@HOUR(.99) = 23

@IF

@IF returns the value of T if X evaluates to non-zero, or F if X evaluates to zero. If F is omitted, it is assumed to be 0.

Examples:

@IF(A1,7,9) = 7, where A1 = 1

@IF(B17==0,0,"non-zero") = "non-zero", where B17 = 1

@INDEX

Returns the value of the cell at the indicated offset from the Range. The column, row, and worksheet offsets are 1-origin, and the resulting reference must not be outside the range.

@INFO

Returns information about the current spreadsheet and its environment. The information returned is determined by the value of item. The case of the argument is ignored.

"Directory"

Current directory

"OSversion"

Current operating system version

"Processor"

Processor (CPU) type

"Recalc"

Recalculation mode, either "manual" or "automatic"

"Release"

Current release number of XESS.

"Screen-Height"

Height in pixels of current display.

"Screen-Width"

Width in pixels of current display.

"System"

Operating system name.

@INIT

@INIT returns its first argument on the first recalculation pass and its second argument on all subsequent recalculation passes when XESS is performing iterative recalculations.

Examples:

C5 = @INIT(1,C5+1) C6 = @INIT(1,C5*C6)

C5 = Cycle! if Iteration Limit = 0

C5 = N if Iteration Limit = N

C6 = Cycle! if Iteration Limit = 0

C6 = N! if Iteration Limit = N

@INT

@INT returns the integer portion of X.

Examples:

@INT(232.45566) = 232

@INT(J20) = -10, where J20 = -10.3

@INT(@PI) = 3

@INTERP2D

@INTERP2D returns interporlated values for a 2 dimensional vector. R1 and R2 have to be same size.

Example:

              A       B
        1      1      2.5
        2      3      4.5
        3      5      8.2
        4      7     10.5
        5      9     13.4

@INTERP2D(A1..A5,B1..B5,C1..C2) = 6.35, 9.35 where C1..C2 = 4,6

@INTERP3D

@INTERP3D returns interporlated value for a 3 dimensional vector. X and Y have to be same size.

Example:

          A       B      C       D       E       F       G
                 12.5   14.5    15.5    18.2    19.5    22.5
    1    11.5       1      2       3       4       5       6
    2    13.5       7      8       9      10      11      12
    3    16.5      13     14      15      16      17      18
    4    17.5      19     20      21      22      23      24
    5    18.5      25     26      27      28      29      30
    6    20.5      31     32      33      34      35      36

@INTERP3D(A1..G6,A11..A12,B11..B12) = 3.25
		            12.5556, 
  where A11..A12 = 12, 15, and B11..B12 = 14, 17

@INTRATE

@INTRATE returns the interest rate for a fully invested investment.

Example:

@INTRATE(@DATE(1993,1,15), @DATE(1993,6,15), 10000, 10545) = 0.1317

@IPMT

@IPMT returns the interest payment for a specified period for an loan or investment based on periodic, constant payments and a constant interest rate. Make sure that the units used for R and P are consistent. For example, for a 5-year loan with 12% annual interest, if you make payments monthly, use 12%/12 for (monthly) R and 5*12 for NP.

Example:

@IPMT(10%/12,1,24,2000,0,0) = 16.6667

@INVDFT

@INVDFT generates the inverse Discrete Fourier Transform of the range R. Like @DFT, R must represent a real vector (either its row or column dimension must be 1), or a complex vector (either its row or column dimension must be 2). The result is a complex vector.

Examples:

A B 1 .99 0 2 3 6.2 3 4.1 1.1 4 3.3 6.3 With @INVDFT(A1..B4) in B6 gives: B C 6 2.8475 3.4 7 -0.8025 -0.2 8 -0.3025 -2.85 9 -0.7525 -0.35 @INVDFT(G4..I6) = Error - @INVDFT, improper dimensions

@INVERT

@INVERT generates the inverse of the matrix M, which must be square. The result matrix is the same size as the argument matrix. Multiplying M times the result matrix (@MMUL) produces an identity matrix (a matrix with 1's in the diagonal and 0's elsewhere).

	@MMUL(A,@INVERT(A))

Examples:

Matrix B9..C10

               B     C
         9      0     1
        10      1     2

@INVERT(B9..C10) =
                   -2     1
                    1     2

@INVFFT

@INVFFT generates the inverse Discrete Fourier Transform or range R using a Fast Fourier Transform algorithm. The length of the vector must be a power of 2. @INVFFT will produce the same results as @INVDFT, but it is much faster (N*log(N)) time as opposed to N2 time).

@IRR

@IRR returns the internal rate of return on an investment given a set cash flows contained in range F, and an initial "guess" G (usually 0). F must contain at least two cash flow values. Negative cash flows represent expenditures, and positive cash flows represent income. Empty cells and text are treated as zeros.

Examples:

@IRR(0,L2..L6) = -0.32606171, where L2..L6 = -80, 90, 67, 72, and -90

@IRR(0,I5..I9) = 1.0462142, where I5..I9 = -700, 750, 900, 600 and 850

@IRR(0,-200,100,300) = ERROR: too many arguments to function

@ISERROR

@ISERROR returns 1 if X "contains" an error, otherwise it returns 0. X "contains" an error if it it would generate an error outside of the @ISERROR function, which means that the expression itself may generate an error, or it may reference a cell which has generated an error.

Examples:

@ISERROR(1) = 0

@ISERROR(3/0) = 1

@ISERROR(@ISERROR(3/0)) = 0

@ISEVEN

Returns 1 if a number is even or 0 if not. If a number is not an integer it is truncated before the test.

@ISNUMBER

@ISNUMBER returns 1 if X is a numeric value, otherwise 0. Empty cells are considered numeric.

Examples:

@ISNUMBER(C10) = 0, where C10 = "Total"

@ISNUMBER(D10) = 1, where D10 = $1000.00

@ISNUMBER(A1) = 1, where A1 is empty

@ISODD

Returns a 1 if a number is odd or 0 if not. If a number is not an integer it is truncated before the test.

@ISSTRING

@ISSTRING returns 1 if X is a string value, otherwise 0. Empty cells are considered numeric.

Examples:

@ISSTRING(C10) = 1, where C10 = "Total"

@ISSTRING(D10) = 0, where D10 = $1000.00

@ISSTRING(A1)= 0, where A1 is empty

@LARGE

@LARGE returns the Nth largest datum in the range R.

Examples:

A1..A9 = 1,4,8,3,7,12,54,8,23

@LARGE(A1..A9,3) = 12

@LARGE(A1..A9,1) = 54

@LARGE(A1..A9,@COUNT(A1..A7)) = 1

@LCM

Returns the least common multiple of the numbers in the list.

@LEFT

@LEFT returns the string composed of the leftmost characters of string S. If N is greater than or equal to the length of S, then the entire string S is returned.

Examples:

F1 = "workstation"

@LEFT(F1,1) = "w"

@LEFT(F1, 4) = "work"

@LEFT(F1,20) = "workstation"

@LENGTH

@LENGTH returns the number of characters in string S.

Examples:

@LENGTH("computer") = 8

@LENGTH(G3) = 14, where G3 = "Expense Report"

@LENGTH(Z7) = 20, where Z7 = "Linear Least Squares"

@LINCOEF

@LINCOEF generates the straight line least squares fit. The output is the values for a1 and a0 and is a row vector if X and Y are row vectors, and a column vector if X and Y are column vectors. This function is equivalent to @POLYCOEF(X,Y,1).

@LINFIT

@LINFIT generates the estimated Y values for a straight line least squares fit. The output of this function is the vector of estimated y values on the left hand side of the above equation. The input vector X and Y must both be row vectors or both column vectors.

The output vector will be a row vector if X and Y are row vectors, and a column vector if X and Y are column vectors. The length of the output vector is equal the the length of the input vectors. This function is equivalent to @POLYFIT(X,Y,1).

Example:

Matrix B1..B5 and C1..C5

             B     C
         1    1     3
         2    2     5
         3    3    11
         4    4    18
         5    5    31
 

@LINFIT(B1..B5,C1..C5) =
                         -0.2
                          6.7
                         13.6
                         20.5
                         27.4

@LLS

@LLS generates the following regression analysis:

1. the linear least squares solution, X, to the overdetermined system of linear equations AX=Y
2. the standard errors of the least square estimates of each element of the parameter vector X, the t-statistics that compare each parameter to zero, and the significance level of each.
3. the variance of the model, the R-squared correlation for the model, and its corresponding F-statistic and significance level.

The output of @LLS is a a table with four rows and n+1 columns, where n is the number of independent variables in the model (i.e., the number of columns in the A matrix). The table is presented in the following format:

        x(1)        x(2)        ...     x(n)        MSE
        SE(x(1))    SE(x(2))    ...     SE(x(n))    RSQUARE
        t(x(1))     t(x(2))     ...     t(x(n))     F(R)
        P(t(x(1)))  P(t(x(2)))  ...     P(t(x(n)))  P(F(R))            

x(i)

the least squares estimate of the ith coefficient, corresponding to the independent variable in the ith column of A.

SE(x(i))

the standard error of xi

t(x(i))

the t-statistic for testing whether xi is significantly different from zero.

P(t(x(i)))

the probability of error in rejecting the null hy pothesis that xi=0, based on two-sided t-test.

MSE

the mean squared error of the model.

R2

the model coefficient of determination (the square of the model correlation coefficient, R)

FR

the F-statistic for testing whether R is significantly different from zero.

P(FR)

the probability of error in rejecting the null hy pothesis that R=0, based on a two-sided t-test.

Matrix A1..C4 and D1..D4

               A    B    C    D
           1   0    0    1    3
           2   1    1    1    5
           3   4    2    1   13
           4   9    3    1   18

@LLS(A1..C4,D1..D4)= 	

        0.75         3.05         2.55         4.05
        1.01         3.15         1.96         0.97
        0.75         0.97         1.30        17.62
        0.59         0.51         0.42         0.17

@LLS(A1..A2,B1..C2) = Error - @LLS, improper dimensions

The first result above is exactly equivalent to @PLS(B1..B4,D1..D4,2)

@LN

@LN returns the log base e of X. The inverse of @LN is the exponentiation function @EXP.

Examples:

@LN(6) = 1.7917595

@LN(@EXP(X4)) = 10, where X4 = 10

@LN(-1) = Error - @LN, domain is x > 0

@LNGAMMA

@LNGAMMA returns the log base e of the gamma function evaluated at X.

Examples:

@LNGAMMA(1.20) = -0.08537

@LNGAMMA(10) = 12.801827

@LNGAMMA(D30) = Error - @LNGAMMA, domain is x > 0, where D30 = 0

@LNGAMMA(K19) = 9.9677617, where K19 = 8.7

@LOG

@LOG returns the log base 10 of X. This function is included for compatibility with WKS and WK1 formats.

Examples:

@LOG(1) = 0

@LOG(14) = 1.146128

@LOG(1.00e+27) = 27

@LOG(0) = Error - @LOG, domain is x > 0

@LOG10

@LOG10 returns the log base 10 of X.

Examples:

@LOG10(1) = 0

@LOG10(14) = 1.146128

@LOG10(1.00e+27) = 27

@LOG10(0) = Error - @LOG10 domain is x > 0

@LOG2

@LOG2 returns the log base 2 of X.

Examples:

@LOG2(1) = 0

@LOG2(1.00e+27) = 89.692

@LOG2(0) = Error! domain x > 0

@LOWER

@LOWER returns returns the alphabetic string S converted to lower-case characters.

Examples:

@LOWER("AIRPORT") = "airport"

@LOWER(G3) = "expense report", where G3 = "Expense Report"

@LOWER(K10) = "connections", where K10 = "CONNECTIONS"

@MADD

@MADD adds the corresponding elements of matrix M2 and matrix M1. If the dimensions of the matrices are incompatible, the function generates an error.

Examples:

Matrix B9..C10

              B     C
       9       0     1
      10       1     2

Matrix D9..E10

              D     E
       9       3     5
      10       4     8

@MADD(B9..C10,D9..E10) =

                    3     6
                    5    10

@MATCH

@MATCH return the relative position of the value V in the range R, according to the rule specified by T:

         1 or omitted      Find largest value in R <= V
         0                 Find first value in R = V
        -1                 Find smallest value in R >= V

If V is a string, it may contain the wildcard characters, asterisk (*) and question mark (?), to represent any sequence of characters and any single character respectively. The items in the R do not need to be in any specific order.

Examples:

                 A               B
        1     Products    Quantity (Boxes)
        2     Apple                     32
        3     Orange                    50
        4     Cherry                    15
        5     Banana                    20
        6     Cantaloupe                10
        7     Strawberry                25

@MATCH(15,B1..B7) = 4

@MATCH(30,B1..B7,1) = 7

@MATCH(30,B1..B7,-1) = 2

@MATCH("Orange",A1..A7,0) = 3

@MATCH("Can*",A1..A7,0) = 6

@MAX

@MAX returns the maximum of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@MAX(4,56,33,100,0,-1) = 100

@MAX(S3..S7) = -1, where S3..S7 = -1,-4,-99,-20,-1.5

@MAX(A1..B2) = 93, where A1 = 0, A2 = -93, B1 = 93, and B2 = 7

@MDURATION

@MDURATION returns the modified Macauley duration of a security assuming $100 face value.

Example:

@MDURATION(@DATE(1990,1,1),@DATE(1995,1,1),10%,8%,2,0) = 3.9379

@MEDIAN

@MEDIAN returns the median (middle value) of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@MEDIAN(A1..A6) = 80.5, where A1..A6 = 65,70,74,87,88,95

@MEDIAN(2, 8, 6, 5, 9) = 6

@MESSAGE

If the value of X is not 0, then @MESSAGE displays S in the Message Line and beeps. @MESSAGE returns the value of X.

Examples:

@MESSAGE(@HOUR(@NOW) > 17, "Time to go home!")

@MID

@MID returns the string of length N2 that starts at position N1 in string S. N1 is the number of characters from the beginning of the string, must be greater than or equal to zero, and less than the length of S. N2 must be greater than or equal to zero. If N1+N2 is greater than the length of S, then the substring starting at position N1 in S is returned. The first character in string S is in position zero.

Examples:

T5 = "Spreadsheet"

@MID(T5,6,5) = "sheet"

@MID(T5,10,1) = "t"

@MID(T5,1,0) = " "

@MIN

@MIN returns the minimum of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@MIN(4,56,33,100,0,-1) = -1

@MIN(S3..S7) = -99, where S3..S7 = -1,-4,-99,-20,-1.5

@MIN(A1..B2) = -93, where A1 = 0, A2 = -93, B1 = 93, and B2 = 7

@MINUTE

@MINUTE returns the minutes (0 to 60) component of the date/time value DT.

Examples:

@MINUTE(.99) = 45

@MINUTE(.6) = 24

@MINUTE(.3) = 12

@MIRR

@MIRR returns the Modified Internal Return Rate for a range of periodic cash flows. Empty cells and text are treated as zeros.

Example:

A B 1 Investment -140000 2 1st year return 34000 3 2nd year return 46000 4 3rd year return 45000 5 4th year return 47000 6 5th year return 46000 @MIRR(B1..B6,11%,12.5%) = 0.14522

@MMUL

@MMUL generates the product of the multiplication of matrix M2 by matrix M1. If the dimensions of the matrices are incompatible, the function generates an error.

Examples:

Matrix B9..C10

             B    C
         9    0    1
        10    1    2

Matrix D9..E10

             D    E
         9    3    5
        10    4    8

@MMUL(B9..C10,D9..E10) =

                     4    8
                     1   21

@MOD

@MOD returns the remainder of X/Y with the same sign as X. (This function is NOT modulus as sometimes documented.)

Examples:

@MOD(8,4) = 0

@MOD(D2,F3) = 12, where D2 = 100 and F3 = 44

@MOD(F5,A1) = Error!, divide by zero, where F5 = 98.6 and A1 = 0

@MOD(-14,3) = -2 (the true modulus is 1)

@MODE

@MODE returns the mode, or most frequently occurring datum, of all the arguments. Empty cells and cells containing text are ignored.

Examples:

@MODE(5,4,10,8,7,5,4,14,5) = 5

@MODE(A1..A7) = 2.3, where A1..A7 = 2.3,3.4,5.0,3.4,3.4,2.3,2.3

@MODULUS

@MODULUS returns the modulus of X/Y.

Examples:

@MODULUS(8,4) = 0

@MODULUS(D2,F3) = 12, where D2 = 100 and F3 = 44

@MODULUS(F5,A1) = Error!, divide by zero, where F5 = 98.6 and A1 = 0

@MODULUS(-14,3) = 1

@MONTH

@MONTH returns the months component of the date/time value DT.

Examples:

@MONTH(24203) = 4, where 24203 represents April 6, 1966

@MONTH(366) = 12, where 366 represents December 31, 1900

@MSQ

@MSQ returns the mean of the squares of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@MSQ(-9,-3,-5,0,1) = 23.2

@MSQ(F10..F14) = 20.4, where F10..F14 = 2, 2, 3, 6, and 7

@MSQ(D1..D6) = 2373.6667, where D1..D6 = 4, 56, 33, 100, 0, and -1

@MSUB

@MSUB subtracts corresponding values of matrix M2 from matrix M1. If the dimensions of the matrices are incompatible, the function generates an error.

Examples:

Matrix B9..C10

              B     C
       9       0     1
      10       1     2

Matrix D9..E10

              D     E
       9       3     5
      10       4     8


@MSUB(B9..C10,D9..E10) =

                   -3   -4
                   -3   -6

@N

@N returns the numeric value of the upper left cell in the range R, or zero if the cell is empty or contains a string. This function is included for compatibility with older spreadsheets.

Examples:

A1..B3		A	B


	1	1	"electrons"
	2	2	"protons"
	3	3	"neutrons"

@N(A1..B3) = 1 @N(B1..B3) = 0 @N(A1..Z25) = $1,000,000.00, where A1 = $1,000,000.00

@NAND

@NAND returns 0 if all arguments are 1; 1 if any argument is 0; otherwise -1 (unknown).

Examples:

A1 = 1, B1 = 1, C1 = 0, D1 = 0, E1 = 1 and A2 = 24

@NAND(A1,B1) = 0

@NAND(A1..E1) = 1

@NAND(A1..A2) = -1

@NETWORKDAYS

@NETWORKDAYS returns the number of whole working days, beginning with S and ending with E, excluding days in Hand weekends.

Examples:

@NETWORKDAYS(@DATE(1993,1,1),@DATE(1993,12,31)) = 261

@NETWORKDAYS(@DATE(1993,1,1),

@DATE(1993,12,31),A1..A5) = 258, where

A1 = @DATEVALUE("1/1/1993"),

A2 = @DATEVALUE("5/25/1993"),

A3 = @DATEVALUE("7/4/1993"),

A4 = @DATEVALUE("9/3/1993"),

A5 = @DATEVALUE("11/28/1993")

@NOMINAL

@NOMINAL returns the nominal annual interest rate for an effective interest rate R and the number of compounding periods per year C.

Example:

@NOMINAL(6.9347%,5) = 0.0675

@NOR

@NOR returns 0 if any argument is 1; 1 if all arguments are 0; otherwise -1 (unknown).

Examples:

A1 = 1, B1 = 1, C1 = 0, D1 = 0, E1 = 1 and A2 = 24

@NOR(C1,D1) = 1

@NOR(A1..E1) = 0

@NOR(A1..A2) = -1

@NOT

@NOT returns 1 if X=0; 0 if X=1; otherwise -1 (unknown).

Examples:

A1 = 1, B1 = 1, C1 = 0, D1 = 0, E1 = 1 and A2 = 24

@NOT(A1) = 0

@NOT(A2) = -1

@NOT(C1) = 1

@NOW

@NOW returns the date/time value corresponding to the current system time and date.

Examples:

@NOW = 33139.37, where 33139.37 represents September 23, 1990 at 8:52:48 A.M.

@MONTH(@NOW) = 9

@HOUR(@NOW) = 8

@NPV

@NPV returns the present value of a series of future cash flows, CF, at a given rate R. Empty cells and text are treated as zeros.

Examples:

@NPV(.095, C3..C8) = 287.29, where the interest rate is 9.5% and the future cash flow is in the range C3..C8 and are each $65

@NPV(.10,C3) = Error! cash flow series must be a range

@NUMTOHEX

@NUMTOHEX returns a string containing the hexadecimal representation of the integer portion of X.

Examples:

@NUMTOHEX(10) = "a"

@NUMTOHEX(C17) ="f", where C17 = 15.289374

@OCCUR

Returns the number of times that the substring "pattern" occurs in "string".

@ODD

Returns the odd number with the next larger absolute value. ODD(2) = 3, ODD(-2) = -3.

@ODDFPRICE

@ODDFPRICE returns the price per $100 face value of a security with an odd (short or long) first period.

Example:

@ODDFPRICE(A1,A2,A3,A4,7.85%,6.25%,100,2,1) = 113.5977, where A1 = @DATE(1992,11,11), A2 = @DATE(2005,3,1), A3 = @DATE(1992,10,15), A4 = @DATE(1993,3,1)

@ODDFYIELD

@ODDFYIELD returns the yield per $100 of a security with an odd (short or long) first period.

Example:

@ODDFYIELD(A1,A2,A3,A4,7.85%,113.598,100,2,1) = 0.0625, where A1 = @DATE(1992,11,11), A2 = @DATE(2005,3,1), A3 = @DATE(1992,10,15), A4 = @DATE(1993,3,1)

@ODDLPRICE

@ODDLPRICE returns the price per $100 face value of a security with an odd (short or long) last period.

Example:

@ODDLPRICE(A1,A2,A3.6.5%,5.35%,100,2,0) = 100.5418, where A1 = @DATE(1992,2,7), A2 = @DATE(1993,8,1), A3 = @DATE(1992,2,4)

@ODDLYIELD

@ODDFYIELD returns the yield per $100 of a security with an odd (short or long) last period.

Example:

@ODDLYIELD(A1,A2,A3.6.5%,100.542,100,2,0) = 0.0535, where A1 = @DATE(1992,2,7), A2 = @DATE(1993,8,1), A3 = @DATE(1992,2,4)

@OR

@OR returns 1 if any argument is 1; 0 if all arguments are 0; otherwise -1 (unknown).

Examples:

A1 = 1, B1 = 1, C1 = 0, D1 = 0, E1 = 1 and A2 = 24

@OR(C1,D1) = 0

@OR(A1..E1) = 1

@OR(A1..A2) = -1

@PERCENTILE

@PERCENTILE returns the datum from the range R which is at the Nth percentile in R. Only numeric data in R are considered. The result will be interpolated if N is not a multiple of 1/(s-1), where s is the size of R.

Examples:

@PERCENTILE(A1..A4,0.65) = 2.95, where A1..A4 = 1,2,3,4

@PERCENTILE(B1..B6,0.75) = 21.35, where A1..A6 = 3.8,23,4,12,67,0.2

@PERCENTRANK

@PERCENTRANK returns the percentile rank of the number N among the values in range R

Examples:

@PERCENTRANK(A1..A6,4.5) = 0.7, where A1..A6 = 1,2,3,4,5,6

@PERCENTRANK(B1..B6,21.35) = 0.77, where B1..B8 = 3.8,23,4,12,67,0.2

@PERMUT

@PERMUT returns "N choose K", or the permutations of K objects that can be chosen from the set N, where order is significant.

Examples:

@PERMUT(10,4) = 5040

@PERMUT(8,1) = 8

@PI

@PI returns the value of p.

Examples:

@PI = 3.1415927...

@PLS

@PLS analyzes the least squares polynomial model. The output of this function is identical to that of @LLS, with the polynomial coefficients listed in order of decreasing degree.

Example:

Matrix A1..C4 and Matrix D1..D4

               A    B    C    D
           1   0    0    1    3
           2   1    1    1    5
           3   4    2    1   11
           4   9    3    1   18


@PLS(B1..B4,D1..D4,2) = 	

        0.75         3.05         2.55         4.05
        1.01         3.15         1.96         0.97
        0.75         0.97         1.30        17.62
        0.59         0.51         0.42         0.17

@PMT

@PMT returns the periodic payment for a loan, given present value PV and interest rate R.

Examples:

@PMT(15000,rate,4) = 4834.90, where rate is a named cell = 11%

@PMT(M4,0.07,G4) = 8058.6404, where G4 = 30 and M4 = 100000

@PMT(1700,11,0) = Error!, "Period" must be an integer>0

@POLY

@POLY returns the value of an Nth-degree polynomial in X. The coefficient arguments may be any combination of numbers, cells containing numbers, or ranges containing numbers. Any non-numeric argument produces an error. @POLY(X,3,4,5) =( 3*X**2+4*X+5)

Examples:

@POLY(2,3,4,5) = 25, where (3*(2**2) + 4*(2) + 5) = 25

@POLY(-1,A1,A2) = 1, where A1 = 2 and A2 = 3 because (2*-1 + 3) = 1

@POLY(3,2,5,4,6) = 117

@POLY(-1,-2,-3,-4) = -3

@POLYCOEF

@POLYCOEF generates the least squares coefficients for the polynomial fit. The output of this function is a vector of length d+1 containing ,1,...,1,0 The output vector will be a row vector if X and Y are row vectors, and a column vector if X and Y are column vectors.

Example:

Matrix B1..B5 and C1..C5

                  B      C
            1      1      3
            2      2      5
            3      3     11
            4      4     18
            5      5     31

@POLYCOEF(B1..B5,C1..C5,2) =

                           1.6428571
                          -2.9571429
                           4.4

@POLYFIT

@POLYFIT generates the least squares polynomial fit. The output of this function is the vector of estimated y-values on the left hand side of the above equation. The input vector X and Y must both be row vectors or both column vectors. The output vector will be a row vector if X and Y are row vectors, and a column vector if X and Y are column vectors. The length of the output vector is equal the the length of the input vectors.

Example:

Matrix B1..B5 and C1..C5

                  B      C
            1      1      3
            2      2      5
            3      3     11
            4      4     18
            5      5     31

@POLYFIT(B1..B5,C1..C5,2) =

                             3.0857143
                             5.0571429
                            10.314286
                            18.857143
                            30.685714

@PPMT

@PPMT returns the payment on the principal for a specific period for an investment based on periodic, constant payments and a constant interest rate. Make sure the units used be R and NP are consistent. For example, for a 5-year loan with 12% annual interest, if you make payment monthly, use 12%/12 for (monthly) R and 5*12 for NP. If you make annual payment on the same loan, use 12% for R and 5 for NP.

Examples:

@PPMT(10%/12,1,60,55000,0,0) = 710.2541

@PPMT(10%/12,1,60,55000,1000) = 723.1678

@PRICE

@PRICE returns the price per $100 face value of a security that pays periodic interest.

Example:

@PRICE(@DATE(1991,3,15),@DATE(1998,10,15),6%,7%,100,2,0) = 94.1854

@PRICEDISC

@PRICEDISC returns the price per $100 face value of a discounted security.

Example:

@PRICEDISC(@DATE(1993,2,1),@DATE(1993,6,1),6.5%,100,0) = 97.8333

@PRICEMAT

@PRICEMAT returns the price per $100 face value of a security that pays interest at maturity.

Example:

@PRICEMAT(@DATE(1993,3,1),@DATE(1993,6,15),@DATE(1992,11,1), 6.5%,0) = 101.8778

@PRODUCT

@PRODUCT multiplies all the numeric elements of the argument list together, ignoring empty and non-numeric values, and returns the product.

Examples:

@PRODUCT(6,5,8,2) = 480

@PRODUCT(A1..A3) = 25.1327, where A1..A3 = 2,@PI,4

@PROPER

@PROPER returns the string S with the first letter of each word capitalized.

Examples:

@PROPER("president") = "President"

@PROPER(P9) = "Chicago", where P9 = "CHICAGO"

@PROPER(E18) = "Chapel Hill", where E18 = "chapel hill"

@PTTEST

@PTTEST returns the significance level (alpha) of the two-sided paired T-test for the paired samples contained in ranges R1 and R2, respectively. Then alpha is the probability of error in rejecting the null hypothesis that mean [y-x]=0. R1 and R2 must have the same dimension.

Examples:

@PTTEST(A1..A4,B1..B4) = 0.64, where A1..A4 = .5, .98, .22, .1, and B1..B4 = .87, .6, .21, .49.

@PTTEST(H5..H9,K5..K9) = 0.0008594, where H5..H9 = 14, 35, 37, 0, 33, and K5..K9 = 67, 77, 94, 34, 99.

@PTTEST(A1..A30,B1..B50) = Error!, ranges must be same dimension

@PURECOUNT

Returns the count of cells in a list of ranges that contain values, excluding the count of cells that contain strings.

@PV

@PV returns the present value of an annuity given periodic payment P, interest rate R and N number of periods.

Examples:

@PV(360, RATE, 4) = 1116.88, where RATE is a named cell = 11%

@PV(V10, C6,5) = 41001.97, where C6 = 7% and V10 = 10000

@PV(4500,0.05,-1) = Error!, "Period" must be an integer >0

@QUARTILE

@QUARTILE finds the quartile Q of the data in range R. This is equivalent to @PERCENTILE(R,Q/4).

Examples:

A1..A8 = 1,2,4,7,8,9,10,12

@QUARTILE(A1..A8,2) = 7.5

@QUARTILE(A1..A8,1) = 3.5

@RADIANS

@RADIANS returns p/180 times X.

Examples:

@RADIANS(1) = 0.017453293

@RADIANS(@DEGREES(25)) = 25

@COS(@RADIANS(G57)) = 0.5, where G57 = 60

@RAND

@RAND returns a uniformly distributed random number on the interval [0,1). The number will change every time the sheet is recalculated.

Examples:

@RAND = .38952085

@RAND * 10 = 7.6609264, a number between 0 and 10

@RAND/1 = 0.85737794

@RANGENAME

@RANGENAME returns the name of the first named range (alphabetically) which contains the cell C.

@RANGEREF

@RANGEREF returns a reference to the range described by corner coordinates N1, N2, N3, and N4. Row index must be relative to 1; column index must be relative to 0.

Examples:

@SUM(@RANGEREF(0,1,0,2)) is equivalent to @SUM(A1..A2)

@AVG(@RANGEREF(1,1,25,10)) is equivalent to @AVG(B1..Z10)

@RANK

@RANK returns the rank of a numeric argument E in the range R based on order O. If O is non-zero, the rank is equal to the position of the element if the list were sorted in ascending order, but if O is zero or omitted, the rank is equal to the position of the element if the list were sorted in descending order. Empty cells and text are ignored. Duplicate numbers will have the same rank.

Examples:

A1..A8 = 5,7,5,2,8,1,9,10

@RANK(A1..A8,7) = 4

@RANK(A1..A8,7,1) = 5

@RATE

@RATE returns the interest rate required to go from present value PV to future value FV in N compounding periods. The formula used is:1111

Examples:

@RATE(5000,3000,10) = .05

@RATE(H8,G8,B6) = .149, where B6 = 5, G8 = $5,000.00, and H8 = $10,000.00

@RATE(8700,-1000,3) = Error!, "PV" and "FV" must have the same sign

@RECEIVED

@RECEIVED returns the value at maturity of a fully invested security. Dates must be entered as a serial date value.

Example:

@RECEIVED(@DATE(1993,1,15),@DATE(1993,6,15),,10000,12.5%,1) = 10545.323

@REGEX

@REGEX returns True (1) if the string S2 matches the pattern specified by the regular expression in string S1, and False (0) otherwise. This function is similar to @EXACT except it allows "wildcard" comparisons by interpreting S1 as a regular expression of the type used in the Find and Extract operations. For more information on regular expressions, see Shortcuts and Tools.

Examples:

@REGEX("t.p","top") = 1

@REGEX("t.*e","table") = 1

@REGEX("t.*e","talk") = 0

@REGEX("F[0-9]","F3") = 1

@REGEX("F[0-9","F3") = Error! Missing ]

@REGEX("a","apple") = 1

@REGEX("ab","apple") = 0

@REPEAT

@REPEAT returns the string S repeated N times.

Examples:

Z1 = "There's no place like home."

@REPEAT(Z1,1) = "There's no place like home."

@REPEAT(Z1,3) = "There's no place like home.There's no place like home.There's no place like home."

@REPEAT(Z1,0) = " "

@REPLACE

@REPLACE returns the string formed by replacing the N2 characters starting at position N1 in string S1 with string S2.

Examples:

@REPLACE("John",1,3,"ack") = "Jack"

@REPLACE(E1,8,3,"Log") = "Expense Logort", where E1 = "Expense Report"

@REPLACE(E1,8,6,"Log") = "Expense Log", where E1 = "Expense Report"

@REPLACE("ABC",0,0,"XYZ") = "XYZABC"

@REVERSE

Returns the characters in string S in reverse order. The first is last, the last is first, etc.

Examples:

@REVERSE("ABCD") = "DCBA"

@RIGHT

@RIGHT returns the string composed of the rightmost N characters of S. If N is greater than or equal to the length of S, then the entire string S is returned.

Examples:

F1 = "workstation"

@RIGHT(F1,1) = "n"

@RIGHT(F1,4) = "tion"

@RIGHT(F1,20) = "workstation"

@RMS

@RMS returns the square root of the mean of squares of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@RMS(2,2,3,6) = 3.6400549

@RMS(D1..D6) = 48.72029, where D1..D6 = 4, 56, 33, 100, 0, and -1

@ROUND

@ROUND returns X rounded to the number of decimal places specified by n (when n is positive); it returns X rounded to a whole number when n is negative.

Examples:

@ROUND(@PI,2) = 3.14

@ROUND(@COS(60),2) = -0.95

@ROUND(1234.5678,-2) = 1200

@ROW

@ROW returns the row index of the cell referenced by C, or the top row if C is a range.

Examples:

@ROW(G42) = 42

@ROW(T10..V25) = 10

@ROW = 3, where @ROW is in cell B3.

@ROWS

@ROWS returns the number of rows in the specified range.

Examples:

@ROWS(C4..C13) = 10

@S

@S returns the string value of the top left cell in range R, or a null string ("") if the cell is empty or contains a numeric value.

Examples:

Matrix A1..B3

                A      B
         1       1    electrons
         2       2    protons
         3       3    neutrons

@S(A1..B3) = " " @S(B1..B3) = "electrons" @S(A1..Z25) = " ", where A2 = $1,000,000.00

@SALARM

@SALARM evaluates the string S as an expression if X evaluates non-zero. The return value is set to the result of evaluating expression S. If X evaluates to zero, S is not evaluated and the return value is zero. Unlike @ALARM, the terminal does not beep (silent alarm). @SALARM differs from @IF by taking a string argument whose contents are evaluated as an expression only when X is non-zero. Important in real-time control.

Examples:

@SALARM(Pressure > 500, "@REMOTE_FN(Pressure)") If "Pressure" exceeds 500, REMOTE_FN is called with the value of "Pressure".

@SEC

Return the secant of X

@SECH

Return the hyperbolic secant of x.

@SECOND

@SECOND returns the seconds (0 to 59) component of date/time value DT.

Examples:

@SECOND(.1000000) = 0

@SECOND(.8755342) = 46

@SECOND(.0645977) = 1

@SETSTRING

Returns a string of length len with alignment align. The default alignment is 0. If the string is longer than len then the original string is returned. The valid alignments are:

	0 - Left-aligned.
	1 - Center-aligned.
	2 - Right-aligned.

@SHEET

The number of the sheet in a cell reference.

@SHEETS

The number of sheets in a range.

@SIGMOID

@SIGMOID returns the value of the sigmoid function. The result is between 0 and 1.

Examples:

@SIGMOID(1) = 0.26894142

@SIGMOID(D14) = .5, where D14 = 0

@SIGMOID(T5) = 1.026188e-10, where T5 = 23.

@SIGN

Returns the sign of the number (1, 0, -1).

@SIN

@SIN returns the sine of the angle X. To convert the argument from degrees to radians, use the @RADIANS function.

Examples:

@SIN(1) = 0.841470984

@SIN(-2) = -0.90929743

@SIN(@RADIANS(R5)) = 0.70710678, where R5 = 45

@SINH

@SINH returns the hyperbolic sine of angle X.

Examples:

@SINH(1) = 1.175201194

@SINH(O12) = -4.0218567, where O12 = -2.1

@SINH(A1/A2) = 0.521095305, where A1 = 1 and A2 = 2

@SLN

@SLN returns the straight-line depreciation allowance given cost C, salvage value S, and allowable life L.

Examples:

@SLN(5000,1500,15) = 233.333

@SLN(S10,S9,20) = 5, where S9 = 400 and S10 = 500

@SLN(500,1000,12) = error!, must have "Cost" >= "Salvage" >= 0

@SMALL

@SMALL returns the Nth smallest number in range R. Cells that are empty or that contain text are ignored.

Examples:

A1..A9 = 1,4,8,3,7,12,54,8,23

@SMALL(A1..A9,3) = 4

@SMALL(A1..A9,1) = 1

@SMALL(A1..A9,@COUNT(A1..A7)) = 54

@SQRT

@SQRT returns the positive square root of X.

Examples:

@SQRT(100) = 10

@SQRT(@ABS(P9)) = 2, where P9 = -4

@SQRT(-3) = Error!, argument is negative

@SQRTPI

Returns SQRT(number*PI).

@SSE

@SSE returns the sum squared error of its arguments. It is equivalent to @VAR(...) * @COUNT(...).

Examples:

@SSE(1.5,2.5,3.5,4.5) = 5

@SSE(-1.5,4.5,7.65,9.56) = 70.31

@SSQ

@SSQ returns the sum of squares of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@SSQ(2,2,3,6) = 53

@SSQ(G1..G4) = 132, where G1 = -8, G2 = -6, G3 = -4, and G4 = -4

@SSQ(D1..D6) = 14242, where D1..D6 = 4, 56, 33, 100, 0, and -1

@STD

@STD returns the population standard deviation (N weighting) of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@STD(1,2,7) = 2.6246693

@STD(Y1..Y5) = 8.2316463, where Y1..Y5 = 22, 24, 29, 34, and 45

@STD(K6..K10) = 0.7076892, where K6..K10 = 1, .33, 0, -0.66, and -0.99

@STDS

@STDS returns the sample standard deviation (N-1 weighting) of its arguments. Empty cells and cells containing strings are not counted.=121

Examples:

@STDS(1,2,7) = 3.2145503

@STDS(Y1..Y5) = 9.2032603, where Y1..Y5 = 22, 24, 29, 34, and 45

@STDS(K6..K10) = 0.79122058, where K6..K10 = 1, .33, 0, -0.66, and -0.99

@STRCAT

@STRCAT returns the concatenation of all its arguments. For cell arguments, the actual formatted contents of the cell are used, even if the cell contains a numeric value.

Example:

@STRCAT("The bottom line is ",B3,"!") = "The bottom line is 1!", where B3 = 1

@STRCAT("The sum is ",@SUM(I1..I5) = "The sum is 4", where I1..I5 = 1, 1, -4, 0, and 6

@STRCAT("The Word is ",@PROPER(S3)) = "The word is Boston", where S3 = "Boston"

@STRING

@STRING returns the string representing the numeric value of X, formatted to N decimal places, where N is in the range 0 to 15. The results are rounded.

Examples:

@STRING(2,4) = "2.0000"

@STRING("2",4) = "0.0000"

@STRING(@SUM(A1..A3),1) = "1.7", where A1 = 0.33, A2 = 0.55, and A3 = 0.77

@STRLEN

@STRLEN returns the total length of all the strings in its argument list. It differs from @LENGTH in that it can take multiple arguments, but more significantly, it returns the length of the formatted contents of a cell, even if it contains a numeric argument.

Example:

@STRLEN("PHYSICS") = 7

@STRLEN(B1) = 34, where B1 ="Engineering Scientific Spreadsheet"

@STRLEN(A1) = 10, where A1 contains the value formatted as currency with two decimal places so that it appears on screen as $1,000.00 (plus a trailing space).

@SUBSTITUTE

Substitutes the new pattern for the occurrences of the old pattern. If instance is not specified, all occurrences are substituted. If instance is specified, only that occurrence number is substituted.

@SUM

@SUM returns the sum of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@SUM(10,20,30,40,50) = 150

@SUM(L1..L3) = 15.10564, where L1 = 1.32342, L2 = 5.45735, and L3 = 8.32487

@SUM(A1,B1,C1,D1) = 0, where A1 = -1, B1 = -9, C1 = 9, and D1 = 1

@SUMNEGATIVE

Returns the sum of the negative values in the list. List can contain ranges, single cells, or expressions.

@SUMPOSITIVE

Returns the sum of the positive values in the list. List can contain ranges, single cells, or expressions.

@SUMPRODUCT

@SUMPRODUCT returns the sum of products of two vectors. R1 must have the same number of rows and columns as R2, or R1 and R2 can be one-dimensional vectors of the same length.

Examples:

Matrix 1, D4..E5

               D       E
          4     2       3
          5     4       6

Matrix 2, D7..E8

               D       E
          7    -5       0
          8     2       8

@SUMPRODUCT(D4..E5,D7..E8) = 46
@SUMPRODUCT(D4..D5,D7..E7) = -10
@SUMPRODUCT(A1..A2, M1..P4) = Error!, incompatible range dimensions

@SUMSQ

Returns the sum of the squares of the values in the list. List can contain ranges, single cells, or expressions.

@SUMXMY2

Returns the sum of squares of the differences of corresponding elements in the ranges. Range1 and Range2 must have conforming sizes.

@SUMX2MY2

Returns the sum of the squares of the values in the first range minus the sum of the squares of the values in the second range. (SUM(X(I)**2-Y(I)**2). Equal to SUMSQ(range1) - SUMSQ(range2) but ensures that both ranges are conforming sizes.

@SUMX2PY2

Returns the sum of the squares of the values in the first range plus the sum of the squares of the values in the second range. (SUM(X(I)**2+Y(I)**2). Equal to SUMSQ(range1) + SUMSQ(range2) but ensures that both ranges are conforming sizes.

@SYD

@SYD returns the "sum-of-years-digits" depreciation allowance given cost C, salvage value S, allowable life L, and depreciation period P. +1+12

Examples:

@SYD(5000,1500,15,14) = 58.333

@SYD(S10,S9,20,2) = 9.048, where S9 = 400 and S10 = 500

@SYD(2500,750,0,5) = Error!, "Life" must be > 0

@T

@T returns the integral of Student's T-distribution with N degrees of freedom from minus infinity to T.

Examples:

@T(14,35) = 1

@T(F3,G6) = 0.8411029, where F3 = 500, and G6 = 1

@T(C1,C2) = Error!, degrees of freedom must be > 0, where C1 = 0 and C2 = -1

@TAN

@TAN returns the tangent of angle X.

Examples:

@TAN(1) = 1.554707725

@TAN(Y1) = 1.619775191, where Y1 = 45

@TANH

@TANH returns the hyperbolic tangent of angle X. The result is between -1 and 1.

Examples:

@TANH(0) = 0

@TANH(G4) = 0.454216432, where G4 = .49

@TANH(X8) = -1, where X8 = -30

@TBILLEQ

@TBILLEQ returns the bond-yield equivalent (BEY) for a Treasury Bill equivalent to a bond, given settlement date S, maturity date M, and discount rate D. Dates must be expressed as serial date values. If the term is one half-year or less, BEY is equivalent to an actual/365 simple interest rate. If the term of the security is more than one-half year, BEY is equivalent to a semiannually compounded Treasury bond yield.

Examples:

@TBILLEQ(@DATE(1993,3,31),@DATE(1993,6,20),9.14%) = 0.09462

@TBILLEQ(@DATE(1993,3,31),@DATE(1993,12,15),9.14%) = 0.09819

@TBILLPRICE

@TBILLPRICE returns the price per $100 face value for a Treasury bill, given settlement date S, maturity date M, and discount rate D. Dates must be expressed as serial date values.

Example:

@TBILLPRICE(@DATE(1993,4,2),@DATE(1993,7,5),9.25%) = 98.5847

@TBILLYIELD

@TBILLYIELD returns the yield on a treasury bill, given settlement date S, maturity date M, and discount rate D. Dates must be expressed as serial date values.

Example:

@TBILLYIELD(@DATE(1993,4,2),@DATE(1993,7,5),97.585) = 0.09478

@TERM

@TERM returns the number of payment periods for an investment given the periodic payment P, the interest rate R and future value FV.

Examples:

@TERM(1050,0.105,300000) = 34.39

@TERM(R13,RATE,S19) = 16.48, where R13 = 360, S19 = 15000, and RATE is a named cell = 11%

@TERM(100,0.095,-1) = error!, "P" and "FV" must have same sign

@TEXT

Formats the value X value using the picture format string S. It returns a string with the value converted to the specified format.

The string S contains a picture defining how to format the value. It is the same format as User Defined Formats, except that the color information is ignored.

@TIME

@TIME returns the time value represented as a fraction of a day, starting at midnight. H must be between 0 and 23, and M and S must be between 0 and 59.

Examples:

@TIME(12,0,0) = 0.5, where 0.5 represents half of a day, which is noon

@TIME(8,45,46) = 0.36511574

@HOUR(@TIME(C1,D1,E1)) = 13, where C1 = 13, D1 = 40, and E1 = 2

@TIMEVALUE

@TIMEVALUE returns the corresponding time value for a given string value S. The function interprets the times specified in the following formats:

	
HH:MM:SS   AM or PM		(1 <= HH <=12)
HH:MM:SS			(0 <= HH <=23)
HH.MM.SS			(0 <= HH <=23)
HH,MM,SS			(0 <= HH <=23)
HHhMMmSSs		(0 <= HH <=23)

In all formats the seconds (SS) may be omitted, in which case they default to 00.

In all formats the hours may be specified with 1 or 2 digits, while the minutes (MM) and seconds (SS) must be 2 digits.

The following are equivalent:

        @TIMEVALUE("12:55:00 AM")
        @TIMEVALUE("12:55 am")
        @TIMEVALUE("00:55:00")
        @TIMEVALUE("00:55")
        @TIMEVALUE("00,55,00")
        @TIMEVALUE("0.55.00")
        @TIMEVALUE("0h55m00s")

@TODAY

@TODAY returns the date value corresponding to the current system date.

Examples:

@DATE(@TODAY) = 33139, where 33139 represents September 23, 1990

@MONTH(@TODAY) = 9

@TRANSPOSE

@TRANSPOSE generates the transpose of the numeric matrix M.

Examples:

Matrix B9..C10

             B    C
         9    1    3
        10    2    4

@TRANSPOSE(B9..C10) =

                     1    2
                     3    4

Matrix  A1..B4

             A    B
         1   .99    0
         2    3    6.2
         3   4.1   1.1
         4   3.3   6.3

@TRANSPOSE(A1..B4) = 

                    .99    3    4.1    3.3
                     0    6.2   1.1    6.3

@TRANSPOSE(B17) = Error!, improper argument type

@TREND

@TREND fits a straight line to KX (known x's) and KY (known y's) using least square method, and then returns the y values along the line for NX (new x's).

Example:

                  B            C
         10    X values    Y values
         11        1          242
         12        2          256
         13        3          263
         14        4          274
         15        5          279
         16        6          285
         17        7          290
         18        8
         19        9
         20       10

@TREND(A1..A2, B11..B17, C11..C17) = 

                             301
                             308.7857
                             316.5714

@TRIM

@TRIM returns the string formed by removing leading, trailing and consecutive embedded spaces from string S.

Examples:

@TRIM(" Alpha Beta Gamma ") = "Alpha Beta Gamma"

@TRUE

@TRUE returns the value 1.

Examples:

@TRUE = 1

@IF(B17 == 0,@TRUE,@FALSE) = 1

@TTEST

@TTEST returns the significance level (alpha) of the two-sided single population T-test for the population samples contained in the range R. Alpha is the probability of error in rejecting the null hypothesis that mean [R]=X.

Examples:

@TTEST(A1..A4,B1) = 0.12093936, where A1..A4 = .5, .98, .22, .1, and B1 = .87,

@TTEST(H5..H9,K5) = 0.0039514106, where H5..H9 = 14, 35, 37, 0, 33, and K5 = 67

@TTEST(V1..V10,W1..W10) = Error!, improper argument type

@TTEST2EV

@TTEST2EV returns the significance level (alpha) of the two-sided dual population T-test for the population samples contained in the ranges R1 and R2 under the assumption that the population variances are the same. Alpha is the probability of error in rejecting the null hypothesis that mean[R1] = mean[R2] assuming var[R1] = var[R2].

Examples:

@TTEST2EV(A1..A4,B1..B4) = 0.71153758, where A1..A4 = .5, .98, .22, .1, and B1..B4 = .87, .6, .21, .49.

@TTEST2EV(H5..H9,K5..K9) = 0.0061236, where H5..H9 = 14, 35, 37, 0, 33, and K5..K9 = 67, 77, 94, 34, 99.

@TTEST2UV

@TTEST2UV returns the significance level (alpha) of the two-sided dual population T-test for the population samples contained in the ranges R1 and R2 without assuming that the population variances are the same. Alpha is the probability of error in rejecting the null hypothesis that mean[R1] = mean[R2], assuming var[R1] and var[R2] are unrelated.

Examples:

@TTEST2UV(A1..A4,B1..B4) = 0.71306706, where A1..A4 = .5, .98, .22, .1, and B1..B4 = .87, .6, .21, .49.

@TTEST2UV(H5..H9,K5..K9) = 0.0083561, where H5..H9 = 14, 35, 37, 0, 33, and K5..K9 = 67, 77, 94, 34, 99.

@UPPER

@UPPER returns the string S converted to uppercase characters.

Examples:

@UPPER("New York") = "NEW YORK"

@UPPER(H7) = "POPULAR VOTE", where H7 = "popular vote"

@VALUE

@VALUE returns the numeric value represented by string S, or 0 if S does not represent a number.

Examples:

@VALUE("324.399") = 324.399

@VALUE(G10) = 1.7, where G10 = "1.7"

@VALUE(M8) = 0, where M8 = "cosine"

@VAR

@VAR returns the population variance (N weighting) of its arguments. Empty cells and cells containing strings are not counted. =12

Examples:

@VAR(1,2,7) = 6.8888889

@VAR(Y1..Y5) = 67.76, where Y1..Y5 = 22, 24, 29, 34, and 45

@VAR(K6..K10) = 0.500824, where K6..K10 = 1, .33, 0, -0.66, and -0.99

@VARS

@VARS returns the sample variance (N-1 weighting) of its arguments. Empty cells and cells containing strings are not counted.

Examples:

@VARS(1,2,7) = 10.333333

@VARS(Y1..Y5) = 84.7, where Y1..Y5 = 22, 24, 29, 34, and 45

@VARS(K6..K10) = 0.62603, where K6..K10 = 1, .33, 0, -0.66, and -0.99

@VDB

@VDB returns the depreciation of an asset between two specific period using the fixed-declining balance method.

Examples:

@VDB(10000,500,120,2,4) = 319.6289

@VECLEN

@VECLEN returns the square root of the sum of squares of its arguments. It calculates the length of a vector in N-dimensional space. In the two-argument case, @VECLEN is the Pythagorean theorem for the hypotenuse of a right triangle, given the length of the other two sides.

Examples:

@VECLEN(3,4) = 5

@VECLEN(-1,-2,-3,0) = 3.7416574

@VECLEN(A1..A2) = 2 where A1 = 1 and A2 = 2.236068

@VECLEN(S1..S3,4) = 5.4772256,where S1 = 1, S2 = 2, and S3 = 3

@VERIFY

Returns the number of the first character in string that is not contained in the list of valid characters. The number returned is 0-based as in the FIND() function. If all characters in the string are found in the list of valid characters, VERIFY returns the length of the string.

@VLOOKUP

@VLOOKUP searches the first column (known as the index column) in range R for the numeric or string value which "matches" X, and returns the value N columns to the right in the row where the match is found. The index column is searched from top to bottom. If the column contains numeric values, they must be in ascending order, and cannot contain an empty cell.

If X is a string value, an exact match must be found or an error is returned.

If X is a numeric value, the matching row is determined by the following rules:

1. Strings and empty cells in the index column are ignored.
2. If the first value in the index column is greater than X, an error is returned.
3. Searching stops when a numeric value which is greater than or equal to X is found in the index column. If the value found is greater than X, the preceding row is the matching row.

Examples:

                  A              B
           1    Lodging        $85.00
           2    Meals          $30.00
           3    Airfare       $698.00
           4    Entertainment  $25.00

@VLOOKUP("Meals",A1..B4,1) = 30

@VLOOKUP("Airfare",A1..B4,1)) = Error - @VLOOKUP, lookup failed to produce a match

@VLOOKUP("Airfare",A1..B4,2)) = Error - @VLOOKUP, column out of range

@VSUM

@VSUM returns the "visual sum" of its arguments. This function differs from @SUM in that it uses the precision and rounding of the actual formatted values appearing in the cells instead of the internal double-precision value of the cell. This ensures that what you see is what you get when calculating a sum.

Examples:

@VSUM(L1..L3) = .99 where L1 = 1/3, L2 = 1/3, and L3 = 1/3 and are displayed with two decimal places

@WEEKDAY

@WEEKDAY returns an integer representing the day of the week on which the day D falls. 1 is Sunday, 7 is Saturday.

Examples:

@WEEKDAY(@DATEVALUE("2/25/1994")) = 6

@WEEKDAY(34466) = 5

@WORKDAY

@WORKDAY returns the serial day value that is D working days after day S (if D is positive) or D working days before day S (if D is negative),excluding weekends and all holidays specified as dates in range H.

Examples:

@WORKDAY(@DATE(1991,1,1),5,B1..B2) = 33249 or 01/11/1991, where A1..A2 = @DATEVALUE("1/7/1991"), @DATEVALUE("1/8/1991")

@WORKDAY(@DATE(1993,1,1),258,A1..A5) = 34334n or 12/31/1991, where A1..A5 = @DATEVALUE("1/1/1993"), @DATEVALUE("5/25/1993"), @DATEVALUE("7/4/1993"), @DATEVALUE("9/3/1993"), @DATEVALUE("11/28/1993")

@XHLOOKUP

@XHLOOKUP is the same as @HLOOKUP except that it has an extra argument.which specifies the file containing the spreadsheet where the lookup table is located. The range argument can be a range specification or a string which contains a range specification to be evaluated in the context of the external spreadsheet file.

Example:

=@XHLOOKUP("table.xs5",1,"input",3)

@XIRR

@XIRR returns the internal rate of return for a series of cash flows (V) with variable intervals (D). V and D must be one-dimensional ranges and have the same size.

Example:

A B 1 02/01/91 -12000 2 05/01/91 2800 3 10/01/91 3250 4 02/01/92 4460 5 04/01/92 5000 @XIRR(0.5,B1..B5,A1..A5) = 0.3632

@XNPV

@XNPV returns the net present value for a series of cash flows (V) with variable intervals (D). V and D must be one-dimensional ranges and have the same size.

Example:

A B 1 01/10/91 -12000 2 04/01/91 3000 3 09/30/91 4150 4 01/10/92 3300 5 04/01/92 3150 @XNPV(9.00%,B1..B5,A1..A5) = 705.1677

@XOR

@XOR returns -1 if any argument is not 0 or 1; otherwise 1 if the total number of arguments with the value 1 is odd; 0 if the total number of arguments with the value 1 is even. Text and empty cells are ignored.

Examples:

A1 = 1, B1 = 1, C1 = 0, D1 = 0, E1 = 1 and A2 = 24

@XOR(A1,B1) = 0

@XOR(A1..E1) = 1

@XOR(A1..A2) = -1

@XVALUE

@XVALUE returns the value of cell C in sheet saved as file N.. When the cell reference C is the same as the cell containing the @XVALUE function, use the hash mark (#) as the second argument. If C is a string containing a range name, that name is resolved in the workbook specified by N. If sheet N is currently loaded into another instance of XESS or some other spreadsheet program and you change a value, you must first save sheet N then recalculate the sheet containing the @XVALUE function to retrieve the new value entered into sheet N.

Examples:

@XVALUE("controls.xs5",A1) returns the value of cell A1 from the sheet saved as the file controls.xs5 in your default directory when the @XVALUE function is stored in a cell other than A1.

@XVALUE("controls.xs5",#) returns the value of cell A1 from the sheet saved as the file controls.xs5 in your default directory when the @XVALUE function is stored in cell A1. =@XVALUE("table.xs5","Sheet3!C5") =@XVALUE("sales1997.xs5","Q4.total")

@XVLOOKUP

@XVLOOKUP is the same as @VLOOKUP except that it has an extra argument.which specifies the file containing the spreadsheet where the lookup table is located. The range argument can be a range specification or a string which contains a range specification to be evaluated in the context of the external spreadsheet file.

Example:

=@XVLOOKUP("table.xs5",1,"input",3)

@YEAR

@YEAR returns the years component of the date/time value DT.

Examples:

@YEAR(24203) = 1966, where 24203 represents April 6, 1966

@YEAR(@TODAY) = 1999, where TODAY = December 31, 1999

@YEARFRAC

@YEARFRAC returns the year fraction representing the number of whole days between S (start date) and E (end date). This function is useful in calculating the percent of benefit or obligation to assign a specific period.

Examples:

@YEARFRAC(@DATE(1994,1,1),@DATE(1994,5,30),0) = 0.4167

@YEARFRAC(@DATE(1994,1,1),@DATE(1994,5,30),1) = 0.4110

@YIELD

@YIELD returns the yield at maturity of a security that pays periodic interest. All dates must be entered as serial date value.

Example:

@YIELD(@DATE(1991,3,15),@DATE(1998,10,15),6%,94.1854,100,2,0) = 0.07

@YIELDDISC

@YIELDDISC returns the annual yield for a discounted security. All dates must be entered as serial date value.

Example:

@YIELDDISC(@DATE(1993,3,1),@DATE(1993,5,1),98,875,100,0) = 0.06827

@YIELDMAT

@YIELDMAT returns the annual yield of a security which pays interest at maturity. All dates must be expressed as serial date values.

Examples:

@YIELDMAT(@DATE(1993,3,1),@DATE(1993,10,1),@DATE(1992,11,5), 6.5%,99.875,0) = 0.06585

Defining the Appearance of Cells

Additionally, what you see on the display is essentially what you get when the sheet is printed to a PostScript printer which supports colors and fonts.

Spreadsheet Appearance

• start-up defaults
• workbook defaults and sheet attributes
• cell defaults
• individual cell attributes
• highlight options
• row/column specifications
• view title specifications

Defaults and Preferences

Start-up Defaults

While XESS maintains a set of "fallback" definitions internally, you may wish to change the local resources (start-up defaults) to suit your preferences. Start-up defaults which can be changed that affect the spreadsheet appearance include:

• Workbook Defaults
• Sheet Attributes
• Cell Defaults
• Highlight Options
• Color Palette
• XESS Aesthetics

Workbook Defaults and Sheet Attributes

Default characteristics are viewed and changed using the Cell Defaults dialog box and the Workbook Defaults dialog box available from the Options menu.

You may save a workbook template of these settings which are then used as the default whenever you create a new workbook. Simply set the desired options and save the workbook with the name xess_template.xs5.

Workbook Defaults

To change default workbook settings:

1. Select Workbook Defaults from the Options menu. XESS displays the Workbook Defaults dialog box.
2. Change any of the values.
3. Apply your selection by clicking OK or Apply.

Sheet Attributes

To change sheet attributes:

1. Select Sheet Attributes from the Options menu. XESS displays the Sheet Attributes dialog box.
2. Change any of the values.
3. Apply your selection by clicking OK or Apply.

Cell Defaults

The Cell Defaults that you can set include:

• Cell format
• Cell font family, size, style and color
• Orientation and alignment
• Underlining and strikethrough
• Automatic text wrapping
• Colors and patterns used to display values

These defaults change all cells, rows, or columns in the workbook that are not already explicitly assigned attributes.

To change Cell Default settings: Select Cell Defaults from the Options menu. XESS displays the Cell Defaults dialog box.

1. Change the desired options.
2. Apply your selection by clicking OK or Apply.

Workbook Defaults versus Individual Cell Attributes

As an alternative to using the Format menu, you can change individual cell formats and attributes using the toolbar or the mouse button 3 (MB3) popup menu.

When you apply formats to specific cells, you do not interfere with the global defaults or the appearance of other cells in the workbook

Cell Border Defaults Dialog

Highlight Options

• Errors
• Negative Values
• Constraint conditions which are not met

You may choose distinct background and foreground colors to flag these results for all cells in the workbook. These colors override any colors already in the cells:

• errors
• negative values
• constraint conditions which are not met.

These colors are applied to the entire sheet and override those selected by Cell Colors for individual cells.

To set the highlight options:

1. Select Highlight Options from the Options menu. XESS displays the Highlight Options dialog box.
2. Click the check button beside the option to activate or deactivate the feature for any or all of the three conditions.
3. Set the background and/or foreground color for each condition by pointing to the designated option button and holding down the left mouse button. Position the mouse on the color of choice and release.
4. Apply your selection by clicking OK or Apply

Cell Formats

You can only choose one format; when you make a selection, XESS automatically turns off the previous selection.

To change default Cell Format:

1. Select Cell Defaults from the Options menu. XESS displays the Cell Defaults dialog box.
2. Click the Cell Format and/or Decimal Places option button to select the desired format.
3. Apply your selection by clicking OK or Apply.

To change the Cell Format for individual cells using the menu:

1. Select a cell or range of cells to be affected.
2. Select Cell Format from the Format menu or MB3 popup menu. XESS displays the Cell Format dialog box.
3. Click the Cell Format and/or Decimal Places option button to select the desired format.
4. If you are defining a Custom format, modify the additional fields.
5. Apply your selection by clicking OK or Apply.

You can also click on the Format and Decimals icon buttons on the toolbar.

Format Types

Scientific

Displays the number in scientific notation (exponent form) with N significant digits.

Fixed

Displays the number using a fixed number of decimal places, specified by N.

General

Displays the number in fixed format or scientific notation, whichever fits. Trailing zeros are not displayed.

Dollars

Displays the number with a leading $ sign and with comma delimiters, as in $1,000,000. Negative values are displayed in parentheses.

Currency

Displays the number using the relevant currency settings from the workstation's Locale.

Custom

Displays the number using the combination of specification from the Custom dialog which allows you to override all settings defined by Locale.

Comma

Displays the number with comma delimiters, as in 1,000,000. Negative values are displayed in parentheses.

Percent

Display the number as a percentage, multiplying it by 100. For example, the value .1 is displayed as 10.00%.

Fraction

Displays the non-integer part of a number as a fraction instead of decimal.

MM/DD/YY

Displays the integer portion of a date/time value in the format 08/01/99.

DD-MMM-YY

Displays the integer portion of a date/time value as a Gregorian date, in the format 08-Aug-91.

MMM-YY

Displays the integer portion of a date/time value in the format Aug-99.

DD-MMM

Displays the integer portion of a date/time value in the format 08-Aug.

YY-MM-DD

Displays the integer portion of a date/time value in the format 99-08-01.

YYYY-MM-DD

Displays the integer portion of a date/time value in the format 2000-01-01.

DD.MM.YY

Displays the integer portion of a date/time value in the format 01.08.99.

HH:MM

Displays the fractional portion of a date/time value in the format 06:15.

HH:MM:SS

Displays the fractional portion of a date/time value in the format 06:15:30.

Hex

Displays the integer portion of the number in base 16 with a leading 0x indicator. For example, the value 30.5 would be displayed as 0x1e.

Logic

Displays 0 as 0, displays 1 as 1, and displays all other values as ? .

Hidden

Cell contents are not displayed.

Text

Displays the characters which were entered to create the cell rather than the resulting value of the cell.

Decimal Places

When the Cell Format selected is Default, the number of decimal places is defined by the Cell Defaults dialog, not by the Toolbar or the Cell Format dialog.

Custom Formats

You can define as many Custom Formats as you need for you application. Custom Formats are saved with the workbook.

While primarily designed for defining multiple currencies in the same workbook, this feature also gives you control over how negative numbers are displayed, what characters are used for the decimal point and thousands separator, and where these are displayed relative to each other.

User Defined formats and the @TEXT function provides additional methods for formatting values for display in the sheet. They let you design your own formats using a shorthand notation. These formats are particularly useful for displaying date and time values.

User Defined Formats

The @TEXT function uses a subset of these definitions, ignoring those that affect color.

The format string may contain any of the following operators. These operators are replaced with the appropriate values in the result string.

Numbers:

#

Ouput significant digits

0

Ouput significant digits or zeros

.

Output the decimal point

,

Output a comma separator

%

Output number as percent including percent character

@

Output the value if it is text; otherwise, output nothing.

"..."

Ouput the text between the quotes

yy

Output the last two digits of the year

yyy

Output the last two digits of the year if it meets the rule for this workbook; otherwise output all 4 digits of the year.

yyyy

Output all four digits of the year.

m

If seen after an h or hh operator, output the minutes in 1-digit format if the minutes are less than 10, or in 2-digit format if the minutes are between 10 and 59.

If seen at any other time, output the month in 1-digit format if the month is less than 10, or in 2-digit format if the month is between 10 and 12.

[m]

Elapsed time in minutes.

mm

If seen after an h or hh operator or before an s or ss operator, then output the minutes in 2-digit format.

If seen at any other time, output the month in 2-digit format.

mmm

Output the month abbreviation.

mmmm

Output the full month name.

d

Output the day in 1-digit format if the day is less than 10, or in 2-digit format if the day is between 10 and 31.

dd

Output the day in 2-digit format.

ddd

Output the abbreviation of the day of the week.

dddd

Output the full name of the day of the week.

h

Output the hour in 1-digit format if the hour is less than 10, or in 2-digit format if the hour is between 10 and 23.

hh

Output the hour in 2-digit format.

[h]

Elapsed time in hours

s

Output the second in 1-digit format if the second is less than 10, or in 2-digit format if the second is between 10 and 59.

ss

Output the second in 2-digit format.

[s]

Elapsed time in seconds

a/p

Output a if the hour is between 0 and 11, or p if the hour is between 12 and 23. If this format specifier is present, the hour will always be displayed using the 12-hour clock (0-12).

am/pm

Output am if the hour is between 0 and 11, or pm if the hour is between 12 and 23. If this format specifier is present, the hour will always be displayed using the 12-hour clock (0-12).

A/P

Output A if the hour is between 0 and 11, or P if the hour is between 12 and 23. If this format specifier is present, the hour will always be displayed using the 12-hour clock (0-12).

AM/PM

Output AM if the hour is between 0 and 11, or PM if the hour is between 12 and 23. If this format specifier is present, the hour will always be displayed using the 12-hour clock (0-12).

\

Escape the normal meaning of the next character and write it as an immediate literal to the output string.

Conditional Definitions

             [>0]#0.00+;[<0]#0.00-;

Because the comparisons with zero are so common, you may omit them when there are no other comparisons. Based on the number of formats specified, the default comparisons are:

                [all]
                [>=0] ; [<0]
                [>0] ; [<0] ; [=0]
                [>0] ; [<0] ; [=0] ; [text]

User defined formats allow you to control the foreground (or font) color of the cell, possibly dependent on its value. The color names or codes are placed within square brackets, separate from the test conditions. A typical use of this is to highlight values in a certain range.

      [>=1000][Color 3]#;[>=500][Color 5]#;[>0][Color 4]#;"ERROR"
      

      #;[Red]-#;#

Fonts

The default font settings for the whole workbook are modified with the Cell Defaults dialog box from the Options menu. However, you can control the font characteristics for the individual cell or range of cells by using the Font Family, Font Style, and Font Size options from the Format or MB3 popup menus.

XESS allows you to assign font characteristics which consist of family, style, and size.

Standard Font Families

• Courier
• Helvetica
• Souvenir
• Times-Roman
• Lubalin Graph
• Avant Garde
• New Century Schoolbook
• Symbol (Symbol)

Additional or alternative fonts can be defined using the resource file.

Font Style

• Normal
• Bold
• Italic
• Bold-Italic

Font Size

• 8 Pt
• 10 Pt
• 12 Pt
• 14 Pt
• 18 Pt
• 24 Pt

Additional font sizes can be defined using the resource file.

User-defined Fonts

Changing Fonts

1. Select Cell Defaults from the Options menu. XESS displays the Cell Defaults dialog box.
2. Click the Font Family option button and select the desired font family.
3. Click the Font Style option button and select the desired font style.
4. Click the Font Size option button and select the desired font style.
5. Apply your selection by clicking OK or Apply.

To change the Font characteristics for individual cells:

1. Select a cell or range of cells to be affected.
2. To change font family, select Font Family from the Format menu or MB3 popup menu to display a cascading menu of available choices. Choose a font family or choose default to use the default setting from Cell Defaults.
3. To change font style, select Font Style from the Format menu or MB3 popup menu to display a cascading menu of available choices. Choose a font style or choose default to use the default setting from Cell Defaults.
4. To change font size, select Font Size from the Format menu or MB3 popup menu to display a cascading menu of available choices. Choose a font size or choose default to use the default setting from Cell Defaults.

Underlining Cell Contents

To change the default Underline specification:

1. Select Cell Defaults from the Options menu. XESS displays the Cell Defaults dialog box.
2. Click the Underline option button and select one of: On to enable single underline Off to disable underlining Double to enable double underline
3. Apply your selection by clicking OK or Apply.

To change the Underline setting for individual cells:

1. Select a cell or range of cells to be affected.
2. Select Underline from the Format menu or MB3 popup menu to display a cascading menu of choices. Choose one of:

   Default to use the cell default setting
   On to enable single underline
   Off to disable underlining
   Double to enable double underline

Alternatively, you can click the underline toggle button icon on the toolbar to cycle through Off/Single/Double options.

Strikethrough

To change the default Strikethrough specification:

1. Select Cell Defaults from the Options menu. XESS displays the Cell Defaults dialog box.
2. Click the Strikethrough option button and select one of:

   On to make strikethrough the default
   Off to disable strikethrough

3. Apply your selection by clicking OK or Apply.

To change the Strikethrough setting for individual cells:

1. Select a cell or range of cells to be affected.
2. Select Strikethrough from the Format menu or MB3 popup menu to display a cascading menu of choices. Choose one of:

   Default to use the cell default setting
   On to set strikethrough attribute
   Off to clear strikethrough attribute

Aligning Cell Contents

Horizontal Alignment

The default for the whole sheet is changed in the Cell Defaults dialog box from the Options menu. However, you can set the alignment for an individual cell or range of cells by using the Alignment option from the Format menu, Toolbar icon buttons, or MB3 popup menu.

To change the default Alignment specification:

1. Select Cell Defaults from the Options menu. XESS displays the Cell Defaults dialog box.
2. Click the Alignment option button and select one of the following:

   Automatic Text is left aligned; numerics are right-aligned
   Right Text and numerics are right-aligned
   Left Text and numerics are left-aligned.
   Center Text and numerics are centered.

3. Apply your selection by clicking OK or Apply.

To change the Alignment setting for individual cells:

1. Select a cell or range of cells to be affected.
2. Click the desired alignment icon button on the Toolbar.

- OR -

Select Alignment from the Format menu or MB3 popup menu to display a cascading menu of choices. Choose an explicit setting or choose default to use the default setting from Cell Defaults.

One additional option, Center Selection, allows text to be centered over a group of cells.

To use Center Selection:

1. Enter the text in the left-most cell of the group of cells.
2. Select the group of cells over which to center the text.
3. Select the columns to be centered over.
4. Select Alignment>Center Selection.

Vertical Alignment

The default for the whole sheet is changed in the Cell Defaults dialog box from the Options menu. However, you can set the vertical alignment for an individual cell or range of cells by using the Vertical Alignment option from the Format menu or MB3 popup menu.

To change the default Vertical Alignment specification:

1. Select Cell Defaults from the Options menu. XESS displays the Cell Defaults dialog box.
2. Click the Vertical Alignment option button and select one of the following: Top Contents aligned with the top of cell Bottom Contents aligned with bottom of cell Center Contents are vertically centered.
3. Apply your selection by clicking OK or Apply.

To change the Vertical Alignment setting for individual cells:

1. Select a cell or range of cells to be affected.
2. Click the desired alignment icon button on the Toolbar.

Select Vertical Alignment from the Format menu or MB3 popup menu to display a cascading menu of choices. Choose an explicit setting or choose default to use the default setting from Cell Defaults.

Orientation

Wrapping Text Within Cell Boundary

The default for the whole sheet is changed in the Cell Defaults dialog box from the Options menu. However, you can set the wrap option for an individual cell or range of cells by using the Wrap option from the Format menu or MB3 popup menu.

To change the default Wrap specification for all cells:

1. Select Cell Defaults from the Options menu . XESS displays the Cell Defaults dialog box.
2. Click the Wrap option button and select one of: On to enable text wrapping Off to disable text wrapping
3. Apply your selection by clicking OK or Apply.

To change the Wrap setting for individual cells:

1. Select a cell or range of cells to be affected.
2. Select Wrap from the Format menu or MB3 popup menu to display a cascading menu of choices. Choose an On/Off setting or choose default to use the default setting from Cell Defaults.

Colors and Patterns

Palette

Assigning cell colors and patterns

1. Select a cell or range of cells to be affected.
2. Select Cell Color from the Format menu or MB3 popup menu. XESS displays the Cell Colors dialog box.
3. Click the Foreground option button and select a color for the text or numbers displayed in the selected cell(A1).
4. Click the Background option button and select a color for the background in the selected cells. (This can be used for either Protected or UnProtected cells.)
5. Click the Pattern option button and select a background pattern, if desired. If a pattern is specified, click the Pattern Color option button and select the color of the pattern foreground.
6. Apply your selection by clicking OK or Apply.

Default Cell Colors

1. Select Cell Defaults from the Options menu. XESS displays the Cell Defaults dialog box.
2. Click the Foreground option button and select a color for the text or numbers displayed in all protected cells.
3. Click the Background option button and select a color for the background in all protected cells.
4. Click the Pattern option button and select a background pattern, if desired. If a pattern is specified, click the Pattern Color option button and select the color of the pattern foreground.
5. Click the UnProtect FG option button and select a color for the text or numbers displayed in all unprotected cells.
6. Click the UnProtect BG option button and select a color for the background in all unprotected cells.
7. Apply your selection by clicking OK or Apply.

If Check Protection is set to OFF in Sheet Default, UnProtect FG and UnProtect BG have essentially no visible impact.

Cell Borders

There are two typical ways to use cell borders. The first places borders around each cell. The second places a single border around a group of cells.

To place borders on each cell in a range of cells:

1. Select the range of cells. If nothing is selected, the current cell is affected.
2. Choose Cell Borders... from the Format menu.
3. In the Cell Borders dialog, choose the color and border style for the left, right, top, and bottom borders. The default border style is no border. Click OK.

To place an outline border around a range of cells:

1. Select the range of cells. If nothing is selected, the current cell is affected.
2. Choose Cell Borders... from the Format menu.
3. In the Cell Borders dialog, choose the color and border style for the outline. Click OK.

Row and Column Attributes

• change column width,
• change row height,
• insert rows and columns,
• delete rows and columns,
• hide rows and columns, and
• create row and column titles

Column Width

You can also set individual columns to be wider or narrower than the default by using the Column Width option under the Format menu or MB3 popup or by simply dragging the column border with the mouse.

Setting Column Width with Menus

1. Select Workbook Defaults from the Options menu. XESS displays the Workbook Defaults dialog box.
2. Enter a number from 1.00 to 255.00 in the Default Column Width entry box.
3. Apply your entry by clicking OK or Apply.

To explicitly set the width of selected columns:

1. Select the column(s) you wish to change.
2. Select Column Width from the Format menu or MB3 popup menu which displays its cascading submenus.
3. Select Column Width... from the submenus. XESS displays the Column Width dialog box.
4. In the Selection entry box, enter the desired column width (1 - 255).
5. Apply your entry by clicking OK or Apply.

To restore the original default column width to selected columns:

1. Select the column(s) you wish to reset to the default.
2. Select Column Width from the Format menu or MB3 popup which displays its cascading submenus.
3. Select Default Column Width from the submenu.

Best Fit Column Width

These two features together allow you to define a column width to achieve optimal page layout and force long text strings to wrap within the column bounds rather than be truncated or overflow into the adjacent cell. Then you can use the snap-to mechanism to adjust the row height to accommodate the wrapped text.

To snap column width to best fit cell contents:

1. Select the column(s) you wish to adjust.
2. Select Column Width from the Format menu or MB3 popup which displays its cascading submenus.
3. Select Snap Width to Contents from the submenu.

Dragging the Column Border

Content Exceeds Column Width

When the column width of a cell is too narrow such that it cannot display a numeric entry completely, XESS alerts you to this condition by showing a bold cell border in the truncated cell. If truncation occurs to the left, the left cell border is highlighted. If the truncation occurs to the right, the right cell border is highlighted. (Cell Alignment determines whether the truncation occurs on the left or the right.)

Row Height

You can also set individual rows to be wider or narrower than the default by using the Row Height option under the Format menu or by simply dragging the row border with the mouse.

Setting the Row Height Using Menus

1. Select Workbook Defaults from the Options menu. XESS displays the Workbook Defaults dialog box.
2. Enter a number in the Default Row Height entry box.
3. Apply your entry by clicking OK or Apply.

To explicitly set the height of selected rows:

1. Select the rows(s) you wish to change.
2. Select Row Height from the Format menu or MB3 popup which displays its cascading submenus.
3. Select Row Height... from the submenus. XESS displays the Row Height dialog box.
4. In the Selection entry box, enter the desired row height.
5. Apply your entry by clicking OK or Apply.

To apply the default row height to selected rows:

1. Select the row(s) you wish to adjust.
2. Select Row Height from the Format menu or MB3 popup which displays its cascading submenus.
3. Select Default Row Height from the submenu.

Best Fit Row Height

To snap row height to best fit cell contents:

1. Select the row(s) you wish to adjust.
2. Select Row Height from the Format menu or MB3 popup which displays its cascading submenus.
3. Select Snap Height to Contents from the submenu.

Dragging the Row Border

Content Exceeds Row Height

If the adjacent cell is not empty, the displayed content will appear truncated. You may want to use the Snap-to option to force the row height to accommodate the tall text or multiple lines of text in the same cell.

Inserting Rows and Columns

To insert new, empty rows:

1. Move the cell cursor to the row above which you would like to insert an empty row. To insert more than one row, select a range containing the number of rows you wish to insert starting at that position.
2. Select Insert from the Edit menu or MB3 popup to display its cascading submenu.
3. Select Insert Row from the Insert submenu.

To insert new, empty columns:

1. Move the cursor one column to the right of where you want to insert an empty column. To insert more than one column, select a range containing the number of columns you wish to insert starting at that position.
2. Select Insert from the Edit menu or MB3 popup to display its cascading submenu.
3. Select Insert Column from the Insert submenu.

Inserting Cells by Row or by Column

To insert cells by row:

1. Select the range where the new cells will be inserted.
2. Click Insert Cells by Row on the Edit menu. A block of empty cells are inserted and previous values are moved down.

1. Select the range where the new cells will be inserted.
2. Click Insert Cells by Column on the Edit menu. A block of empty cells are inserted and previous values are moved right.

Deleting Rows and Columns

If you delete a row or column, the data in the deleted row or column is lost, but all dependencies in the sheet are preserved. The ranges which span the deleted row are contracted to prevent inclusion of unwanted cells.

To delete a row(s):

1. Select the row(s) to be deleted.
2. Select Delete from the Edit menu or MB3 popup to display its cascading submenu.
3. Select Delete Row from the Delete submenu.

To delete a column(s):

1. Select the column(s) to be deleted.
2. Select Delete from the Edit menu or MB3 popup to display its submenu.
3. Select Delete Column from the Delete submenu.

Deleting Cells by Row or by Column

To delete cells by row:

1. Select the range of cells to be deleted.
2. Click Delete Cells by Row on the Edit menu. The selected cells will be deleted and the cells below it will be moved up to fill the space.

1. Select the range of cells to be deleted.
2. Click Delete Cells by Column on the Edit menu. The selected cells will be deleted and the cells to the right of it will be moved left to fill the space.

Hiding Rows and Columns

To hide a range of columns using the Hide Columns operation:

1. Select the column or columns to be hidden.
2. Choose Column Width... from the Format menu. XESS displays the list of options.
3. Choose Hide Column.

To unhide a range of columns using the Unhide Columns operation:

1. Select a range of columns which includes the ones that are hidden.
2. Choose Column Width... from the Format menu. XESS displays the list of options.
3. Choose Unhide Column.

If you have hidden rows/columns at the edge of the sheet, position the cursor in the first row or column that is not hidden and then unhide. For example, the column A is hidden, place the cursor in column B and Unhide Column to restore A.

Rows and columns can also be hidden by dragging their button borders (described above).

Creating View Titles

XESS supports three variations of view titles: row headings, column heading, row and column headings. The text for view titles is entered before the view is protected with the Lock View Titles operation. After view titles are set, the cursor will no longer move into that area. When you lock a view title, XESS changes the applicable border color from to designate the locked area.

To make a change to data in a view title area, select Create New View from the View menu which displays a secondary view area without the view title locks. You can make the changes in the secondary window which are automatically reflected in the view with the title locks.

Column View Titles

To create column view titles:

1. Use your usual method (keyboard, mouse, or import operations) to enter text for column titles.
2. Move the cursor to the row immediately below the last row to be included in the Column View Title. For example, if column headings occupy Rows 1 through 4, move the cursor to Row 5.
3. Select Lock View Titles from the View menu.

All rows above the cell cursor are highlighted and protected as a title area.

Row View Titles

To create row view titles:

1. Use your usual method (keyboard, mouse, or import operations) to enter text for row headings.
2. Move the cursor to the column immediately to the right of the rightmost column to be included in the Row View Title. For example, if row headings occupy Columns A and B, move the cursor to Column C.
3. Select Lock View Titles from the View menu.

All columns to the left of the cell cursor are highlighted and protected as a title area.

Row and Column View Title

To create both title rows and title columns:

1. Use your usual method (keyboard, mouse, or import operations) to enter text for row and column headings.
2. Move the cursor to the row immediately below and to the column to the right of the desired title area.
3. Select Lock View Titles from the View menu.

All columns and rows above and to the left of the cell cursor are highlighted and protected as a title area. When you scroll down, the column view titles will remain onscreen; when you scroll right the row view titles will remain onscreen.

Unlocking View Titles

Tools

• Recalculate - forces recalculation of the workbook.
• Cell Protection - sets and clears cell protection.
• Transpose -swaps row and column orientation of cells
• Data Fill - extend a sequence of values over a range
• Goal Seek - varies the value of an independent variable in order to achieve a desired result in another variable.
• Solver - solve a multi-rule, multi-variable system
• Sort - sorts data in ascending or descending order.
• Extract - extracting data from internal databases.
• Overlay - editor for free-form drawing and illustration.

These are powerful features that give you control over the spreadsheet beyond the logic embedded in cells. For instance, you can create what-if scenarios, asking XESS what value is required to achieve a certain outcome. You can quickly identify patterns in research data, and sort the data by criteria you select. You can visualize your data in the manner most useful to you, then discard the new view or save it under a new name, preserving the original workbook intact.

Recalculate

Cell Protection

Transpose

             A      B      C
    1      One    Two    Three
    2        10     20     30

             A      B
    5      One      10
    6      Two      20
    7      Three    30

Data Fill Tool

To use Data Fill:

1. Enter the values for the first cells in range. (Enough cells to define the pattern)
2. Select the range to contain the sequence of values, including the cells containing the initial pattern.
3. Select Data Fill from the Tools menu (or Ctrl+d). The pattern of values will be extended to the entire selection.

Goal Seek

Using this method, XESS usually finds the desired value in two or three iterations for linear relationships, more for higher-order dependencies.

To perform Goal Seek:

1. Select Goal Seek from the Tools menu. XESS displays the Goal Seek dialog box.
2. Enter the following specifications in the entry boxes:

   Variable Cell

   The name or address of the cell value to change.

   Target Cell

   The name or address of the cell containing the target value.

   New Value

   The value to be reached.

3. Click Start to begin the search. XESS performs the calculation and displays the results on screen, with the Goal Seek dialog box still displayed. If necessary, you can move the Goal Seek dialog box to view the results of the goal seek operation.
4. Click Cancel to exit the Goal Seek dialog.

If the value of the target cell does not respond to changes in the variable cell, XESS stops goal seek, delivers an error message, and restores the sheet to its original values.

If the target value is not reached in ten (10) iterations, goal seek pauses and enables the Continue button allowing you to choose whether or not to continue with the goal seek operation.

Solver

Constraints are rules that must be met in order to have a valid solution to the problem. They restrict the posible values a cell may have and define the relationships among cells which must be maintained in the solution. Example constraints are:

         A3 > 20
         revenue > expenses

To use the Solver:

1. Select Solver... from the Tools menu. XESS displays the Solver dialog box.
2. Enter the following specifications in the entry boxes:

   Target Cell

   The name or address of the cell that is the target of the solution.

   Seek to:

   The goal to be reached - min, max, or specific value.

   Variable Cell(s)

   The names or addresses of the cells whose values will be changed when searching for a solution.

   Constraint(s):

   The current constraints for this problem. You can click on an existing constraint and then Delete it.

   Add Constraint:

   Enter a new constraint rule here and then click Add.

3. Click Start to start the solver. XESS performs the calculations and displays the intermediate results on the cells, with the Solver dialog box still displayed. You can modify your constraints and restart the Solver.
4. Click Cancel to exit the Solver dialog.