The following table lists the mathematical functions that are supported.
| Function | Description |
| @ABS(X) | The absolute value of X. |
| @ACOS(X) | The arc cosine of X. |
| @ASIN(X) | The arc sine of X. |
| @ATAN(X) | The 2-quadrant arc tangent of X. |
| @ATAN2(X, Y) | The 4-quadrant arc tangent of Y/X. |
| @CEIL(X) | The smallest integer greater than or equal to X. |
| @COS(X) | The cosine of X. |
| @COSH(X) | The hyperbolic cosine of X. |
| @DEGREES(X) | Converts the angle expressed in radians to degrees ( ). |
| @DET(M) | The determinant of the matrix range M, which must be a square matrix. |
| @DOT(R1, R2) | The dot product of the vectors R1 and R2. |
| @EXP(X) | e raised to the X power. |
| @FACT(N) | The value of N!. |
| @FLOOR(X) | The largest integer less than or equal to X. |
| @FRAC(X) | The fractional portion of X. |
| @GAMMA(X) | The value of the gamma function evaluated at X. |
| @GRAND | A 12th-degree binomial approximation to a Gaussian random number with zero mean and unit variance. |
| @INT(X) | The integer portion of X. |
| @LN(X) | The natural log (base e) of X. |
| @LNGAMMA(X) | The log base e of the gamma function evaluated at X. |
| @LOG(X) | The log base 10 of X. |
| @LOG10(X) | The log base 10 of X. |
| @LOG2(X) | The log base 2 of X. |
| @MOD(X, Y) | The remainder of X/Y. |
| @MODULUS(X, Y) | The modulus of X/Y. |
| @PI | The value of pi. |
| @POLY(X, ...) | The value of an Nth-degree polynomial in X. |
| @PRODUCT(X, ...) | The product of all the numeric values in the argument list. |
| @RADIANS(X) | Converts the angle expressed in degrees to radians ( ). |
| @RAND | A uniform random number on the interval (0,1). |
| @ROUND(X, n) | X rounded to n number of decimal places (0 to 15). |
| @SIGMOID(X) | The value of the sigmoid function. |
| @SIN(X) | The sine of X. |
| @SINH(X) | The hyperbolic sine of X. |
| @SQRT(X) | The positive square root of X. |
| @SUMPRODUCT(R1, R2) | The dot product of the vectors R1 and R2, where R1 and R2 are of equal dimension. |
| @TAN(X) | The tangent of X. |
| @TANH(X) | The hyperbolic tangent of X. |
| @TRANSPOSE(M) | The transpose of matrix M. |
| @VECLEN(...) | The square root of the sum of squares of its arguments. |
The following table lists statistical functions that are supported.
| Function | Purpose |
| @AVG(...) | The average (arithmetic mean) of its arguments. |
| @CORR(R1, R2) | Pearson's product-moment correlation coefficient for the paired data in ranges R1 and R2. |
| @COUNT(...) | A count of its non-blank arguments. |
| @F(M, N, F) | The integral of Snedecor's F-distribution with M and N degrees of freedom from minus infinity to F. |
| @ERF(L[, U]) | Error function integrated between 0 and L; if U specified, between L and U. |
| @ERFC(L) | Complementary error function integrated between L and infinity. |
| @FORECAST(...) | Predicted Y values for given X. |
| @FTEST(R1, R2) | The significance level ( ) of the two-sided F-test on the variances of the data specified by ranges R1 and R2. |
| @GMEAN(...) | The geometric mean of its arguments. |
| @HMEAN(...) | The harmonic mean of its arguments. |
| @LARGE(R, N) | The Nth largest value in range R. |
| @MAX(...) | The maximum of its arguments. |
| @MEDIAN(...) | The median (middle value) of the range R1. |
| @MIN(...) | The minimum of its arguments. |
| @MODE(...) | The mode or most frequently occurring value. |
| @MSQ(...) | The mean of the squares of its arguments. |
| @PERCENTILE(R, N) | The value from the range R that is at the Nth percentile in R. |
| @PERCENTRANK(R, N) | The percentile rank of the number N among the values in range R. |
| @PERMUT(S, T) | The number of T objects that can be chosen from the set S, where order is significant. |
| @PTTEST(R1, R2) | The significance level ( ) of the two-sided T-test for the paired samples contained in ranges R1 and R2. |
| @QUARTILE(R, Q) | The quartile Q of the data in range R. |
| @RANK(E, R[, O]) | The rank of a numeric argument E in the range R. |
| @SSQ(...) | The sum of squares of its arguments. |
| @RMS(...) | The root of the mean of squares of its arguments. |
| @SMALL(R, N) | The Nth smallest number in range R. |
| @SSE(...) | The sum squared error of its arguments. It is equivalent to @VAR(...) @COUNT(...). |
| @STD(...) | The population standard deviation (N weighting) of its arguments. |
| @STDS(...) | The sample standard deviation (N-1 weighting) of its arguments. |
| @SUM(...) | The sum of its arguments. |
| @T(N, T) | The integral of Student's T-distribution with N degrees of freedom from minus infinity to T. |
| @TTEST(R, X) | The significance level of the two-sided single population T-test for the population samples contained in range R. |
| @TTEST2EV(R1, R2) | The significance level ( ) of the two-sided dual population T-test for ranges R1 and R2, where the population variances are equal. |
| @TTEST2UV(R1, R2) | The significance level ( ) of the two-sided dual population T-test for ranges R1 and R2, where the population variances are not equal. |
| @VAR(...) | The sample variance (N weighting) of its arguments. |
| @VARS(...) | The sample variance (N-1 weighting) of its arguments. |
| @VSUM(...) | The visual sum of its arguments, using precision and rounding of formatted cell values. |
The following table lists conditional statistical functions that are supported.
| Function | Purpose |
| @CAVG(..., C) | Conditional average. |
| @CCOUN(..., C) | Conditional count. |
| @CMAX(..., C) | Conditional maximum. |
| @CMIN(..., C) | Conditional minimum. |
| @CSTD(..., C) | Conditional sample standard deviation (N weighting). |
| @CSTDS(..., C) | Conditional sample standard deviation (N-1 weighting). |
| @CSUM(..., C) | Conditional sum. |
| @CVAR(..., C) | Conditional population variance (N weighting). |
| @CVARS(..., C) | Conditional population variance (N-1 weighting). |
The following table lists string functions that are supported.
| Function | Purpose |
| @CHAR(N) | The character represented by the code N. |
| @CLEAN(S) | The string formed by removing all non-printing characters from the string S. |
| @CODE(S) | The ASCII code for the first character in string S. |
| @EXACT(S1, S2) | Returns true (1) if string S1 exactly matches string S2, otherwise returns 0. |
| @FIND(S1, S2, N) | The index of the first occurrence of S1 in S2. |
| @HEXTONUM(S) | The numeric value for the hexadecimal interpretation of S. |
| @LEFT(S, N) | The string composed of the leftmost N characters of S. |
| @LENGTH(S) | The number of characters in S. |
| @LOWER(S) | S converted to lower case. |
| @MID(S, N1, N2) | The string of length N2 that starts at position N1 in S. |
| NUMTOHEX(X) | The hexadecimal representation of the integer portion of X. |
| @PROPER(S) | The string S with the first letter of each word capitalized. |
| @REGEX(S1, S2) | Returns true (1) if string S1 exactly matches string S2; otherwise returns false (0). Allows "wildcard"' comparisons by interpreting S1 as a regular expression. |
| @REPEAT(S, N) | The string S repeated N times. |
| @REPLACE(S1, N1, N2, S2) | The string formed by replacing the N2 characters starting at position N1 in S1 with string S2. |
| @RIGHT(S, N) | The string composed of the rightmost N characters of S. |
| @STRCAT(...) | The concatenation of all its arguments. |
| @STRING(X, N) | The string representing the numeric value of X, to N decimal places. |
| @STRLEN(...) | The total length of all strings in its arguments. |
| @TRIM(S) | The string formed by removing spaces from the string S. |
| @UPPER(S) | The string S converted to upper case. |
| @VALUE(S) | The numeric value represented by the string S; otherwise 0 if S does not represent a number. |
The following table lists the supported logic functions.
| Function | Purpose |
| @FALSE | The logical value 0. |
| @FILEEXISTS(S) | 1 if file S can be opened for reading; otherwise 0. |
| @IF(X, T, F) | The value of T if X evaluates to 1, or F if X evaluates to 0. |
| @ISERROR(X) | Returns 1 if X "contains" an error, otherwise 0. |
| @ISNUMBER(X) | 1 if X is a numeric value; otherwise 0. |
| @ISSTRING(X) | 1 if X is a string value; otherwise 0. |
| @TRUE | The logical value 1. |
| @AND(...) | 0 if any arguments are 0; 1 if all arguments are 1; otherwise -1. |
| @NAND(...) | 0 if all arguments are 1; 1 if any arguments are 0; otherwise -1. |
| @NOR(...) | 0 if any arguments are 1; 1 if all arguments are 0; otherwise -1. |
| @NOT(X) | 0 if X=1; 1 if X=0; otherwise -1. |
| @OR(...) | 0 if all arguments are 0; 1 if any arguments are 1; otherwise -1. |
| @XOR(...) | -1 if any of the arguments are not 0 or 1; otherwise 0 if the total number of arguments with the value 1 is even; 1 if the total number of arguments with the value 1 is odd. |
The following table lists the supported financial functions.
| Function | Description |
| @COUPDAYBS(S, M, F[, B]) | The number of days between the beginning of the coupon period to the settlement date. |
| @ACCRINT(I, Ft, S, R, P, F[, B]) | Accrued interest for a security that pays periodic interest. |
| @ACCRINTM(I, S, R, P[, B]) | Accrued interest for a security that pays interest at maturity. |
| @COUPDAYS(S, M, F[, B]) | The number of days in the coupon period that the settlement date is in. |
| @COUPDAYSNC(S, M, F[, B]) | The number of days between the settlement date and the next coupon date. |
| @COUPNCD(S, M, F[, B]) | The next coupon date after the settlement date. |
| @COUPNUM(S, M, F[, B]) | The number of coupon payments between the settlement date and maturity date. |
| @COUPPCD(S, M, F[, B]) | The previous (most recent) coupon date before the settlement date. |
| @CTERM(R, FV, PV) | The number of compounding periods for an investment. |
| @CUMIPMT(R, NP, PV, S, E, T) | The cumulative interest on a loan between start period S and end period E. |
| @CUMPRINC(R, NP, PV, S, E, T) | The cumulative principal paid on a loan between start period S and end period E. |
| @DB(C, S, L, P[, M]) | Fixed-declining depreciation allowance. |
| @DDB(C, S, L, N) | Double-declining depreciation allowance. |
| @DISC(S, M, P, R[, B]) | The discount rate for a security. |
| @DOLLARDE(FD, F) | Converts a dollar amount expressed as a fraction form into a decimal form. |
| @DOLLARFR(DD, F) | Converts a dollar amount expressed as a decimal form into a fraction form. |
| @DURATION(S, M, R, Y, F[, B]) | The Macauley duration of a security assuming $100 face value. |
| @EFFECT(NR, NP) | Returns the effective annual interest rate. |
| @FV(P, R, N) | Future value of an annuity. |
| @FVSCHEDULE(P, S) | The future value of an initial investment after compounding a series of interest rates. |
| @INTRATE(S, M, I, R[, B]) | The interest rate for a fully invested security. |
| @IPMT(R, P, NP, PV, FV[, T]) | The interest payment for a specific period for an investment based on periodic, constant payments, and a constant interest rate. |
| @IRR(G, F) | The internal rate of return on an investment. (See also @XIRR and @MIRR.) |
| @MDURATION(S, M, R, Y, F[, B]) | The modified Macauley duration of a security assuming $100 face value. |
| @MIRR(CF, FR, RR) | The modified internal rate of return for a series of periodic cash flows. |
| @NOMINAL(ER, NP) | The nominal annual interest rate. |
| @ODDFPRICE(S, M, I, FC, R, Y, RD, F[, B]) | The price per $100 face value of a security with an odd (short or long) first period. |
| @ODDFYIELD(S, M, I, FC, R, PR, RD, F[, B]) | The yield per of a security with an odd (short or long) first period. |
| @PMT(PV, R, N) | The periodic payment for a loan. |
| @PPMT(R, P, NP, PV, FV, T) | The payment on the principal for a specific period for an investment based on periodic, constant payments, and a constant interest rate. |
| @PRICE(S, M, R, Y, RD, F[, B]) | The price per $100 face value of a security that pays periodic interest. |
| @PRICEDISC(S, M, D, RD[, B]) | The price per $100 face value of a discounted security. |
| @PRICEMAT(S, M, I, R, Y[, B]) | The price per $100 face value of a security that pays interest at maturity. |
| @PV(P, R, N) | The present value of an annuity |
| @RATE(FV, PV, N) | The interest rate required to reach future value FV. |
| @RECEIVED(S, M, I, D, [, B]) | The amount received at maturity for a fully vested security. |
| @SLN(C, S, L) | The straight-line depreciation allowance. |
| @SYD(C, S, L, N) | The "sum-of-years-digits" depreciation allowance. |
| @TBILLEQ(S, M, D) | The bond-equivalent yield (BEY) for a Treasury Bill. |
| @TBILLYIELD(S, M, D) | The yield on a Treasury bill. |
| @TERM(P, R, FV) | The number of payment periods for an investment. |
| @VDB(C, S, L, S, E) | Fixed-declining depreciation allowance between two periods. |
| @XIRR(G, V, D) | Internal rate of return for a series of cash flows with variable intervals. |
| @XNPV(R, V, D) | Returns the net present value for a series of cash flows with variable intervals. |
| @YIELD(S, M, R, PR, RD, F[, B]) | Yield of a security that pays periodic interest. |
| @YIELDMAT(S, M, I, R, PR[, B]) | Annual yield of a security which pays interest at maturity. |
The following table lists the supported date and time functions.
| Function | Description |
| @DATE(Y, M, D) | The date value for year Y, month M, and day D. |
| @DATEVALUE(S) | The corresponding date value for a given string S. |
| @DAYS360(S, E) | The number of days between two dates, based on a 30/360 day count system. |
| @DAY(DT) | The day number in the date/time value DT. |
| @EDATE(S, M) | The date/time value representing number of months (M) before or after start date (S). |
| @EOMONTH(S, M) | The date/time value representing the last day of the month M months after S, if M is positive, or M months before if M is negative. |
| @HOUR(DT) | The hour value (0-23) of date/time value DT. |
| @MINUTE(DT) | The minute value (0-59) of date/time value DT. |
| @MONTH(DT) | The number of the month in date/time value DT. |
| @NETWORKDAYS(S, E[, H]) | The number of whole working days, starting at S and going to E, excluding weekends and holidays. |
| @NOW | The date/time value of the current system date and time. |
| @SECOND(DT) | The seconds value (0-59) of the date/time value DT. |
| @TIME(H, M, S) | The time value for hour H, minute M, and second S. |
| @TIMEVALUE(S) | The corresponding time value for a given string value S. |
| @TODAY | The date value of the current system date. |
| @WEEKDAY(D) | The integer representing the day of the week on which the day D falls. 1 is Sunday, 7 is Saturday. |
| @WORKDAY(S, D[, H]) | The day that is D working days after S, if D is positive, or before S, if D is negative, excluding weekends and all holidays specified as dates in range H. |
| @YEAR(DT) | The year value of date/time value DT. |
| @YEARFRAC(S, E[, B]) | The portion of the year represented by the number of days between start date (S) and end date (E). |
The following table lists miscellaneous supported functions.
| Function | Purpose |
| @CELLREF(N1, N2) | A reference to the cell in column N1 and row N2. |
| @CHOOSE(N, ...) | The Nth argument from the list. |
| @COL(C) | The column address of the cell referenced by C. |
| @COLS(R) | The number of columns in the specified range R. |
| @HLOOKUP(X, S, R) | The value of the cell in range S that is R number of rows beneath X. |
| @INIT(X1, X2) | The first argument on the first recalculation pass and the second argument on all subsequent recalculation passes when Objective Grid is performing iterative calculations. |
| @INTERP2D(R1, R2, N) | The interpolation value for a 2-dimensional vector. |
| @INTERP3D(R, X, Y) | The interpolation value for a 3-dimensional vector. |
| @MATCH(V, R[, T]) | The relative position in range R of value V based on positioning criteria T. |
| @N(R) | The numeric value of the top left cell in range R. |
| @RANGEREF(N1, N2, N3, N4) | A reference to the range defined by coordinates N1 through N4. |
| @ROW(C) | The row address of the cell referenced by C. |
| @ROWS(R) | The number of rows in the specified range R. |
| @S(R) | The string value of the top left cell in range R. |
| @VLOOKUP(X, S, C) | The value of the cell in range S that is C number of columns to the right of X. |
Some Objective Gridfunctions return a result that is a range or cell reference. Objective Grid does not include these indirect references in determining the pattern of recalculation. Plan carefully before using these functions. See Section 14.6.11.3, "Computed Cell References," for more information.
The following table lists supported embedded tools.
| Function | Description |
| @DFT(R) | The Discrete Fourier Transform of the range R. |
| @EIGEN(M) | The eigenvalues of the matrix M. |
| @FFT(R) | The Discrete Fourier Transform of the range R using a fast Fourier Transform algorithm. |
| @FREQUENCY(R, B) | Returns a frequency distribution for values R with a set of intervals B. |
| @INVDFT(R) | The inverse of the Discrete Fourier Transform of the range R. |
| @INVERT(M) | The inverse of matrix M. |
| @INVFFT(R) | The inverse of the Discrete Fourier Transform of the range R using a fast Fourier Transform algorithm. |
| @LINFIT(X, Y) | The straight line least squares fit. This function is equivalent to @POLYFIT(X, Y, 1). |
| @LLS(A, Y) | The linear least squares solution X to the over-determined system of equations AX=Y. |
| @MMUL(M1, M2) | The product of multiplying matrix M2 by matrix M1. |
| @PLS(X, Y, d) | Analyzes the least squares polynomial model Y=P(X), where P is a polynomial of degree d. |
| @POLYCOEF(X, Y, d) | The least squares coefficients for the polynomial fit Y=P(X), where P is a polynomial of degree d. |
| @TRANSPOSE(M) | The transpose of matrix M. |
| @TREND(NX, KX, KY) | The y values for new x values given existing x and y values. |
Embedded tools should not be contained within other functions or arithmetic operations in a single formula. For example, the formula @INVERT(@MMUL(A1..C4,F1..I3)) is not allowed. You may, however, copy, move and format embedded tools just as any other function.
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and Stingray, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.