Copyright 1990 - 1997 Applied Information Systems, Inc.
Chapel Hill, NC, USA
All Rights Reserved
Xess is an advanced spreadsheet designed specifically for the X Windows environment with network interoperability. 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 Connection applications are 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.
In some cases cyclic dependencies are useful in that they can represent "iterative" calculations, which Xess supports. Iterative calculation in Xess is useful when two or more cells mutually depend on each other such that each time they are recalculated, their values become closer and closer to the desired answer.
When the "Iteration Limit" field is set to a non-zero value, iterative calculation is enabled. In this mode, Xess will make multiple recalculation passes, still preserving the natural order and minimal recalculation rules described above, until either the iteration limit has been reached or, if constraint checking is enabled, until all constraints are satisfied, whichever comes first.
Calculations with logical operators -- ! (logical NOT), && (logical AND), || (logical OR), and ?: (conditional) -- consider a non-zero value to be True and a zero value to be False. Integer operators -- ~ (complement), & (bitwise AND), | (bitwise OR), ^ (bitwise EXCLUSIVE-OR), and % (modulus) convert their operands to 32-bit integers before performing the operation.
Xess formulas can calculate with numbers, text, logical values, cell references, and other formulas. For example, you can easily calculate the sum of a series of cells, the total of values in a column, a minimum or maximum value within a range, the rounded result of another formula, or the absolute value of a cell entry. Formulas can express complex inter-dependencies among cells, and they can define constraints on the calculation, such as limits on acceptable resulting values or specific conditions under which a calculation should take place.
Once entered in a cell, formulas are hidden behind the scenes, perform their work in the background, and display only the result of their calculation. To view the formula in a cell, position the cell cursor to the desired cell. Xess displays the formula in the Edit Line of the spreadsheet area, if one exists.
If the Edit Line is not being displayed, you will need to initiate cell editing by pressing [F2]. The cell formula will now be shown in the cell.
Xess also provides a wide array of functions. Functions are predefined formulas that perform certain tasks. Some functions are used alone; others are used within formulas. Xess provides many specialized functions spreadsheets for almost any application area.
For more information, see:
Cell Contents
Cell Defaults
Cell Formats
Cell References
Formulas
Functions
Embedded Tools
The following attributes are supported:
There are two typical ways to use cell borders. The first places borders around each cell. The second places a single border around a group of cells.
To place borders on each cell in a range of cells:
+ - .
You can format numeric values to be displayed in several
ways, even as dates or times that are entered as numbers but
displayed in the specified date or time format.
To tell Xess to interpret the entry as a date or time and not a formula, you must change the cell format to one of the date/time formats before entering the contents of the cell, or use a date function.
You can use a shorthand method for entering dates. Type a slash (/) followed by any supported date format such as: /mm/dd/yy, /dd-mmm-yy, /dd.mmm.yy. The date or time entered with this method is converted to the equivalent date and time value.
Xess provides a rich set of built-in functions and formats for manipulating and displaying date and time values.
To set the Cell Defaults:
Note: The number of decimal places displayed does not affect the precision of Xess calculations, except for the @VSUM (visual sum) function.
8, 10, 12, 14, 18, 24, and user-defined ones
To set protection on a cell-by-cell basis, select "Cell Protection" under the Tools menu.
The color palette displayed in the Cell Colors Dialog can be changed with resources to have up to 63 colors.
Default Restore the cell format and decimal places
to the values selected on the Cell Defaults
options dialog.
Scientific in scientific notation (exponentiation)
Fixed using a fixed number of decimal places
General in the same format as the Fixed format. Data
values that are too long for the column are
displayed in Scientific format.
Dollars with a leading dollar sign ($), with comma
delimiters, and negative numbers in parentheses.
Currency with the format specified as a local currency by
the Locale Defaults Dialog.
Custom Currency with the currency format specified explicitly for
this cell by additional fields in the Cell Format
Dialog.
Comma like Fixed format, but with commas delimiting
the thousands, millions, etc.
Percent displays a value as a percentage with trailing
percent (%) sign.
MM/DD/YY as a date in the format 12/25/91
DD-MMM-YY as a date in the format 25-Dec-91
MMM-YY as a date in the format Dec-91
DD-MMM as a date in the format 25-Dec
YY-MM-DD as a date in the format 96-12-25
DD.MM.YY as a date in the format 25.12.96
HH:MM as a time in the format hours:minutes,
in a 24-hour clock starting at midnight.
HH:MM:SS as a time in the format hours:minutes:seconds,
in a 24-hour clock starting at midnight.
Fraction as an integer plus a fraction of 1, 2, or 3
digits, determined by the Decimal Places setting
Hex in hexadecimal format, displayed in base 16
with a leading 0x indicator
Logic as 0 or 1 for logic levels. Other values are
displayed as ? .
Hidden cell contents are (blank) not displayed.
Text as text when numeric values are entered. Also,
displays formula entries in the cells as text
as opposed to placing the resulting value of
the formula.
Note: The number of decimal places displayed does not affect the precision of Xess calculations, except for the @VSUM (visual sum) function.
The Xess spreadsheet display includes tabs for direct access to any sheet within the current workbook. You can insert/delete pages via menu operations. You can also select and name ranges of cells which drill through multiple pages. Range references in dialog boxes have been appropriately updated to accommodate the multi-page environment.
Sheet1, Sheet2,...Sheetn
The alternate form of sheet names is the same as column names:
A,B,C...FAM
The upper left cell in the first sheet is:
Sheet1.A1 or A.A1
If an explicit sheet name is not specified, the cell reference is
assumed to be on the current sheet.
One or both of the cells which define the boundaries for a range may be qualified with a sheet name. Thus:
Sheet3.A1..Sheet9.A1 or C.A1..I.A1
defines a range of 7 cells, drilling through the sheets.
Sheet3.A1..A5 or C.A1..A5
defines a 5 cell range on Sheet3, which may not be the current
sheet. By varying the sheet name, the column, and the row you
can reference 3-dimensional ranges. For example:
Sheet1.A1..Sheet10.E10 or A.A1..J.E10
defines a cubic solid containing 500 cells (10 sheets * 5 columns *
10 rows).
You may use the indirect cell reference technique (#) in cases where the @XVALUE function is stored in the same relative cell location as the second argument in the function. For example, if the @XVALUE function is stored in cell A1 and you wish to return the value from cell A1 in a different sheet, then use the construct, @XVALUE("othersheet.xs4",#).
@XHLOOKUP and @XVLOOKUP function the same as @HLOOKUP and @VLOOKUP respectively with the additional feature that the lookup tables can reside in a different sheet.
To enter a note in a cell or to modify an existing note:
To delete a note from a cell:
To reference a cell by address: - Type the column and row coordinates of the cell in the formula. For example, to reference the cell at Row 5 in Column D, type D5, OR - While entering a formula, move the mouse pointer to the cell to be referenced and click once. In input mode, Xess inserts the cell reference after the last character entered. In edit mode, Xess inserts the cell reference at the cursor location.
To reference a contiguous range of cells by address: - Type the column and row coordinates of two cells in opposite corners of the range to be referenced, with two periods (..) between the coordinates. For example, to reference the first five columns of the first five rows of the spreadsheet, type A1..E5, OR - Move the mouse pointer to one corner of the range to be referenced. Press the left mouse button and drag the mouse to the opposite corner, then release the mouse button. In input mode, Xess inserts the cell reference after the last character typed. In edit mode, Xess inserts the cell reference at the cursor location.
To reference a cell or range of cells by name: - Type the name assigned to the cell or range directly in the formula. Using mnemonic names instead of cell addresses generally improves the readability of the spreadsheet formulas. Cell and range names must be defined before they can be used.
Absolute Cell/Range References are interpreted by Xess as fixed, no matter where you move or copy the cell. To specify an absolute cell address, insert a dollar sign ($) before the address coordinate to be fixed, or before both coordinates if the row and column coordinates are both fixed.
To specify an absolute cell address, insert a dollar sign ($) before the address coordinate to remain fixed. For example:
Use [F4] when entering or editing a formula to toggle the absolute/relative settings for a cell reference (e.g. A1, $A$1, $A1, A$1).
xess4 [options] [file.xs4]
Options:
-rows r display "r" rows
-r r
-cols c display "c" columns
-c c
-size r c display "r" rows and "c" columns
-s r c
-position x y initialize upper left corner to "x","y"
-pos x y
-p x y
-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
-version display current version level and exit
-v
-class classname specify alternate class name/resource file
-help display help text and exit
-h
A 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 Connection applications are 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 (api/example22.c).
Lock Connections
-causes Xess to refuse all connection requests from remote clients,
including the Xess Connection Manager (api/example22.c),
thus ensuring the privacy of your data in a networked environment.
Connection Status
-shows a list of current connections.
Your Xess distribution media contains many example Xess "connection"
source programs written in "C" and "FORTRAN" . On the tape they are located
in api/ directory. The file names are example1.c, example2.c, ...,
example42.c.
The lib/ directory 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 an 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 the value into the destination cell
or range as a constant.
Copy Options
In addition to values and/or formulas copied, any specific
cell options that may have been defined may also copied. For
example: color, font, justification, formats.
The Copy Options dialog under the Options menu determined which
of these characteristics are copied.
Move
Moves a selected cell or range of cells (including all data
values, formulas, formatting and cell references) to a
designated location, overwriting existing data in the
destination range. Any formulas in the spreadsheet which
refer to the cells that have been moved are automatically
updated to refer to the new location.
Clear
Removes all data in the selected cell or range of cells.
Use the following sequence to perform a copy, move, or clear operation within
the same sheet:
Note: If you accidently erase the wrong data, or accidently overwrite some cells with a copy or move, Xess allows you to easily recover from your mistake using Undo.
To copy data to another sheet being displayed by a separate copy of the Xess program:
XessLite is a rich subset of the Xess spreadsheet designed specifically to meet the needs of Linux personal computing. While XessLite worksheet files are upwardly compatible with the full Xess spreadsheet, XessLite features differ from Xess as follows:
The Xessu system is a full-function Xess spreadsheet "unit" which can be accessed only within host applications which have embedded the spreadsheet technology. This system is designed specifically for qualifying OEM partners who wish to add spreadsheet-based functionality to the host application.
While the embedded Xessu spreadsheet offers the same underlying functionality as the standard Xess spreadsheet, the following restrictions apply:
End users can license the standard Xess spreadsheet system from AIS or an authorized distributor and substitute it for Xessu, eliminating the restrictions.
Embedded Tools are a powerful feature in Xess. Their power comes from their ability to return data in a matrix, not just the resident cell. This function makes non-scalar operations such as matrix manipulation and "live" recalculation as easy to use as an ordinary spreadsheet function.
Embedded Tools are used to compute things like Fourier transforms, eigenvalues, line fits, and regression analysis.
More information on using Embedded Tools.
If the *smartTextEntry resource is set, entry into cells formatted with the Text attribute are treated differently. The need to prefix numeric values is removed so that 555-1212 could be entered directly.
//r-
This feature can be useful for drawing lines to demark areas of your
spreadsheet when printed. However, if you wish to enter a character
string that begins with a slash (/), you must be sure to prepend
the entry with one of the special text-indicator characters: ' " ^
To edit an entry that occupies more than one cell, double click the cell which actually contains the text. Xess will display the full entry on the edit line in the Edit Line. If you select the Wrap format option, Xess will automatically wrap the long text to fit within the bounds of the column width. When the column width changes the contents will automatically re-wrap accordingly.
To enter a negative number, type a minus sign (-) and the number. Do not use parentheses to indicate negatives. However, certain formats (e.g. Dollars or Comma) cause negative numbers to display with parentheses.
Do not use spaces or commas when entering numbers. If you wish, you can display commas by changing the format.
Be careful not to substitute a lower case L for the numeral 1 or the upper case O for the numeral 0.
You can use scientific notation to enter a number. If the calculated number fits within the cell size and format, Xess displays it in full. Otherwise, it displays the value in scientific notation or displays the special width indicator, depending on the cell format.
Whereas numeric entries remain the same until you change them, cells defined by formulas are automatically recalculated to reflect changes in referenced cells -- even where there are complex interdependencies among cells.
Once entered in a cell, formulas are hidden behind the scenes, performing their work in the background and displaying only the result of their calculation. To view the formula in a cell, simply select the cell.
Xess displays the formula in the Input/Edit lines in the Edit Line. You can edit the formula or values in cells at any time. Xess also includes a "Text" display format which causes the formula itself, not the cell value, to be displayed on the sheet.
If the Edit Line is not being displayed, select Edit Cell or press [F2] to edit the value within the cell area itself.
Xess will not allow a syntactically incorrect expression to be entered.
@SUM(A1..A20)\D50
instructs Xess to recalculate @SUM(A1..A20) whenever the
contents of D50 change.
This feature is particularly important when you have a constraint expression containing a constant offset that produces a cell reference outside the cell range referenced in a dependent formula. Under these circumstances, Automatic Recalculation would not necessarily be triggered. For example,
@CCOUNT(C4..C100,# > #{0,-1}) counts all the cells in the range C4..C100 whose value is greater than the contents of the cell im- mediately above.
In order for C4 to be evaluated, it must be compared to C3 - which is not part of the explicit range, C4..C100. Without indicating an explicit dependency, C4 would never be evaluated properly. So, in this case, we would indicate the dependency as follows:
@CCOUNT(C4..C100,# > #{0,-1})\C3..C99
which tells Xess to recalculate whenever any cell in the range
C3..C99 changes.
If you are exporting from an Xess4 or Xessu sheet for subsequent import into XessLite, be sure that your cell utilization does not exceed the bounds of XessLite .
CSV (Comma Separated Values) format uses commas to separate cell values with text strings in double quotes.
Text format does not use a delimiter but uses the column width to determine cell (field) width. This creates a tabular representation of the spreadsheet data. The Text format is also used when pasting with mouse button 2 into a non-Xess window.
Note also that if a cell element overflows into an adjacent cell, it is necessary to include the root cell into the range selected. You may also include HTML elements directly into spreadsheet cells. These elements will be passed through, as text, to the HTML file and ultimately processed by the browser.
To extract a set of records from a selected range:
For example, if you ask Xess to open a WKS-format file, the file filter appears as *.wks and the Files list box includes all files with names ending in *.wks.
This is especially useful when you have many files on disk and the list is cumbersome to search. For example, you can change the file filter to display:
To move "up" directory levels, position the cursor on the current directory name with the extension "/.." and double-click the left mouse button. The files under the new directory are listed inside the Files list box.
To scroll through the list of directory names, click the up and down arrows along the right side of the Directories list box or drag the scroll bar.
To view the full pathname of the current directory inside the Directories list box, first position the cursor on the horizontal scroll bar located below the Directories list box. Second, while holding down the left mouse button drag the mouse to the left. Alternatively, click on the small arrow located to the left of the scroll bar until the desired view is obtained.
Alternatively, first position the cursor on the desired filename inside the Files list box and click the left mouse button to select(highlight)the name. Next, click OK or press [Return] to load the file.
To quickly select and load a file into the spreadsheet, position the cursor on the desired filename inside the Files list box and double-click the left mouse button.
For example, you can search for cells whose values are greater than 100 and less than 150, or for cells whose values are less than the average of the three preceding cells. This facility is extremely useful for spotting patterns in scientific data.
When searching by text pattern, a Unix-style regular expression is entered in the "Target" box. For example, a target pattern of "[cbh]at" will match any cell containing one or more of the words "cat," "bat," or "hat." A target pattern of "^C" will match any cell whose formatted text begins with an upper-case C.
When searching by numerical constraint, a valid Xess constraint expression must be entered in the "Target" box and the "Numeric Comparison" must be selected. As an example, to look for values in range named "data" which are more than three standard deviations from the mean, the constraint expression "@ABS(#) > 3*@STDS(data)" could be used as the target.
To Find the first occurrence of an entry in a range:
The following styles are supported for each font family:
=(expression) ; (constraint expression) //where expression defines the calculations needed to generate the cell's value, and constraint expression places limits on acceptable values or the circumstances under which the calculation should take place. Text followed by the string "//" is a comment.
Xess formulas look just like algebraic formulas; they contain values and operators that define the relationships between values, known in this context as operands because they are the entities that operators act on. Follow these guidelines for entering formulas:
0 1 2 3 4 5 6 7 8 9 . + - @ =
Xess Formulas can contain the following operators to define relationship between values.
Operator Precedence Definition
% 14 percent
** 13 Exponentiation
+ 12 Unary plus
- 12 Unary minus
~ 12 Bit-wise complement (integer)
! 12 Logical not
* 11 Multiplication
/ 11 Division
% 11 Modulus (integer)
+ 10 Addition
- 10 Subtraction
<< 9 Shift left (integer)
>> 9 Shift right (integer)
< 8 Less Than
> 8 Greater Than
<= 8 Less Than or Equal
>= 8 Greater Than or Equal
== 7 Equal
!= 7 Not Equal
& 6 Bit-wise And (integer) or String Concatenation
^ 5 Bit-wise Exclusive Or (integer)
| 4 Bit-wise Or (integer)
&& 3 Logical And
|| 2 Logical Or
?: 1 Conditional
In formulas with more than one operators, Xess calculates operators in
the order presented above. That is, AND/OR/NOT relationships are
considered before equal/unequal relationships, and subtraction/addition
is considered before multiplication/division.
To change the precedence of operators, enclose higher priority operations in parentheses.
Here are some special notes about Xess operators:
@SUM(A1?B1..C20:C10..D15)
returns the sum of B1..C20 if A1 evaluates to non-zero; otherwise it
returns the sum of C10..D15.
Other functions are used for more specialized purposes such as computing the future value of an investment or the product of multiplying one cell range by another range. Some functions perform calculations that arithmetic operators cannot handle such as text-string manipulations. Xess functions fall into the following categories:
Mathematical Functions perform calculations with numeric values as arguments, returning numeric values. Also see Embedded Tools.
Statistical Functions perform aggregation and counting operations on a group of values expressed as a list of arguments, returning numeric values.
Conditional Statistical Functions operate much like statistical functions, except that the last argument is a constraint expression that Xess evaluates for each cell in the argument list. Only cells that meet constraint criteria are included in the calculation. Conditional Statistical return a numeric value.
String Functions manipulate and evaluate character strings. For example, string functions can return the length of a string, find the first occurrence of a string in a range, change a string from upper to lower case and vice versa, or replace one string with another.
Logical Functions return one value if an argument meets certain criteria, another value if it does not. Logical functions are used as an adjunct to conditional statements, where the outcome is based on the validity of a statement.
Digital Logical Functions return the values 0, 1 or -1 (unknown) based on the value of its arguments. Digital logic functions evaluate the integer portion of a value. Any value not equal to 0 or 1 is considered unknown.
Financial Functions perform common financial calculations, such as calculating the future value of an annuity at a given interest rate, straight-line depreciation, and depreciation. Xess also includes is a set of financial trading functions which compute things like annuities, bonds, coupons, and Treasury Bills.
Date and Time Functions return values corresponding to the specified date, month, year, hour, minute or second. They can also return the current system time and date.
Miscellaneous Functions perform a variety of calculations, such as returning a reference to specific Cells or ranges or returning the Nth argument from a list of arguments.
Embedded Tools are a powerful feature in Xess. Their power comes from their ability to return data in a matrix, not just the resident cell. This function makes non-scalar operations such as matrix manipulation and "live" recalculation as easy to use as an ordinary spreadsheet function.
Perform calculations with numeric values as arguments, returning numeric values. For matrix functions, curve fitting, Fourier Transforms, etc. see Embedded Tools.
Function Returns
@ABS(X) Returns the absolute value of X
X - a numeric value
@ACOS(X) Returns the arc cosine of X
X - a numeric value between -1 and 1
@ACOSH(X) Returns the hyperbolic arc cosine of X
X - a numeric value greater than or equal to 1
@ASIN(X) Returns the arc sine of X
X - a numeric value between -1 and 1
@ASINH(X) Returns the hyperbolic arc sine of X
X - a numeric value
@ATAN(X) Returns the 2-quadrant arc tangent of X
X - a numeric value
@ATANH(X) Returns the hyperbolic arc tangent of X
X - a numeric value greater than -1 and less
than 1.
@ATAN2(X,Y) Returns the 4-quadrant arc tangent of Y/X
X, Y - numeric values, one of them must be non-zero
@CEIL(X) Returns the smallest integer greater than or equal to X
X - a numeric value
@COS(X) Returns the cosine of X
X - a numeric value
@COSH(X) Returns the hyperbolic cosine of X
X - a numeric value
@DEGREES(X) Returns 180/pi times X, converting radians to degrees.
X - a numeric value
@DET(M) Returns the determinant of the matrix range M, which
must be a square matrix.
M - a range, which must contain an equal number of
rows and columns.
@DOT(R1,R2) Returns the dot product of the vectors R1 and R2
R1, R2 - each a range, which must contain an equal
number of rows and columns, or must be a dimension
1 x n (row vector) or n x 1 (column vector).
@EXP(X) Returns e raised to the X power
X - a numeric value representing exponentiation
@FACT(X) Returns X * (X-1) * ... * 1 (X factorial, or X!)
X - a numeric value
@FLOOR(X) Returns the largest integer less than or equal to X
X - a numeric value
@FRAC(X) Returns the fractional portion of X
X - a numeric value
@GAMMA(X) Returns the value of the gamma function evaluated at X
X - a numeric value which must be non-negative
@GRAND Returns a 12th-degree binomial approximation to a Gaussian
random number with zero mean and unit variance
@INT(X) Returns the integer portion of X
X - a numeric value
@LN(X) Returns the log base e of X
X - a numeric value greater than 0
@LNGAMMA(X) Returns the log base e of the gamma function evaluated at X
X - a numeric value greater than 0
@LOG(X) Returns the log of X
X - a numeric value greater than 0
@LOG10(X) Returns the log base 10 of X
X - a numeric value greater than 0
@LOG2(X) Returns the log base 2 of X
X - a numeric value greater than 0
@MOD(X,Y) Returns the remainder of X/Y, with the same sign as X.
This is NOT the modulus, see @MODULUS below for that.
X, Y - numeric values
@MODULUS(X,Y) Returns the modulus of X/Y.
X, Y - numeric values
@PI Returns a numeric approximation of PI.
@POLY(X,...) Returns the value of the Nth degree polynomial
in X, where N is the number of data in the argument
list. The argument list may contain numbers and
cell references, including ranges. Any non-numeric
argument causes an error.
@POLY(2,3,4,5) = (3*(2**2)+4*2+5) = 25
@PRODUCT(...) Multiplies all the numeric elements of the argument
list together, ignoring empty and non-numeric values,
and returns the product. Each argument can be a
number, a cell or a range.
@RADIANS(D) Returns PI/180 * D, converting degrees to radians.
D is a numeric value.
@RAND Returns a uniformly distributed random number on
the interval [0,1]. The number will change every
time the spreadsheet is recalculated.
@ROUND(X,N) Returns the number X rounded to the number of decimal
places specified by the integer N, for N between 15
and -15, inclusive.
@ROUND(@PI,2) = 3.14
@ROUND(1234.5678,-2) = 1200
@SIGMOID(X) Returns the value of the sigmoid function 1/(1 + exp(-X))
X - a numeric value
@SIN(X) Returns the sine of X
X - a numeric value
@SINH(X) Returns the hyperbolic sine of X
X - a numeric value
@SQRT(X) Returns the positive square root of X
X - a positive numeric value
@SUMPRODUCT(R1,R2)
Returns the dot product of the vectors R1 and R2
R1, R2 - each a range, which must contain an equal number of
rows and columns, or must be a dimension 1 x n (row vector) or
n x 1 (column vector).
@TAN(X) Returns the tangent of X
X - a numeric value
@TANH(X) Returns the hyperbolic tangent of X
X - a numeric value
@TRANSPOSE(M) Generates the transpose of matrix M.
@VECLEN(...) Returns the square root of the sum of squares of its arguments
argumentlist - any combination of numbers, cells or ranges
Perform aggregation and counting operations on a group of values expressed as a list of arguments, returning numeric values.
@AVG(...) Returns the average (mean) of its arguments
argumentlist - any combination of numbers, cells or ranges
@CORR(R1,R2) Returns Pearson's product-moment correlation coefficient for
the paired data in ranges R1 and R2
R1, R2 must have the same dimensions
@COUNT(...) A count of its non-blank arguments
argumentlist - any combination of numbers, cells or ranges
@F(M,N,F) Returns the integral of Snedecor's F-distribution with M and N
degrees of freedom from minus infinity to F
M,N,F - numeric values
@ERF(L[,U]) Returns the error function integrated between L (lower
limit) and U (upper limit). If U is omitted, @ERF
integrates between 0 and L.
L, R - a non-negative numeric value
@ERFC(L) Returns the comlementary error function integrated
between L and infinity. @ERFC(L)=1-@ERF(L).
L - a non-negative numeric value
@FORECAST(X,R1,R2)
Returns a predicted Y value for X based on a linear
regression of R1 and R2 where they represent the
known x values and y values respectively.
X - a numeric value
R1, R2 - Ranges of numeric values. R1 and R2 must be
1 dimentional ranges with same sizes.
@FORECAST(20, A1..A5, B1..B5)=13.60349
where A1..A5={3,10,18,20} and B1..B5={8,10,13,11}
@FREQUENCY(R, B)
Returns a frequency distribution for a set of values R
with a set of intervals B.
R - a range of values on which frequencies will be counted
B - a range of intervals used to group values in R
@FREQUENCY(A1..A8, B1..B2)={3,2,2}
where A1..A8 = {70,79,80,61,83,93,88,97} and
B1..B2 ={80,90}
@FTEST(R1,R2) Returns the significance level (alpha) of the two-sided
F-test on the variances of the data specified by
ranges R1 and R2
@GMEAN(...) Returns the geometric mean of its arguments
argumentlist - any combination of numbers, cells,
or ranges.
@HMEAN(...) Returns the harmonic mean of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@LARGE(R,N) Returns the Nth largest datum in range R.
R - Range of data
N - Integer specifying which datum to choose.
@LARGE(A1..A10,3) = 45
where A1..A10={38,3,9,45,7,90,17,2,75,12}
@MAX(...) Returns the maximum of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@MEDIAN(...) Returns the median (middle value) of the range R1.
@MEDIAN(A1..A6) = 3.5
where A1..A6 = {1,2,3,4,5,6}
@MIN(...) Returns the minimum of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@MODE(...) Returns the mode, or most frequently occurring datum,
of all the arguments. Empty cells and cells containing
text are ignored.
@MODE(2, 5, 12, 5) = 5;
@MSQ(...) Returns the mean of the squares of its arguments
argumentlist - any combination of numbers, cells, or ranges.
@PERCENTILE(R,N)
Returns the datum from the range R which is at the Nth
percentile in R. Only numeric data in R are considered.
The result will be interpolated if N is not a multiple
of 1/(s-1), where s is the size of R.
R - A range of data
N - A numeric value between 0 and 1, inclusive.
@PERCENTILE(A1..A4, 0.4) = 14.2
where A1..A4 = {23, 4, 12, 67}
@PERCENTRANK(R,D[,S])
Returns the percentile rank of the datum D in range R.
If D does not match one of the values in R, the
function interpolates to return the correct percentage rank.
R - Range of numeric data
D - Datum to find
S = Number of significant digits
@PERCENTRANK(A1..A10, 4) = 0.33
where A1..A10 = {1,2,3,4,5,6,7,8,9,10}.
@PERMUT(S,T) Returns "S choose T", or the number of T objects that
can be chosen from the set S, where order is significant.
S - Number of objects to choose from
T - Number of objects to be chosen
@PERMUT(100, 3) = 970200
@PTTEST(R1,R2) Returns the significance level (alpha) of the two-sided T-test
for the paired samples contained in ranges R1 and R2
R1 and R2 must have the same size.
@QUARTILE(R,Q) Finds the quartile Q of the data in range R.
Equivalent to @PERCENTILE(R, Q/4)
R = Range of cells
Q = Quartile as follows:
0 Minimum value
1 First quartile (25th percentile)
2 Second quartile (50th percentile)
3 Third quartile (75th percentile)
4 Maximum value
@QUARTILE(A1..B4, 2) = 7.5
where A1..B4 = {1,2,4,7,8,9,10,12}
@RANK(E,R,[O]) Returns the rank of a numeric argument E in the
range R. The rank of a number is its size relative to
other values in the list, which is equivalent to it's
position in the list after the list is sorted. RANK
gives duplicate numbers the same rank. The presence of
duplicate numbers will affect the ranks of subsequent
numbers. For examples, if there are two rank of 3,
the next rank will be 5.
E - a numeric value whose rank you want to find
R - a reference to a range of values. Non-numeric
values will be ignored.
O - a numeric value specifying the way the numbers to
be ranked. RANK ranks E as if R is in descending order
if O is 0 or omitted. Otherwise, it ranks E as if
R is in ascending order.
@RANK(5, C2..C6) = 2
@RANK(5, C2..C6, 1) = 3
@RANK(7, C2..C6, 1) = 5
where C2..C6 = {2,7,5,1,5}
@RMS(...) Returns the square root of the mean of squares of its
arguments, which may be any combination of numbers, cells,
and ranges. Blank cells and cells containing strings
are not counted.
@SMALL(R,N) Returns the Nth smallest number in range R.
@SMALL(A1..A10,3) = 7
where A1..A10={38,3,9,45,7,90,17,2,75,12}
@SSE(...) Returns the sum squared error of its arguments. This function
equivalent to @VAR(...)/@COUNT(...)
argumentlist - any combination of numbers, cells, or ranges
@SSQ(...) Returns the sum of squares of its arguments
argumentlist - any combination of numbers, cells, or ranges
@STD(...) Returns the population standard deviation (N weighting) of its
arguments
argumentlist - any combination of numbers, cells, or ranges
@STDS(...) Returns the population standard deviation (N-1 weighting) of
its arguments
argumentlist - any combination of numbers, cells, or ranges
@SUM(...) Returns the sum of its arguments
argumentlist - any combination of numbers, cells, or ranges
@T(N,T) Returns the integral of Student's T-distribution with
N degrees of freedom from minus infinity to T.
N, T - numeric values
@TTEST(R,X) Returns the significance level (alpha) of the two-sided single
population T-test for the population samples contained
in range R
R - a range
X - a numeric value
@TTEST2EV(R1,R2)
Returns the significance level (alpha) of the two-sided dual
population T-test for ranges R1 and R2, where their
variances are equal
@TTEST2UV(R1,R2)
Returns the significance level (alpha) of the two-sided single
population T-test for ranges R1 and R2, where their
variances are not equal
@VAR(...) Returns the population variance (N weighting) of its arguments
argumentlist - any combination of numbers, cells, or ranges
@VARS(...) Returns the population variance (N-1 weighting) of its
arguments argumentlist - any combination of numbers,
cells, or ranges
@VSUM(...) Returns the "visual sum" of its arguments, using precision and
rounding of formatted cell values
argumentlist - any combination of numbers, cells, or ranges
Operate much like statistical functions, except that the last argument is a constraint expression that Xess evaluates for each cell in the argument list. Only cells that meet constraint criteria are included in the calculation. Conditional Statistical return a numeric value.
@CAVG(...,C) Returns the conditional average (mean) of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CCOUNT(...,C) Returns a conditional count of its non-blank arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CMAX(...,C) Returns the conditional maximum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CMIN(...,C) Returns the conditional minimum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSTD(...,C) Returns the conditional standard deviation (N weighting) of
its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSTDS(...,C) Returns the conditional sample deviation (N-1 weighting)
of its arguments.
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CSUM(...,C) Returns the conditional sum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CVAR(...,C) Returns the conditional population variance (N weighting)
of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
@CVARS(...,C) Returns the conditional sample variance (N-1 weighting)
of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression
Manipulate and evaluate character strings. For example, string functions can return the length of a string, find the first occurrence of a string in a range, change a string from upper to lower case and vice versa, or replace one string with another.
@CHAR(N) Returns the character represented by N in the ASCII
character set.
N - an integer value between 1 and 255.
@CLEAN(S) Returns a "printable" representation of string S by
stripping out unprintable characters.
@CODE(S) Returns the ASCII code for the first character in string S.
S - a string value
@EXACT(S1,S2) Returns 1 if S1 exactly matches string S2; otherwise, 0
S1, S2 - string values
@FIND(S1,S2,N) Returns the index of the first occurrence of S1 in S2
S1, S2 - String values
N - a numeric value
@FORMAT(F,N,X) Returns the string formed by formatting the value X using
the Xess format code F and precision N
F - Xess format code
N - the precision level, from 0 to 15
X - a numeric value
@HEXTONUM(S) Returns the numeric value for the hexadecimal interpretation
of S.
S - a string value
@LEFT(S,N) Returns the string composed of the leftmost N characters of S
If N is greater than or equal to the length of S, then the
entire string S is returned
S - a string value
N - a numeric value
@LENGTH(S) Returns the number of characters in S
S - a string value
@LOWER(S) The string S is converted to lower case
S - a string value
@MID(S,N1,N2) Returns the string of length N2 that starts at position N1 in S.
S - a string value
@NUMTOHEX(X) Returns the hexadecimal representation of the integer
portion of X, for -2147483648 <= X <= 2147483647.
X - a numeric value
@PROPER(S) Returns a copy of string S with the first letter of
every word capitalized.
@REGEX(R,S) Returns True(1) if the strings R and S match, and
and False (0) otherwise. This function is a regular
expression pattern matcher and is similar to @EXACT(S1,S2)
function, except @REGEX allows "wildcard" comparisons.
R - A string representing a regular expression
S - A string
@REGEX("t.*e", "table") = True
@REGEX("F[0-9]", "F3") = True
@REPEAT(S,N) Returns a string containing N copies of string S.
@REPLACE(S1,N1,N2,S2)
Returns the string formed by replacing the N2
characters starting at position N1 in string
S1 with string S2.
@REPLACE("Expense Report",8,3,"Log") = "Expense Logort"
@REPLACE("Expense Report",8,6,"Log") = "Expense Log"
@REPLACE("ABC",8,3,"XYZ") = "XYZABC"
@RIGHT(S,N) Returns the string composed of the rightmost N
characters of S. If N is greater than or
equal to the length of S, then the entire string
S is returned.
@STRCAT(...) Returns the concatenation of all its arguments.
argumentlist - any combination of numbers, cells, or ranges
@STRING(X,N) Returns the string representing the numeric value of X,
to N decimal places
X - a numeric value
N - a numeric value between 0 and 15
@STRLEN(...) Returns the total length of all strings in its arguments
argumentlist - any combination of numbers, cells, or ranges
@TRIM(S) Returns the string formed by removing leading, trailing,
and consecutive embedded spaces from string S.
@UPPER(S) Returns the string S converted to upper case.
S - a string value
@VALUE(S) Returns the numeric value represented by the string S
or 0 if S does not represent a number.
S - a string value
Return one value if an argument meets certain criteria, another value if it does not. Logical functions are used as an adjunct to conditional statements, where the outcome is based on the validity of a statement.
@FALSE Returns the logical value 0
@FILEEXISTS(S) Returns 1 if file S can be opened to read; otherwise 0
S - a string value
@IF(X,T,F) Returns the value of T if X evaluates to non-zero,
or F if X evaluates to zero
X - numeric value
T,F - numeric or string values, cell or range references
@ISERROR(X) Returns 1 if X "contains" an error, otherwise 0.
X is an expression, which may include references to
other cells. X "contains" an error if the expression
causes an error, or if it refers to any cells which
have errors.
@ISERROR(3/0) = 1
@ISERROR(A1) = 1 where A1 contains an error value
@ISERROR(@ISERROR(3/0)) = 0
@ISNUMBER(X) Returns 1 if X is a numeric value; otherwise 0
X - a numeric or a string value
@ISSTRING(X) Returns 1 if X is a string value; otherwise 0
X - a numeric or a string value
@TRUE Returns the logical value 1 (true).
Return the values 0, 1 or -1 (unknown) based on the value of its arguments. Digital logic functions evaluate the integer portion of a value. Any value not equal to 0 or 1 is considered unknown.
@AND(...) Returns 1 if all arguments are 1; 0 if any arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges
@NAND(...) 0 if all arguments are 1; 1 if any arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges
@NOR(...) 0 if any arguments are 1; 1 if all arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges
NOT(X) Returns 1 if X=0; 0 if X=1; otherwise -1
argumentlist - any combination of numbers, cells or ranges
X - a numeric value
@OR(...) Logical OR function. Returns 1 if any argument in
the list equals 1, 0 if not, and -1 if there is any
argument in the list not equal to 0 or 1.
@XOR(...) Returns -1 if any arguments are UNKNOWN;
1 if the total number of arguments with the value 1 is odd;
0 if the total number of arguments with the value 1 is even.
argumentlist - any combination of numbers, cells or ranges
Perform common financial calculations, such as calculating the future value of an annuity at a given interest rate, straight-line depreciation, depreciation, or the payment term for a given investment. Xess also includes is a set of financial trading functions which compute things like annuities, bonds, coupons, and Treasury Bills.
The set of functions which are used in financial trading 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,
@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
@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