Copyright 1990 - 1998 Applied Information Systems, Inc.
Chapel Hill, NC, USA
All Rights Reserved
Xess is an advanced spreadsheet designed specifically for Linux personal computing. By using the intuitive Xess user interface, you can easily access its powerful computational and graphical tools.
The Xess spreadsheet provides a flexible and comfortable visual environment in which to handle complex calculations and data manipulations. 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.
Like those in other spreadsheets, cells in an Xess spreadsheet contain constant numeric values, text strings, and formulas that calculate new values. However, with its extensive range of features and functions designed for the advanced user, Xess defines a new generation of spreadsheets.
The entire spreadsheet environment can be customized to reflect specific user or site preferences. This includes short-cut accelerator keys and start-up defaults.
Recalculation options include:
When Foreground is set Xess enforces "minimal" recalculation, which means only the cells throughout the entire spreadsheet which are potentially affected by an operation which you perform (such as editing a cell, or moving a range of cell) are recalculated. This feature minimizes the work performed during recalculation and thus speeds up your computations. Minimal recalculation is in effect whenever the recalculation mode in the Recalc Options dialog is set to Automatic and the method is set to Foreground.
The performance gain in recalculation can be significant, depending on the size and complexity of the sheet. Frugal recalculation is in effect whenever the recalculation mode in the Recalc Options dialog is set to "Automatic" and method is set to "As Needed".
Note that As Needed does not currently support Constraint Checking and Iterative Recalculation.
In some cases cyclic dependencies are useful in that they can represent "iterative" calculations, which Xess supports. Iterative calculation in Xess 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 is set to a non-zero value, 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, whichever comes first.
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.
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 inter-dependencies among cells, and they can define constraints on the calculation, such as limits on acceptable resulting 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, position the cell cursor to the desired cell. Xess displays the formula in the Edit Line of the spreadsheet area, if one exists.
If the Edit Line is not being displayed, you will need to initiate cell editing by pressing [F2]. The cell formula will now be shown in the cell.
Xess also provides a wide array of functions. Functions are predefined formulas that perform certain tasks. Some functions are used alone; others are used within formulas. Xess provides many specialized functions spreadsheets for almost any application area.
For more information, see:
Cell Contents
Cell Defaults
Cell Formats
Cell References
Formulas
Functions
Embedded Tools
The following attributes are supported:
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:
+ - .
You can format numeric values to be displayed in several
ways, even as dates or times that are entered as numbers but
displayed in the specified date or time format.
To tell Xess to interpret the entry as a date or time and not a formula, you must change the cell format to one of the date/time formats before entering the contents of the cell, or use a date function.
You can use a shorthand method for entering dates. Type a slash (/) followed by any supported date format such as: /mm/dd/yy, /dd-mmm-yy, /dd.mmm.yy. The date or time entered with this method is converted to the equivalent date and time value.
Xess provides a rich set of built-in functions and formats for manipulating and displaying date and time values.
To set the Cell Defaults:
Note: The number of decimal places displayed does not affect the precision of Xess calculations, except for the @VSUM (visual sum) function.
8, 10, 12, 14, 18, 24, and user-defined ones
To set protection on a cell-by-cell basis, select "Cell Protection" under the Tools menu.
The color palette displayed in the Cell Colors Dialog can be changed with resources to have up to 63 colors.
Default Restore the cell format and decimal places
to the values selected on the Cell Defaults
options dialog.
Scientific in scientific notation (exponentiation)
Fixed using a fixed number of decimal places
General in the same format as the Fixed format. Data
values that are too long for the column are
displayed in Scientific format.
Dollars with a leading dollar sign ($), with comma
delimiters, and negative numbers in parentheses.
Currency with the format specified as a local currency by
the Locale Defaults Dialog.
Custom Currency with the currency format specified explicitly for
this cell by additional fields in the Cell Format
Dialog.
Comma like Fixed format, but with commas delimiting
the thousands, millions, etc.
Percent displays a value as a percentage with trailing
percent (%) sign.
MM/DD/YY as a date in the format 12/25/91
DD-MMM-YY as a date in the format 25-Dec-91
MMM-YY as a date in the format Dec-91
DD-MMM as a date in the format 25-Dec
YY-MM-DD as a date in the format 96-12-25
DD.MM.YY as a date in the format 25.12.96
HH:MM as a time in the format hours:minutes,
in a 24-hour clock starting at midnight.
HH:MM:SS as a time in the format hours:minutes:seconds,
in a 24-hour clock starting at midnight.
Fraction as an integer plus a fraction of 1, 2, or 3
digits, determined by the Decimal Places setting
Hex in hexadecimal format, displayed in base 16
with a leading 0x indicator
Logic as 0 or 1 for logic levels. Other values are
displayed as ? .
Hidden cell contents are (blank) not displayed.
Text as text when numeric values are entered. Also,
displays formula entries in the cells as text
as opposed to placing the resulting value of
the formula.
Note: The number of decimal places displayed does not affect the precision of Xess calculations, except for the @VSUM (visual sum) function.
The Xess spreadsheet display includes tabs for direct access to any sheet within the current workbook. You can insert/delete pages via menu operations. You can also select and name ranges of cells which drill through multiple pages. Range references in dialog boxes have been appropriately updated to accommodate the multi-page environment.
Sheet1, Sheet2,...Sheetn
The alternate form of sheet names is the same as column names:
A,B,C...FAM
The upper left cell in the first sheet is:
Sheet1.A1 or A.A1
If an explicit sheet name is not specified, the cell reference is
assumed to be on the current sheet.
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
defines a range of 7 cells, drilling through the sheets.
Sheet3.A1..A5 or C.A1..A5
defines a 5 cell range on Sheet3, which may not be the current
sheet. By varying the sheet name, the column, and the row you
can reference 3-dimensional ranges. For example:
Sheet1.A1..Sheet10.E10 or A.A1..J.E10
defines a cubic solid containing 500 cells (10 sheets * 5 columns *
10 rows).
You may use the indirect cell reference technique (#) in cases where the @XVALUE function is stored in the same relative cell location as the second argument in the function. For example, if the @XVALUE function is stored in cell A1 and you wish to return the value from cell A1 in a different sheet, then use the construct, @XVALUE("othersheet.xs4",#).
@XHLOOKUP and @XVLOOKUP function the same as @HLOOKUP and @VLOOKUP respectively with the additional feature that the lookup tables can reside in a different sheet.
To enter a note in a cell or to modify an existing note:
To delete a note from a cell:
To reference a cell by address: - Type the column and row coordinates of the cell in the formula. For example, to reference the cell at Row 5 in Column D, type D5, OR - 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.
To reference a contiguous range of cells by address: - Type the column and row coordinates of two cells in opposite corners of the range to be referenced, with two periods (..) between the coordinates. For example, to reference the first five columns of the first five rows of the spreadsheet, type A1..E5, OR - Move the mouse pointer to one corner of the 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 of cells by name: - Type the name assigned to the cell or range directly in the formula. Using mnemonic names instead of cell addresses generally improves the readability of the spreadsheet formulas. Cell and range names must be defined before they can be used.
Absolute Cell/Range References are interpreted by Xess as fixed, no matter where you move or copy the cell. 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 both fixed.
To specify an absolute cell address, insert a dollar sign ($) before the address coordinate to remain fixed. For example:
Use [F4] when entering or editing a formula to toggle the absolute/relative settings for a cell reference (e.g. A1, $A$1, $A1, A$1).
xslite4 [options] [file.xs4]
Options:
-rows r display "r" rows
-r r
-cols c display "c" columns
-c c
-size r c display "r" rows and "c" columns
-s r c
-position x y initialize upper left corner to "x","y"
-pos x y
-p x y
-xsname name name this instance of Xess
-xsn name
-nomenu exclude all menu choices except Help
from the main menu
-version display current version level and exit
-v
-class classname specify alternate class name/resource file
-help display help text and exit
-h
Whether or not constraint expressions are evaluated at recalculation is controlled in Recalc Options dialog under the Options menu.
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 || #==C7means, "the value of the current cell is the sum of cells A1 and A2, and that value must be either greater than 2 and less than or equal to the value of cell B5, or equal to the value of cell C7."
Constraint expressions are used in several other contexts within Xess, including the Search facility and Extract tool, and the conditional statistical functions. In these contexts, the symbol # always refers to the "current cell," the cell against which the constraint is being evaluated. In many cases, we may wish to include the values of a cell's neighbors in the constraint. For example, we could test whether a cell value was greater than its neighbor to the right by the following constraint expression:
# > @CELLREF(@COL(#)+1,@ROW(#))For convenience in referring to neighboring cells, Xess provides a shorthand way of referring to cells relative to the position of the current cell. The notation "#{1}" refers to the cell one column to the right of the current cell, so that "# > #{1}" is equivalent to the more verbose expression above. The notation "#{-2}" refers to the cell two columns to the left of the current cell. The notation "#{0,-1}" refers to the cell one row above the current cell, and #{1,1} refers to the cell one column to the right and one row down from the current cell.
You may also copy data from one sheet to another. In this scenario, you may copy both values and formulas - but not formulas as values. For efficiency consideration, Xess only copies attributes of cells which are not empty in accordance with options set in Copy Options Dialog.
Copy Formulas
Copies formulas from an active cell or cell range into
a designated cell or range, overwriting existing data
in the destination range. Xess automatically translates
relative cell references in the copied formulas to reflect
their new locations. For example if cell A10 contains the
formula =@SUM(A1..A9) and is copied to cell B10, then B10
will contain the formula =@SUM(B1..B9).
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 the value into the destination cell
or range as a constant.
Copy Options
In addition to values and/or formulas copied, any specific
cell options that may have been defined may also copied. For
example: color, font, justification, formats.
The Copy Options dialog under the Options menu determined which
of these characteristics are copied.
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. Any formulas in the spreadsheet which
refer to the cells that have been moved are automatically
updated to refer to the new location.
Clear
Removes all data in the selected cell or range of cells.
Use the following sequence to perform a copy, move, or clear operation within
the same sheet:
Note: If you accidently erase the wrong data, or accidently overwrite some cells with a copy or move, Xess allows you to easily recover from your mistake using Undo.
To copy data to another sheet being displayed by a separate copy of the Xess program:
XessLite is a rich subset of the Xess spreadsheet designed specifically to meet the needs of Linux personal computing. While XessLite worksheet files are upwardly compatible with the full Xess spreadsheet, XessLite features differ from Xess as follows:
The Xessu system is a full-function Xess spreadsheet "unit" which can be accessed only within host applications which have embedded the spreadsheet technology. This system is designed specifically for qualifying OEM partners who wish to add spreadsheet-based functionality to the host application.
While the embedded Xessu spreadsheet offers the same underlying functionality as the standard Xess spreadsheet, the following restrictions apply:
End users can license the standard Xess spreadsheet system from AIS or an authorized distributor and substitute it for Xessu, eliminating the restrictions.
Embedded Tools are a powerful feature in Xess. Their power comes from their ability to return data in a matrix, not just the resident cell. This function makes non-scalar operations such as matrix manipulation and "live" recalculation as easy to use as an ordinary spreadsheet function.
Embedded Tools are used to compute things like Fourier transforms, eigenvalues, line fits, and regression analysis.
More information on using Embedded Tools.
If the *smartTextEntry resource is set, entry into cells formatted with the Text attribute are treated differently. The need to prefix numeric values is removed so that 555-1212 could be entered directly.
//r-
This feature can be useful for drawing lines to demark areas of your
spreadsheet when printed. However, if you wish to enter a character
string that begins with a slash (/), you must be sure to prepend
the entry with one of the special text-indicator characters: ' " ^
To edit an entry that occupies more than one cell, double click the cell which actually contains the text. Xess will display the full entry on the edit line in the Edit Line. If you select the Wrap format option, Xess will automatically wrap the long text to fit within the bounds of the column width. When the column width changes the contents will automatically re-wrap accordingly.
To enter a negative number, type a minus sign (-) and the number. Do not use parentheses to indicate negatives. However, certain formats (e.g. Dollars or Comma) cause negative numbers to display with parentheses.
Do not use spaces or commas when entering numbers. If you wish, you can display commas by changing the format.
Be careful not to substitute a lower case L for the numeral 1 or the upper case O for the numeral 0.
You can use scientific notation to enter a number. If the calculated number fits within the cell size and format, Xess displays it in full. Otherwise, it displays the value in scientific notation or displays the special width indicator, depending on the cell format.
Whereas numeric entries remain the same until you change them, cells defined by formulas are automatically recalculated to reflect changes in referenced cells -- even where there are complex interdependencies among cells.
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, simply select the cell.
Xess displays the formula in the Input/Edit lines in the Edit Line. You can edit the formula or values in cells at any time. Xess also includes a "Text" display format which causes the formula itself, not the cell value, to be displayed on the sheet.
If the Edit Line is not being displayed, select Edit Cell or press [F2] to edit the value within the cell area itself.
Xess will not allow a syntactically incorrect expression to be entered.
@SUM(A1..A20)\D50
instructs Xess to recalculate @SUM(A1..A20) whenever the
contents of D50 change.
This feature is particularly important when you have a constraint expression containing a constant offset that produces a cell reference outside the cell range referenced in a dependent formula. Under these circumstances, Automatic Recalculation would not necessarily be triggered. For example,
@CCOUNT(C4..C100,# > #{0,-1}) counts all the cells in the range C4..C100 whose value is greater than the contents of the cell im- mediately 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 would indicate the dependency as follows:
@CCOUNT(C4..C100,# > #{0,-1})\C3..C99
which tells Xess to recalculate whenever any cell in the range
C3..C99 changes.
If you are exporting from an Xess4 or Xessu sheet for subsequent import into XessLite, be sure that your cell utilization does not exceed the bounds of XessLite .
CSV (Comma Separated Values) format uses commas to separate cell values with text strings in double quotes.
Text format does not use a delimiter but uses the column width to determine cell (field) width. This creates a tabular representation of the spreadsheet data. The Text format is also used when pasting with mouse button 2 into a non-Xess window.
Note also that if a cell element overflows into an adjacent cell, it is necessary to include the root cell into the range selected. You may also include HTML elements directly into spreadsheet cells. These elements will be passed through, as text, to the HTML file and ultimately processed by the browser.
To extract a set of records from a selected range:
For example, if you ask Xess to open a WKS-format file, the file filter appears as *.wks and the Files list box includes all files with names ending in *.wks.
This is especially useful when you have many files on disk and the list is cumbersome to search. For example, you can change the file filter to display:
To move "up" directory levels, position the cursor on the current directory name with the extension "/.." and double-click the left mouse button. The files under the new directory are listed inside the Files list box.
To scroll through the list of directory names, click the up and down arrows along the right side of the Directories list box or drag the scroll bar.
To view the full pathname of the current directory inside the Directories list box, first position the cursor on the horizontal scroll bar located below the Directories list box. Second, while holding down the left mouse button drag the mouse to the left. Alternatively, click on the small arrow located to the left of the scroll bar until the desired view is obtained.
Alternatively, first position the cursor on the desired filename inside the Files list box and click the left mouse button to select(highlight)the name. Next, click OK or press [Return] to load the file.
To quickly select and load a file into the spreadsheet, position the cursor on the desired filename inside the Files list box and double-click the left mouse button.
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 scientific data.
When searching by text pattern, a Unix-style regular expression is entered in the "Target" box. For example, a target pattern of "[cbh]at" will match any cell containing one or more of the words "cat," "bat," or "hat." A target pattern of "^C" will match any cell whose formatted text begins with an upper-case C.
When searching by numerical constraint, a valid Xess constraint expression must be entered in the "Target" box and the "Numeric Comparison" must be selected. As an example, to look for values in range named "data" which are more than three standard deviations from the mean, the constraint expression "@ABS(#) > 3*@STDS(data)" could be used as the target.
To Find the first occurrence of an entry in a range:
The following styles are supported for each font family:
=(expression) ; (constraint expression) //where expression defines the calculations needed to generate the cell's value, and constraint expression places limits on acceptable values or the circumstances under which the calculation should take place. Text followed by the string "//" is a comment.
Xess formulas look just like algebraic formulas; they contain values and operators that define the relationships between values, known in this context as operands because they are the entities that operators act on. Follow these guidelines for entering formulas:
0 1 2 3 4 5 6 7 8 9 . + - @ =
Xess Formulas can contain the following operators to define relationship between values.
Operator Precedence Definition
% 14 percent
** 13 Exponentiation
+ 12 Unary plus
- 12 Unary minus
~ 12 Bit-wise complement (integer)
! 12 Logical not
* 11 Multiplication
/ 11 Division
% 11 Modulus (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 Bit-wise And (integer) or String Concatenation
^ 5 Bit-wise Exclusive Or (integer)
| 4 Bit-wise Or (integer)
&& 3 Logical And
|| 2 Logical Or
?: 1 Conditional
In formulas with more than one operators, Xess calculates operators in
the order presented above. That is, AND/OR/NOT relationships are
considered before equal/unequal relationships, and subtraction/addition
is considered before multiplication/division.
To change the precedence of operators, enclose higher priority operations in parentheses.
Here are some special notes about Xess operators:
@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.
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 Functions perform calculations with numeric values as arguments, returning numeric values. Also see Embedded Tools.
Statistical Functions perform aggregation and counting operations on a group of values expressed as a list of arguments, returning numeric values.
Conditional Statistical Functions operate much like statistical functions, except that the last argument is a constraint expression that Xess evaluates for each cell in the argument list. Only cells that meet constraint criteria are included in the calculation. Conditional Statistical return a numeric value.
String Functions manipulate and evaluate character strings. For example, string functions can return the length of a string, find the first occurrence of a string in a range, change a string from upper to lower case and vice versa, or replace one string with another.
Logical Functions return one value if an argument meets certain criteria, another value if it does not. Logical functions are used as an adjunct to conditional statements, where the outcome is based on the validity of a statement.
Digital Logical Functions return the values 0, 1 or -1 (unknown) based on the value of its arguments. Digital logic functions evaluate the integer portion of a value. Any value not equal to 0 or 1 is considered unknown.
Financial Functions perform common financial calculations, such as calculating the future value of an annuity at a given interest rate, straight-line depreciation, and depreciation. Xess also includes is a set of financial trading functions which compute things like annuities, bonds, coupons, and Treasury Bills. These functions are not supported in XessLite.
Date and Time Functions return values corresponding to the specified date, month, year, hour, minute or second. They can also return the current system time and date.
Miscellaneous Functions perform a variety of calculations, such as returning a reference to specific Cells or ranges or returning the Nth argument from a list of arguments.
Embedded Tools are a powerful feature in Xess. Their power comes from their ability to return data in a matrix, not just the resident cell. This function makes non-scalar operations such as matrix manipulation and "live" recalculation as easy to use as an ordinary spreadsheet function.
Perform calculations with numeric values as arguments, returning numeric values. For matrix functions, curve fitting, Fourier Transforms, etc. see Embedded Tools.
Function Returns
@ABS(X) Returns the absolute value of X
X - a numeric value
@ACOS(X) Returns the arc cosine of X
X - a numeric value between -1 and 1
@ACOSH(X) Returns the hyperbolic arc cosine of X
X - a numeric value greater than or equal to 1
@ASIN(X) Returns the arc sine of X
X - a numeric value between -1 and 1
@ASINH(X) Returns the hyperbolic arc sine of X
X - a numeric value
@ATAN(X) Returns the 2-quadrant arc tangent of X
X - a numeric value
@ATANH(X) Returns the hyperbolic arc tangent of X
X - a numeric value greater than -1 and less
than 1.
@ATAN2(X,Y) Returns the 4-quadrant arc tangent of Y/X
X, Y - numeric values, one of them must be non-zero
@CEIL(X) Returns the smallest integer greater than or equal to X
X - a numeric value
@COS(X) Returns the cosine of X
X - a numeric value
@COSH(X) Returns the hyperbolic cosine of X
X - a numeric value
@DEGREES(X) Returns 180/pi times X, converting radians to degrees.
X - a numeric value
@DET(M) Returns the determinant of the matrix range M, which
must be a square matrix.
M - a range, which must contain an equal number of
rows and columns.
@DOT(R1,R2) Returns the dot product of the vectors R1 and R2
R1, R2 - each a range, which must contain an equal
number of rows and columns, or must be a dimension
1 x n (row vector) or n x 1 (column vector).
@EXP(X) Returns e raised to the X power
X - a numeric value representing exponentiation
@FACT(X) Returns X * (X-1) * ... * 1 (X factorial, or X!)
X - a numeric value
@FLOOR(X) Returns the largest integer less than or equal to X
X - a numeric value
@FRAC(X) Returns the fractional portion of X
X - a numeric value
@GAMMA(X) Returns the value of the gamma function evaluated at X
X - a numeric value which must be non-negative
@GRAND Returns a 12th-degree binomial approximation to a Gaussian
random number with zero mean and unit variance
@INT(X) Returns the integer portion of X
X - a numeric value
@LN(X) Returns the log base e of X
X - a numeric value greater than 0
@LNGAMMA(X) Returns the log base e of the gamma function evaluated at X
X - a numeric value greater than 0
@LOG(X) Returns the log of X
X - a numeric value greater than 0
@LOG10(X) Returns the log base 10 of X
X - a numeric value greater than 0
@LOG2(X) Returns the log base 2 of X
X - a numeric value greater than 0
@MOD(X,Y) Returns the remainder of X/Y, with the same sign as X.
This is NOT the modulus, see @MODULUS below for that.
X, Y - numeric values
@MODULUS(X,Y) Returns the modulus of X/Y.
X, Y - numeric values
@PI Returns a numeric approximation of PI.
@POLY(X,...) Returns the value of the Nth degree polynomial
in X, where N is the number of data in the argument
list. The argument list may contain numbers and
cell references, including ranges. Any non-numeric
argument causes an error.
@POLY(2,3,4,5) = (3*(2**2)+4*2+5) = 25
@PRODUCT(...) Multiplies all the numeric elements of the argument
list together, ignoring empty and non-numeric values,
and returns the product. Each argument can be a
number, a cell or a range.
@RADIANS(D) Returns PI/180 * D, converting degrees to radians.
D is a numeric value.
@RAND Returns a uniformly distributed random number on
the interval [0,1]. The number will change every
time the spreadsheet is recalculated.
@ROUND(X,N) Returns the number X rounded to the number of decimal
places specified by the integer N, for N between 15
and -15, inclusive.
@ROUND(@PI,2) = 3.14
@ROUND(1234.5678,-2) = 1200
@SIGMOID(X) Returns the value of the sigmoid function 1/(1 + exp(-X))
X - a numeric value
@SIN(X) Returns the sine of X
X - a numeric value
@SINH(X) Returns the hyperbolic sine of X
X - a numeric value
@SQRT(X) Returns the positive square root of X
X - a positive numeric value
@SUMPRODUCT(R1,R2)
Returns the dot product of the vectors R1 and R2
R1, R2 - each a range, which must contain an equal number of
rows and columns, or must be a dimension 1 x n (row vector) or
n x 1 (column vector).
@TAN(X) Returns the tangent of X
X - a numeric value
@TANH(X) Returns the hyperbolic tangent of X
X - a numeric value
@TRANSPOSE(M) Generates the transpose of matrix M.
@VECLEN(...) Returns the square root of the sum of squares of its arguments
argumentlist - any combination of numbers, cells or ranges
Perform aggregation and counting operations on a group of values expressed as a list of arguments, returning numeric values.
@AVG(...) Returns the average (mean) of its arguments
argumentlist - any combination of numbers, cells or ranges
@CORR(R1,R2) Returns Pearson's product-moment correlation coefficient for
the paired data in ranges R1 and R2
R1, R2 must have the same dimensions
@COUNT(...) A count of its non-blank arguments
argumentlist - any combination of numbers, cells or ranges
@F(M,N,F) Returns the integral of Snedecor's F-distribution with M and N
degrees of freedom from minus infinity to F
M,N,F - numeric values
@ERF(L[,U]) Returns the error function integrated between L (lower
limit) and U (upper limit). If U is omitted, @ERF
integrates between 0 and L.
L, R - a non-negative numeric value
@ERFC(L) Returns the comlementary error function integrated
between L and infinity. @ERFC(L)=1-@ERF(L).
L - a non-negative numeric value
@FORECAST(X,R1,R2)
Returns a predicted Y value for X based on a linear
regression of R1 and R2 where they represent the
known x values and y values respectively.
X - a numeric value
R1, R2 - Ranges of numeric values. R1 and R2 must be
1 dimentional ranges with same sizes.
@FORECAST(20, A1..A5, B1..B5)=13.60349
where A1..A5={3,10,18,20} and B1..B5={8,10,13,11}
@FREQUENCY(R, B)
Returns a frequency distribution for a set of values R
with a set of intervals B.
R - a range of values on which frequencies will be counted
B - a range of intervals used to group values in R
@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(R1,R2) Returns the significance level (alpha) of the two-sided
F-test on the variances of the data specified by
ranges R1 and R2
@GMEAN(...) Returns the geometric mean of its arguments
argumentlist - any combination of numbers, cells,
or ranges.
@HMEAN(...) Returns the harmonic mean of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@LARGE(R,N) Returns the Nth largest datum in range R.
R - Range of data
N - Integer specifying which datum to choose.
@LARGE(A1..A10,3) = 45
where A1..A10={38,3,9,45,7,90,17,2,75,12}
@MAX(...) Returns the maximum of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@MEDIAN(...) Returns the median (middle value) of the range R1.
@MEDIAN(A1..A6) = 3.5
where A1..A6 = {1,2,3,4,5,6}
@MIN(...) Returns the minimum of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@MODE(...) Returns the mode, or most frequently occurring datum,
of all the arguments. Empty cells and cells containing
text are ignored.
@MODE(2, 5, 12, 5) = 5;
@MSQ(...) Returns the mean of the squares of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@PERCENTILE(R,N)
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.
R - A range of data
N - A numeric value between 0 and 1, inclusive.
@PERCENTILE(A1..A4, 0.4) = 14.2
where A1..A4 = {23, 4, 12, 67}
@PERCENTRANK(R,D[,S])
Returns the percentile rank of the datum D in range R.
If D does not match one of the values in R, the
function interpolates to return the correct percentage rank.
R - Range of numeric data
D - Datum to find
S = Number of significant digits
@PERCENTRANK(A1..A10, 4) = 0.33
where A1..A10 = {1,2,3,4,5,6,7,8,9,10}.
@PERMUT(S,T) Returns "S choose T", or the number of T objects that
can be chosen from the set S, where order is significant.
S - Number of objects to choose from
T - Number of objects to be chosen
@PERMUT(100, 3) = 970200
@PTTEST(R1,R2) Returns the significance level (alpha) of the two-sided T-test
for the paired samples contained in ranges R1 and R2
R1 and R2 must have the same size.
@QUARTILE(R,Q) Finds the quartile Q of the data in range R.
Equivalent to @PERCENTILE(R, Q/4)
R = Range of cells
Q = Quartile as follows:
0 Minimum value
1 First quartile (25th percentile)
2 Second quartile (50th percentile)
3 Third quartile (75th percentile)
4 Maximum value
@QUARTILE(A1..B4, 2) = 7.5
where A1..B4 = {1,2,4,7,8,9,10,12}
@RANK(E,R,[O]) Returns the rank of a numeric argument E in the
range R. The rank of a number is its size relative to
other values in the list, which is equivalent to it's
position in the list after the list is sorted. RANK
gives duplicate numbers the same rank. The presence of
duplicate numbers will affect the ranks of subsequent
numbers. For examples, if there are two rank of 3,
the next rank will be 5.
E - a numeric value whose rank you want to find
R - a reference to a range of values. Non-numeric
values will be ignored.
O - a numeric value specifying the way the numbers to
be ranked. RANK ranks E as if R is in descending order
if O is 0 or omitted. Otherwise, it ranks E as if
R is in ascending order.
@RANK(5, C2..C6) = 2
@RANK(5, C2..C6, 1) = 3
@RANK(7, C2..C6, 1) = 5
where C2..C6 = {2,7,5,1,5}
@RMS(...) Returns the square root of the mean of squares of its
arguments, which may be any combination of numbers, cells,
and ranges. Blank cells and cells containing strings
are not counted.
@SMALL(R,N) Returns the Nth smallest number in range R.
@SMALL(A1..A10,3) = 7
where A1..A10={38,3,9,45,7,90,17,2,75,12}
@SSE(...) Returns the sum squared error of its arguments. This function
equivalent to @VAR(...)/@COUNT(...)
argumentlist - any combination of numbers, cells, or ranges
@SSQ(...) Returns the sum of squares of its arguments
argumentlist - any combination of numbers, cells, or ranges
@STD(...) Returns the population standard deviation (N weighting) of its
arguments
argumentlist - any combination of numbers, cells, or ranges
@STDS(...) Returns the population standard deviation (N-1 weighting) of
its arguments
argumentlist - any combination of numbers, cells, or ranges
@SUM(...) Returns the sum of its arguments
argumentlist - any combination of numbers, cells, or ranges
@T(N,T) Returns the integral of Student's T-distribution with
N degrees of freedom from minus infinity to T.
N, T - numeric values
@TTEST(R,X) Returns the significance level (alpha) of the two-sided single
population T-test for the population samples contained
in range R
R - a range
X - a numeric value
@TTEST2EV(R1,R2)
Returns the significance level (alpha) of the two-sided dual
population T-test for ranges R1 and R2, where their
variances are equal
@TTEST2UV(R1,R2)
Returns the significance level (alpha) of the two-sided single
population T-test for ranges R1 and R2, where their
variances are not equal
@VAR(...) Returns the population variance (N weighting) of its arguments
argumentlist - any combination of numbers, cells, or ranges
@VARS(...) Returns the population variance (N-1 weighting) of its
arguments argumentlist - any combination of numbers,
cells, or ranges
@VSUM(...) Returns the "visual sum" of its arguments, using precision and
rounding of formatted cell values
argumentlist - any combination of numbers, cells, or ranges
Operate much like statistical functions, except that the last argument is a constraint expression that Xess evaluates for each cell in the argument list. Only cells that meet constraint criteria are included in the calculation. Conditional Statistical return a numeric value.
@CAVG(...,C) Returns the conditional average (mean) of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CCOUNT(...,C) Returns a conditional count of its non-blank arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CMAX(...,C) Returns the conditional maximum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CMIN(...,C) Returns the conditional minimum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSTD(...,C) Returns the conditional standard deviation (N weighting) of
its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSTDS(...,C) Returns the conditional sample deviation (N-1 weighting)
of its arguments.
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSUM(...,C) Returns the conditional sum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CVAR(...,C) Returns the conditional population variance (N weighting)
of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CVARS(...,C) Returns the conditional sample variance (N-1 weighting)
of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
Manipulate and evaluate character strings. For example, string functions can return the length of a string, find the first occurrence of a string in a range, change a string from upper to lower case and vice versa, or replace one string with another.
@CHAR(N) Returns the character represented by N in the ASCII
character set.
N - an integer value between 1 and 255.
@CLEAN(S) Returns a "printable" representation of string S by
stripping out unprintable characters.
@CODE(S) Returns the ASCII code for the first character in string S.
S - a string value
@EXACT(S1,S2) Returns 1 if S1 exactly matches string S2; otherwise, 0
S1, S2 - string values
@FIND(S1,S2,N) Returns the index of the first occurrence of S1 in S2
S1, S2 - String values
N - a numeric value
@FORMAT(F,N,X) Returns the string formed by formatting the value X using
the Xess format code F and precision N
F - Xess format code
N - the precision level, from 0 to 15
X - a numeric value
@HEXTONUM(S) Returns the numeric value for the hexadecimal interpretation
of S.
S - a string value
@LEFT(S,N) Returns the string composed of the leftmost N characters of S
If N is greater than or equal to the length of S, then the
entire string S is returned
S - a string value
N - a numeric value
@LENGTH(S) Returns the number of characters in S
S - a string value
@LOWER(S) The string S is converted to lower case
S - a string value
@MID(S,N1,N2) Returns the string of length N2 that starts at position N1 in S.
S - a string value
@NUMTOHEX(X) Returns the hexadecimal representation of the integer
portion of X, for -2147483648 <= X <= 2147483647.
X - a numeric value
@PROPER(S) Returns a copy of string S with the first letter of
every word capitalized.
@REGEX(R,S) Returns True(1) if the strings R and S match, and
and False (0) otherwise. This function is a regular
expression pattern matcher and is similar to @EXACT(S1,S2)
function, except @REGEX allows "wildcard" comparisons.
R - A string representing a regular expression
S - A string
@REGEX("t.*e", "table") = True
@REGEX("F[0-9]", "F3") = True
@REPEAT(S,N) Returns a string containing N copies of string S.
@REPLACE(S1,N1,N2,S2)
Returns the string formed by replacing the N2
characters starting at position N1 in string
S1 with string S2.
@REPLACE("Expense Report",8,3,"Log") = "Expense Logort"
@REPLACE("Expense Report",8,6,"Log") = "Expense Log"
@REPLACE("ABC",8,3,"XYZ") = "XYZABC"
@RIGHT(S,N) 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.
@STRCAT(...) Returns the concatenation of all its arguments.
argumentlist - any combination of numbers, cells, or ranges
@STRING(X,N) Returns the string representing the numeric value of X,
to N decimal places
X - a numeric value
N - a numeric value between 0 and 15
@STRLEN(...) Returns the total length of all strings in its arguments
argumentlist - any combination of numbers, cells, or ranges
@TRIM(S) Returns the string formed by removing leading, trailing,
and consecutive embedded spaces from string S.
@UPPER(S) Returns the string S converted to upper case.
S - a string value
@VALUE(S) Returns the numeric value represented by the string S
or 0 if S does not represent a number.
S - a string value
Return one value if an argument meets certain criteria, another value if it does not. Logical functions are used as an adjunct to conditional statements, where the outcome is based on the validity of a statement.
@FALSE Returns the logical value 0
@FILEEXISTS(S) Returns 1 if file S can be opened to read; otherwise 0
S - a string value
@IF(X,T,F) Returns the value of T if X evaluates to non-zero,
or F if X evaluates to zero
X - numeric value
T,F - numeric or string values, cell or range references
@ISERROR(X) Returns 1 if X "contains" an error, otherwise 0.
X is an expression, which may include references to
other cells. X "contains" an error if the expression
causes an error, or if it refers to any cells which
have errors.
@ISERROR(3/0) = 1
@ISERROR(A1) = 1 where A1 contains an error value
@ISERROR(@ISERROR(3/0)) = 0
@ISNUMBER(X) Returns 1 if X is a numeric value; otherwise 0
X - a numeric or a string value
@ISSTRING(X) Returns 1 if X is a string value; otherwise 0
X - a numeric or a string value
@TRUE Returns the logical value 1 (true).
Return the values 0, 1 or -1 (unknown) based on the value of its arguments. Digital logic functions evaluate the integer portion of a value. Any value not equal to 0 or 1 is considered unknown.
@AND(...) Returns 1 if all arguments are 1; 0 if any arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges
@NAND(...) 0 if all arguments are 1; 1 if any arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges
@NOR(...) 0 if any arguments are 1; 1 if all arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges
NOT(X) Returns 1 if X=0; 0 if X=1; otherwise -1
argumentlist - any combination of numbers, cells or ranges
X - a numeric value
@OR(...) Logical OR function. Returns 1 if any argument in
the list equals 1, 0 if not, and -1 if there is any
argument in the list not equal to 0 or 1.
@XOR(...) Returns -1 if any arguments are UNKNOWN;
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.
argumentlist - any combination of numbers, cells or ranges
Perform common financial calculations, such as calculating the future value of an annuity at a given interest rate, straight-line depreciation, depreciation, or the payment term for a given investment. Xess also includes is a set of financial trading functions which compute things like annuities, bonds, coupons, and Treasury Bills. These functions are not supported in XessLite.
@DDB(C,S,L,N) Returns the double-declining depreciation allowance
C - cost
S - salvage value
L - allowable value
N - number of depreciation periods
@FV(P,R,N) Returns the future value of an annuity
P - periodic payment
R - interest rate
N - number of periods
@IRR(G,F) Returns the internal rate of return on an investment
Related functions: @XIRR and @MIRR
G - a numeric value (an initial "guess" G)
F - cash flow, represented by a range. It must contain
at least two cash flow values
@NPV(R,CF) Returns the present value of a series of future cash flows at
a given rate. Related functions: @MNPV and @XNPV.
R - periodic interest rate
CF - future cash flow series (must be a range)
@PMT(P,R,N) Returns the periodic payment for a loan, given the
present value P and interest rate R, and the number
of periods N.
P,R,N - Numeric values
@PV(P,R,N) Returns the present value of an annuity given
periodic payment P, interest rate R, and N
periods.
@RATE(F,P,N) Returns the interest rate required to go from present
value P to future value F in N compounding periods.
@SLN(C,S,L) Returns the straight-line depreciation allowance
given cost C, salvage value S, and allowable life L.
@SYD(C,S,L,N) Returns the "sum-of-years-digits" depreciation allowance
given cost C, salvage value S, allowable life L, and
N depreciation periods.
@TERM(P,R,FV) Returns the number of payment periods for an investment
P - amount of periodic payments
R - interest rate
FV - future value of the investment
Return values corresponding to the specified date, month, year, hour, minute or second. They can also return the current system time and date.
@DATE(Y,M,D) returns the date value for year Y, month M, and date D.
Y - year
M - month
D - day
@DATEVALUE(S) Returns the corresponding date value for a given string S.
S - string value of a date.
Examples:
@DATEVALUE("September-91")
@DATEVALUE("3/24/54")
@DAY(DT) Returns the day number in the date/time value DT.
DT - a date/time value
@DAYS360(S,E) Returns the number of days between two dates, based
on a 30/360 day count system. A 30/360 day count
system assumes 30-day months, except the following
case: if E is on the 31st day of the month, and S is
neither on the 30th or 31st day of the month, E
remains to be the 31st day of the month.
S - start date
E - end date
@DAYS360(@DATEVALUE("5/30/93"), @DATEVALUE("6/1/93")) = 1
@DAYS360(@DATEVALUE("1/1/92"), @DATEVALUE("7/1/93")) = 180
@DAYS360(@DATEVALUE("7/1/93"), @DATEVALUE("7/31/93) = 30
@EDATE(S,M) 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
same day of the month as S.
S - Start date, a date/time value
M - Months, an integer
@EDATE(@DATEVELUE("3/2/92"), 2) = 33726 or 05/02/92
@EDATE(@DATEVALUE("3/31/91"), -6) = 33146 or 09/30/90
@EDATE(@DATEVALUE("3/31/91"), -1) = 33297 or 02/28/91
@EOMONTH(S,M) 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.
S - Start date, a date/time value
M - Months, an integer
@EDATE(@DATEVALUE("3/2/92"), 2) = 34120 or 05/31/93
@EDATE(@DATEVALUE("3/15/93"), -13) = 33663 or 02/29/93
@HOUR(DT) Returns the hour value (0-23) of date/time value DT.
DT - a date/time value
@MINUTE(DT) Returns the minute value (0-59) of date/time value DT.
DT - a date/time value
@MONTH(DT) Returns the number of the month in date/time value DT.
DT - a date/time value
@NETWORKDAYS(S,E[,H])
Returns the number of whole working days, starting
at S and going to E, excluding weekends and holidays
in H. If a holiday happens to be on weekend, only one
day will be excluded.
S - Starting date, a date/time value
E - Ending date, a date/time value
H - A range of dates to exclude, such as holidays.
@NETWORKDAYS(@DATEVALUE("1/1/93"),@DATEVALUE("12/31/93"),
A38..A40) = 258
where A38..A40={01/01/93, 05/25/93, 07/04/93}
@NOW Returns the date/time value of the current system date
and time.
@SECOND(T) Returns the seconds (0 - 59) component of the
date/time value T.
@TIME(H,M,S) Returns the time value represented as a fraction of a day,
starting at midnight. H (hours) must be between 0 and 23,
M (minutes) and S (seconds) must be between 0 and 59.
@TIMEVALUE(S) Returns the corresponding time value for a given string
value S.
Example:
@TIMEVALUE("12:55:00 AM")
@TIMEVALUE("0:55.00")
@TODAY Returns the date value corresponding to the current
system date.
@WEEKDAY(D) Returns an integer representing the day of the week
on which the day D falls. 1 is Sunday, 7 is Saturday.
@WORKDAY(S,D[,H]) Returns the day that is D working days after S, if D
is positive, before S, if D is negtive, excluding
weekends and all holidays specified as dates in range
H. If a holiday happens to be on weekend, only one
day will be excluded.
S - Starting date, a date/time value
D - The number of non-weekend and non-holiday days
before and after S.
H - A range of dates to exclude, such as holidays.
@NETWORKDAYS(@DATEVALUE("1/1/93"),@DATEVALUE("12/31/93"),
A38..A40) = 258
where A38..A40={01/01/93, 05/25/93, 07/04/93}
@YEAR(DT) Returns the year value of date/time value DT.
DT - a date/time value
@YEARFRAC(S,E[,B])
Returns the portion of the year represented by
the number of days between start date (s) and
end date (E).
S - start date value
E - end date value
B = the day count basis to be used:
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
Perform a variety of calculations, such as returning a reference to specific Cells or ranges or returning the Nth argument from a list of arguments.
@@(R) Reference to the cell or range R
@ALARM(X,S) If X evaluates non-zero, then the string S is evaluated
as an expression, and the terminal beeps. The return value
is set to the result of evaluating the S expression. If
X evaluates to zero, S is not evaluated and the return
value is zero. See also @SALARM function below.
e.g. @ALARM(pressure > 500, "@REMOTE_FN(Pressure)")
@ANNOTATE(S,X,Y[,H[,V]])
Generates a string suitable for use as an Xess graph
annotation, in which case it will cause the string S
to be displayed at position (X,Y) within the graph. The
optional parameters H and V specify how the string S
will be justified with respect to the point (X,Y).
H and V represnet horizontal and vertical justication values.
(H,V) -> X,Y Placement
(0,0) bottom left corner of S (the default).
(0,1) top left corner of S.
(1,0) bottom right corner of S.
(1,1) top right corner of S.
S - a string value
X,Y - graph coordinates
H,V - optional text justification parameters
Example:
@ANNOTATE("Average",5/2,12,1,0.5) = "(2.5,12,1,0.5)Average"
@CELLREF(C,R) Returns the value contained in cell C,R.
C - Column, an integer value
R - Row, an integer value
@CHOOSE(N,...) Returns the value at the Nth place in the argument
list.
N - an integer.
@CHOOSE(2,"mary","sue","beth") ==> "sue"
@COL(C) Returns the column associated with cell C, or the
left-most column if C is a range. Returns the column
of the current cell if C is omitted.
@COLS(R) Returns the number of columns in range R.
@HLOOKUP(X,R,N) Searches the first row (known as the index row)
in range R for the numeric or string value which "matches"
X, and returs the value N rows below in the column
where the match is found. The index row is searched from
left to right. 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 column is determined by the following rules:
1) Strings and blank 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.
See also @XHLOOKUP.
@INIT(X1,X2) Returns its first argument on the first recalculation pass
and its second argument on all subsequent recalculation
passes when Xess is performing iterative calculations.
X1 - initial value
X2 - iterative or subsequent value
@INTERP2D(R1,R2,N)
Returns interporlation value for a 2 dimensional
vector.
R1 - a range reference points to x values of the vector;
R2 - a range reference points to y values of the
vector. XR and YR have to be same size.
N - a numerical value or range for which the
interporlation value will be based on.
@INTERP3D(R,X,Y)
Returns interporlation value for a 3 dimensional
vector. X and Y ranges must be the same size.
R - a range reference points to x, y and z values of
the vector;
X, Y - numerical values or ranges for which the
interporlation value will be based on.
@MATCH(V,R[,T]) Returns the relative position in range R of
value V in a specific way.
V - the value to be matched. V can be a string, a
numeric value. If V is a string, T must be 0. MATCH
allows regular expression type "wildcard".
T - match type
T > 0 means find largest value in R <= V
T = 0 or omitted means find first value in R = V
T < 0 means find smallest value in R >= V
@MESSAGE(X,S) If X evaluates non-zero, the string S is displayed on
the status line and the terminal beeps. The return
value of the function is set to the value of X.
e.g. @MESSAGE(@HOUR(@NOW) < 17, "Time to go home!")
Tells you that it is time to go home after 5:00 PM.
@N(R) Returns the numeric value of the top left cell in range R
R - a range
@RANGEREF(UC,UR,LC,LR)
Returns a reference to the range described by
the corner co-ordinates UC, UR, LC, LR.
UC = Upper left column of the range
UR = Upper left row of the range
LC = Lower right column of the range
LR = Lower right row of the range
@ROW(C) Returns the row index of the cell referenced by C,
the top row of C if C is a range, or the current
row if C is omitted.
@ROWS(R) Returns the number of rows in the specified range R.
@S(R) Returns the string value of the top left cell in range R.
Returns a null string if the cell is blank or contains a
numeric value.
R - a range
@SALARM(X,S) If X evaluates non-zero, then the string S is evaluated
as an expression. The return value is set to the result
of evaluating the S expression. If X evaluates to zero,
S is not evaluated and the return value is zero. Does
not beep like the @ALARM function above.
e.g. @SALARM(pressure > 500, "@REMOTE_FN(Pressure)")
@VLOOKUP(X,R,N) Searches the first column (known as the index column)
in range R for the numeric or string value which "matches"
X, and returs 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 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 blank 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.
4) If there are no numeric values in the index column which are
greater than or equal to X, the last row in R is considered
to be the matching row.
See also @XVLOOKUP.
@XHLOOKUP(F,X,R,N)
Searches the first row (known as the index row) in range R
in file F for the numeric or string value which "matches"
X, and returs the value N rows below in the column
where the match is found. The index row is searched from
left to right. 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 column is determined by the following rules:
1) Strings and blank 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.
@XVALUE(N,C) Returns the value of cell C in sheet N.
N - a string containing the pathname of another
spreadsheet.
C - a valid cell reference or a string containing a cell
reference; you can specify a cell reference from the other
sheet as well by using a cell name enclosed in quotes.
@XVLOOKUP(F,X,R,N) Searches the first column (known as the index column)
in range R in spreadsheet file F 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 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 blank 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.
4) If there are no numeric values in the index column which are
greater than or equal to X, the last row in R is considered
to be the matching row.
From the user's point of view, embedded tools are very much like normal spreadsheet functions,such as "@SUM," except that they return a range of values instead of a single value. Embedded tools are entered into cells and recalculated in the same manner as other spreadsheet functions, except that they should not be part of a more complex formula; i.e., it is not acceptable to enter the formula "@MMUL(@TRANSPOSE(matrix1),matrix2)" to multiply the transpose of matrix1 by matrix2.
The cell in which the embedded tool is entered will serve as the upper left corner of the range of values which the tool returns. The values returned by the embedded tool are "tagged" with a reference to the cell containing the tool. The tag is a backslash followed by the address of the cell address containing the embedded tool. This tag not only helps you remember how the value got there, but is used by Xess to determine the natural recalculation order.
Except for the fact that they are specially tagged, the values returned by the embedded tool are just like constant values that were typed in or read in from a file. Therefore, if you erase the cell containing the embedded tool, the tagged values will not disappear, they simply become constant values that can be erased, moved, or copied just like any other constant values in the spreadsheet.
Remember, DO NOT combine embedded tools with other functions or arithmetic operations in a single formula.
@DFT(R)
The Discrete Fourier Transform of the 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 always a complex vector.
R - a range representing the vector to be transformed.
@EIGEN(M)
Generates the eigenvalues of the matrix M, which must be square
and symmetric.
M - a matrix which must be square and symmetric.
@FFT(R) Generates the Discrete Fourier Transform of the range R
using a Fast Fourier Transform algorithm. The length of
the vector must be a power of 2. @FFT will produce the
same results as @DFT, but is much faster (N*log(N)) times
as opposed to N**2 times.
R - a range representing the vector to be transformed.
@INVDFT(R)
Generates the inverse of the Discrete Fourier Transform of the
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 always
a complex vector.
R - a range representing the vector to be inverse transformed.
@INVERT(M)
Generates the inverse of matrix M, which must be square.
M - a square matrix.
@INVFFT(R)
Generates the inverse of the Discrete Fourier Transform of the
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 is much faster (N*log(N)) time
as opposed to N**2 time.
R - a range representing the vector to be inverse transformed.
@LINCOEF(X,Y)
Generates the straight line least squares fit.
X - a range representing a column vector of independent variable
values.
Y - a range representing a column vector of dependent variable
values
@LINFIT(X,Y)
returns the straight line least squares fit.
X - a column vector of values for the independent variable.
Y - a column vector of values for the dependent variable.
This function is equivalent to @POLYFIT(X,Y,1).
@LLS(X,Y) (for all version prior to Xess 3.1.2b)
The linear least squares solution X to the overdetermined
system of equations AX=Y.
X - a range representing the coefficient matrix which
must intercept the origin (rightmost column of X must
be all 1's)
Y - a column vector of values for the dependent variable
@LLS(X,Y,flag) (Xess 3.1.2b and later)
The linear least squares solution X to the overdetermined
system of equations AX=Y.
X - a range representing the coefficient matrix
Y - a column vector of values for the dependent variable
flag = 1 (X data intercepts origins)
0 (X data does not intercept the origin)
@MMUL(M1,M2)
Generates the product of multiplying matrix M2 by matrix M1.
M1 and M2 can be multiplied only if number of columns in
M1 is equal to the number of rows in M2.
M1, M2 - matrices.
@PLS(X,Y,d) (for all version prior to Xess 3.1.2b)
analyzes the least squares polynomial model. The output is
identical to that of @LLS, with the polynomial coefficients
listed in the order of decreasing degree.
X - a range representing a row or column vector of independent
variable values.
Y - a range representing a row or column vector of dependent
variable values.
d - polynomial degree in range 1 - 10
@PLS(X,Y,d,[,flag]) (Xess 3.1.2b and later)
analyzes the least squares polynomial model. The output is
identical to that of @LLS, with the polynomial coefficients
listed in the order of decreasing degree.
X - a range representing a row or column vector of independent
variable values.
Y - a range representing a row or column vector of dependent
variable values.
d - polynomial degree in range 1 - 10
flag - omitted/1 X data intercepts the origin
0 X data does not intercept the origin
@POLYCOEF(X,Y,d)
Returns the least squares coefficients for the polynomial fit.
X - a range representing a row or column vector of independent
variable values.
Y - a range representing a row or column vector of dependent
variable values.
@POLYFIT(X,Y,d)
Returns the least squares polynomial fit.
X - a column vector of values for the independent variable.
Y - a column vector of values for the dependent variable.
d - the polynomial degree in the range of 1 to 10.
@TRANSPOSE(M)
The transpose of matrix M.
M - a matrix.
@TREND(NX, KX, KY)
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)
NX - new x values for which TREND will return the y-values
KX - known x values
KY - known y values
Function Returns
@@(R) Reference to the cell or range R
@ABS(X) Returns the absolute value of X
X - a numeric value
@ACOS(X) Returns the arc cosine of X
X - a numeric value between -1 and 1
@ACOSH(X) Returns the hyperbolic arc cosine of X
X - a numeric value greater than or equal to 1
@ALARM(X,S) If X evaluates non-zero, then the string S is evaluated
as an expression, and the terminal beeps. The return value
is set to the result of evaluating the S expression. If
X evaluates to zero, S is not evaluated and the return
value is zero. See also @SALARM function below.
e.g. @ALARM(pressure > 500, "@REMOTE_FN(Pressure)")
@AND(...) Returns 1 if all arguments are 1; 0 if any arguments are 0;
-1 if any arguments are neither 0 nor 1.
argument list - any combination of numbers, cells or ranges
@ANNOTATE(S,X,Y[,H[,V]])
Generates a string suitable for use as an Xess graph
annotation, in which case it will cause the string S
to be displayed at position (X,Y) within the graph. The
optional parameters H and V specify how the string S
will be justified with respect to the point (X,Y).
H and V represnet horizontal and vertical justication values.
(H,V) -> X,Y Placement
(0,0) bottom left corner of S (the default).
(0,1) top left corner of S.
(1,0) bottom right corner of S.
(1,1) top right corner of S.
S - a string value
X,Y - graph coordinates
H,V - optional text justification parameters
Example:
@ANNOTATE("Average",5/2,12,1,0.5) = "(2.5,12,1,0.5)Average"
@ASIN(X) Returns the arc sine of X
X - a numeric value between -1 and 1
@ASINH(X) Returns the hyperbolic arc sine of X
X - a numeric value
@ATAN(X) Returns the 2-quadrant arc tangent of X
X - a numeric value
@ATANH(X) Returns the hyperbolic arc tangent of X
X - a numeric value greater than -1 and less
than 1.
@ATAN2(X,Y) Returns the 4-quadrant arc tangent of Y/X
X, Y - numeric values, one of them must be non-zero
@AVG(...) Returns the average (mean) of its arguments
argumentlist - any combination of numbers, cells or ranges
@CAVG(...,C) Returns the conditional average (mean) of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CCOUNT(...,C) Returns a conditional count of its non-blank arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CEIL(X) Returns the smallest integer greater than or equal to X
X - a numeric value
@CELLREF(C,R) Returns the value contained in cell C,R.
C - Column, an integer value
R - Row, an integer value
@CHAR(N) Returns the character represented by N in the ASCII
character set.
N - an integer value between 1 and 255.
@CHOOSE(N,...) Returns the value at the Nth place in the argument
list.
N - an integer.
@CHOOSE(2,"mary","sue","beth") ==> "sue"
@CLEAN(S) Returns a "printable" representation of string S by
stripping out unprintable characters.
@CMAX(...,C) Returns the conditional maximum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CMIN(...,C) Returns the conditional minimum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CODE(S) Returns the ASCII code for the first character in string S.
S - a string value
@COL(C) Returns the column associated with cell C, or the
left-most column if C is a range. Returns the column
of the current cell if C is omitted.
@COLS(R) Returns the number of columns in range R.
@CORR(R1,R2) Returns Pearson's product-moment correlation coefficient for
the paired data in ranges R1 and R2
R1, R2 must have the same dimensions
@COS(X) Returns the cosine of X
X - a numeric value
@COSH(X) Returns the hyperbolic cosine of X
X - a numeric value
@COUNT(...) A count of its non-blank arguments
argumentlist - any combination of numbers, cells or ranges
@CCOUNT(...,C) Returns a conditional count of its non-blank arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSTD(...,C) Returns the conditional standard deviation (N weighting) of
its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSTDS(...,C) Returns the conditional sample deviation (N-1 weighting)
of its arguments.
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSUM(...,C) Returns the conditional sum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CTERM(R,FV,PV) Returns the number of compounding periods for an investment
R - Rate of return ( e.g. .07 for 7 %)
FV - future value of the investment
PV - present value of the investment
@CVAR(...,C) Returns the conditional population variance (N weighting)
of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CVARS(...,C) Returns the conditional sample variance (N-1 weighting)
of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@DATE(Y,M,D) returns the date value for year Y, month M, and date D.
Y - year
M - month
D - day
@DATEVALUE(S) Returns the corresponding date value for a given string S.
S - string value of a date.
Examples:
@DATEVALUE("September-91")
@DATEVALUE("3/24/54")
@DAY(DT) Returns the day number in the date/time value DT.
DT - a date/time value
@DAYS360(S,E) Returns the number of days between two dates, based
on a 30-month/360-day year.
S,E - date/time values
@DAYS360(@DATEVALUE("16-NOV-93"),@DATEVALUE("17-NOV-93"))
@DDB(C,S,L,N) Returns the double-declining depreciation allowance
C - cost
S - salvage value
L - allowable value
N - number of depreciation periods
@DEGREES(X) Returns 180/pi times X, converting radians to degrees.
X - a numeric value
@DET(M) Returns the determinant of the matrix range M, which
must be a square matrix.
M - a range, which must contain an equal number of
rows and columns.
@DFT(R) The Discrete Fourier Transform of the 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 always a
complex vector.
R - a range representing the vector to be transformed.
@DOT(R1,R2) Returns the dot product of the vectors R1 and R2
R1, R2 - each a range, which must contain an equal
number of rows and columns, or must be a dimension
1 x n (row vector) or n x 1 (column vector).
@EDATE(S,M) 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
same day of the month as S.
S - Start date, a date/time value
M - Months, an integer
@EIGEN(M) Generates the eigenvalues of the matrix M, which must be square
and symmetric.
M - a matrix which must be square and symmetric.
@EOMONTH(S,M) 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.
S - Start date, a date/time value
M - Months, an integer
@ERF(L[,U]) Returns the error function integrated between L (lower
limit) and U (upper limit). If U is omitted, @ERF
integrates between 0 and L.
L, R - a non-negative numeric value
@ERFC(L) Returns the comlementary error function integrated
between L and infinity. @ERFC(L)=1-@ERF(L).
L - a non-negative numeric value
@EXACT(S1,S2) Returns 1 if S1 exactly matches string S2; otherwise, 0
S1, S2 - string values
@EXP(X) Returns e raised to the X power
X - a numeric value representing exponentiation
@F(M,N,F) Returns the integral of Snedecor's F-distribution with M and N
degrees of freedom from minus infinity to F
M,N,F - numeric values
@FACT(X) Returns X * (X-1) * ... * 1 (X factorial, or X!)
X is a numeric value.
@FALSE Returns the logical value 0
@FFT(R) Generates the Discrete Fourier Transform of the range R
using a Fast Fourier Transform algorithm. The length of
the vector must be a power of 2. @FFT will produce the
same results as @DFT, but is much faster (N*log(N)) times
as opposed to N**2 times.
R - a range representing the vector to be transformed.
@FIND(S1,S2,N) Returns the index of the first occurrence of S1 in S2
S1, S2 - String values
N - a numeric value
@FILEEXISTS(S) Returns 1 if file S can be opened to read; otherwise 0
S - a string value
@FIND(I,T,N) Returns the index of the first occurrence of string
I in string T, starting at search position N in
string T. Note that the index of a string starts
with 0.
@FIND("bc","abcdefg",0)=1
@FLOOR(X) Returns the largest integer less than or equal to X
X - a numeric value
@FORECAST(X,R1,R2)
Returns a predicted Y value for X when R1 and R2 are
known x values and y values respectively
@FORMAT(F,N,X) Returns the string formed by formatting the value X using
the Xess format code F and precision N
F - Xess format code
N - the precision level, from 0 to 15
X - a numeric value
@FRAC(X) Returns the fractional portion of X
X - a numeric value
@FREQUENCY(R, B)
Returns a frequency distribution for a set of values R
with a set of intervals B.
R - a range of values on which frequencies will be counted
B - a range of intervals used to group values in R
@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(R1,R2) Returns the significance level (alpha) of the two-sided
F-test on the variances of the data specified by
ranges R1 and R2
@FV(P,R,N) Returns the future value of an annuity
P - periodic payment
R - interest rate
N - number of periods
@GAMMA(X) Returns the value of the gamma function evaluated at X
X - a numeric value which must be non-negative
@GMEAN(...) Returns the geometric mean of its arguments
argumentlist -- any combination of numbers, cells,
or ranges.
@GRAND Returns a 12th-degree binomial approximation to a Gaussian
random number with zero mean and unit variance
@HEXTONUM(S) Returns the numeric value for the hexadecimal
interpretation of S.
S - a string value
@HLOOKUP(X,R,N) Searches the first row (known as the index row)
in range R for the numeric or string value which "matches"
X, and returs the value N rows below in the column
where the match is found. The index row is searched from
left to right. 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 column is determined by the following rules:
1) Strings and blank 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.
@HMEAN(...) Returns the harmonic mean of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@HOUR(DT) Returns the hour value (0-23) of date/time value DT.
DT - a date/time value
@IF(X,T,F) Returns the value of T if X evaluates to non-zero,
or F if X evaluates to zero
X - numeric value
T,F - numeric or string values, cell or range references
@INIT(X1,X2) Returns its first argument on the first recalculation pass
and its second argument on all subsequent recalculation
passes when Xess is performing iterative calculations.
X1 - initial value
X2 - iterative or subsequent value
@INT(X) Returns the integer portion of X
X - a numeric value
@INTERP2D(R1,R2,N)
Returns interporlation value for a 2 dimensional
vector.
R1 - a range reference points to x values of the vector;
R2 - a range reference points to y values of the
vector. XR and YR have to be same size.
N - a numerical value or range for which the
interporlation value will be based on.
@INTERP3D(R,X,Y)
Returns interporlation value for a 3 dimensional
vector. X and Y ranges must be the same size.
R - a range reference points to x, y and z values of
the vector;
X, Y - numerical values or ranges for which the
interporlation value will be based on.
@INVDFT(R) Generates the inverse of the Discrete Fourier Transform
of the 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 always a complex vector.
R - a range representing the vector to be inverse transformed.
@INVERT(M) Generates the inverse of matrix M, which must be square.
M - a square matrix.
@INVFFT(R) Generates the inverse of the Discrete Fourier
Transform of the 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 is much faster
(N*log(N)) time as opposed to N**2 time.
R - a range representing the vector to be inverse
transformed.
@IRR(G,F) Returns the internal rate of return on an investment
Related functions: @MIRR and @XIRR
G - a numeric value (an initial "guess" G)
F - cash flow, represented by a range. It must contain
at least two cash flow values
@ISERROR(X) Returns 1 if X "contains" an error, otherwise 0.
X is an expression, which may include references to
other cells. X "contains" an error if the expression
causes an error, or if it refers to any cells which
have errors.
@ISNUMBER(X) Returns 1 if X is a numeric value; otherwise 0
X - a numeric or a string value
@ISSTRING(X) Returns 1 if X is a string value; otherwise 0
X - a numeric or a string value
@LARGE(R,N) Returns the Nth largest datum in range R.
R - Range of data
N - Integer specifying which datum to choose.
@LARGE(A1..A10,3) = 7
where A1..A10={38,3,9,45,7,90,17,2,75,12}
@LEFT(S,N) Returns the string composed of the leftmost N characters of S
If N is greater than or equal to the length of S, then the
entire string S is returned
S - a string value
N - a numeric value
@LENGTH(S) Returns the number of characters in S
S - a string value
@LINCOEF(X,Y) Generates the straight line least squares fit.
X - a range representing a column vector of independent
variable values
Y - a range representing a column vector of dependent variable
variable values
@LINFIT(X,Y) Returns the straight line least squares fit.
X - a column vector of values for the independent variable.
Y - a column vector of values for the dependent variable.
This function is equivalent to @POLYFIT(X,Y,1).
@LLS(X,Y) (for all version prior to Xess 3.1.2b)
The linear least squares solution X to the overdetermined
system of equations AX=Y.
X - a range representing the coefficient matrix which
must intercept the origin (rightmost column of X must
be all 1's)
Y - a column vector of values for the dependent variable
@LLS(X,Y,flag) (Xess 3.1.2b and later)
The linear least squares solution X to the overdetermined
system of equations AX=Y.
X - a range representing the coefficient matrix
Y - a column vector of values for the dependent variable
flag = 1 (X data intercepts origins)
0 (X data does not intercept the origin)
@LN(X) Returns the log base e of X
X - a numeric value greater than 0
@LNGAMMA(X) Returns the log base e of the gamma function evaluated at X
X - a numeric value greater than 0
@LOG(X) Returns the log of X
X - a numeric value greater than 0
@LOG10(X) Returns the log base 10 of X
X - a numeric value greater than 0
@LOG2(X) Returns the log base 2 of X
X - a numeric value greater than 0
@LOWER(S) The string S is converted to lower case
S - a string value
@MATCH(V,R[,T]) Returns the relative position in range R of
value V in a specific way.
V - the value to be matched. V can be a string, a
numeric value. If V is a string, T must be 0. MATCH
allows regular expression type "wildcard".
T - match type
T > 0 means find largest value in R <= V
T = 0 or omitted means find first value in R = V
T < 0 means find smallest value in R >= V
@MAX(...) Returns the maximum of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@MEDIAN(...) Returns the median (middle value) of the range R1.
@MESSAGE(X,S) If X evaluates non-zero, the string S is displayed on
the status line and the terminal beeps. The return
value of the function is set to the value of X.
e.g. @MESSAGE(@HOUR(@NOW) > 17, "Time to go home!")
Tells you that it is time to go home after 5:00 PM.
@MID(S,N1,N2) Returns the string of length N2 that starts at position N1 in S.
S - a string value
@MIN(...) Returns the minimum of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@MINUTE(DT) Returns the minute value (0-59) of date/time value DT.
DT - a date/time value
@MMUL(M1,M2) Generates the product of multiplying matrix M2 by matrix M1.
M1 and M2 can be multiplied only if number of columns in
M1 is equal to the number of rows in M2.
M1, M2 - matrices.
@MOD(X,Y) Returns the remainder of X/Y, with the same sign as X.
This is NOT the modulus, see @MODULUS below for that.
X, Y - numeric values
@MODE(...) Returns the mode, or most frequently occurring datum,
of all the arguments. Empty cells and cells containing
text are ignored.
@MODULUS(X,Y) Returns the modulus of X/Y, which is always positive.
X, Y - numeric values
@MONTH(DT) Returns the number of the month in date/time value DT.
DT - a date/time value
@MSQ(...) Returns the mean of the squares of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@N(R) Returns the numeric value of the top left cell in range R
R - a range
@NAND(...) 0 if all arguments are 1; 1 if any arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges
@NETWORKDAYS(S,E[,H])
Returns the number of whole working days, starting
at S and going to E, excluding days in H.
S - Starting date, a date/time value
E - Ending date, a date/time value
H - A range of dates to exclude, such as holidays.
@NOR(...) 0 if any arguments are 1; 1 if all arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges
NOT(X) Returns 1 if X=0; 0 if X=1; otherwise -1
argumentlist - any combination of numbers, cells or ranges
X - a numeric value
@NOW Returns the date/time value of the current system date
and time.
@NPV(R,CF) Returns the present value of a series of future cash
flows at a given rate. Related functions: @MNPV, @XNPV.
R - periodic interest rate
CF- future cash flow series (must be a range)
@NUMTOHEX(X) Returns the hexadecimal representation of the integer
portion of X, for -2147483648 <= X <= 2147483647.
X - a numeric value
@OR(...) Logical OR function. Returns 1 if any argument in
the list equals 1, 0 if not, and -1 if there is any
argument in the list not equal to 0 or 1.
@PERCENTILE(R,N)
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.
R - A range of data
N - A numeric value between 0 and 1, inclusive.
@PERCENTRANK(R,D[,S])
Returns the percentile rank of the datum D in range R,
to significance S.
R - Range of numeric data
D - Datum to find
S = Number of significant digits
@PERMUT(S,T) Returns "S choose T", or the number of T objects that
can be chosen from the set S, where order is significant.
S - Number of objects to choose from
T - Number of objects to be chosen
@PI Returns a numeric approximation of PI.
@PLS(X,Y,d) (for all version prior to Xess 3.1.2b)
analyzes the least squares polynomial model. The output is
identical to that of @LLS, with the polynomial coefficients
listed in the order of decreasing degree.
X - a range representing a row or column vector of independent
variable values which must intercept the origin
Y - a range representing a row or column vector of dependent
variable values.
d - polynomial degree in range 1 - 10
@PLS(X,Y,d,[,flag]) (Xess 3.1.2b and later)
analyzes the least squares polynomial model. The output is
identical to that of @LLS, with the polynomial coefficients
listed in the order of decreasing degree.
X - a range representing a row or column vector of independent
variable values.
Y - a range representing a row or column vector of dependent
variable values.
d - polynomial degree in range 1 - 10
flag - omitted/1 X data intercepts the origin
0 X data does not intercept the origin
@PMT(P,R,N) Returns the periodic payment for a loan, given the
present value P and interest rate R, and the number
of periods N.
P,R,N - Numeric values
@POLY(X,...) Returns the value of the Nth degree polynomial
in X, where N is the number of data in the argument
list. The argument list may contain numbers and
cell references, including ranges. Any non-numeric
argument causes an error.
@POLY(2,3,4,5) = (3*(2**2)+4*2+5) = 25
@POLYCOEF(X,Y,d)
Returns the least squares coefficients for the polynomial fit.
X - a range representing a row or column vector of independent
variable values.
Y - a range representing a row or column vector of dependent
variable values.
d - polynomial degree in the range 1 to 10.
@POLYFIT(X,Y,d) Returns the least squares polynomial fit.
X - a column vector of values for the independent variable.
Y - a column vector of values for the dependent variable.
d - the polynomial degree in the range of 1 to 10.
@PRODUCT(...) Multiplies all the numeric elements of the argument
list together, ignoring empty and non-numeric values,
and returns the product.
@PROPER(S) Returns a copy of string S with the first letter of
every word capitalized.
@PTTEST(R1,R2) Returns the significance level (alpha) of the two-sided T-test
for the paired samples contained in ranges R1 and R2
R1 and R2 must have the same size.
@PV(P,R,N) Returns the present value of an annuity given
periodic payment P, interest rate R, and N
periods.
@QUARTILE(R,Q) Finds the quartile Q of the data in range R.
Equivalent to @PERCENTILE(R, Q/4)
R = Range of cells
Q = Quartile as follows:
0 Minimum value
1 First quartile (25th percentile)
2 Second quartile (50th percentile)
3 Third quartile (75th percentile)
4 Maximum value
@QUARTILE(A1..B4, 2) = 7.5
where A1..B4 = {1,2,4,7,8,9,10,12}
@RADIANS(D) Returns PI/180 * D, converting degrees to radians.
D is a numeric value.
@RAND Returns a uniformly distributed random number on
the interval [0,1]. The number will change every
time the spreadsheet is recalculated.
@RANGEREF(UC,UR,LC,LR)
Returns a reference to the range described by
the corner co-ordinates UC, UR, LC, LR.
UC = Upper left column of the range
UR = Upper left row of the range
LC = Lower right column of the range
LR = Lower right row of the range
@RANK(E,R[,O]) Returns the rank of a numeric argument E in the
range R. The rank of a number is its size relative to
other values in the list, which is equivalent to it's
position in the list after the list is sorted. RANK
gives duplicate numbers the same rank. The presence of
duplicate numbers will affect the ranks of subsequent
numbers. For examples, if there are two rank of 3,
the next rank will be 5.
E - a numeric value whose rank you want to find
R - a reference to a range of values. Non-numeric
values will be ignored.
O - a numeric value specifying the way the numbers to
be ranked. RANK ranks E as if R is in descending order
if O is 0 or omitted. Otherwise, it ranks E as if
R is in ascending order.
@RANK(5, C2..C6) = 2
@RANK(5, C2..C6, 1) = 3
@RANK(7, C2..C6, 1) = 5
where C2..C6 = {2,7,5,1,5}
@RATE(F,P,N) Returns the interest rate required to go from present
value P to future value F in N compounding periods.
@REGEX(R,S) Returns True(1) if the strings R and S match, and
and False (0) otherwise. This function is a regular
expression pattern matcher and is similar to @EXACT(S1,S2)
function, except @REGEX allows "wildcard" comparisons.
R - A string representing a regular expression
S - A string
@REGEX("t.*e", "table") = True
@REGEX("F[0-9]", "F3") = True
@REPEAT(S,N) Returns a string containing N copies of string S.
@REPLACE(S1,N1,N2,S2)
Returns the string formed by replacing the N2
characters starting at position N1 in string
S1 with string S2.
@REPLACE("Expense Report",8,3,"Log") = "Expense Logort"
@REPLACE("Expense Report",8,6,"Log") = "Expense Log"
@REPLACE("ABC",8,3,"XYZ") = "XYZABC"
@RIGHT(S,N) 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.
@RMS(...) Returns the square root of the mean of squares of its
arguments, which may be any combination of numbers, cells,
and ranges. Blank cells and cells containing strings
are not counted.
@ROUND(X,N) Returns the number X rounded to the number of decimal
places specified by the integer N, for N between 15
and -15, inclusive.
@ROUND(@PI,2) = 3.14
@ROUND(1234.5678,-2) = 1200
@ROW(C) Returns the row index of the cell referenced by C,
the top row of C if C is a range, or the current
row if C is omitted.
@ROWS(R) Returns the number of rows in the specified range R.
@S(R) Returns the string value of the top left cell in range R.
Returns a null string if the cell is blank or contains a
numeric value.
R - a range
@SALARM(X,S) If X evaluates non-zero, then the string S is evaluated
as an expression. The return value is set to the result
of evaluating the S expression. If X evaluates to zero,
S is not evaluated and the return value is zero. Does
not beep like the @ALARM function above.
e.g. @SALARM(pressure > 500, "@REMOTE_FN(Pressure)")
@SECOND(T) Returns the seconds (0 - 59) component of the
date/time value T.
@SIGMOID(X) Returns the value of the sigmoid function 1/(1 + exp(-X))
X - a numeric value
@SIN(X) Returns the sine of X
X - a numeric value
@SINH(X) Returns the hyperbolic sine of X
X - a numeric value
@SLN(C,S,L) Returns the straight-line depreciation allowance
given cost C, salvage value S, and allowable life L.
@SMALL(R,N) Returns the Nth smallest number in range R.
@SQRT(X) Returns the positive square root of X
X - a positive numeric value
@SSE(...) Returns the sum squared error of its arguments. This function
equivalent to @VAR(...)/@COUNT(...)
argumentlist - any combination of numbers, cells, or ranges
@SSQ(...) Returns the sum of squares of its arguments
argumentlist - any combination of numbers, cells, or ranges
@STD(...) Returns the population standard deviation (N weighting) of its
arguments
argumentlist - any combination of numbers, cells, or ranges
@STDS(...) Returns the population standard deviation (N-1 weighting) of
its arguments
argumentlist - any combination of numbers, cells, or ranges
@STRCAT(...) Returns the concatenation of all its arguments.
argumentlist - any combination of numbers, cells, or ranges
@STRING(X,N) Returns the string representing the numeric value of X,
to N decimal places
X - a numeric value
N - a numeric value between 0 and 15
@STRLEN(...) Returns the total length of all strings in its arguments
argumentlist - any combination of numbers, cells, or ranges
@SUM(...) Returns the sum of its arguments
argumentlist - any combination of numbers, cells, or ranges
@SUMPRODUCT(R1,R2)
Returns the dot product of the vectors R1 and R2
R1, R2 - each a range, which must contain an equal number of
rows and columns, or must be a dimension 1 x n (row vector) or
n x 1 (column vector).
@SYD(C,S,L,N) Returns the "sum-of-years-digits" depreciation allowance
given cost C, salvage value S, allowable life L, and
N depreciation periods.
@T(N,T) Returns the integral of Student's T-distribution with
N degrees of freedom from minus infinity to T.
N, T - numeric values
@TAN(X) Returns the tangent of X
X - a numeric value
@TANH(X) Returns the hyperbolic tangent of X
X - a numeric value
@TERM(P,R,FV) Returns the number of payment periods for an investment
P - amount of periodic payments
R - interest rate
FV - future value of the investment
@TIME(H,M,S) Returns the time value represented as a fraction of a day,
starting at midnight. H (hours) must be between 0 and 23,
M (minutes) and S (seconds) must be between 0 and 59.
@TIMEVALUE(S) Returns the corresponding time value for a given string
value S.
Example:
@TIMEVALUE("12:55:00 AM")
@TIMEVALUE("0:55.00")
@TODAY Returns the date value corresponding to the current
system date.
@TRANSPOSE(M) Generates the transpose of matrix M.
@TREND(NX, KX, KY)
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)
NX - new x values for which TREND will return the y-values
KX - known x values
KY - known y values
@TRIM(S) Returns the string formed by removing leading, trailing,
and consecutive embedded spaces from string S.
@TRUE Returns the logical value 1 (true).
@TTEST(R,X) Returns the significance level (alpha) of the two-sided single
population T-test for the population samples contained
in range R
R - a range
X - a numeric value
@TTEST2EV(R1,R2)
Returns the significance level (alpha) of the two-sided dual
population T-test for ranges R1 and R2, where their
variances are equal
@TTEST2UV(R1,R2)
Returns the significance level (alpha) of the two-sided single
population T-test for ranges R1 and R2, where their
variances are not equal
@UPPER(S) Returns the string S converted to upper case.
S - a string value
@VALUE(S) Returns the numeric value represented by the string S
or 0 if S does not represent a number.
S - a string value
@VAR(...) Returns the population variance (N weighting) of its arguments
argumentlist - any combination of numbers, cells, or ranges
@VARS(...) Returns the population variance (N-1 weighting) of its arguments
argumentlist - any combination of numbers, cells, or ranges
@VECLEN(...) Returns the square root of the sum of squares of its arguments
argumentlist - any combination of numbers, cells or ranges
@VLOOKUP(X,R,N) Searches the first column (known as the index column)
in range R for the numeric or string value which "matches"
X, and returs 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 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 blank 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.
4) If there are no numeric values in the index column which are
greater than or equal to X, the last row in R is considered
to be the matching row.
@VSUM(...) Returns the "visual sum" of its arguments, using precision and
rounding of formatted cell values
argumentlist - any combination of numbers, cells, or ranges
@WEEKDAY(D) Returns an integer representing the day of the week
on which the day D falls. 1 is Sunday, 7 is Saturday.
@WORKDAY(S,D[,H]) Returns the day that is D working days after day S,
excluding weekends and all holidays specified as dates
in range H.
@XOR(...) Returns -1 if any arguments are UNKNOWN;
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.
argumentlist - any combination of numbers, cells or ranges
@XVALUE(N,C) Returns the value of cell C in sheet N.
N - a string containing the pathname of another
spreadsheet.
C - a valid cell reference or a string containing a cell
reference; you can specify a cell reference from the other
sheet as well by using a cell name enclosed in quotes.
@XHLOOKUP(F,X,R,N)
Searches the first row (known as the index row) in range R
in file F for the numeric or string value which "matches"
X, and returs the value N rows below in the column
where the match is found. The index row is searched from
left to right. 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 column is determined by the following rules:
1) Strings and blank 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.
@XVLOOKUP(F,X,R,N) Searches the first column (known as the index column)
in range R in spreadsheet file F 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 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 blank 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.
4) If there are no numeric values in the index column which are
greater than or equal to X, the last row in R is considered
to be the matching row.
@YEAR(DT) Returns the four-digit year value of a date/time value DT.
DT - a date/time value
@YEARFRAC(S,E[,B])
Returns the year fraction representing the number of
whole days between S (start date) and E (end date)
S - a date value that represents the start date
E - a date value that represents the end date
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
To perform goal-seeking:
Enter the following information in the dialog box:
Xess changes the view so that the requested cell appears in the view.
You can create and save up to hundred (100) different named graphs for a given spreadsheet. You can define up to twenty (20) different graph datasets for each graph. You can view up to twenty (20) graphs (each) at the same time in both the graph edit window and view graph window on screen while you continue to work on the spreadsheet, or save graphs as Encapsulated Postscript files to be printed later on a Postscript printer or included in other documents.
Function data is plotted by connecting the points with line or spline segments. On a color display, each line appears in a different color and they can be modified in the Data Set Options dialog box.
You can also specify what type line connects the points (e.g. dotted, dashed), width, and color.
The data points to be graphed do not need to be spaced evenly on the X-axis. In fact, for scatter plots they do not even need to be in order. Line and spline segments will connect adjacent points in the value lists. A break in a line or spline segment is indicated by leaving an X-Y pair of data blank. This is usually done by inserting a blank line or column at the appropriate location.
The data for an X-axis or Y-axis can be either string or numeric. If the data is string, the string values become the labels for successive locations on the axis. A typical use is to show monthly values of some type in a trend plot.
The type of line segment or No Segment determines whether the data is outline and, if so, with what type line.
A maximum of twenty sets of data may be combined on a single bar graph.
The points associated with each set of accumulated data are grouped by their X-axis label to permit easy comparison between groups. Each set of data (or bar segment) is represent by a different color or pattern.
The type of line segment or No Segment determines whether the data is outline and, if so, with what type line.
A maximum of twenty sets of data may be combined on a single bar graph.
Slices are "removed" from the pie by placing a blank pair of cells before and after the affected cells.
If multiple Y Ranges are specified, then multiple pies are drawn within the single graph window area.
Xess provides 3D rotation of surface graphs.
or
you may first select the data before proceeding with step 3 as follows:
Select a range of data to be graphed by using the mouse to highlight a range of data. For selecting remote ranges, use the Select... option from the Edit menu.
If you select only one data set, those values are assumed to be Y-values whose X-values are automatically numbered beginning with 1. If you select more that one data set (X and Y data), the first row or column in the range is assumed to be X data. If there are more columns that rows, then the X data is defined to be the first row. Otherwise X data is defined to be the first column.
or
For a quick line or bar graph, simply click the corresponding graph icon button on the toolbar.
To modify characteristics about the graph data, select Data Sets from the Edit menu.
The above actions may not necessarily create the desired graph type since the graph range selected may be incorrect or insufficient to create such a a graph. An appropriate error message is displayed on the Edit Line of the main window. This requires that you may have to modify the data ranges using the Graph Editorto obtain the desired results.
You may customize the following options:
To customize the X, Y, or Z axes:
Click Auto Scale ON if you want Xess to automatically define axis scaling, based on its evaluation of the data to be plotted.
As a default, Xess automatically defines axis scaling. Click this button OFF to designate a scale in the Max, Min entry box. Enter the starting and the ending range for the axis values.
Click Auto Scale button OFF to designate a scale in the Max, Min entry box. Enter the starting and the ending range for the axis values.
Note for date/time axes: the unformatted numeric value must be entered - not a formatted date/time representation.
You can select automatic tics placement or enter the desired ticks.
As a default, Xess places tick marks on the axis. Click this button OFF and define the number of major and minor tick in designated entry box as described below.
You have the option to specify the number of ticks for each axis.
To enter the desired number of ticks inside the Major and Minor entry boxes, first click OFF the check button titled Auto Ticks. This disables automatic placement of ticks. Next, position the cursor inside the Major entry box and click the left mouse button to activate the text insertion cursor. At the cursor position enter the desired number of ticks to be placed on the axis. Repeat this for Minor Ticks if desired.
You can select logarithmic scaling for the given axis.
To enable logarithmic scaling, click the Logarithmic Scale button ON.
You have the option of placing the ticks inside or outside the axis line.
As a default, the ticks are placed outside the axis line. Click the Tics Out button OFF to enable inward ticks.
This option allows you to separate the axis (with ticks) from the graph area.
Click the Exploded Axis button ON to enable the separation.
This option allows you to specify the color for the axis.
Click on the option box to display the color selection. Holding down the left mouse button, point to the desired color and release.
This option allows you to place grid lines on the graph for the given axis. The grid lines are drawn perpendicular to the axis.
Click the Grid Lines button ON to enable the grid option.
This option allows you to specify the color for the grid, if displayed.
Click on the option box to display the color selection. Holding down the left mouse button, point to the desired color and release.
Xess centers the axis label text along its axis outside the graph area.
To activate the text insertion cursor inside the Axis Label entry box, first position the cursor inside the entry box and click the left mouse button. At the cursor position enter the label text. The label is centered by default.
You may format the text using the following delimiters:
^ Center with respect to the previous line
{ Begins a block of text
} Ends a block of text
- Next character or text block to be subscripted
^ Next character or text block to be superscripted
\ Turns off special properties of the preceding characters so
they can be printed out
\n\
Forces new line
To select the appropriate font for the axis label text, click on the option button to display the font selection. Holding down the left mouse button point to the desired font and release.
To select the appropriate font style for the axis label text, click on the option button to display the style selection. Holding down the left mouse button point to the desired style and release.
You can select the size to be 8, 10, 12, 14, 18, 24 for the axis label text.
To select the appropriate size, click on the option button to display the size selection. Holding down the left mouse button point to the desired size and release.
To select the appropriate color for the axis label, click on the option button to display the color selection. Holding down the left mouse button point to the desired color and release.
To select the appropriate font for the axis, click on the option button to display the font selection. Holding down the left mouse button point to the desired font and release.
To select the appropriate font style for the tick mark, click on the option button to display the style selection. Holding down the left mouse button point to the desired style and release.
You can select the size to be 8, 10, 12, 14, 18, 24 for the tick mark.
To select the appropriate size, click on the option button to display the size selection. Holding down the left mouse button point to the desired size and release.
To select the appropriate color, click on the option button to display the color selection. Holding down the left mouse button point to the desired color and release.
The Data Set Options dialog box lets you alter the following:
The X Data, Y Data, and Z Data areas display the X, Y and Z ranges of the selected graph range. The selected graph range may contain more than one data set. You may define up to twenty (20) separate data sets for each axis.
These additional data points are used in conjunction with X and Y data. On Hi-Lo graphs they represent the high and low stock values for corresponding close (Y) data.
On error bars, these values represent confidence intervals.
These data points are used for Box graphs and specify the low and high values for the bottoms and tops of boxes which are drawn around each current data point.
The Lo Var entry is also used to specify the Auxiliary Y values in a Control graph.
The Median entry is used for Box graphs and specifies the median values which are marked in the Box graph. They will be represented on the graph by the same marker type used for the current value except that the marker type will not be filled to distinguish it.
Annotations allow you to specify a coordinate at which a text label will be displayed in the data area of the graph, and how the label is positioned with respect to the associated data coordinate.
The cell(s) you specify for annotation must include the @annotate function.
This option determines whether or not data points should be connected, and if so, in what manner. The choices are:
Line Data points are connected with straight lines. It also
draws straight lines around bars of Bar Charts and Histograms.
In Surface graphs Line segment superimposes a grid on a plotted
surface.
Spline Data points are connected using cubic splines, thereby plotting
a flowing connection through the data points.
Bar Data points are displayed as a bar graph.
If you wish to show the distribution of a set of data points, click ON the Histogram check button. The range of values specified for X Data is divided into sub-ranges called buckets. The count of the number of items within each sub-range is displayed. You indicate the number of buckets by setting the number of tick marks for the X axis. You can also set the range of data using Max and Min values in the X-Axis Options dialog box.
If you wish to apply shading to bars, surface, and contour plots, click on the Shading option button. While holding down the left mouse button, select one of the following and release:
Directed Light The surface is shaded as though lit from a light
source positioned at X = 0, Y = 1 and Z = 1.
The location of the light source can be changed
in the dialog box field provided.
Z Height The surface is shaded based on the value of the Z-Axis
data, where minimum values are purple and maximum
values are red.
Select 'None' to disable shading.
These options determine how lines are drawn on the graph wherever used to construct line or bar graphs. Click on the corresponding option button to display the options. While holding down the left mouse button, select the desired choice and release.
Style No Line, Solid, Dotted, Dashed, Long Dashed, Dot-Dashed
Width Thin, Medium, Thick
Color color palette defined to Xess
Markers are used with Line, Hi-Lo, and Polar graphs. Xess ignores this setting when dealing with other types of graphs. Click on the Marker option menu to display the menu. While holding down the left mouse button, point to one of the desired choice and release.
The following types are available: None, Square, Circle, Triangle, Diamond, Inverted Triangle, Hi-Lo, and Error Bar.
These options are used to determine how enclosed graph components (e.g. bars) or filled and what color is used. Click on the corresponding option menu to display the menu. While holding down the left mouse button, point to one of the desired choice and release.
Style Controls how the color is applied to the object. If,
Solid, is selected, the unaltered Fill Color is
applied to the graph component. If one of the
'Gray' options is selected, the Fill Color results
in a different intensity of color. If a pattern
is selected, then the Fill Color determines the
foreground color of the pattern.
Color color palette defined to Xess
Background COlor Used for the fill pattern background. Default is
then default background for the graph.
To display a legend, you must first turn on the Legend check button in the Graph Options dialog box. The legend is displayed to the right of the graph. For lines and graphs, the legend displays the line and marker style for each data series. For bar graphs or histograms, the legend shows bar colors. For contour graphs, the legend shows colors and levels. Surface graphs have no legend.
You may alter the appearance of the legend using this group of options as follows:
Text Type in the desired description for the dataset.
Others Click on the corresponding option menu to display the
menu. While holding down the left mouse button, point
to one of the desired choice and release.
Save also causes any copies of the graph which may have been inserted in the sheet or converted to a View window to be updated to match the latest version of the graph.
After graphs are saved, any one of them can be displayed instantaneously by selecting the appropriate graph name from the Choose Graph dialog box displayed by selecting Edit Graph from the Graph menu on the main menu bar.
Click on the Graph Type option box to display the graph types. While holding down the left mouse button, point to the desired type and release. The following graph types are supported: scatter, line, bar, pie, surface, contour, polar, histogram, hi-lo.
When you select a particular graph type, Xess automatically sets appropriate values in the the Data Set Options dialog box which are applied to all the data ranges defined for the graph. You may then use the Data Set Options dialog box to further customize what you see for each dataset.
For example, you could specify a general graph type of 'bar', then go to the Data Set Options dialog box and change the line segment for one of the datasets to 'line'. This results in a graph with mixed graph types.
Warning!! Although it is easy to switch from one graph type to another the graph range selected may be incorrect or insufficient to create such a a graph. An appropriate error message is displayed in the Message Line at the bottom of the main window. This requires that you may have to modify the data ranges to obtain the desired results. For more information, please read Graph DataSet Dialog.
If you want the graph to automatically redraw after the contents of a cell change, then click ON the Redraw on Recalculation check button.
If you want to see the results of changes to the graph options immediately after you select OK or Apply, then ensure that the Defer Redraw check button is OFF. When this check button is ON you must explicitly invoke Redraw from the File menu in the Graph Editor.
Click this option ON to display legend to the right and center of the graph. For line graphs, the legend displays the line and marker style for each data series. For a bar graph or histogram, the legend shows bar colors. Surface graphs have no legend. Click this selection OFF for surface graphs.
CLick this option ON to improve differentiation of graph options (lines, bars, etc.) in Line, Scatter, Hi-Lo, Box, and Control graphs when data sets are tighly clustered such that the data points obscure each other.
For Stacked Bar graphs, Skew Data is ignored and always treated as OFF.
For Bar graphs, Skew Data is ignored and always treated as ON.
You may select from the color palette for both the background of the graph and the graph itself (foreground). To select a color, click on the corresponding option button. While holding down the left mouse button, point to the desired color and release.
You may change the default font, style, and size for the graph as a whole, which can be overridden for each section. To select a color, click on the corresponding option button. While holding down the left mouse button, point to the desired color and release.
To activate the text insertion cursor inside the Graph Title entry box, first position the cursor inside the entry box and click the left mouse button. At the cursor position enter the label text. The title is centered by default.
You may format the title text using the following delimiters:
^ Center with respect to the previous line
{ Begins a block of text
} Ends a block of text
- Next character or text block to be subscripted
^ Next character or text block to be superscripted
\ Turns off special properties of the preceding characters so
they can be printed out
\n\
Forces new line
You may change the title font, style, size, and color for the graph. Click on the corresponding option button. While holding down the left mouse button, point to the desired selection and release.
To Save a graph for subsequent use:
You may save up to one hundred (100) graphs per spreadsheet.
To rotate a surface graph that is displayed in a graph window:
Position the mouse cursor on the desired axis to be rotated. While holding the mouse button down, drag the mouse in the direction of rotation. Release the mouse button when the desired rotation is achieved.
-or-
Set the desired Theta and Phi values inside the corresponding entry boxes according to the following:
Theta The angle at which the surface rotates around the
Z-Axis. It ranges from 0 - 89.
Phi The angle at which you look down upon the surface.
It ranges from 0 - 89.
If you wish to modify the graph, you must select Edit from the Graph menu which invokes the Graph Editor. Once you have completed the modifications and save the graph, the View Graph is automatically updated with the modifications.
This message . . . Means . . .
Error: Goalseek - variable cell must be The cell which changes
a constant. during Goalseek must
contain a constant
value.
Xess displays error messages in the Message Line of the
of the display and sometimes in popup dialog boxes. A full list of Xess error
messages and what they mean is contained in Error Messages in the Xess User's
Guide.
When importing a text file or pasting text into the spreadsheet, Xess looks for crossings of a "whitespace threshold" or blanks which traverse the same location in each line. The threshold can be set to any value between 0 and 100. When the threshold is crossed, Xess makes a column separation where the white space count inside the imported text file reaches a peak.
There must be a peak, i.e., a positive threshold crossing followed by a negative threshold crossing to create a column separation. The extreme case (0 and 100) have the following properties:
0 - There is never a negative threshold crossing, so there
are no column separations. The import creates only
one column; the entire text of the line goes into the
cell.
100 - Threshold crossings occur only where white space cuts
through the entire file.
If you are importing into XessLite from an Xess3 or Xessu sheet, be sure that your cell utilization does not exceed the bounds of XessLite . If so, you will be warned and your data truncated.
For the Import TSV operation, "tabs" contained in the file being imported are used as the "only" delimiters and the text is formatted appropriately inside the spreadsheet.
For the Import CSV operation, "commas" contained in the file being imported are used as the "only" delimiters and the text is formatted appropriately inside the spreadsheet.
The default filename extension for Xess Import TSV/CSV formats is ".tsv" and "csv" respectively.
To Import any of these file types into the spreadsheet:
Click on Filter to display filename(s) beginning with these characters.
For more information about the File Display dialog box, please refer to help topic "File Display Dialog".
These locale settings are used to define applicable cell formats and the character used as decimal point when entering numeric data into a cell or when formatting relevant cell data.
The interpretation of cell input varies based on the current locale settings. If the decimal point is changed from period to comma, then comma is recognized in keyboard input as the decimal point. Because comma now has this meaning, semi-colon (;) is used to separate lists of items as arguments to functions. For example:
=@MAX(A1..A10, 0.5)
would become:
=@MAX(A1..A10; 0,5)
Changing the locale settings automatically changes existing
formulas.
If the decimal point in the locale setting is changed to a character other than period or comma, then period is still used on input in order to avoid ambiguities in parsing formulas.
The MB3 functionality can be disabled by defining the resource, *popupMenu, to be False, in the Xess resource file.
You can also use the arrow and tab keys on your keyboard to move the cursor. Each time you press an arrow key, the cursor moves one row or column in the direction of the arrow. Each time you press the tab key, the cursor moves to the next unprotected cell.
If you move the cursor to a position outside the current view, Xess moves the spreadsheet view over one column or row so that the current cell is always visible.
Meta<Key> in combination with selective keys (hardware dependent) provides a quick access to different areas of the spreadsheet. The key actions and the corresponding response is listed below:
KEY ACTION HARDWARE RESULT
Meta + PgUp Sun-4 The Cell Cursor moves to cell
A1 with the exception where the
Compose Character + Dec titles are set using "Set Title"
Prev Screen.
Alt + PgUp RS6000
Extend char + Prev HP700/800
Meta + PgDn Sun-4 The Cell Cursor moves to the
bottom extent of the sheet to
Compose Character + Dec the left.
Next Screen.
Alt + PgDn RS6000
Extend char + Next HP700/800
Meta + up/down arrow Sun-4 The Cell Cursor moves up/down
key. in the current column into the
next cell that contains data
Compose Character + Dec which is adjacent to a cell
up/down arrow key. above or below that does not
contain any data.
Alt + up/down arrow RS6000
key.
Extend char + up/down HP700/800
arrow key.
Meta + left/right Sun-4 The Cell Cursor moves left/right
arrow key. row in the current row into
the next cell that contains data
Compose Character + Dec which is adjacent to a cell to
left/right arrow the left or right that does not
key. contain any data.
Alt + left/right RS6000
arrow key.
Extend char + HP700/800
left/right
arrow key.
To move the view left or right one column at a time:
To Go To a designated cell:
To use TOP/BOTTOM
The following guidelines apply to named ranges:
$ _
Names ranges defined using the quoting method can define any characters,
including special ones:
= '1997/Q3' / '1996/Q3'
Limitation:
Note that cell names which "look" like a row/column address such as ACT1 cannot be used as a name. This means that names which consist of 1,2, or 3 characters less than or equal to "AMJ" followed by a number cannot be used as a name.
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. If you share a storage device with other users, it is a good idea for all users to agree to begin file names with your initials. Since Xess sorts file names alphabetically, this ensures that your files will be displayed together in a directory listing.
Follow these conventions in assigning spreadsheet names:
CAUTION: If you are on a system that restricts the length of its file names (for example, to eight characters) and you assign a name with more than eight characters, Xess cuts it to eight characters. For example, CONTROLTERMINAL and CONTROLTEST would both be truncated to CONTROLT. The last file saved would then overwrite the prior version of either file.
When opening a spreadsheet, Xess clears the current spreadsheet, if there is one, and loads the entire spreadsheet along with its default characteristics such as column width and format. If another sheet is open, you will be prompted to Save or Discard changes before proceeding with Open.
You can also use Open... to load and use spreadsheets in non-Xess formats or to load columnar-format data from text files (for more information, see the help topic "Importing Data").
The extension, .xs, refers to sheets created by version 1 or 2 of Xess. These sheets can be loaded into Xess version 3 or higher by specifying the File Format of 'XS'.
If you are opening an Xess sheet in XessLite, be sure that your cell utilization in Xess does not exceed the bounds of XessLite . If so, you will be warned and your data truncated.
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 will require some modification.
The default filename extension for Xess Open WK1/WKS formats is ".wk1" and ".wks" respectively. The default filename extension for WK3 is ".wk3"; the default filename extension for XLS is ".xls". The XLS formats support Excel version 3, 4, 5, and 7 files for input.
When loading WK*or XLS spreadsheets, please note:
or
Click the "open folder" icon button on the toolbar.
To insert a link to an image into a cell:
The Overlay Editor dialog contains eight icons and several selection options. Clicking on an icon places you in a drawing mode. The icons have the following meanings:
Move to Back Select
Draw Line Draw Text
Draw Hollow Rectangle Draw Filled Rectangle
Draw Hollow Ellipse Draw Filled Ellipse
The selection options control colors, patterns, and fonts.
To enter text on the sheet, select Draw Text, click the left mouse button at the desired starting location in the sheet for the text, and begin typing the text string. Press [Return] when done.
Copyright 1990 - 1998 Applied Information Systems, Inc.
Chapel Hill, NC, USA
All Rights Reserved
Xess is an advanced spreadsheet designed specifically for Linux personal computing. By using the intuitive Xess user interface, you can easily access its powerful computational and graphical tools.
The Xess spreadsheet provides a flexible and comfortable visual environment in which to handle complex calculations and data manipulations. 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.
Like those in other spreadsheets, cells in an Xess spreadsheet contain constant numeric values, text strings, and formulas that calculate new values. However, with its extensive range of features and functions designed for the advanced user, Xess defines a new generation of spreadsheets.
The entire spreadsheet environment can be customized to reflect specific user or site preferences. This includes short-cut accelerator keys and start-up defaults.
To set a horizontal page break:
To change Page options:
If you select Other, then the Page Height and Page Width boxes become open for data entry; otherwise they are display-only.
Orientation is independent of Page Size: this means when you select landscape and page size is set to Letter (A), the height and width dimensions are not switched when you change orientation from portrait to landscape - the printer simply knows to rotate the printed output.
Off Do not use this option.
Horizontal Reduce the size of the selected print area to fit within
the page width eliminating overflow.
Vertical Reduce the size of a selected print area to fit within
the page height eliminating overflow.
Both Reduce the size of the selected print area (width and
height) to fit within the bounds of a single page.
Note: if Fit to Page is selected, then margin settings and Shrink/
Expand options are ignored.
Off Do not use this option.
Horizontal Center the page contents so that the left and right
margins are equal.
Vertical Center the page contents so that the top and bottom
margins are equal.
Both Center the page contents so that left/right margins
are equal and top/bottom margins are equal.
Note: if Fit to Page option is set to something other than Off, this option is ignored.
PostScript Formats are defined through four mechanisms:
To define multiple print commands in the resource file, set the resource similar to the following:
*printers: tag1:command1\n\
tag2:command2\n\
tag3:command3\n\
.
.
.
tag20:command20
For example:
*printers: Default:lpr %s\n\
Preview:dxpview %s
Note that the output device need not be a printer - it could be a PostScript
previewer, for example. Make sure that the last entry is not followed by the
\n\
\ sequence.
or
Select one of the following from the File menu:
Note: When this options is selected, Constraint Checking and Iteration Recalculation are not allowed.
When the "Iteration Limit" field is set to a non-zero value, iterative calculation is enabled. In this mode, Xess will make multiple recalculation passes, still preserving the natural order and minimal recalculation rules, until either the iteration limit has been reached or, if constraint checking is enabled, until all constraints are satisfied, whichever comes first.
Xess regular expressions consist of a string of characters. Unless a character is "special" it must match the corresponding character in the string which is being evaluated. For example, "abc" is a regular expression which matches any string containing "abc" as a substring. The "special" characters are as follows:
. A period matches any character. Example: A.C matches "ABC"
or "AXC."
^ Matches only at the beginning of a field. Example: ^ABC matches
"ABC" but not "AABC."
$ Matches only at the end of a field. Example: ABC$ matches
"AABC" but not "ABCD."
[x-y] Matches any character between the character x and the character y.
Example: [0-5]$ finds fields ending in the characters 0 through 5.
[xyz] Matches any character in the list xyz. Example: [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.
* Matches zero or more occurrences of the preceding pattern.
Example: ^A.*B$ matches any string that begins with "A" and ends
with "B."
+ Like *, but matches one or more occurrences of the preceding
pattern. Example: A.+B matches "ACB" and "A123B" but not "AB."
These methods can be combined with strings of non-special characters.
Thus, the pattern [brc]at finds "bat," "rat" and "cat."
String comparions will treat upper/lower case and accented/unaccented
characters according to Collating Options settings.
"<resource name>": "<value>"where "resource name" is one of the Xess resources and "value" is one of the possible values for the resource.
The resource file must be named "Xslite" and placed in /usr/lib/X11/app-defaults/.
If you choose the second option the resource definitions described under System-wide Preferences must be prefixed with the class name as follows:
"<class name>"*"<resource name>": "<value>"where "class name" in this case is "Xslite."
For an exact definition of the resources, refer to the resource file named Xslite included with the distribution. You may tailor it to your specification then save it as "Xslite".
The Save function does not remove your spreadsheets from memory, so you can continue to make changes to them 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 a spreadsheet with the same name as one already on your disk (possibly an earlier version of the same spreadsheet), one of two things happens:
or
Select Save/Save... from the File menu. If this is the first Save, Xess displays the Save Sheet dialog box for you to specify the spreadsheet filename. If this is not the first time, Xess will save the sheet with the current settings.
or
Because there are differences between spreadsheet programs, you may have to modify certain formulas or cells to get the file to work properly in the other spreadsheet. Here are some principal differences:
While dragging the mouse, a box is drawn is drawn around the cells being selected and this range is displayed in the Message Line. You can use the same method to select a block of columns.
One option is to scroll through the spreadsheet using the horizontal and vertical scroll bars. To quickly scroll the spreadsheet to the desired viewing area, position the cursor on the slider bar (vertical or horizontal), while holding down the left mouse button, drag the slider until the desired viewing area is reached. Once the desired viewing area is reached, you can use the mouse to select a range of cells as described above.
Another option is to create another view of the spreadsheet and use it to scroll to the new area being selected. A selection started in one view can be extended in the second view by pressing Shift and click the left mouse button.
To view or change Default Sheet Options settings:
Default Column Width Type the desired column width.
Default Row Height Type the desired row height.
Grid Lines Click checkbutton ON to display row/column
grid lines. Click checkbutton OFF to
remove row/column grids lines. This also
determines whether to not grid lines are
printed.
Grid Color Move the pointer to the color OptionButton
and hold down the left mouse button. Move to
to the desired color and release. This deter-
mines the color of the grid on screen as well
as printouts to color printers. The
color palette can be changed.
Move On Entry This option indicates the direction the cell
cursor should move after you complete a cell
entry. This option can be a time-saver in
spreadsheets with a redundant pattern of data
entry, for example: a long vertical column of
numbers to enter.
Click on the option button to display the list
of available choices. While holding down the
left mouse button, move the pointer to the
desired choice and release. The options are:
OFF Stays in the same cell
UP Moves up one cell
DOWN Moves down one cell
RIGHT Moving right one cell
LEFT Moves left one cell
Enable Protection Determines whether or not cell protection is
enforced. Click the check button ON if you
want cell protection enforced. (Cell protection
is set from the Tools menu and Cell Defaults
dialog box.) To ignore cell protection, click
the check button OFF.
Default Sheet Nmaes Determines the default format for the names
of new sheets in a workbook. These names
may be of the form Sheet1, Sheet2,... or
A, B,... This option does not affect the
form of existing sheets.
Case Collating Options Determines how Xess treats upper/lower case
as described in Collating Options.
Accent Collating Options Determines how Xess treats accented/
unaccented characters as described in
Collating Options
To change the name of a sheet:
"crêpe"!A5
"1997 Sales"!B5..J5
"Sales/Quarter"!B12
Before sorting a range of cells, consider these rules that Xess follows for sorting:
Note: If you specified that Xess not update cell references during the sort:
Range Type the addresses of opposite corners of the
range to be sorted, separated by dots
(for e.g: A1..D5). Alternatively, use the mouse
to select a range from the spreadsheet. Then
position the mouse inside the range box and
press the PASTE button. The range is automatically
entered inside the Range entry box.
Key 1...Key 5 Enter any cell address of the column index of
the column on which to base the sort. For
example, to sort by the contents of column B,
enter B1. If you enter a range, Xess uses the
leftmost column of the range.
Key 1 is the primary key, key 2 is the
secondary, and so forth.
Descending Click the check button ON for sorting in
descending order of magnitude. By default,
Xess sorts in ascending order.
Update References
By default, Xess updates cell references when
it moves formulas in cells during a sort. To
tell Xess not to update cell references,
click this button off.
Case... If you wish to disable case sensitivity or
change collating sequence with respect to
case, select the appropriate option.
Accents... If you wish to disable accent sensitivity or
change collating sequence with respect to
accented characters, select the appropriate
accent option.
For example, If the last operation was an editing operation, the button would read "Undo Edit." After the Undo has been performed, the Undo button will be grayed out until another "undoable" operation occurs.
To undo the last destructive operation:
or
Top Goto the (absolute) top of the view sheet.
Bottom Goto the (absolute) bottom of the view sheet.
Go To Goto a specific cell address in the view sheet
Lock View Titles Define a group of cell from the cell cursor
up and to the left which will remain on screen
when the view sheet is scrolled. Locked view
titles are designated by red/darkened row
borders.
Unlock View Titles Disable the titles that are set with the
preceding option.
Create New View Displays an independent viewing window
of the current sheet loaded.
Zoom Enlarge or shrink the display of items in the
current view.
Note that you can apply any available functions to the view in focus such
as Search, Sort, Extract.
To define a new view: