Access Keys:
Skip to content (Access Key - 0)

What Xess functions are available?

A list of functions is available in Xess and information on how to use them are available by clicking on "Help" within Xess. A list is also included here.

@@(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(X,Y)       (for all version prior to Xess 3.1.2b)
                The linear least squares solution X to the overdetermined
                system of equations AX=Y.
                X - a range representing the coefficient matrix which
                must intercept the origin (rightmost column of X must
                be all 1's)
                Y - a column vector of values for the dependent variable

@LLS(X,Y,flag)  (Xess 3.1.2b and later)
                The linear least squares solution X to the overdetermined
                system of equations AX=Y.
                X - a range representing the coefficient matrix
                Y - a column vector of values for the dependent variable
                flag =   1   (X data intercepts origins)
                         0   (X data does not intercept the origin)

@LN(X)          Returns the log base e of X
                X - a numeric value greater than 0

@LNGAMMA(X)     Returns the log base e of the gamma function evaluated at X
                X - a numeric value greater than 0

@LOG(X)         Returns the log of X
                X - a numeric value greater than 0

@LOG10(X)       Returns the log base 10 of X
                X - a numeric value greater than 0

@LOG2(X)        Returns the log base 2 of X
                X - a numeric value greater than 0

@LOWER(S)       The string S is converted to lower case
                S - a string value

@MATCH(V,R[,T]) Returns the relative position in range R of
                value V in a specific way.
                V - the value to be matched.  V can be a string,  a
                    numeric value.  If V is a string, T must be 0.  MATCH
                    allows regular expression type "wildcard".
                T - match type
                    T > 0 means find largest value in R <= V
                    T = 0 or omitted means find first value in R = V
                    T < 0 means find smallest value in R >= V

@MAX(...)       Returns the maximum of its arguments
                argumentlist - any combination of numbers, cells, or ranges.

@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)     (for all version prior to Xess 3.1.2b)
                analyzes the least squares polynomial model. The output is
                identical to that of @LLS, with the polynomial coefficients
                listed in the order of decreasing degree.
                X - a range representing a row or column vector of independent
                    variable values which must intercept the origin
                Y - a range representing a row or column vector of dependent
                    variable values.
                d - polynomial degree in range 1 - 10

@PLS(X,Y,d,[,flag])  (Xess 3.1.2b and later)
                analyzes the least squares polynomial model. The output is
                identical to that of @LLS, with the polynomial coefficients
                listed in the order of decreasing degree.
                X - a range representing a row or column vector of independent
                    variable values.
                Y - a range representing a row or column vector of dependent
                    variable values.
                d - polynomial degree in range 1 - 10
                flag - omitted/1     X data intercepts the origin
                       0             X data does not intercept the origin

@PMT(P,R,N)     Returns the periodic payment for a loan, given the
                present value P and interest rate R, and the number
                of periods N.
                P,R,N - Numeric values

@POLY(X,...)    Returns the value of the Nth degree polynomial
                in X, where N is the number of data in the argument
                list.  The argument list may contain numbers and
                cell references, including ranges.  Any non-numeric
                argument causes an error.
                @POLY(2,3,4,5) = (3*(2**2)+4*2+5) = 25

@POLYCOEF(X,Y,d)
                Returns the least squares coefficients for the polynomial fit.
                X - a range representing a row or column vector of independent
                variable values.
                Y - a range representing a row or column vector of dependent
                variable values.
                d - polynomial degree in the range 1 to 10.

@POLYFIT(X,Y,d) Returns the least squares polynomial fit.
                X - a column vector of values for the independent variable.
                Y - a column vector of values for the dependent variable.
                d - the polynomial degree in the range of 1 to 10.

@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 or a string containing a cell
                reference; you can specify a cell reference from the other
                sheet as well by using a cell name enclosed in quotes.

@XHLOOKUP(F,X,R,N)
                Searches the first row (known as the index row) in range R
                in file F for the numeric or string value which "matches"
                X, and returs the value N rows below in the column
                where the match is found.  The index row is searched from
                left to right.  If X is a string value, an exact match must
                be found or an error is returned.  If X is a numeric value,
                the matching column is determined by the following rules:
                1) Strings and blank cells in the index row are ignored.
                2) If the first value in the index row is greater than X,
                   an error is returned.
                3) Searching stops when a numeric value which is greater than
                   or equal to X is found in the index row.  If the value
                   found is greater than X, the preceding column is the
                   matching column.
                4) If there are no numeric values in the index row which are
                   greater than or equal to X, the last column in R is
                   considered to be the matching column.

@XVLOOKUP(F,X,R,N) Searches the first column (known as the index column)
                in range R in spreadsheet file F for the numeric or string
                value which "matches" X, and returns the value N columns to
                the right in the row where the match is found.

                The index column is searched from top to bottom.  If X is
                a string value, an exact match must be found or an error
                is returned.  If X is a numeric value, the matching row is
                determined by the following rules:
                1) Strings and blank cells in the index column are ignored.
                2) If the first value in the index column is greater than X,
                   an error is returned.
                3) Searching stops when a numeric value which is greater than
                   or equal to X is found in the index column.  If the value
                   found is greater than X, the preceding row is the matching
                   row.
                4) If there are no numeric values in the index column which are
                   greater than or equal to X, the last row in R is considered
                   to be the matching row.

@YEAR(DT)       Returns the four-digit year value of a date/time value DT.
                DT - a date/time value

@YEARFRAC(S,E[,B])
                Returns the year fraction representing the number of
                whole days between S (start date) and E (end date)
                S - a date value that represents the start date
                E - a date value that represents the end date
                B - the day count basis to be used
                    0 or omitted        30/360
                    1                   actual/actual
                    2                   actual/360
                    3                   actual/365

@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

IS&T Contributions

Documentation and information provided by IS&T staff members


Last Modified:

February 25, 2009

Get Help

Request help
from the Help Desk
Report a security incident
to the Security Team
Labels:
olc-xess olc-xess Delete
xess xess Delete
function function Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
Feedback
This product/service is:
Easy to use
Average
Difficult to use

This article is:
Helpful
Inaccurate
Obsolete
Adaptavist Theme Builder (4.2.3) Powered by Atlassian Confluence 3.5.13, the Enterprise Wiki