MS Excel Functions

The following sections list all of the worksheet functions by category.

Database functions
Function Description
DAVERAGE- Returns the average of selected database entries
DCOUNT- Counts the cells that contain numbers in a database
DCOUNTA- Counts nonblank cells in a database
DGET- Extracts from a database a single record that matches the specified criteria
DMAX- Returns the maximum value from selected database entries
DMIN- Returns the minimum value from selected database entries
DPRODUCT- Multiplies the values in a particular field of records that match the criteria in a database
DSTDEV- Estimates the standard deviation based on a sample of selected database entries
DSTDEVP- Calculates the standard deviation based on the entire population of selected database entries
DSUM- Adds the numbers in the field column of records in the database that match the criteria
DVAR- Estimates variance based on a sample from selected database entries
DVARP- Calculates variance based on the entire population of selected database entries

Date and time functions
Function Description
DATE- Returns the serial number of a particular date
DATEVALUE- Converts a date in the form of text to a serial number
DAY- Converts a serial number to a day of the month
DAYS360- Calculates the number of days between two dates based on a 360-day year
EDATE- Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH- Returns the serial number of the last day of the month before or after a specified number of months
HOUR- Converts a serial number to an hour
MINUTE- Converts a serial number to a minute
MONTH- Converts a serial number to a month
NETWORKDAYS- Returns the number of whole workdays between two dates
NOW- Returns the serial number of the current date and time
SECOND- Converts a serial number to a second
TIME- Returns the serial number of a particular time
TIMEVALUE- Converts a time in the form of text to a serial number
TODAY- Returns the serial number of today’s date
WEEKDAY- Converts a serial number to a day of the week
WEEKNUM- Converts a serial number to a number representing where the week falls numerically with a year
WORKDAY- Returns the serial number of the date before or after a specified number of workdays
YEAR- Converts a serial number to a year
YEARFRAC- Returns the year fraction representing the number of whole days between start_date and end_date

Engineering functions
Function Description
BESSELI- Returns the modified Bessel function In(x)
BESSELJ- Returns the Bessel function Jn(x)
BESSELK- Returns the modified Bessel function Kn(x)
BESSELY- Returns the Bessel function Yn(x)
BIN2DEC- Converts a binary number to decimal
BIN2HEX- Converts a binary number to hexadecimal
BIN2OCT- Converts a binary number to octal
COMPLEX- Converts real and imaginary coefficients into a complex number
CONVERT- Converts a number from one measurement system to another
DEC2BIN- Converts a decimal number to binary
DEC2HEX- Converts a decimal number to hexadecimal
DEC2OCT- Converts a decimal number to octal
DELTA- Tests whether two values are equal
ERF- Returns the error function
ERFC- Returns the complementary error function
GESTEP- Tests whether a number is greater than a threshold value
HEX2BIN- Converts a hexadecimal number to binary
HEX2DEC- Converts a hexadecimal number to decimal
HEX2OCT- Converts a hexadecimal number to octal
IMABS- Returns the absolute value (modulus) of a complex number
IMAGINARY- Returns the imaginary coefficient of a complex number
IMARGUMENT- Returns the argument theta, an angle expressed in radians
IMCONJUGATE- Returns the complex conjugate of a complex number
IMCOS- Returns the cosine of a complex number
IMDIV- Returns the quotient of two complex numbers
IMEXP- Returns the exponential of a complex number
IMLN- Returns the natural logarithm of a complex number
IMLOG10- Returns the base-10 logarithm of a complex number
IMLOG2- Returns the base-2 logarithm of a complex number
IMPOWER- Returns a complex number raised to an integer power
IMPRODUCT- Returns the product of from 2 to 29 complex numbers
IMREAL- Returns the real coefficient of a complex number
IMSIN- Returns the sine of a complex number
IMSQRT- Returns the square root of a complex number
IMSUB- Returns the difference between two complex numbers
IMSUM- Returns the sum of complex numbers
OCT2BIN- Converts an octal number to binary
OCT2DEC- Converts an octal number to decimal
OCT2HEX- Converts an octal number to hexadecimal

Financial functions
Function Description
ACCRINT- Returns the accrued interest for a security that pays periodic interest
ACCRINTM- Returns the accrued interest for a security that pays interest at maturity
AMORDEGRC- Returns the depreciation for each accounting period by using a depreciation coefficient
AMORLINC- Returns the depreciation for each accounting period
COUPDAYBS- Returns the number of days from the beginning of the coupon period to the settlement date
COUPDAYS- Returns the number of days in the coupon period that contains the settlement date
COUPDAYSNC- Returns the number of days from the settlement date to the next coupon date
COUPNCD- Returns the next coupon date after the settlement date
COUPNUM- Returns the number of coupons payable between the settlement date and maturity date
COUPPCD- Returns the previous coupon date before the settlement date
CUMIPMT- Returns the cumulative interest paid between two periods
CUMPRINC- Returns the cumulative principal paid on a loan between two periods
DB- Returns the depreciation of an asset for a specified period by using the fixed-declining balance method
DDB- Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify
DISC- Returns the discount rate for a security
DOLLARDE- Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
DOLLARFR- Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
DURATION- Returns the annual duration of a security with periodic interest payments
EFFECT- Returns the effective annual interest rate
FV- Returns the future value of an investment
FVSCHEDULE- Returns the future value of an initial principal after applying a series of compound interest rates
INTRATE- Returns the interest rate for a fully invested security
IPMT- Returns the interest payment for an investment for a given period
IRR- Returns the internal rate of return for a series of cash flows
ISPMT- Calculates the interest paid during a specific period of an investment
MDURATION- Returns the Macauley modified duration for a security with an assumed par value of $100
MIRR- Returns the internal rate of return where positive and negative cash flows are financed at different rates
NOMINAL- Returns the annual nominal interest rate
NPER- Returns the number of periods for an investment
NPV- Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
ODDFPRICE- Returns the price per $100 face value of a security with an odd first period
ODDFYIELD- Returns the yield of a security with an odd first period
ODDLPRICE- Returns the price per $100 face value of a security with an odd last period
ODDLYIELD- Returns the yield of a security with an odd last period
PMT- Returns the periodic payment for an annuity
PPMT- Returns the payment on the principal for an investment for a given period
PRICE- Returns the price per $100 face value of a security that pays periodic interest
PRICEDISC- Returns the price per $100 face value of a discounted security
PRICEMAT- Returns the price per $100 face value of a security that pays interest at maturity
PV- Returns the present value of an investment
RATE- Returns the interest rate per period of an annuity
RECEIVED- Returns the amount received at maturity for a fully invested security
SLN- Returns the straight-line depreciation of an asset for one period
SYD- Returns the sum-of-years’ digits depreciation of an asset for a specified period
TBILLEQ- Returns the bond-equivalent yield for a Treasury bill
TBILLPRICE- Returns the price per $100 face value for a Treasury bill
TBILLYIELD- Returns the yield for a Treasury bill
VDB- Returns the depreciation of an asset for a specified or partial period by using a declining balance method
XIRR- Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV- Returns the net present value for a schedule of cash flows that is not necessarily periodic
YIELD- Returns the yield on a security that pays periodic interest
YIELDDISC- Returns the annual yield for a discounted security; for example, a Treasury bill
YIELDMAT- Returns the annual yield of a security that pays interest at maturity

Information functions
Function Description
CELL- Returns information about the formatting, location, or contents of a cell
ERROR.TYPE- Returns a number corresponding to an error type
INFO- Returns information about the current operating environment
ISBLANK- Returns TRUE if the value is blank
ISERR- Returns TRUE if the value is any error value except #N/A
ISERROR- Returns TRUE if the value is any error value
ISEVEN- Returns TRUE if the number is even
ISLOGICAL- Returns TRUE if the value is a logical value
ISNA- Returns TRUE if the value is the #N/A error value
ISNONTEXT- Returns TRUE if the value is not text
ISNUMBER- Returns TRUE if the value is a number
ISODD- Returns TRUE if the number is odd
ISREF- Returns TRUE if the value is a reference
ISTEXT- Returns TRUE if the value is text
N- Returns a value converted to a number
NA- Returns the error value #N/A
TYPE- Returns a number indicating the data type of a value

Logical functions
Function Description
AND- Returns TRUE if all of its arguments are TRUE
FALSE- Returns the logical value FALSE
IF- Specifies a logical test to perform
NOT- Reverses the logic of its argument
OR- Returns TRUE if any argument is TRUE
TRUE- Returns the logical value TRUE

Lookup and reference functions
Function Description
ADDRESS- Returns a reference as text to a single cell in a worksheet
AREAS- Returns the number of areas in a reference
CHOOSE- Chooses a value from a list of values
COLUMN- Returns the column number of a reference
COLUMNS- Returns the number of columns in a reference
GETPIVOTDATA- Returns data stored in a PivotTable
HLOOKUP- Looks in the top row of an array and returns the value of the indicated cell
HYPERLINK- Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
INDEX- Uses an index to choose a value from a reference or array
INDIRECT- Returns a reference indicated by a text value
LOOKUP- Looks up values in a vector or array
MATCH- Looks up values in a reference or array
OFFSET- Returns a reference offset from a given reference
ROW- Returns the row number of a reference
ROWS- Returns the number of rows in a reference
RTD- Retrieves real-time data from a program that supports COM automation(Automation: A way to work with an application’s objects from another application or development tool. Formerly called OLE Automation, Automation is an industry standard and a feature of the Component Object Model (COM).)
TRANSPOSE- Returns the transpose of an array
VLOOKUP- Looks in the first column of an array and moves across the row to return the value of a cell

Math and trigonometry functions
Function Description
ABS- Returns the absolute value of a number
ACOS- Returns the arccosine of a number
ACOSH- Returns the inverse hyperbolic cosine of a number
ASIN- Returns the arcsine of a number
ASINH- Returns the inverse hyperbolic sine of a number
ATAN- Returns the arctangent of a number
ATAN2- Returns the arctangent from x- and y-coordinates
ATANH- Returns the inverse hyperbolic tangent of a number
CEILING- Rounds a number to the nearest integer or to the nearest multiple of significance
COMBIN Returns the number of combinations for a given number of objects
COS- Returns the cosine of a number
COSH- Returns the hyperbolic cosine of a number
DEGREES- Converts radians to degrees
EVEN- Rounds a number up to the nearest even integer
EXP- Returns e raised to the power of a given number
FACT- Returns the factorial of a number
FACTDOUBLE- Returns the double factorial of a number
FLOOR- Rounds a number down, toward zero
GCD- Returns the greatest common divisor
INT- Rounds a number down to the nearest integer
LCM- Returns the least common multiple
LN- Returns the natural logarithm of a number
LOG- Returns the logarithm of a number to a specified base
LOG10- Returns the base-10 logarithm of a number
MDETERM- Returns the matrix determinant of an array
MINVERSE- Returns the matrix inverse of an array
MMULT- Returns the matrix product of two arrays
MOD- Returns the remainder from division
MROUND- Returns a number rounded to the desired multiple
MULTINOMIAL- Returns the multinomial of a set of numbers
ODD- Rounds a number up to the nearest odd integer
PI- Returns the value of pi
POWER- Returns the result of a number raised to a power
PRODUCT- Multiplies its arguments
QUOTIENT- Returns the integer portion of a division
RADIANS- Converts degrees to radians
RAND- Returns a random number between 0 and 1
RANDBETWEEN- Returns a random number between the numbers you specify
ROMAN- Converts an arabic numeral to roman, as text
ROUND- Rounds a number to a specified number of digits
ROUNDDOWN- Rounds a number down, toward zero
ROUNDUP- Rounds a number up, away from zero
SERIESSUM- Returns the sum of a power series based on the formula
SIGN- Returns the sign of a number
SIN- Returns the sine of the given angle
SINH- Returns the hyperbolic sine of a number
SQRT- Returns a positive square root
SQRTPI- Returns the square root of (number * pi)
SUBTOTAL- Returns a subtotal in a list or database
SUM- Adds its arguments
SUMIF- Adds the cells specified by a given criteria
SUMPRODUCT- Returns the sum of the products of corresponding array components
SUMSQ- Returns the sum of the squares of the arguments
SUMX2MY2- Returns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2- Returns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2- Returns the sum of squares of differences of corresponding values in two arrays
TAN- Returns the tangent of a number
TANH- Returns the hyperbolic tangent of a number
TRUNC- Truncates a number to an integer

Statistical functions
Function Description
AVEDEV- Returns the average of the absolute deviations of data points from their mean
AVERAGE- Returns the average of its arguments
AVERAGEA- Returns the average of its arguments, including numbers, text, and logical values
BETADIST- Returns the beta cumulative distribution function
BETAINV- Returns the inverse of the cumulative distribution function for a specified beta distribution
BINOMDIST- Returns the individual term binomial distribution probability
CHIDIST- Returns the one-tailed probability of the chi-squared distribution
CHIINV- Returns the inverse of the one-tailed probability of the chi-squared distribution
CHITEST- Returns the test for independence
CONFIDENCE- Returns the confidence interval for a population mean
CORREL- Returns the correlation coefficient between two data sets
COUNT- Counts how many numbers are in the list of arguments
COUNTA- Counts how many values are in the list of arguments
COUNTBLANK- Counts the number of blank cells within a range
COUNTIF- Counts the number of nonblank cells within a range that meet the given criteria
COVAR- Returns covariance, the average of the products of paired deviations
CRITBINOM- Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
DEVSQ- Returns the sum of squares of deviations
EXPONDIST- Returns the exponential distribution
FDIST- Returns the F probability distribution
FINV- Returns the inverse of the F probability distribution
FISHER- Returns the Fisher transformation
FISHERINV- Returns the inverse of the Fisher transformation
FORECAST- Returns a value along a linear trend
FREQUENCY- Returns a frequency distribution as a vertical array
FTEST- Returns the result of an F-test
GAMMADIST- Returns the gamma distribution
GAMMAINV- Returns the inverse of the gamma cumulative distribution
GAMMALN- Returns the natural logarithm of the gamma function, Γ(x)
GEOMEAN- Returns the geometric mean
GROWTH- Returns values along an exponential trend
HARMEAN- Returns the harmonic mean
HYPGEOMDIST- Returns the hypergeometric distribution
INTERCEPT- Returns the intercept of the linear regression line
KURT- Returns the kurtosis of a data set
LARGE- Returns the k-th largest value in a data set
LINEST- Returns the parameters of a linear trend
LOGEST- Returns the parameters of an exponential trend
LOGINV- Returns the inverse of the lognormal distribution
LOGNORMDIST- Returns the cumulative lognormal distribution
MAX- Returns the maximum value in a list of arguments
MAXA- Returns the maximum value in a list of arguments, including numbers, text, and logical values
MEDIAN- Returns the median of the given numbers
MIN- Returns the minimum value in a list of arguments
MINA- Returns the smallest value in a list of arguments, including numbers, text, and logical values
MODE- Returns the most common value in a data set
NEGBINOMDIST- Returns the negative binomial distribution
NORMDIST- Returns the normal cumulative distribution
NORMINV- Returns the inverse of the normal cumulative distribution
NORMSDIST- Returns the standard normal cumulative distribution
NORMSINV- Returns the inverse of the standard normal cumulative distribution
PEARSON- Returns the Pearson product moment correlation coefficient
PERCENTILE- Returns the k-th percentile of values in a range
PERCENTRANK- Returns the percentage rank of a value in a data set
PERMUT- Returns the number of permutations for a given number of objects
POISSON- Returns the Poisson distribution
PROB- Returns the probability that values in a range are between two limits
QUARTILE- Returns the quartile of a data set
RANK- Returns the rank of a number in a list of numbers
RSQ- Returns the square of the Pearson product moment correlation coefficient
SKEW- Returns the skewness of a distribution
SLOPE- Returns the slope of the linear regression line
SMALL- Returns the k-th smallest value in a data set
STANDARDIZE- Returns a normalized value
STDEV- Estimates standard deviation based on a sample
STDEVA- Estimates standard deviation based on a sample, including numbers, text, and logical values
STDEVP- Calculates standard deviation based on the entire population
STDEVPA- Calculates standard deviation based on the entire population, including numbers, text, and logical values
STEYX- Returns the standard error of the predicted y-value for each x in the regression
TDIST- Returns the Student’s t-distribution
TINV- Returns the inverse of the Student’s t-distribution
TREND- Returns values along a linear trend
TRIMMEAN- Returns the mean of the interior of a data set
TTEST- Returns the probability associated with a Student’s t-test
VAR- Estimates variance based on a sample
VARA- Estimates variance based on a sample, including numbers, text, and logical values
VARP- Calculates variance based on the entire population
VARPA- Calculates variance based on the entire population, including numbers, text, and logical values
WEIBULL Returns the Weibull distribution
ZTEST- Returns the one-tailed probability-value of a z-test

Text functions
REPLACE, REPLACEB
Function Description
ASC- Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
BAHTTEXT- Converts a number to text, using the ß (baht) currency format
CHAR- Returns the character specified by the code number
CLEAN- Removes all nonprintable characters from text
CODE- Returns a numeric code for the first character in a text string
CONCATENATE- Joins several text items into one text item
DOLLAR- Converts a number to text, using the $ (dollar) currency format
EXACT- Checks to see if two text values are identical
FIND, FINDB- Finds one text value within another (case-sensitive)
FIXED- Formats a number as text with a fixed number of decimals
JIS- Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
LEFT, LEFTB- Returns the leftmost characters from a text value
LEN, LENB- Returns the number of characters in a text string
LOWER Converts text to lowercase
MID, MIDB- Returns a specific number of characters from a text string starting at the position you specify
PHONETIC- Extracts the phonetic (furigana) characters from a text string
PROPER- Capitalizes the first letter in each word of a text value
Replaces characters within text
REPT- Repeats text a given number of times
RIGHT, RIGHTB- Returns the rightmost characters from a text value
SEARCH, SEARCHB- Finds one text value within another (not case-sensitive)
SUBSTITUTE- Substitutes new text for old text in a text string
T- Converts its arguments to text
TEXT- Formats a number and converts it to text
TRIM- Removes spaces from text
UPPER- Converts text to uppercase
VALUE- Converts a text argument to a number

External functions
Function Description
EUROCONVERT- Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation)
SQL.REQUEST- Connects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming