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.
A powerful feature of Xess is its understanding that the world does not stand still. In a world of spreadsheets that only capture a snapshot in time, Xess is a step up to live action -- capable of sending and receiving data and instructions from other X Windows programs, even on other computers, and automatically recalculating every referenced area as it goes.
This capability allows Xess to monitor and display data from multiple sources as it is calculated and to pass this information to other programs in a real-time environment.
A toolkit (API) is provided which makes it easy for C or Fortran programmers to develop customized applications or even extensions to the spreadsheet itself.
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.
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.
Xess formulas can calculate with numbers, text, logical values, cell references, and other formulas. For example, you can easily calculate the sum of a series of cells, the total of values in a column, a minimum or maximum value within a range, the rounded result of another formula, or the absolute value of a cell entry. Formulas can express complex interdependencies among cells, and they can define constraints on the calculation, such as limits on acceptable 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, simply select the cell. Xess displays the formula in the Input line of the Control/Status area. You can edit the formula or values in cells at any time.
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 that are not found in typical financial spreadsheets.
For more information, see:
Cell Contents
Cell Defaults
Cell Formats
Cell References
Formulas
Functions
Embedded Tools
The following attributes are supported:
+ - .
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 also 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 entered with this method is converted to the equivalent reference to @date.
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
The color palette displayed in the Cell Colors Dialog can be changed.
To set protection on a cell-by-cell basis, select "Cell Protection" under the Tools menu.
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.
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:SS as a time in the format hours:minutes:seconds,
in a 24-hour clock starting at midnight.
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.
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.xs3",#).
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:
Xess3 [options] [file.xs3]
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
-connect a|r|l accept, refuse, or lock connections at
-con a|r|l startup
-xsname name name this instance of Xess
-xsn name
-nomenu exclude all menu choices except Help
from the main menu
-help display this text and exit
-h
The most compelling feature of Xess is its understanding that the world does not stand still. In a world of spreadsheets that only capture a snapshot in time, Xess is a step up to live action-- capable of sending and receiving data and commands from other X windows programs, even on other computers, and automatically recalculating every referenced work as it goes. For example you can:
The Connection menu enables and disables receiving connection requests from other programs.
Accept Connections
-enables Xess to accept connection requests from remote clients.
Refuse Connections
-causes Xess to refuse connection requests from any remote client
except the Xess Connection Manager (xsapiTOOLKIT/example22.c).
Lock Connections
-disables all
-enables Xess to lock out the Xess Connection manager
(xsapiTOOLKIT/example22.c) from connecting to an Xess instance.
Connection Status
-causes Xess to refuse all connection requests from remote clients,
including the Xess Connection Manager (xsapiTOOLKIT/example22.c),
thus ensuring the privacy of your data in a networked environment.
Your Xess distribution media contains several example Xess "connection"
source programs written in "C" and "FORTRAN" . On the tape they are located
in xsapiTOOLKIT/ directory. The file names are example1.c, example2.c, ...,
example30.c.
This directory also contains the toolkit library "libXess.a" and a "Makefile".
To build these example programs, just "cd" into the appropriate directory and type,
% make all
To run one of these Xess client programs, first select Accept Connections from
the Connections menu of the menu bar. Next, from a xterm (terminal window),
type a client program name(e.g. example8) and press [Return]. In a few moments,
the client should connect to the spreadsheet.
PLEASE NOTE: The only prerequisites for creating a client application which utilizes the Xess API library are a C or FORTRAN compiler and the X11 (release 3 or higher) object library.
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 it 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:
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.
Embedded Tool Returns
@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(A,Y)
The linear least squares solution X to the overdetermined
system of equations AX=Y.
A - a range representing the coefficient matrix
Y - a column vector of values for the dependent variable
@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)
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.
@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
//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 label. Xess will display the full entry on the edit line in the Control/Status area. If you select the Wrap format option, Xess will automatically wrap the long label to fit within the bounds of the column width. When column width changes the contents will re-wrap accordingly.
To enter a negative number, type a minus sign (-) and the number. Do not use parentheses to indicate negatives. However, if you change the numeric format to Dollars or Comma, Xess displays negative numbers in parentheses.
Do not use spaces 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 Control/Status area. 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.
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.
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.
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.
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 highest 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.
Many financial functions require specifying a Day Count Basis. A Day Count Basis indicates the way in which the days in a month and the days in a year are to be counted. Most of the financial functions in securities involve 4 different Day Count Bases: 30/360, actual/actual, actual/360 and actual/360.
30/360 Day Count Basis assumes 30-day months and 360-day years (12 months x 30 days). Xess also follows the "End-of-Month" rule which assumes that a security pays interest on the last day of the month will always make its interest on the last day of the month. Some special rules are followed when calculating the days between two dates on 30/360 Day Count Basis:
Let Start_Date = D1/M1/Y1, End_Date = D2/M2/Y2,
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
@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
@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
@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
@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).
@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
@ACCRINT(I,Ft,S,R,P,F[,B])
Returns the accrued interest for a security that pays
periodic interest. Accrued interest is the amount the
buyer must compensate the seller for the portion of
the next coupon interest payment the seller has earned
but will not receive from the issuer.
I - issue date
Ft - first coupon date of the security.
S - settlement date
R - annual coupon rate
P - par value of the security
F - number of coupon payments per year
B - day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
Example:
A security has the following terms: 4/1/93 settlement date,
6/1/93 maturity date, 10% semiannual coupon, $1000 par
value, 30/360 bais
@accrint(34060,34121,10%,1000,2,0) = 33.333
@ACCRINTM(I,S,R,P[,B])
Returns the accrued interest for a security that pays
interest at maturity
I - the issue date
S - the settlement date
R - the annual coupon rate
P - the par value of the security
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPDAYBS(S,M,F[,B])
Returns the number of days between the beginning of the
coupon period to the settlement date.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPDAYS(S,M,F[,B])
Returns the number of days in the coupon period that
the settlement date is in.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPDAYSNC(S,M,F[,B])
Returns the number of days between the settlement date
and the next coupon date.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPNCD(S,M,F[,B])
Returns the next coupon date after the settlement date.
S - the settlement date.
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPNUM(S,M,F[,B])
Returns the number of coupon payments between the
settlement date and munurity date.
S - settlement date
M - maturity date
F - the number of coupon payments per year
B - day count basis
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPPCD(S,M,F[,B])
Returns the previous (most recent) coupon date before
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - day count basis
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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
@CUMIPMT(R,NP,PV,S,E,T)
Returns the cumulative interest on a a loan between S
(start period) and E (end period).
R - interest rate
NP - the total number of payment periods. Make sure
the units used be R and NP are consistent. For
example, for a 5-year loan with 12% annual
interest, if you make payment monthly, use 12%/12
for (monthly) R and 5*12 for NP. If you mkae
annual payment on the same loan, use 12% for R
and 5 for NP.
PV - the present value of the loan.
S - start period. It's the first period in the calculation.
E - end period. It's the last period in the calculation.
T - timing of the payment
0 payment is made at the end of the period.
1 payment is made at the beginning of the period.
@CUMPRINC(R,NP,PV,S,E,T)
Returns the cumulative principal paid on a a loan between S
(start period) and E (end period).
R - interest rate
NP - the total number of payment periods. Make sure
the units used be R and NP are consistent. For
example, for a 5-year loan with 12% annual
interest, if you make payment monthly, use 12%/12
for (monthly) R and 5*12 for NP. If you mkae
annual payment on the same loan, use 12% for R
and 5 for NP.
PV - the present value of the loan.
S - start period. It's the first period in the calculation.
E - end period. It's the last period in the calculation.
T - timing of the payment
0 payment is made at the end of the period.
1 payment is made at the beginning of the period.
@DB(C,S,L,P[,M])
Returns the depreciation of an asset for a specific
period using the fixed-declining balance method.
C - the initial cost of the asset.
S - salvage , the value at the end of the depreciation.
L - life, the total number of periods over which the asset
is being depreciated.
P - the period the depreciation will be calculated.
M - the number of months in the first period. if
omitted, The default is 12.
@DDB(C,S,L,N) Returns the double-declining depreciation allowance
C - cost
S - salvage value
L - allowable value
N - number of depreciation periods
@DISC(S,M,P,R[,B])
Returns the discount rate for a security.
S - the settlement date
M - the maturity date
P - the price of the security per $100 face value
R - the security's redemption value per $100 face
value at maturity
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@DOLLARDE(FD,F) Coverts a dollar amount expressed as a fraction form into a
a decimal form.
FD - the dollar amount in fractional form.
F - the integer to used as the denominator of the
fraction.
@DOLLARDE(10.04, 16) = 10.25
@DOLLARDE(10.2, 8) = 10.25
@DOLLARFR(DD,F) Coverts a dollar amount expressed as a dedimal form into a
a fraction form.
FD - the dollar amount in decimal form.
F - the integer to used as the denominator of the
fraction.
@DOLLARFR(10.25, 16) = 10.04
@DOLLARFR(10.25, 8) = 10.2
@DURATION(S,M,R,Y,F[,B])
Returns the Macauley duration of a security assuming
$100 face value. The Macauley duration is defined a sthe
weighted average of the present value of the cash flows.
S - the settlement date
M - the maturity date
R - the security's annual coupon rate
Y - the security's annual yield
F - the number of coupon payments per year
B - day count basis
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@EFFECT(NR,NP) Returns the effectve annual interest rate.
NR - nominal annual interest rate
NP - number of compounding periods per year.
@EFFECT(6%, 4) = 0.061364 or 6.1364%
@FV(P,R,N) Returns the future value of an annuity
P - periodic payment
R - interest rate
N - number of periods
@FVSCHEDULE(P,S)
Returns the future value of an initial investment
after compounding a series of interest rates.
P - initial investment principal
S - a range of numeric numbers represents an interest
@FVSCHEDULE(100, A1..A4) = 141.04
where A1..A4 = {6%,8%,10%,12%}
@INTRATE(S,M,I,R[,B])
Returns the interest rate for a fully invested
security
S - settlement date
M - maturity date
I - amount of investment in the security
R - redemption at mutirity
B - day count basis
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@IPMT(R,P,NP,PV,FV[,T])
Returns the interest payment for a specific period for
an investment based on periodic, constant payments and
a constant interest rate.
R - interest rate per period
P - the period for which the interest will be calculated
NP - the total number of payment periods. Make sure
the units used be R and NP are consistent. For
example, for a 5-year loan with 12% annual
interest, if you make payment monthly, use 12%/12
for (monthly) R and 5*12 for NP. If you mkae
annual payment on the same loan, use 12% for R
and 5 for NP.
PV - present value of the investment.
FV - future value or a cash balance you would like to
attain at the end of the last period.
T - timing of the payment
0 payment is made at the end of the period.
1 payment is made at the beginning of the period.
@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
@MDURATION(S,M,R,Y,F[,B])
Returns the modified Macauley duration of a security
assuming $100 face value.
S - the settlement date
M - the maturity date
R - the security's annual coupon rate
Y - the security's annual yield
F - the number of coupon payments per year
B - day count basis
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@MIRR(CF,FR,RR)
Returns the modified internal rate of return fro a
series of periodic cash flows.
CF - a reference to a series of cash flows. CF must
contain at least one positive value (income) and
one negative value (payment).
FR - finance interest rate
RR - reinvestment interest rate.
@NOMINAL(ER, NP)
Returns the nominal annual interest rate.
ER - effective annual interest rate.
NP - number of compounding periods per year.
@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)
@ODDFPRICE(S,M,I,FC,R,Y,RD,F[,B])
Returns the price per $100 face value of a security
with an odd (shor or long) first period.
S - settlement date
M - maturity date
I - issue date
FC - first coupon date of the security
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@ODDFYIELD(S,M,I,FC,R,PR,RD,F[,B])
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
I - issue date
FC - first coupon date of the security
R - annual coupon rate of the security
PR - the price of the security per $100 face value
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@ODDLPRICE(S,M,LC,R,Y,RD,F[,B])
Returns the price per $100 face value of a security
with an odd (short or long) last period.
S - settlement date
M - maturity date
LC - first coupon date of the security
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@ODDLYIELD(S,M,LC,R,PR,RD,F[,B])
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
LC - Last coupon date of the security
R - annual coupon rate of the security
PR - the price of the security per $100 face value
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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
@PPMT(R,P,NP,PV,FV,T)
Returns the payment on the principal for a specific
period for an investment based on periodic, constant
payments and a constant interest rate.
R - interest rate per period
P - the period for which the interest will be calculated
NP - the total number of payment periods. Make sure
the units used be R and NP are consistent. For
example, for a 5-year loan with 12% annual
interest, if you make payment monthly, use 12%/12
for (monthly) R and 5*12 for NP. If you mkae
annual payment on the same loan, use 12% for R
and 5 for NP.
PV - present value of the investment.
FV - future value or a cash balance you would like to
attain at the end of the last period.
T - timing of the payment
0 payment is made at the end of the period.
1 payment is made at the beginning of the period.
@PRICE(S,M,R,Y,RD,F[,B])
Returns the price per $100 face value of a security
that pays periodic interest
S - settlement date
M - maturity date
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@PRICEDISC(S,M,D,RD[,B])
Returns the price per $100 face value of a discounted
security.
S - settlement date
M - maturity date
D - discount rate of the security
RD - redemption value of the security at maturity per
$100 face value
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@PRICEMAT(S,M,I,R,Y[,B])
Returns the price per $100 face value of a security
that pays interest at maturity
S - settlement date
M - maturity date
I - issue date
R - annual coupon rate of the security
Y - annual yield of the security
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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.
@RECEIVED(S,M,I,D[,B])
Returns the value at maturity of a fully invested
security. Dates must be entered as a serial date value.
S = Settlement date, date of purchase
M = Maturity date.
I = Investment amount
D = Discount rate
B = Day count basis as follows:
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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.
@TBILLEQ(S,M,D) Returns the bond-equivalent yield (BEY) for a Treasury Bill.
If the term is one half-year or less, BEY is
equivalent to a actural/365 simple interest rate. If
the term of the security is more than one-half year,
BEY is equivalent to a semiannually compounded
Treasury bond yield.
S = Settlement date
M = Maturity date
D = Discount rate of Treasury Bill
@TBILLPRICE(S,M,D)
Returns the price per $100 face value for a Tresury
bill.
S - settlement date
M - maturity date
D - discount rate of the Tresury bill
@TBILLYIELD(S,M,D)
Returns the yield on a treasury bill, given settlement
date S, maturity date M, and discount rate D.
S = Settlement date
M = Maturity date
D = Discount rate of Treasury Bill
@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
@VDB(C.S,L,S,E) Returns the depreciation of an asset between two specific
period using the fixed-declining balance method.
C - the initial cost of the asset.
S - salvage , the value at the end of the depreciation.
L - life, the total number of periods over which the asset
is being depreciated.
S - Starting period for the calculation
E - Ending period for the calculation.
@XIRR(G,V,D) Returns the internal rate of return for a series of
cash flows with variable intervals
G - a guessed value of the result
V - a range, contains a series of cash flows
D - a range, contains the schedule of cash flows in V
V and D must be one-dimentional ranges and have the
same size.
@XNPV(R,V,D) Returns the net present value for a series of cahs
flows with variable intervals.
R - discount rate to apply to the cash flows
V - a range, contains a series of cash flows
D - a range, contains the schedule of cash flows in V
V and D must be one-dimentional ranges and have the
same size.
@YIELD(S,M,R,PR,RD,F[,B])
Returns the yield of a security that pays periodic
interest
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
R - annual coupon rate of the security
PR - the price of the security per $100 face value
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@YIELDDISC(S,M,PR,RD[,B])
Returns the annual yield for a discounted security
S - settlement date
M - maturity date
PR - the price of the security per $100 face value
RD - redemption value of the security at maturity per
$100 face value
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@YIELDMAT(S,M,I,R,PR[,B])
Returns the annual yield of a security which pays interest at
maturity. All dates must be expressed as serial date values.
S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
B = the day count basis to be used:
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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
@@(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.
@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.
Function Returns
@@(R) Reference to the cell or range R
@ABS(X) Returns the absolute value of X
X - a numeric value
@ACCRINT(I,Ft,S,R,P,F[,B])
Returns the accrued interest for a security that pays
periodic interest. Accrued interest is the amount the
buyer must compensate the seller for the portion of
the next coupon interest payment the seller has earned
but will not receive from the issuer.
I - issue date
Ft - first coupon date of the security.
S - settlement date
R - annual coupon rate
P - par value of the security
F - number of coupon payments per year
B - day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
Example:
A security has the following terms: 4/1/93 settlement date,
6/1/93 maturity date, 10% semiannual coupon, $1000 par
value, 30/360 bais
@accrint(34060,34121,10%,1000,2,0) = 33.333
@ACCRINTM(I,S,R,P,[B])
Returns the accrued interest for a security that pays
interest at maturity
I - the issue date
S - the settlement date
R - the annual coupon rate
P - the par value of the security
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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
@COUPDAYBS(S,M,F,[B])
Returns the number of days between the beginning of the
coupon period to the settlement date.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPDAYS(S,M,F,[B])
Returns the number of days in the coupon period that
the settlement date is in.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPDAYSNC(S,M,F,[B])
Returns the number of days between the settlement date
and the next coupon date.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPNCD(S,M,F,[B])
Returns the next coupon date after the settlement date.
S - the settlement date.
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPNUM(S,M,F,[B])
Returns the number of coupon payments between the
settlement date and munurity date.
S - settlement date
M - maturity date
F - the number of coupon payments per year
B - day count basis
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@COUPPCD(S,M,F,[B])
Returns the previous (most recent) coupon date before
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - day count basis
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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
@CUMIPMT(R,N,P,S,E,T)
Returns the cumulative interest paid between S
(start) and E (end) on a loan.
R - Rate, a number
N - Number of payments, an integer > 0
P - Present Value
S - Start of loan
E - End of loan
T - Type is the timing of the loan: 0 means payment
at the end of each period, and 1 means payment at
the beginning of each period.
@CUMPRINC(R,N,P,S,E,T)
Returns the cumulative principle paid between S
(start) and E (end) on a loan.
R - Rate, a number
N - Number of payments, an integer > 0
P - Present Value
S - Start of loan
E - End of loan
T - Type is the timing of the loan: 0 means payment
at the end of each period, and 1 means payment at
the beginning of each period.
@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"))
@DB(C,S,L,N,M) Returns the fixed-declining real depreciation of an
asset for a specified period.
C - initial Cost of asset
S - Salvage value of asset
L - Number of periods over which the asset is
depreciating (Life)
N - Number of periods over which to calculate the
depreciation.
M - (optional) Number of Months in the first year,
which is assumed to be 12 if omitted.
@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.
@DISC(S,M,P,R,B)
Returns the discount rate of a security.
S - Settlement date
M - Maturity
P - Price per $100
R - Redemption value per $100
B - Basis is the standard day count basis
@DOLLARDE(D,F) Returns the amount in Dollar Decimal form of an amount
expressed in fractional form.
D - Dollars (in decimal form)
F - Fraction (denominator of fraction)
@DOLLARFR(D,F) Returns the amount in Dollar Fractional form of an
amount expressed in decimal form.
D - Dollars (in fractional form)
F - Fraction (denominator of fraction)
@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).
@DURATION(S,M,C,Y,F,B)
Returns the Macauley duration for an assumed par
value of $100.
S - Settlement date, a date/time value
M - Maturity date, a date/time value
C - Annual Coupon rate
Y - Annual Yield
F - Frequency -- number of payments per year.
B - Basis is the day count basis
@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
@EFFECT(R,P) Returns the effective annual interest rate.
R - Nominal Rate
P - Number of compounding Periods per year
@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
@FVSCHEDULE(P,R)
Returns the future value of an initial principle
after compounding by a series of interest rates.
P - Principal, or Present value
R - An array of interest rates by which to compound
@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.
@INTRATE(S,M,I,R,B)
Returns the interest rate of a fully invested security
S - Settlement date
M - Maturity
I - Investment amount
R - Redemption amount
B - Basis
@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.
@IPMT(R,P,NP,PV,FV[,T])
Returns the interest payment for a specific period for
an investment based on periodic, constant payments and
a constant interest rate.
R - interest rate per period
P - the period for which the interest will be calculated
NP - the total number of payment periods. Make sure
the units used be R and NP are consistent. For
example, for a 5-year loan with 12% annual
interest, if you make payment monthly, use 12%/12
for (monthly) R and 5*12 for NP. If you mkae
annual payment on the same loan, use 12% for R
and 5 for NP.
PV - present value of the investment.
FV - future value or a cash balance you would like to
attain at the end of the last period.
T - timing of the payment
0 payment is made at the end of the period.
1 payment is made at the beginning of the period.
@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(A,Y) The linear least squares solution X to the overdetermined
system of equations AX=Y.
A - a range representing the coefficient matrix
Y - a column vector of values for the dependent variable
@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.
@MDURATION(S,M,R,Y,F[,B])
Returns the modified Macauley duration of a security
assuming $100 face value.
S - the settlement date
M - the maturity date
R - the security's annual coupon rate
Y - the security's annual yield
F - the number of coupon payments per year
B - day count basis
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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
@MIRR(V,F,R) Returns the Modified Internal Return Rate for a range
of periodic cash flows.
V - A range of values representing periodic income
(positive values) and payments (negative values)
F - Finance rate, the interest paid on the payments
R - Reinvestment rate, the rate of return on the income
@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.
@NOMINAL(R,C) Returns the nominal annual interest rate for an
effective interest rate and the number of compounding
periods each year.
R - Effective interest rate
C - Compounding periods per year
@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
@ODDFPRICE(S,M,FC,R,Y,RD,F[,B])
Returns the price per $100 face value of a security
with an odd (shor or long) first period.
S - settlement date
M - maturity date
FC - first coupon date of the security
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@ODDFYIELD(S,M,I,FC,R,PR,RD,F[,B])
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
I - issue date
FC - first coupon date of the security
R - annual coupon rate of the security
PR - the price of the security per $100 face value
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@ODDLPRICE(S,M,LC,R,Y,RD,F,[B])
Returns the price per $100 face value of a security
with an odd (short or long) last period.
S - settlement date
M - maturity date
LC - first coupon date of the security
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@ODDLYIELD(S,M,LC,R,PR,RD,F,[B])
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
LC - Last coupon date of the security
R - annual coupon rate of the security
PR - the price of the security per $100 face value
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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) 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.
@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.
@PPMT(R,P,NP,PV,FV,T)
Returns the payment on the principal for a specific
period for an investment based on periodic, constant
payments and a constant interest rate.
R - interest rate per period
P - the period for which the interest will be calculated
NP - the total number of payment periods. Make sure
the units used be R and NP are consistent. For
example, for a 5-year loan with 12% annual
interest, if you make payment monthly, use 12%/12
for (monthly) R and 5*12 for NP. If you mkae
annual payment on the same loan, use 12% for R
and 5 for NP.
PV - present value of the investment.
FV - future value or a cash balance you would like to
attain at the end of the last period.
T - timing of the payment
0 payment is made at the end of the period.
1 payment is made at the beginning of the period.
@PRICE(S,M,R,Y,RD,F[,B])
Returns the price per $100 face value of a security
that pays periodic interest
S - settlement date
M - maturity date
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@PRICEDISC(S,M,D,RD[,B])
Returns the price per $100 face value of a discounted
security.
S - settlement date
M - maturity date
D - discount rate of the security
RD - redemption value of the security at maturity per
$100 face value
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@PRICEMAT(S,M,I,R,Y[,B])
Returns the price per $100 face value of a security
that pays interest at maturity
S - settlement date
M - maturity date
I - issue date
R - annual coupon rate of the security
Y - annual yield of the security
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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.
@RECEIVED(S,M,I,D[,B])
Returns the value at maturity of a fully invested
security. Dates must be entered as a serial date value.
S = Settlement date, date of purchase
M = Maturity date.
I = Investment amount
D = Discount rate
B = Day count basis as follows:
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@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
@TBILLEQ(S,M,D) Returns the bond-equivalent yield (BEY) for a Treasury Bill.
If the term is one half-year or less, BEY is
equivalent to a actural/365 simple interest rate. If
the term of the security is more than one-half year,
BEY is equivalent to a semiannually compounded
Treasury bond yield.
S = Settlement date
M = Maturity date
D = Discount rate of Treasury Bill
@TBILLPRICE(S,M,D)
Returns the price per $100 face value for a Tresury
bill.
S - settlement date
M - maturity date
D - discount rate of the Tresury bill
@TBILLYIELD(S,M,D)
Returns the yield on a treasury bill, given settlement
date S, maturity date M, and discount rate D.
S = Settlement date
M = Maturity date
D = Discount rate of Treasury Bill
@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
@VDB(C,S,L,S,E)
Returns the depreciation of an asset between two specific
period using the fixed-declining balance method.
C - the initial cost of the asset.
S - salvage , the value at the end of the depreciation.
L - life, the total number of periods over which the asset
is being depreciated.
S - Starting period for the calculation.
E - Ending period for the calculation.
@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.
@XIRR(G,V,D) Returns the internal rate of return for a series of
cash flows with variable intervals
G - a guessed value of the result
V - a range, contains a series of cash flows
D - a range, contains the schedule of cash flows in V
V and D must be one-dimentional ranges and have the
same size.
@XNPV(R,V,D) Returns the net present value for a series of cahs
flows with variable intervals.
R - discount rate to apply to the cash flows
V - a range, contains a series of cash flows
D - a range, contains the schedule of cash flows in V
V and D must be one-dimentional ranges and have the
same size.
@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
@YEAR(DT) Returns the year value of 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
@YIELD(S,M,R,PR,RD,F[,B])
Returns the yield of a security that pays periodic
interest
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
R - annual coupon rate of the security
PR - the price of the security per $100 face value
RD - redemption value of the security at maturity per
$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@YIELDDISC(S,M,PR,RD[,B])
Returns the annual yield for a discounted security
S - settlement date
M - maturity date
PR - the price of the security per $100 face value
RD - redemption value of the security at maturity per
$100 face value
B - the day count basis to be used
0 or omitted 30/360
1 actual/actual
2 actual/360
3 actual/365
@YIELDMAT(S,M,I,R,PR[,B])
Returns the annual yield of a security which pays interest at
maturity. All dates must be expressed as serial date values.
S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
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 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.
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 Control/Status area 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 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.
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.
Color color palette defined to Xess
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 on the Control/Status area 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.
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".
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:
$ . _
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 "FAN" followed by a number cannot be used as a name.
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 load and use spreadsheets from other spreadsheet programs or 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'.
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".
When loading WK*or XLS spreadsheets, please note:
or
Click the "open folder" icon button on the toolbar.
Copyright 1990 - 1996 Applied Information Systems, Inc.
Chapel Hill, NC, USA
All Rights Reserved
Xess is an advanced spreadsheet designed specifically for the X Windows
environment. 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.
A powerful feature of Xess is its understanding that the world does not stand still. In a world of spreadsheets that only capture a snapshot in time, Xess is a step up to live action -- capable of sending and receiving data and instructions from other X Windows programs, even on other computers, and automatically recalculating every referenced area as it goes.
This capability allows Xess to monitor and display data from multiple sources as it is calculated and to pass this information to other programs in a real-time environment.
A toolkit (API) is provided which makes it easy for C or Fortran programmers to develop customized applications or even extensions to the spreadsheet itself.
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.
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:
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.
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
Check 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.
Toolbar Determines whether to a set of icon buttons
are displayed below the menu bar as described
in the toolbar topic.
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
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.
Note that you can apply any available functions to the view in focus such
as Search, Sort, Extract.
To define a new view:
"<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 "Xess3" 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 "Xess3".
For an exact definition of the resources, refer to the resource file named Xess3 (or Xess3.dat on VMS) included with the distribution. You may tailor it to your specification then save it as "Xess3" (or Xess3.dat on VMS).