Try TT Now

Types of Formulas

Formulas must begin with an equal (=) sign and conform to the syntax:

= expression; constraint expression // comment

where:

  • expression defines the calculations needed to generate the value of a cell
  • constraint expression places limits on acceptable values or the circumstances under which the calculation should take place. Constraint expressions establish conditions under which a formula operates or boundaries for valid results of the formula. A constraint expression can reference the cell in which it resides, using the symbol #
  • comment is any text you want to attach to the cell. This text is not used in the formula.

Example

 =A1 + A2 ; #>2 && #<=B5 || #==C7

In this example, the value of the current cell is the sum of cells A1 and A2. That value must be either greater than 2 and less than or equal to the value of cell B5, or equal to the value of cell C7.

Use the following formulas with Autotrader to help define its trading behavior:

Mathematical Formulas

FormulaDescription

@ABS(X)

Absolute value of X

@ACOS(X)

Arc cosine of X

@ASIN(X)

Arc sine of X

@ATAN(X)

2-quadrant arc tangent of X

@ATAN2(X, Y)

4-quadrant arc tangent of Y/X

@CEIL(X)

Smallest integer greater than or equal to X

@COS(X)

Cosine of X

@COSH(X)

Hyperbolic cosine of X

@DEGREES(X)

Converts the angle expressed in radians to degrees ( )

@DET(M)

Determinant of the matrix range M, which must be a square matrix

@DOT(R1, R2)

Dot product of the vectors R1 and R2

@EXP(X)

-e raised to the X power

@FACT(N)

Value of N!

@FLOOR(X)

Largest integer less than or equal to X

@FRAC(X)

Fractional portion of X

@GAMMA(X)

Value of the gamma function evaluated at X

@GRAND

12th-degree binomial approximation to a Gaussian random number with zero mean and unit variance

@INT(X)

Integer portion of X

@LN(X)

Natural log (base e) of X

@LNGAMMA(X)

Log base e of the gamma function evaluated at X

@LOG(X)

Log base of X

@LOG10(X)

Log base 10 of X

@LOG2(X)

Log base 2 of X

@MOD(X, Y)

Remainder of X/Y

@MODULUS(X, Y)

Modulus of X/Y

@PI

Value of p

@POLY(X, ...)

Value of an Nth-degree polynomial in X

@PRODUCT(X, ...)

Product of all the numeric values in the argument list

@RADIANS(X)

Converts the angle expressed in degrees to radians ( )

@RAND

Uniform random number on the interval [0,1)

@ROUND(X, n)

X rounded to n number of decimal places (0 to 15)

@SIGMOID(X)

Value of the sigmoid function

@SIN(X)

Sine of X

@SINH(X)

Hyperbolic sine of X

@SQRT(X)

Positive square root of X

@SUMPRODUCT(R1, R2)

Dot product of the vectors R1 and R2, where R1 and R2 are of equal dimension

@TAN(X)

Tangent of X

@TANH(X)

Hyperbolic tangent of X

@TRANSPOSE(M)

Transpose of matrix M

@VECLEN(...)

Square root of the sum of squares of its arguments

Statistical Formulas

FormulaDescription

@AVG(...)

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(...)

Count of its non-blank arguments

@F(M, N, F)

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)

Significance level ( ) of the two-sided F-test on the variances of the data specified by ranges R1 and R2

@GMEAN(...)

Geometric mean of its arguments

@HMEAN(...)

Harmonic mean of its arguments

@LARGE(R, N)

Nth largest value in range R

@MAX(...)

Maximum of its arguments

@MEDIAN(...)

Median (middle value) of the range R1

@MIN(...)

Minimum of its arguments

@MODE(...)

Mode, or most frequently occurring value

@MSQ(...)

Mean of the squares of its arguments

@PERCENTILE(R, N)

Value from the range R which is at the Nth percentile in R

@PERCENTRANK(R, N)

Percentile rank of the number N among the values in range R

@PERMUT(S, T)

Number of T objects that can be chosen from the set S, where order is significant

@PTTEST(R1, R2)

Significance level ( ) of the two-sided T-test for the paired samples contained in ranges R1 and R2

@QUARTILE(R, Q)

Quartile Q of the data in range R

@RANK(E, R[, O])

Rank of a numeric argument E in the range R

@RMS(...)

Root of the mean of squares of its arguments

@SMALL(R, N)

Nth smallest number in range R

@SSE(...)

Sum squared error of its arguments. It is equivalent to @VAR(...)x@COUNT(...)

@SSQ(...)

Sum of squares of its arguments

@STD(...)

Population standard deviation (N weighting) of its arguments

@STDS(...)

Sample standard deviation (N-1 weighting) of its arguments

@SUM(...)

Sum of its arguments

@T(N, T)

Integral of T-distribution with N degrees of freedom from minus infinity to T

@TTEST(R, X)

Significance level (a) of the two-sided single population T-test for the population samples contained in range R

@TTEST2EV(R1, R2)

Significance level (a) of the two-sided dual population T-test for ranges R1 and R2, where the population variances are equal

@TTEST2UV(R1, R2)

Significance level (a) of the two-sided dual population T-test for ranges R1 and R2, where the population variances are not equal

@VAR(...)

Sample variance (N weighting) of its arguments

@VARS(...)

Sample variance (N-1 weighting) of its arguments

@VSUM(...)

Visual sum of its arguments, using precision and rounding of formatted cell values

String Functions

FormulaDescription

@CHAR(N)

Character represented by the code N

@CLEAN(S)

String formed by removing all non-printing characters from the string S

@CODE(S)

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)

Index of the first occurrence of S1 in S2

@HEXTONUM(S)

Numeric value for the hexadecimal interpretation of S

@LEFT(S, N)

String composed of the leftmost N characters of S

@LENGTH(S)

Number of characters in S

@LOWER(S)

S converted to lower case

@MID(S, N1, N2)

String of length N2 that starts at position N1 in S

@NUMTOHEX(X)

Hexadecimal representation of the integer portion of X

@PROPER(S)

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)

String S repeated N times

@REPLACE(S1, N1, N2, S2)

String formed by replacing the N2 characters starting at position N1 in S1 with string S2

@RIGHT(S, N)

String composed of the rightmost N characters of S

@STRCAT(...)

Concatenation of all its arguments

@STRING(X, N)

String representing the numeric value of X, to N decimal places

@STRLEN(...)

Total length of all strings in its arguments

@TRIM(S)

String formed by removing spaces from the string S

@UPPER(S)

String S converted to upper case

@VALUE(S)

Numeric value represented by the string S; otherwise 0 if S does not represent a number

Logic Functions

FormulaDescription

@AND(...)

0 if any arguments are 0; 1 if all arguments are 1; otherwise -1

@FALSE

Logical value 0

@FILEEXISTS(S)

1 if file S can be opened for reading; otherwise 0

@IF(X, T, F)

Value of T if X evaluates to on-zero, or F if X evaluates to zero

@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

@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

@TRUE

Logical value 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

Financial Functions

FormulaDescription

@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

@COUPDAYBS(S, M, F[, B])

Number of days between the beginning of the coupon period to the settlement date

@COUPDAYS(S, M, F[, B])

Number of days in the coupon period that the settlement date is in

@COUPDAYSNC(S, M, F[, B])

Number of days between the settlement date and the next coupon date

@COUPNCD(S, M, F[, B])

Next coupon date after the settlement date

@COUPNUM(S, M, F[, B])

Number of coupon payments between the settlement date and maturity date

@COUPPCD(S, M, F[, B])

Previous (most recent) coupon date before the settlement date

@CTERM(R, FV, PV)

Number of compounding periods for an investment

@CUMIPMT(R, NP, PV, S, E, T)

Cumulative interest on a loan between start period S and end period E

@CUMPRINC(R, NP, PV, S, E, T)

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])

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])

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)

Future value of an initial investment after compounding a series of interest rates

@INTRATE(S, M, I, R[, B])

Interest rate for a fully invested security

@IPMT(R, P, NP, PV, FV[, T])

Interest payment for a specific period for an investment based on periodic, constant payments and a constant interest rate

@IRR(G, F)

Internal rate of return on an investment. See also @XIRR and @MIRR

@MDURATION(S, M, R, Y, F[, B])

Modified Macauley duration of a security assuming $100 face value

@MIRR(CF, FR, RR)

Modified internal rate of return for a series of periodic cash flows

@NOMINAL(ER, NP)

Nominal annual interest rate

@ODDFPRICE(S, M, I, FC, R, Y, RD, F[, B])

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])

Yield per of a security with an odd (short or long) first period

@PMT(PV, R, N)

Periodic payment for a loan

@PPMT(R, P, NP, PV, FV, T)

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])

Price per $100 face value of a security that pays periodic interest

@PRICEDISC(S, M, D, RD[, B])

Price per $100 face value of a discounted security

@PRICEMAT(S, M, I, R, Y[, B])

Price per $100 face value of a security that pays interest at maturity

@PV(P, R, N)

Present value of an annuity

@RATE(FV, PV, N)

Interest rate required to reach future value FV

@RECEIVED(S, M, I, D, [, B])

Amount received at maturity for a fully vested security

@SLN(C, S, L)

Straight-line depreciation allowance

@SYD(C, S, L, N)

"Sum-of-years-digits" depreciation allowance

@TBILLEQ(S, M, D)

Bond-equivalent yield (BEY) for a Treasury Bill

@TBILLYIELD(S, M, D)

Yield on a Treasury bill

@TERM(P, R, FV)

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

Date and Time Functions

FormulaDescription

@DATE(Y, M, D)

Date value for year Y, month M, and day D

@DATEVALUE(S)

Corresponding date value for a given string S

@DAYS360(S, E)

Number of days between two dates, based on a 30/360 day count system

@DAY(DT)

Day number in the date/time value DT

@EDATE(S, M)

Date/time value representing number of months (M) before or after start date (S)

@EOMONTH(S, M)

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)

Hour value (0-23) of date/time value DT

@MINUTE(DT)

Minute value (0-59) of date/time value DT

@MONTH(DT)

Number of the month in date/time value DT

@NETWORKDAYS(S, E[, H])

Number of whole working days, starting at S and going to E, excluding weekends and holidays

@NOW

Date/time value of the current system date and time

@SECOND(DT)

Seconds value (0-59) of the date/time value DT

@TIME(H, M, S)

Time value for hour H, minute M, and second S

@TIMEVALUE(S)

Corresponding time value for a given string value S

@TODAY

Date value of the current system date

@WEEKDAY(D)

Integer representing the day of the week on which the day D falls starting at 1 (Sunday) and ending with 7 (Saturday)

@WORKDAY(S, D[, H])

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)

Year value of date/time value DT

@YEARFRAC(S, E[, B])

Portion of the year represented by the number of days between start date (S) and end date (E)

Miscellaneous Functions

Note: Some functions may return a result that is a range or cell reference. These indirect references are not used in determining the pattern of recalculation. Plan carefully before using these functions.

FormulaDescription

@CELLREF(N1, N2)

Reference to the cell in column N1 and row N2

@CHOOSE(N, ...)

Nth argument from the list

@COL(C)

Column address of the cell referenced by C

@COLS(R)

Number of columns in the specified range R

@HLOOKUP(X, S, R)

Value of the cell in range S that is R number of rows beneath X

@INIT(X1, X2)

First argument on the first recalculation pass and the second argument on all subsequent recalculation passes when is set to perform iterative calculations

@INTERP2D(R1, R2, N)

Interpolation value for a 2-dimensional vector

@INTERP3D(R, X, Y)

Interpolation value for a 3-dimensional vector

@MATCH(V, R[, T])

Relative position in range R of value V based on positioning criteria T

@N(R)

Numeric value of the top left cell in range R

@RANGEREF(N1, N2, N3, N4)

Reference to the range defined by coordinates N1 through N4

@ROW(C)

Row address of the cell referenced by C

@ROWS(R)

Number of rows in the specified range R

@S(R)

String value of the top left cell in range R

@VLOOKUP(X, S, C)

Value of the cell in range S that is C number of columns to the right of X

Embedded Tools

Note: You should not include embedded tools within other functions or arithmetic operations in a single formula. However, you can copy, move, and format embedded tools just like any other function.

FormulaDescription

@DFT(R)

Discrete Fourier Transform of the range R

@EIGEN(M)

Eigen values of the matrix M

@FFT(R)

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)

Inverse of the Discrete Fourier Transform of the range R

@INVERT(M)

Inverse of matrix M

@INVFFT(R

Inverse of the Discrete Fourier Transform of the range R using a fast Fourier Transform algorithm.

@LINFIT(X, Y)

Straight line least squares fit. This function is equivalent to @POLYFIT(X, Y, 1)

@LLS(A, Y)

Linear least squares solution X to the over-determined system of equations AX=Y

@MMUL(M1, M2)

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)

Least squares coefficients for the polynomial fit Y=P(X), where P is a polynomial of degree d

@TRANSPOSE(M)

Transpose of matrix M

@TREND(NX, KX, KY)

Y values for new x values given existing x and y values