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. |
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. |
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). |
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. |
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. |
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. |
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). |
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 for .NET 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. |
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. |