Formula Syntaxes
You can use functions when you create formulas in your forms.
You can create formulas using functions in the formula section of the cell view. You can use these formulas to run calculations, calculate amounts instead of entering them manually, and customize titles. Cell coordinates in formulas must be written in uppercase. For example, write =ROUND(A2+B3,2), rather than =ROUND(a2+b3,2).
The functions you can include in your formulas are listed in the following table under these categories:
|
Function Category |
Function ID |
Description |
Example Syntax |
|---|---|---|---|
|
Date and Time |
DATE |
Calculates a date and specifies the date and time. |
DATE |
|
Date and Time |
DATEDIF |
Calculates the difference between two dates, in the unit parameter. |
DATEDIF(Date1, Date2, Units) |
|
Date and Time |
DATEVALUE |
Interprets a string as a date. |
DATEVALUE(Datestring) |
|
Date and Time |
DAY |
Returns the day of the given date value. |
DAY(Number) |
|
Date and Time |
DAYS |
Calculates the difference between two date values. |
DAYS(Date2, Date1) |
|
Date and Time |
DAYS360 |
Calculates the difference between two date values in days, based on a 360-day basis. |
DAYS360(Date2, Date1[, Format]) |
|
Date and Time |
EDATE |
Shifts the given start date by a given number of months. |
EDATE(Startdate, Months) |
|
Date and Time |
EOMONTH |
Returns the date of the last day of a month, which falls months away from the start date. |
EOMONTH(Startdate, Months) |
|
Date and Time |
HOUR |
Returns the hour component of a given time. |
HOUR(Time) |
|
Date and Time |
INTERVAL |
Returns the interval string from a given number of seconds. |
INTERVAL(Seconds) |
|
Date and Time |
ISOWEEKNUM |
Returns an ISO week number that corresponds to a week of the year. |
ISOWEEKNUM(Date) |
|
Date and Time |
MINUTE |
Returns the minute component of a given time. |
MINUTE(Time) |
|
Date and Time |
MONTH |
Returns the month for the given date value. |
MONTH(Number) |
|
Date and Time |
NETWORKDAYS |
Returns the number of working days between two given dates. |
NETWORKDAYS(Date1, Date2[, Holidays]) |
|
Date and Time |
NETWORKDAYS.INTL |
Returns the number of working days between two given dates. |
NETWORKDAYS.INTL(Date1, Date2[, Mode [, Holidays]]) |
|
Date and Time |
NOW |
Returns the current date and time. |
NOW() |
|
Date and Time |
SECOND |
Returns the second component of a given time. |
SECOND(Time) |
|
Date and Time |
TIME |
Calculates the time from a given hour, minute, and second. |
TIME(Hour, Minute, Second) |
|
Date and Time |
TIMEVALUE |
Interprets a string as time. |
TIMEVALUE(Timestring) |
|
Date and Time |
TODAY |
Returns the current date. |
TODAY() |
|
Date and Time |
WEEKDAY |
Computes a number between 1-7 representing the day of the week. |
WEEKDAY(Date, Type) |
|
Date and Time |
WEEKNUM |
Returns a week number that corresponds to the week of the year. |
WEEKNUM(Date, Type) |
|
Date and Time |
WORKDAY |
Returns the working day number of days from the start day. |
WORKDAY(Date, Shift[, Holidays]) |
|
Date and Time |
WORKDAY.INTL |
Returns the working day number of days from the start day. |
WORKDAY(Date, Shift[, Mode[, Holidays]]) |
|
Date and Time |
YEAR |
Returns the year as a number according to the internal calculation rules. |
YEAR(Number) |
|
Date and Time |
YEARFRAC |
Computes the difference between two date values, in a fraction of years. |
YEARFRAC(Date2, Date1[, Format]) |
|
Engineering |
BIN2DEC |
Converts the binary number entered to a decimal number. |
BIN2DEC(Number) |
|
Engineering |
BIN2HEX |
Converts the binary number entered to a hexadecimal number. |
BIN2HEX(Number, Places) |
|
Engineering |
BIN2OCT |
Converts the binary number entered to a octal number. |
BIN2OCT(Number, Places) |
|
Engineering |
BITAND |
Returns a bitwise "and" of the parameters. |
BITAND(Number1, Number2) |
|
Engineering |
BITLSHIFT |
Shifts a number left by n bits. |
BITLSHIFT(Number, Shift) |
|
Engineering |
BITOR |
Returns a bitwise "or" of the parameters. |
BITOR(Number1, Number2) |
|
Engineering |
BITRSHIFT |
Shifts a number right by n bits. |
BITRSHIFT(Number, Shift) |
|
Engineering |
BITXOR |
Returns a bitwise "exclusive or" of the parameters. |
BITXOR(Number1, Number2) |
|
Engineering |
COMPLEX |
Returns a complex number from the Re and Im parts. |
COMPLEX(Re, Im[, Symbol]) |
|
Engineering |
DEC2BIN |
Converts the decimal number entered between –512 and 511 to a binary number. |
DEC2BIN(Number, Places) |
|
Engineering |
DEC2HEX |
Converts the decimal number entered to a hexadecimal number. |
DEC2HEX(Number, Places) |
|
Engineering |
DEC2OCT |
Converts the decimal number entered to a octal number. |
DEC2OCT(Number, Places) |
|
Engineering |
DELTA |
Returns TRUE (1) if both numbers are equal, otherwise it returns FALSE (0). |
DELTA(Number_1, Number_2) |
|
Engineering |
ERF |
Returns values of the Gaussian error integral. |
ERF(Lower_Limit, Upper_Limit) |
|
Engineering |
ERFC |
Returns complementary values of the Gaussian error integral between x and infinity. |
ERFC(Lower_Limit) |
|
Engineering |
HEX2BIN |
Converts the hexadecimal number entered to a binary number. |
HEX2BIN(Number, Places) |
|
Engineering |
HEX2DEC |
Converts the hexadecimal number entered to a decimal number. |
HEX2DEC(Number) |
|
Engineering |
HEX2OCT |
Converts the hexadecimal number entered to an octal number. |
HEX2OCT(Number, Places) |
|
Engineering |
IMABS |
Returns the module of a complex number. |
IMABS(Complex) |
|
Engineering |
IMAGINARY |
Returns the imaginary part of a complex number. |
IMAGINARY(Complex) |
|
Engineering |
IMARGUMENT |
Returns the argument of a complex number. |
IMARGUMENT(Complex) |
|
Engineering |
IMCONJUGATE |
Returns conjugate of a complex number. |
IMCONJUGATE(Complex) |
|
Engineering |
IMCOS |
Returns the cosine of a complex number. |
IMCOS(Complex) |
|
Engineering |
IMCOSH |
Returns the hyperbolic cosine of a complex number. |
IMCOSH(Complex) |
|
Engineering |
IMCOT |
Returns the cotangent of a complex number. |
IMCOT(Complex) |
|
Engineering |
IMCSC |
Returns the cosecant of a complex number. |
IMCSC(Complex) |
|
Engineering |
IMCSCH |
Returns the hyperbolic cosecant of a complex number. |
IMCSCH(Complex) |
|
Engineering |
IMDIV |
Divides two complex numbers. |
IMDIV(Complex1, Complex2) |
|
Engineering |
IMEXP |
Returns the exponent of a complex number. |
IMEXP(Complex) |
|
Engineering |
IMLN |
Returns the natural logarithm of a complex number. |
IMLN(Complex) |
|
Engineering |
IMLOG2 |
Returns the binary logarithm of a complex number. |
IMLOG2(Complex) |
|
Engineering |
IMLOG10 |
Returns the base-10 logarithm of a complex number. |
IMLOG10(Complex) |
|
Engineering |
IMPOWER |
Returns a complex number raised to a given power. |
IMPOWER(Complex, Number) |
|
Engineering |
IMPRODUCT |
Multiplies complex numbers. |
IMPRODUCT(Complex1 ...Complex30) |
|
Engineering |
IMREAL |
Returns the real part of a complex number. |
IMREAL(Complex) |
|
Engineering |
IMSEC |
Returns the secant of a complex number. |
IMSEC(Complex) |
|
Engineering |
IMSECH |
Returns the hyperbolic scans of a complex number. |
IMSECH(Complex) |
|
Engineering |
IMSIN |
Returns the sine of a complex number. |
IMSIN(Complex) |
|
Engineering |
IMSINH |
Returns the hyperbolic sine of a complex number. |
IMSINH(Complex) |
|
Engineering |
IMSQRT |
Returns the square root of a complex number. |
IMSQRT(Complex) |
|
Engineering |
IMSUB |
Subtracts two complex numbers. |
IMSUB(Complex1, Complex2) |
|
Engineering |
IMSUM |
Adds complex numbers. |
IMSUM(Complex1 ...Complex30) |
|
Engineering |
IMTAN |
Returns the tangent of a complex number. |
IMTAN(Complex) |
|
Engineering |
OCT2BIN |
Converts the octal number entered to a binary number. |
OCT2BIN(Number, Places) |
|
Engineering |
OCT2DEC |
Converts the octal number entered to a decimal number. |
OCT2DEC(Number) |
|
Engineering |
OCT2HEX |
Converts the octal number entered to a hexadecimal number. |
OCT2HEX(Number, Places) |
|
Information |
ISBINARY |
Returns TRUE if the value provided is a valid binary number. |
ISBINARY(Value) |
|
Information |
ISBLANK |
Returns TRUE if the reference to a cell is blank. |
ISBLANK(Value) |
|
Information |
ISERR |
Returns TRUE if the value is an error value except for #N/A!. |
ISERR(Value) |
|
Information |
ISERROR |
Returns TRUE if the value is a general error value. |
ISERROR(Value) |
|
Information |
ISEVEN |
Returns TRUE if the value is an even integer, or FALSE if the value is odd. |
ISEVEN(Value) |
|
Information |
ISFORMULA |
Checks whether the referenced cell is a formula. |
ISFORMULA(Value) |
|
Information |
ISLOGICAL |
Tests for a logical value (TRUE or FALSE). |
ISLOGICAL(Value) |
|
Information |
ISNA |
Returns TRUE if the value is an #N/A! error. |
ISNA(Value) |
|
Information |
ISNONTEXT |
Determines if the cell contents are text or numbers, and returns FALSE if the cell contents are text. |
ISNONTEXT(Value) |
|
Information |
ISNUMBER |
Returns TRUE if the value refers to a number. |
ISNUMBER(Value) |
|
Information |
ISODD |
Returns TRUE if the value is odd, or FALSE if the number is even. |
ISODD(Value) |
|
Information |
ISREF |
Returns TRUE if provided value is an #REF! error. |
ISREF(Value) |
|
Information |
ISTEXT |
Returns TRUE if the cell contents refer to text. |
ISTEXT(Value) |
|
Information |
SHEET |
Returns the sheet number of a given value or a formula sheet number if no argument is provided. |
SHEET([Value]) |
|
Information |
SHEETS |
Returns the number of a sheet for a given reference or the number of all sheets in a workbook when no argument is provided. |
SHEETS([Value]) |
|
Information |
NA |
Returns #N/A! error value. |
NA(Value) |
|
Financial |
CUMIPMT |
Returns the cumulative interest paid on a loan between a start period and an end period. |
CUMIPMT(Rate, Nper, Pv, Start, End, Type) |
|
Financial |
CUMPRINC |
Returns the cumulative principal paid on a loan between a start period and an end period. |
CUMPRINC(Rate, Nper, Pv, Start, End, Type) |
|
Financial |
DB |
Returns the depreciation of an asset for a period using the double-declining balance method. |
DDB(Cost, Salvage, Life, Period[, Factor]) |
|
Financial |
DOLLARDE |
Converts a price entered with a special notation to a price displayed as a decimal number. |
DOLLARDE(Price, Fraction) |
|
Financial |
DOLLARFR |
Converts a price displayed as a decimal number to a price entered with a special notation. |
DOLLARFR(Price, Fraction) |
|
Financial |
EFFECT |
Calculates the effective annual interest rate from a nominal interest rate and the number of compounding periods per year. |
EFFECT (Nominal_rate, Npery) |
|
Financial |
FV |
Returns the future value of an investment. |
FV(Rate, Nper, Pmt[, Pv,[ Type]]) |
|
Financial |
FVSCHEDULE |
Returns the future value of an investment based on a rate schedule. |
FV(Pv, Schedule) |
|
Financial |
IPMT |
Returns the interest portion of a given loan payment in a given payment period. |
IPMT(Rate, Per, Nper, Pv[, Fv[, Type]]) |
|
Financial |
ISPMT |
Returns the interest paid for a given period of an investment with equal principal payments. |
ISPMT(Rate, Per, Nper, Value) |
|
Financial |
MIRR |
Returns the modified internal value for cashflows. |
MIRR(Flows, FRate, RRate) |
|
Financial |
NOMINAL |
Returns the nominal interest rate. |
NOMINAL(Effect_rate, Npery) |
|
Financial |
NPER |
Returns the number of periods for an investment assuming periodic, constant payments and a constant interest rate. |
NPER(Rate, Pmt, Pv[, Fv[, Type]]) |
|
Financial |
NPV |
Returns the net present value. |
NPV(Rate, Value1, …, Value30) |
|
Financial |
PDURATION |
Returns the number of periods to reach a specific value. |
PDURATION(Rate, Pv, Fv) |
|
Financial |
PMT |
Returns the periodic payment for a loan. |
PMT(Rate, Nper, Pv[, Fv[, Type]]) |
|
Financial |
PPMT |
Calculates the principal portion of a given loan payment. |
PPMT(Rate, Per, Nper, Pv[, Fv[, Type]]) |
|
Financial |
PV |
Returns the present value of an investment. |
PV(Rate, Nper, Pmt[, Fv[, Type]]) |
|
Financial |
RATE |
Returns the interest rate per period of an annuity. |
RATE(Nper, Pmt, Pv[, Fv[, Type[, guess]]]) |
|
Financial |
RRI |
Returns an equivalent interest rate for the growth of an investment. |
RRI(Nper, Pv, Fv) |
|
Financial |
SLN |
Returns the depreciation of an asset for one period, based on a straight-line method. |
SLN(Cost, Salvage, Life) |
|
Financial |
SYD |
Returns the "sum-of-years" depreciation for an asset in a period. |
SYD(Cost, Salvage, Life, Period |
|
Financial |
TBILLEQ |
Returns the bond-equivalent yield for a Treasury bill. |
TBILLEQ(Settlement, Maturity, Discount) |
|
Financial |
TBILLPRICE |
Returns the price per $100 face value for a Treasury bill. |
TBILLPRICE(Settlement, Maturity, Discount) |
|
Financial |
TBILLYIELD |
Returns the yield for a Treasury bill. |
TBILLYIELD(Settlement, Maturity, Price) |
|
Financial |
XNPV |
Returns the net present value. |
XNPV(Rate, Payments, Dates) |
|
Logical |
AND |
Returns TRUE if all arguments are TRUE. |
AND(Logicalvalue1, Logicalvalue2 ...Logicalvalue30) |
|
Logical |
FALSE |
Returns the logical value FALSE. |
FALSE() |
|
Logical |
IF |
Specifies a logical test to be performed. |
IF(Test, Then value, Otherwisevalue) |
|
Logical |
IFNA |
Returns the value if the cell does not contains the #N/A (value not available) error value, or the alternative value if it does. |
IFNA(Value, Alternate_value) |
|
Logical |
IFERROR |
Returns the value if the cell does not contains an error value, or the alternative value if it does. |
IFERROR(Value, Alternate_value) |
|
Logical |
NOT |
Complements (inverts) a logical value. |
NOT(Logicalvalue) |
|
Logical |
SWITCH |
Evaluates a list of arguments, consisting of an expression followed by a value. |
SWITCH(Expression1, Value1[, Expression2, Value2[..., Expression_n, Value_n]]) |
|
Logical |
OR |
Returns TRUE if at least one argument is TRUE. |
OR(Logicalvalue1, Logicalvalue2 ...Logicalvalue30) |
|
Logical |
TRUE |
The logical value is set to TRUE. |
TRUE() |
|
Logical |
XOR |
Returns true if an odd number of arguments evaluates to TRUE. |
XOR(Logicalvalue1, Logicalvalue2 ...Logicalvalue30) |
|
Lookup and Reference |
CHOOSE |
Uses an index to return a value from a list of up to 30 values. |
CHOOSE(Index, Value1, …, Value30) |
|
Lookup and Reference |
COLUMN |
Returns the column number of a given reference or formula reference if an argument is not provided. |
COLUMNS([Reference]) |
|
Lookup and Reference |
COLUMNS |
Returns the number of columns in the given reference. |
COLUMNS(Array) |
|
Lookup and Reference |
FORMULATEXT |
Returns a formula in a given cell as a string. |
FORMULATEXT(Reference) |
|
Lookup and Reference |
HLOOKUP |
Searches horizontally with reference to the adjacent cells to the bottom. |
HLOOKUP(Search_Criterion, Array, Index, Sort_Order) |
|
Lookup and Reference |
INDEX |
Returns the content of a cell, specified by row and column number, or an optional range name. |
INDEX(Reference, Row, Column, Range) |
|
Lookup and Reference |
MATCH |
Returns the relative position of an item in an array that matches a specified value. |
MATCH(Searchcriterion, Lookuparray, Type) |
|
Lookup and Reference |
OFFSET |
Returns the value of a cell offset by a certain number of rows and columns from a given reference point. |
OFFSET(Reference, Rows, Columns, Height, Width) |
|
Lookup and Reference |
ROW |
Returns the row number of a given reference or formula reference if an argument is not provided. |
ROW([Reference]) |
|
Lookup and Reference |
ROWS |
Returns the number of rows in the given reference. |
ROWS(Array) |
|
Lookup and Reference |
VLOOKUP |
Searches vertically with reference to adjacent cells to the right. |
VLOOKUP(Search_Criterion, Array, Index, Sort_Order) |
|
Math and Trigonometry |
Enter Package Data |
Returns the absolute value of a number. |
ABS(Number) |
|
Math and Trigonometry |
ACOS |
Returns the inverse trigonometric cosine of a number. |
ACOS(Number) |
|
Math and Trigonometry |
ACOSH |
Returns the inverse hyperbolic cosine of a number. |
ACOSH(Number) |
|
Math and Trigonometry |
ACOT |
Returns the inverse trigonometric cotangent of a number. |
ACOT(Number) |
|
Math and Trigonometry |
ACOTH |
Returns the inverse hyperbolic cotangent of a number. |
ACOTH(Number) |
|
Math and Trigonometry |
ARABIC |
Converts a number from the roman form. |
ARABIC(String) |
|
Math and Trigonometry |
ASIN |
Returns the inverse trigonometric sine of a number. |
ASIN(Number) |
|
Math and Trigonometry |
ASINH |
Returns the inverse hyperbolic sine of a number. |
ASINH(Number) |
|
Math and Trigonometry |
ATAN |
Returns the inverse trigonometric tangent of a number. |
ATAN(Number) |
|
Math and Trigonometry |
ATAN2 |
Returns the inverse trigonometric tangent of the specified x and y coordinates. |
ATAN2(Numberx, Numbery) |
|
Math and Trigonometry |
ATANH |
Returns the inverse hyperbolic tangent of a number. |
ATANH(Number) |
|
Math and Trigonometry |
BASE |
Converts a positive integer to a specified base into a text from the numbering system. |
BASE(Number, Radix, [Minimumlength]) |
|
Math and Trigonometry |
CEILING |
Rounds a number up to the nearest multiple of significance. |
CEILING(Number, Significance) |
|
Math and Trigonometry |
CEILING.MATH |
Rounds a number up to the nearest multiple of significance. |
CEILING.MATH(Number, Significance[, Mode]]) |
|
Math and Trigonometry |
CEILING.PRECISE |
Rounds a number up to the nearest multiple of significance. |
CEILING.PRECISE(Number[, Significance]) |
|
Math and Trigonometry |
COMBIN |
Returns the number of combinations (without repetitions). |
COMBIN(Number, Number) |
|
Math and Trigonometry |
COMBINA |
Returns the number of combinations (with repetitions). |
COMBINA(Number, Number) |
|
Math and Trigonometry |
COS |
Returns the cosine of the given angle (in radians). |
COS(Number) |
|
Math and Trigonometry |
COSH |
Returns the hyperbolic cosine of the given value. |
COSH(Number) |
|
Math and Trigonometry |
COT |
Returns the cotangent of the given angle (in radians). |
COT(Number) |
|
Math and Trigonometry |
COTH |
Returns the hyperbolic cotangent of the given value. |
COTH(Number) |
|
Math and Trigonometry |
COUNTUNIQUE |
Counts the number of unique values in a list of specified values and ranges. |
COUNTUNIQUE(Value1, [Value2, ...]) |
|
Math and Trigonometry |
CSC |
Returns the cosecant of the given angle (in radians). |
CSC(Number) |
|
Math and Trigonometry |
CSCH |
Returns the hyperbolic cosecant of the given value. |
CSCH(Number) |
|
Math and Trigonometry |
DECIMAL |
Converts a text with characters from a number system to a positive integer in the base radix given. |
DECIMAL("Text", Radix) |
|
Math and Trigonometry |
DEGREES |
Converts radians into degrees. |
DEGREES(Number) |
|
Math and Trigonometry |
EVEN |
Rounds a positive number up to the next even integer and a negative number down to the next even integer. |
EVEN(Number) |
|
Math and Trigonometry |
EXP |
Returns the result of the constant e raised to the power of a number. |
EXP(Number) |
|
Math and Trigonometry |
FACT |
Returns the factorial of a number. |
FACT(Number) |
|
Math and Trigonometry |
FACTDOUBLE |
Returns the double factorial of a number. |
FACTDOUBLE(Number) |
|
Math and Trigonometry |
FLOOR |
Rounds a number down to the nearest multiple of significance. |
FLOOR(Number, Significance) |
|
Math and Trigonometry |
FLOOR.MATH |
Rounds a number down to the nearest multiple of significance. |
FLOOR.MATH(Number[, Significance[, Mode]]) |
|
Math and Trigonometry |
FLOOR.PRECISE |
Rounds a number down to the nearest multiple of significance. |
FLOOR.PRECISE(Number[ Significance]) |
|
Math and Trigonometry |
GCD |
Returns the greatest common divisor of numbers. |
GCD(Number1, Number2, ...) |
|
Math and Trigonometry |
INT |
Rounds a number down to the nearest integer. |
INT(Number) |
|
Math and Trigonometry |
ISO.CEILING |
Rounds a number up to the nearest multiple of significance. |
ISO.CEILING(Number[, Significance]) |
|
Math and Trigonometry |
LCM |
Returns the least common multiplicity of numbers. |
LCM(Number1, Number2, ...) |
|
Math and Trigonometry |
LN |
Returns the natural logarithm based on the constant e of a number. |
LN(Number) |
|
Math and Trigonometry |
LOG |
Returns the logarithm of a number to the specified base. |
LOG(Number, Base) |
|
Math and Trigonometry |
LOG10 |
Returns the base-10 logarithm of a number. |
LOG10(Number) |
|
Math and Trigonometry |
MOD |
Returns the remainder when one integer is divided by another. |
MOD(Dividend, Divisor) |
|
Math and Trigonometry |
MROUND |
Rounds a number to the nearest multiplicity. |
MROUND(Number, Base) |
|
Math and Trigonometry |
MULTINOMIAL |
Returns the number of multiset combinations. |
MULTINOMIAL(Number1, Number2, ...) |
|
Math and Trigonometry |
ODD |
Rounds a positive number up to the nearest odd integer and a negative number down to the nearest odd integer. |
ODD(Number) |
|
Math and Trigonometry |
PI |
Returns 3.14159265358979, the value of the mathematical constant PI to 14 decimal places. |
PI() |
|
Math and Trigonometry |
POWER |
Returns a number raised to another number. |
POWER(Base, Exponent) |
|
Math and Trigonometry |
PRODUCT |
Returns the product of numbers. |
PRODUCT(Number1, Number2, …, Number30) |
|
Math and Trigonometry |
QUOTIENT |
Returns the integer part of a division. |
QUOTIENT(Dividend, Divisor) |
|
Math and Trigonometry |
RADIANS |
Converts degrees to radians. |
RADIANS(Number) |
|
Math and Trigonometry |
RAND |
Returns a random number between 0 and 1. |
RAND() |
|
Math and Trigonometry |
RANDBETWEEN |
Returns a random integer between two numbers. |
RAND(Lowerbound, Upperbound) |
|
Math and Trigonometry |
ROMAN |
Converts a number to roman form. |
ROMAN(Number[, Mode]) |
|
Math and Trigonometry |
ROUND |
Rounds a number to a certain number of decimal places. |
ROUND(Number, Count) |
|
Math and Trigonometry |
ROUNDDOWN |
Rounds a number down, towards zero, to a certain precision. |
ROUNDDOWN(Number, Count) |
|
Math and Trigonometry |
ROUNDUP |
Rounds a number up, away from zero, to a certain precision. |
ROUNDUP(Number, Count) |
|
Math and Trigonometry |
SEC |
Returns the secant of the given angle (in radians). |
SEC(Number) |
|
Math and Trigonometry |
SECH |
Returns the hyperbolic secant of the given value. |
SEC(Number) |
|
Math and Trigonometry |
SERIESSUM |
Evaluates a series at a point. |
SERIESSUM(Number, Number, Number, Coefficients) |
|
Math and Trigonometry |
SIN |
Returns the sine of the given angle (in radians). |
SIN(Number) |
|
Math and Trigonometry |
SINH |
Returns the hyperbolic sine of the given value. |
SINH(Number) |
|
Math and Trigonometry |
SIGN |
Returns the sign of a number. |
SIGN(Number) |
|
Math and Trigonometry |
SQRT |
Returns the positive square root of a number. |
SQRT(Number) |
|
Math and Trigonometry |
SQRTPI |
Returns the square root of a number times pi. |
SQRTPI(Number) |
|
Math and Trigonometry |
SUBTOTAL |
Returns an aggregate result using other functions that are specified by a number. |
SUBTOTAL(Function, Number1, Number2, ... Number30) |
|
Math and Trigonometry |
SUM |
Adds the numbers in a range of cells. |
SUM(Number1, Number2, …, Number30) |
|
Math and Trigonometry |
SUMIF |
Adds the numbers in a range of cells specified by given criteria. |
SUMIF(Range, Criteria, Sumrange) |
|
Math and Trigonometry |
SUMIFS |
Returns the sum of the values of cells in a range that meets multiple criteria in multiple ranges. |
SUMIFS(Sum_Range , Criterion_range1 , Criterion1 [, Criterion_range2 , Criterion2 [,...]]) |
|
Math and Trigonometry |
SUMPRODUCT |
Multiplies corresponding elements in the given arrays, and returns the sum of those products. |
SUMPRODUCT(Array1, Array2...Array30) |
|
Math and Trigonometry |
SUMSQ |
Returns the sum of the squares of the arguments. |
SUMSQ(Number1, Number2, …, Number30) |
|
Math and Trigonometry |
SUMX2MY2 |
Returns the sum of the square differences. |
SUMX2MY2(Range1, Range2) |
|
Math and Trigonometry |
SUMX2PY2 |
Returns the sum of the square sums. |
SUMX2PY2(Range1, Range2) |
|
Math and Trigonometry |
SUMXMY2 |
Returns the sum of the square of differences. |
SUMXMY2(Range1, Range2) |
|
Math and Trigonometry |
TAN |
Returns the tangent of the given angle (in radians). |
TAN(Number) |
|
Math and Trigonometry |
TANH |
Returns the hyperbolic tangent of the given value. |
TANH(Number) |
|
Math and Trigonometry |
TRUNC |
Truncates a number by removing decimal places. |
TRUNC(Number, Count) |
|
Matrix Functions |
MMULT |
Calculates the array product of two arrays. |
MMULT(Array, Array) |
|
Matrix Functions |
MEDIANPOOL |
Calculates a smaller range, which is a median of a window_size, in a given range, for every stride element. |
MEDIANPOOL(Range, Window_size, Stride) |
|
Matrix Functions |
MAXPOOL |
Calculates a smaller range which is a maximum of a window_size, in a given range, for every stride element. |
MAXPOOL(Range, Window_size, Stride) |
|
Matrix Functions |
TRANSPOSE |
Transposes the rows and columns of an array. |
TRANSPOSE(Array) |
|
Operator |
HF.ADD |
Adds two values. |
HF.ADD(Number, Number) |
|
Operator |
HF.CONCAT |
Concatenates two strings. |
HF.CONCAT(String, String) |
|
Operator |
HF.DIVIDE |
Divides two values. |
HF.DIVIDE(Number, Number) |
|
Operator |
HF.EQ |
Tests two values for equality. |
HF.EQ(Value, Value) |
|
Operator |
HF.LT |
Tests two values for less-than relation. |
HF.LT(Value, Value) |
|
Operator |
HF.GT |
Tests two values for greater-than relation. |
HF.GT(Value, Value) |
|
Operator |
HF.MINUS |
Subtracts two values. |
HF.MINUS(Number, Number |
|
Operator |
HF.MULTIPLY |
Multiplies two values. |
HF.MULTIPLY(Number, Number) |
|
Operator |
HF.NE |
Tests two values for inequality. |
HF.NE(Value, Value) |
|
Operator |
HF.POW |
Computes power of two values. |
HF.POW(Number, Number) |
|
Operator |
HF.UMINUS |
Negates the value. |
HF.UMINUS(Number) |
|
Operator |
HF.UNARY_PERCENT |
Applies percent operator. |
HF.UNARY_PERCENT(Number) |
|
Operator |
HF.UPLUS |
Applies unary plus |
HF.UPLUS(Number) |
|
Statistical |
AVEDEV |
Returns the average deviation of the arguments. |
AVEDEV(Number1, Number2, ...Number30) |
|
Statistical |
AVERAGE |
Returns the average of the arguments. |
AVERAGE(Number1, Number2, ...Number30) |
|
Statistical |
AVERAGEA |
Returns the average of the arguments. |
AVERAGEA(Value1, Value2, ... Value30) |
|
Statistical |
AVERAGEIF |
Returns the arithmetic mean of all cells in a range that satisfy a given condition. |
AVERAGEIF(Range, Criterion [, Average_Range ]) |
|
Statistical |
BESSELI |
Calculates the Bessel function value. |
BESSELI(x, n) |
|
Statistical |
BESSELJ |
Calculates the Bessel function value. |
BESSELJ(x, n) |
|
Statistical |
BESSELK |
Calculates the Bessel function value. |
BESSELK(x, n) |
|
Statistical |
BESSELY |
Calculates the Bessel function value. |
BESSELY(x, n) |
|
Statistical |
BETA.DIST |
Returns the density of Beta distribution. |
BETA.DIST(Number1, Number2, Number3, Boolean[, Number4[, Number5]]) |
|
Statistical |
BETADIST |
Returns the density of Beta distribution. |
BETADIST(Number1, Number2, Number3, Boolean[, Number4[, Number5]]) |
|
Statistical |
BETA.INV |
Returns the inverse Beta distribution value. |
BETA.INV(Number1, Number2, Number3[, Number4[, Number5]]) |
|
Statistical |
BETAINV |
Returns the inverse of Beta distribution value. |
BETAINV(Number1, Number2, Number3[, Number4[, Number5]]) |
|
Statistical |
BINOM.DIST |
Returns the density of binomial distribution. |
BINOM.DIST(Number1, Number2, Number3, Boolean) |
|
Statistical |
BINOMDIST |
Returns the density of binomial distribution. |
BINOMDIST(Number1, Number2, Number3, Boolean) |
|
Statistical |
BINOM.INV |
Returns the inverse binomial distribution value. |
BINOM.INV(Number1, Number2, Number3) |
|
Statistical |
CHIDIST |
Returns the probability of chi-square right-side distribution. |
CHIDIST(X, Degrees) |
|
Statistical |
CHIINV |
Returns the inverse of chi-square right-side distribution. |
CHIINV(P, Degrees) |
|
Statistical |
CHIINVRT |
Returns the inverse of chi-square right-side distribution. |
CHIINVRT(P, Degrees) |
|
Statistical |
CHISQ.DIST |
Returns the value of chi-square distribution. |
CHISQ.DIST(X, Degrees, Mode) |
|
Statistical |
CHIDISTRT |
Returns the probability of chi-square right-side distribution. |
CHIDISTRT(X, Degrees) |
|
Statistical |
CHISQ.DIST.RT |
Returns probability of chi-square right-side distribution. |
CHISQ.DIST.RT(X, Degrees) |
|
Statistical |
CHISQ.INV |
Returns the inverse of chi-square distribution. |
CHISQ.INV.RT(P, Degrees) |
|
Statistical |
CHISQ.INV.RT |
Returns the inverse of chi-square right-side distribution. |
CHISQ.INV.RT(P, Degrees) |
|
Statistical |
CHISQ.TEST |
Returns the chi-square test value for a dataset. |
CHISQ.TEST(Array1, Array2) |
|
Statistical |
CHITEST |
Returns chi-square test value for a dataset. |
CHITEST(Array1, Array2) |
|
Statistical |
CONFIDENCE |
Returns the upper confidence bound for a normal distribution. |
CONFIDENCE(Alpha, Stdev, Size) |
|
Statistical |
CONFIDENCE.NORM |
Returns the upper confidence bound for a normal distribution. |
CONFIDENCE.NORM(Alpha, Stdev, Size) |
|
Statistical |
CONFIDENCE.T |
Returns the upper confidence bound for a T distribution. |
CONFIDENCE.T(Alpha, Stdev, Size) |
|
Statistical |
CORREL |
Returns the correlation coefficient between two data sets. |
CORREL(Data1, Data2) |
|
Statistical |
COUNT |
Counts how many numbers are in the list of arguments. |
COUNT(Value1, Value2, ... Value30) |
|
Statistical |
COUNTA |
Counts how many values are in the list of arguments. |
COUNTA(Value1, Value2, ... Value30) |
|
Statistical |
COUNTBLANK |
Counts the number of empty cells. |
COUNTBLANK(Range) |
|
Statistical |
COUNTIF |
Counts the number of cells that meet with certain criteria within a cell range. |
COUNTIF(Range, Criteria) |
|
Statistical |
COUNTIFS |
Counts the rows or columns that meet criteria in multiple ranges. |
COUNTIFS(Range1, Criterion1 [, Range2, Criterion2 [, ...]]) |
|
Statistical |
COVAR |
Returns the covariance between two data sets, population normalized. |
COVAR(Data1, Data2) |
|
Statistical |
COVARIANCE.P |
Returns the covariance between two data sets, population normalized. |
COVARIANCE.P(Data1, Data2) |
|
Statistical |
COVARIANCEP |
Returns the covariance between two data sets, population normalized. |
COVARIANCEP(Data1, Data2) |
|
Statistical |
COVARIANCE.S |
Returns the covariance between two data sets, sample normalized. |
COVARIANCE.S(Data1, Data2) |
|
Statistical |
COVARIANCES |
Returns the covariance between two data sets, sample normalized. |
COVARIANCES(Data1, Data2) |
|
Statistical |
CRITBINOM |
Returns the inverse binomial distribution value. |
CRITBINOM(Number1, Number2, Number3) |
|
Statistical |
DEVSQ |
Returns the sum of squared deviations. |
DEVSQ(Number1, Number2, ...Number30) |
|
Statistical |
EXPON.DIST |
Returns the density of an exponential distribution. |
EXPON.DIST(Number1, Number2, Boolean) |
|
Statistical |
EXPONDIST |
Returns the density of an exponential distribution. |
EXPONDIST(Number1, Number2, Boolean) |
|
Statistical |
FDIST |
Returns the probability of F right-side distribution. |
FDIST(X, Degree1, Degree2) |
|
Statistical |
FINV |
Returns the inverse of F right-side distribution. |
FINV(P, Degree1, Degree2) |
|
Statistical |
F.DIST |
Returns the value of F distribution. |
F.DIST(X, Degree1, Degree2, Mode) |
|
Statistical |
F.DIST.RT |
Returns the probability of F right-side distribution. |
F.DIST.RT(X, Degree1, Degree2) |
|
Statistical |
FDISTRT |
Returns the probability of F right-side distribution. |
FDISTRT(X, Degree1, Degree2) |
|
Statistical |
F.INV |
Returns the inverse of F distribution. |
F.INV.RT(P, Degree1, Degree2) |
|
Statistical |
F.INV.RT |
Returns the inverse of F right-side distribution. |
F.INV.RT(P, Degree1, Degree2) |
|
Statistical |
FINVRT |
Returns the inverse of F right-side distribution. |
FINVRT(P, Degree1, Degree2) |
|
Statistical |
FISHER |
Returns the Fisher transformation value. |
FISHER(Number) |
|
Statistical |
FISHERINV |
Returns the inverse of the Fischer transformation value. |
FISHERINV(Number) |
|
Statistical |
F.TEST |
Returns the f-test value for a dataset. |
F.TEST(Array1, Array2) |
|
Statistical |
FTEST |
Returns the f-test value for a dataset. |
FTEST(Array1, Array2) |
|
Statistical |
GAMMA |
Returns the Gamma function value. |
GAMMA(Number) |
|
Statistical |
GAMMA.DIST |
Returns the density of the Gamma distribution. |
GAMMA.DIST(Number1, Number2, Number3, Boolean |
|
Statistical |
GAMMADIST |
Returns the density of the Gamma distribution. |
GAMMADIST(Number1, Number2, Number3, Boolean) |
|
Statistical |
GAMMALN |
Returns the natural logarithm of the Gamma function. |
GAMMALN(Number) |
|
Statistical |
GAMMALN.PRECISE |
Returns the natural logarithm of the Gamma function. |
GAMMALN.PRECISE(Number) |
|
Statistical |
GAMMA.INV |
Returns the inverse of the Gamma distribution value. |
GAMMA.INV(Number1, Number2, Number3) |
|
Statistical |
GAMMAINV |
Returns the inverse of the Gamma distribution value. |
GAMMAINV(Number1, Number2, Number3) |
|
Statistical |
GAUSS |
Returns the probability of a Gaussian variable fall more than this many times the standard deviation from the mean. |
GAUSS(Number) |
|
Statistical |
GEOMEAN |
Returns the geometric average. |
GEOMEAN(Number1, Number2, ...Number30) |
|
Statistical |
HARMEAN |
Returns the harmonic average. |
HARMEAN(Number1, Number2, ...Number30) |
|
Statistical |
HYPGEOMDIST |
Returns the density of the hypergeometric distribution. |
HYPGEOMDIST(Number1, Number2, Number3, Number4, Boolean) |
|
Statistical |
HYPGEOM.DIST |
Returns the density of the hypergeometric distribution. |
HYPGEOM.DIST(Number1, Number2, Number3, Number4, Boolean) |
|
Statistical |
LARGE |
Returns the k-th largest value in a range. |
LARGE(Range, K) |
|
Statistical |
LOGNORM.DIST |
Returns the density of the lognormal distribution. |
LOGNORM.DIST(X, Mean, Stddev, Mode) |
|
Statistical |
LOGNORMDIST |
Returns the density of the lognormal distribution. |
LOGNORMDIST(X, Mean, Stddev, Mode) |
|
Statistical |
LOGNORM.INV |
Returns the inverse value of the lognormal distribution. |
LOGNORM.INV(P, Mean, Stddev) |
|
Statistical |
LOGNORMINV |
Returns the inverse value of the lognormal distribution. |
LOGNORMINV(P, Mean, Stddev) |
|
Statistical |
LOGINV |
Returns the inverse value of the lognormal distribution. |
LOGINV(P, Mean, Stddev) |
|
Statistical |
MAX |
Returns the maximum value in a list of arguments. |
MAX(Number1, Number2, ...Number30) |
|
Statistical |
MAXA |
Returns the maximum value in a list of arguments. |
MAXA(Value1, Value2, ... Value30) |
|
Statistical |
MEDIAN |
Returns the median of a set of numbers. |
MEDIAN(Number1, Number2, ...Number30) |
|
Statistical |
MIN |
Returns the minimum value in a list of arguments. |
MIN(Number1, Number2, ...Number30) |
|
Statistical |
MINA |
Returns the minimum value in a list of arguments. |
MINA(Value1, Value2, ... Value30) |
|
Statistical |
NEGBINOM.DIST |
Returns the density of negative binomial distribution. |
NEGBINOM.DIST(Number1, Number2, Number3, Mode) |
|
Statistical |
NEGBINOMDIST |
Returns the density of negative binomial distribution. |
NEGBINOMDIST(Number1, Number2, Number3, Mode) |
|
Statistical |
NORM.DIST |
Returns the density of normal distribution. |
NORM.DIST(X, Mean, Stddev, Mode) |
|
Statistical |
NORMDIST |
Returns the density of normal distribution. |
NORMDIST(X, Mean, Stddev, Mode) |
|
Statistical |
NORM.S.DIST |
Returns the density of normal distribution. |
NORM.S.DIST(X, Mode) |
|
Statistical |
NORMSDIST |
Returns the density of normal distribution. |
NORMSDIST(X, Mode) |
|
Statistical |
NORM.INV |
Returns the inverse value of normal distribution. |
NORM.INV(P, Mean, Stddev) |
|
Statistical |
NORMINV |
Returns the inverse value of normal distribution. |
NORMINV(P, Mean, Stddev) |
|
Statistical |
NORM.S.INV |
Returns the inverse value of normal distribution. |
NORM.S.INV(P) |
|
Statistical |
NORMSINV |
Returns the inverse value of normal distribution. |
NORMSINV(P) |
|
Statistical |
PEARSON |
Returns the correlation coefficient between two data sets. |
PEARSON(Data1, Data2) |
|
Statistical |
PHI |
Returns the probability density of normal distribution. |
PHI(X) |
|
Statistical |
POISSON |
Returns the density of Poisson distribution. |
POISSON(X, Mean, Mode) |
|
Statistical |
POISSON.DIST |
Returns the density of Poisson distribution. |
POISSON.DIST(X, Mean, Mode) |
|
Statistical |
POISSONDIST |
Returns the density of Poisson distribution. |
POISSONDIST(X, Mean, Mode) |
|
Statistical |
RSQ |
Returns the squared correlation coefficient between two data sets. |
RSQ(Data1, Data2) |
|
Statistical |
SKEW |
Returns the skewness of a sample. |
SKEW(Number1, Number2, ...Number30) |
|
Statistical |
SKEW.P |
Returns the skewness of a population. |
SKEW.P(Number1, Number2, ...Number30) |
|
Statistical |
SKEWP |
Returns the skewness of a population. |
SKEWP(Number1, Number2, ...Number30) |
|
Statistical |
SLOPE |
Returns the slope of a linear regression line. |
SLOPE(Array1, Array2) |
|
Statistical |
SMALL |
Returns the k-th smallest value in a range. |
SMALL(Range, K) |
|
Statistical |
STANDARDIZE |
Returns a normalized value with regards to the expected value and the standard deviation. |
STANDARDIZE(X, Mean, Stddev) |
|
Statistical |
STDEV |
Returns the standard deviation of a sample. |
STDEV(Value1, Value2, ... Value30) |
|
Statistical |
STDEVA |
Returns the standard deviation of a sample. |
STDEVA(Value1, Value2, ... Value30) |
|
Statistical |
STDEVP |
Returns the standard deviation of a population. |
STDEVP(Value1, Value2, ... Value30) |
|
Statistical |
STDEV.P |
Returns the standard deviation of a population. |
STDEV.P(Value1, Value2, ... Value30) |
|
Statistical |
STDEVPA |
Returns the standard deviation of a population. |
STDEVPA(Value1, Value2, ... Value30) |
|
Statistical |
STDEV.S |
Returns the standard deviation of a sample. |
STDEV.S(Value1, Value2, ... Value30) |
|
Statistical |
STDEVS |
Returns the standard deviation of a sample. |
STDEVS(Value1, Value2, ... Value30) |
|
Statistical |
STEYX |
Returns the standard error for the predicted y value for each x value. |
STEYX(Array1, Array2) |
|
Statistical |
TDIST |
Returns the density of the Student-t distribution, both-sided or right-tailed. |
TDIST(X, Degrees, Mode) |
|
Statistical |
T.DIST |
Returns the density of the Student-t distribution. |
T.DIST(X, Degrees, Mode) |
|
Statistical |
T.DIST.2T |
Returns the density of the Student-t distribution, both-sided. |
T.DIST.2T(X, Degrees) |
|
Statistical |
TDIST2T |
Returns the density of the Student-t distribution, both-sided. |
TDIST2T(X, Degrees) |
|
Statistical |
T.DIST.RT |
Returns the density of the Student-t distribution, right-tailed. |
T.DIST.RT(X, Degrees) |
|
Statistical |
TDISTRT |
Returns the density of the Student-t distribution, right-tailed. |
TDISTRT(X, Degrees) |
|
Statistical |
TINV |
Returns the inverse of the Student-t distribution, both-sided. |
TINV(P, Degrees) |
|
Statistical |
T.INV |
Returns the inverse of the Student-t distribution. |
T.INV(P, Degrees) |
|
Statistical |
T.INV.2T |
Returns the inverse of the Student-t distribution, both-sided. |
T.INV.2T(P, Degrees) |
|
Statistical |
TINV2T |
Returns the inverse of the Student-t distribution, both-sided. |
TINV2T(P, Degrees) |
|
Statistical |
TTEST |
Returns the t-test value for a dataset. |
TTEST(Array1, Array2) |
|
Statistical |
T.TEST |
Returns the t-test value for a dataset. |
T.TEST(Array1, Array2) |
|
Statistical |
VAR |
Returns the variance of a sample. |
VAR(Value1, Value2, ... Value30) |
|
Statistical |
VARA |
Returns the variance of a sample. |
VARA(Value1, Value2, ... Value30) |
|
Statistical |
VARP |
Returns the variance of a population. |
VARP(Value1, Value2, ... Value30) |
|
Statistical |
VAR.P |
Returns the variance of a population. |
VAR.P(Value1, Value2, ... Value30) |
|
Statistical |
VARPA |
Returns the variance of a population. |
VARPA(Value1, Value2, ... Value30) |
|
Statistical |
VAR.S |
Returns the variance of a sample. |
VAR.S(Value1, Value2, ... Value30) |
|
Statistical |
VARS |
Returns the variance of a sample. |
VARS(Value1, Value2, ... Value30) |
|
Statistical |
WEIBULL |
Returns the density of the Weibull distribution. |
WEIBULL(Number1, Number2, Number3, Boolean) |
|
Statistical |
WEIBULL.DIST |
Returns the density of the Weibull distribution. |
WEIBULL.DIST(Number1, Number2, Number3, Boolean) |
|
Statistical |
WEIBULLDIST |
Returns the density of the Weibull distribution. |
WEIBULLDIST(Number1, Number2, Number3, Boolean) |
|
Statistical |
Z.TEST |
Returns the z-test value for a dataset. |
Z.TEST(Array, X[, Sigma]) |
|
Statistical |
ZTEST |
Returns the z-test value for a dataset. |
ZTEST(Array, X[, Sigma]) |
|
Text |
CHAR |
Converts a number into a character according to the current code table. |
CHAR(Number) |
|
Text |
CLEAN |
Returns text that has been "cleaned" of line breaks and other non-printable characters. |
CLEAN("Text") |
|
Text |
CODE |
Returns a numeric code for the first character in a text string. |
CODE("Text") |
|
Text |
CONCATENATE |
Combines several text strings into one string. |
CONCATENATE("Text1", …, "Text30") |
|
Text |
EXACT |
Returns TRUE if both text strings are exactly the same. |
EXACT(Text, Text) |
|
Text |
FIND |
Returns the location of one text string inside another. |
FIND( "Text1", "Text2"[, Number]) |
|
Text |
LEFT |
Extracts a given number of characters from the left side of a text string. |
LEFT("Text", Number) |
|
Text |
LEN |
Returns the length of a given text. |
LEN("Text") |
|
Text |
LOWER |
Converts text to lowercase. |
LOWER(Text) |
|
Text |
MID |
Returns a substring of a given length starting from Start_position. |
MID(Text, Start_position, Length) |
|
Text |
PROPER |
Capitalizes the words in a given text string. |
PROPER("Text") |
|
Text |
REPLACE |
Replaces a substring of a text of a given length that starts at a given position. |
REPLACE(Text, Start_position, Length, New_text) |
|
Text |
REPT |
Repeats a text a given number of times. |
REPT("Text", Number) |
|
Text |
RIGHT |
Extracts a given number of characters from the right side of a text string. |
RIGHT("Text", Number) |
|
Text |
SEARCH |
Returns the location of one text string inside another. (Allows the use of wildcards.) |
SEARCH( "Text1", "Text2"[, Number]) |
|
Text |
SPLIT |
Divides the text around a specified character or string, and puts each fragment into a separate cell in the row. |
SPLIT(Text, Delimiter, [Split_by_each], [Remove_empty_text]) |
|
Text |
SUBSTITUTE |
Replaces the string of Old_text with New_text. Replaces only a specific occurrence of Old_text if the last parameter, Occurrence, is provided. |
SUBSTITUTE(Text, Old_text, New_text, [Occurrence]) |
|
Text |
T |
Returns text if the given value is text, otherwise it returns an empty string. |
T(Value) |
|
Text |
TEXT |
Converts a number into text according to a given format. |
TEXT(Number, Format) |
|
Text |
TRIM |
Strips extra spaces from a text. |
TRIM("Text") |
|
Text |
UNICHAR |
Returns the character created by using a given code point. |
UNICHAR(Number) |
|
Text |
UNICODE |
Returns the Unicode code point corresponding to the first character of a text. |
UNICODE(Text) |
|
Text |
UPPER |
Converts text to uppercase. |
UPPER(Text) |