List of functions by category

All the functions covered in Formulas and Functions Help are listed below by category. You can also browse the functions by clicking the links at the left.

Date and time functions

The date and time functions help you work with dates and times to solve problems such as finding the number of working days between two dates or finding the name of the day of the week a date will fall on.

DATE	Combines separate values for year, month, and day and returns a date/time value. Although dates can usually be entered directly as strings (for example, “12/31/2010”), using the DATE function ensures the date will be interpreted consistently, regardless of the date format specified in your Date & Time preferences.
DATEDIF	Returns the number of days, months, or years between two dates.
DATEVALUE	Returns a date/time value for a given date string. This function is provided for compatibility with other spreadsheet apps.
DAY	Returns the day of the month for a given date/time value.
DAYNAME	Returns the name of the day of the week for a date/time value or a number. Day 1 is Sunday.
DAYS360	Returns the number of days between two dates based on twelve 30-day months and a 360-day year.
EDATE	Returns a date that is some number of months before or after a given date.
EOMONTH	Returns a date that is the last day of the month some number of months before or after a given date.
HOUR	Returns the hour for a given date/time value.
MINUTE	Returns the minutes for a given date/time value.
MONTH	Returns the month for a given date/time value.
MONTHNAME	Returns the name of the month for a number. Month 1 is January.
NETWORKDAYS	Returns the number of working days between two dates. Working days exclude weekends and any other specified dates.
NOW	Returns a date/time value based on the current time shown on your computer's clock.
SECOND	Returns the seconds for a given date/time value.
TIME	Converts separate values for hours, minutes, and seconds into a date/time value.
TIMEVALUE	Returns the time as a decimal fraction of a 24-hour day for a given date/time value or time string.
TODAY	Returns the current date based on your computer's clock. The time is set to 12:00 a.m.
WEEKDAY	Returns a number that is the day of the week for a given date.
WEEKNUM	Returns the number of the week within the year for a given date.
WORKDAY	Returns the date that is the given number of working days before or after a given date. Working days exclude weekends and any other specified dates.
YEAR	Returns the year for a given date/time value.
YEARFRAC	Returns the fraction of a year represented by the number of whole days between two dates.

Duration functions

The duration functions help you work with periods of time (durations) by converting between different time periods, such as hours, days, and weeks.

DUR2DAYS	Converts a duration value to a number of days.
DUR2HOURS	Converts a duration value to a number of hours.
DUR2MILLISECONDS	Converts a duration value to a number of milliseconds.
DUR2MINUTES	Converts a duration value to a number of minutes.
DUR2SECONDS	Converts a duration value to a number of seconds.
DUR2WEEKS	Converts a duration value to a number of weeks.
DURATION	Combines separate values for weeks, days, hours, minutes, seconds, and milliseconds and returns a duration value.
STRIPDURATION	Evaluates a given value and returns either the number of days represented, if a duration value, or the given value. This function is included for compatibility with other spreadsheet apps.

Engineering functions

The engineering functions help you calculate some common engineering values and convert between different numeric bases.

BASETONUM	Converts a number of the specified base into a number in base 10.
BESSELJ	Returns the integer Bessel function J_n(x).
BESSELY	Returns the integer Bessel function Y_n(x).
BIN2DEC	Converts a binary number to the corresponding decimal number.
BIN2HEX	Converts a binary number to the corresponding hexadecimal number.
BIN2OCT	Converts a binary number to the corresponding octal number.
CONVERT	Converts a number from one measurement system to its corresponding value in another measurement system.
DEC2BIN	Converts a decimal number to the corresponding binary number.
DEC2HEX	Converts a decimal number to the corresponding hexadecimal number.
DEC2OCT	Converts a decimal number to the corresponding octal number.
DELTA	Determines whether two values are exactly equal. This function uses exact equality. By comparison, the = comparison operator uses string-based equality.
ERF	Returns the error function integrated between two values.
ERFC	Returns the complementary ERF function integrated between a given lower bound and infinity.
GESTEP	Determines if one value is greater than or exactly equal to another value. This function uses exact equality. By comparison, the = comparison operator uses string-based equality.
HEX2BIN	Converts a hexadecimal number to the corresponding binary number.
HEX2DEC	Converts a hexadecimal number to the corresponding decimal number.
HEX2OCT	Converts a hexadecimal number to the corresponding octal number.
NUMTOBASE	Converts a number from base 10 into a number in the specified base.
OCT2BIN	Converts an octal number to the corresponding binary number.
OCT2DEC	Converts an octal number to the corresponding decimal number.
OCT2HEX	Converts an octal number to the corresponding hexadecimal number.

Financial functions

The financial functions help you work with cash flows, depreciable assets, annuities, and investments by solving problems such as the amount of annual depreciation of an asset, the interest earned on an investment, and the current market price of a bond.

ACCRINT	Returns the accrued interest on a security that pays periodic interest. The amount returned is the total interest accrued since the issue date, not the amount accrued since the last coupon payment.
ACCRINTM	Calculates the total accrued interest added to the purchase price of a security and paid to the seller when the security pays interest only at maturity.
BONDDURATION	Returns the weighted average of the present value of the cash flows for an assumed par value of $100.
BONDMDURATION	Returns the modified weighted average of the present value of the cash flows for an assumed par value of $100.
COUPDAYBS	Returns the number of days between the beginning of the coupon period in which settlement occurs and the settlement date.
COUPDAYS	Returns the number of days in the coupon period in which settlement occurs.
COUPDAYSNC	Returns the number of days between the settlement date and the end of the coupon period in which settlement occurs.
COUPNUM	Returns the number of coupons remaining to be paid between the settlement date and the maturity date.
CUMIPMT	Returns the total interest included in loan or annuity payments over a chosen time interval based on fixed periodic payments and a fixed interest rate.
CUMPRINC	Returns the total principal included in loan or annuity payments over a chosen time interval based on fixed periodic payments and a fixed interest rate.
DB	Returns the amount of depreciation of an asset for a specified period using the fixed-declining balance method.
DDB	Returns the amount of depreciation of an asset based on a specified depreciation rate.
DISC	Returns the annual discount rate of a security that pays no interest and is sold at a discount to its redemption value.
EFFECT	Returns the effective annual interest rate from the nominal annual interest rate based on the number of compounding periods per year.
FV	Returns the future value of an investment based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate.
INTRATE	Returns the effective annual interest rate for a security that pays interest only at maturity.
IPMT	Returns the interest portion of a specified loan or annuity payment based on fixed, periodic payments and a fixed interest rate.
IRR	Returns the internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals.
ISPMT	Returns the periodic interest portion for a fixed rate loan where an equal principal reduction occurs at the beginning of each period and an interest payment on the outstanding balance is made at the end of each period. This function is provided primarily for compatibility with tables imported from other spreadsheet apps.
MIRR	Returns the modified internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals. The rate earned on positive cash flows and the rate paid to finance negative cash flows can differ.
NOMINAL	Returns the nominal annual interest rate from the effective annual interest rate based on the number of compounding periods per year.
NPER	Returns the number of payment periods for a loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate.
NPV	Returns the net present value of an investment based on a series of potentially irregular cash flows that occur at regular time intervals.
PMT	Returns the fixed periodic payment for a loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate.
PPMT	Returns the principal portion of a specified loan or annuity payment based on fixed periodic payments and a fixed interest rate.
PRICE	Returns the price of a security that pays periodic interest per $100 of redemption (par) value.
PRICEDISC	Returns the price of a security that is sold at a discount to redemption value and does not pay interest per $100 of redemption (par) value.
PRICEMAT	Returns the price of a security that pays interest only at maturity per $100 of redemption (par) value.
PV	Returns the present value of an investment or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate.
RATE	Returns the interest rate of an investment, loan, or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate.
RECEIVED	Returns the maturity value for a security that pays interest only at maturity.
SLN	Returns the depreciation of an asset for a single period using the straight-line method.
SYD	Returns the amount of depreciation of an asset for a specified period using the sum-of-the-years-digits method.
VDB	Returns the amount of depreciation of an asset over a chosen time interval, based on a specified depreciation rate.
YIELD	Returns the effective annual interest rate for a security that pays regular periodic interest.
YIELDDISC	Returns the effective annual interest rate for a security that is sold at a discount to redemption value and pays no interest.
YIELDMAT	Returns the effective annual interest rate for a security that only pays interest at maturity.

Logical and information functions

The logical and information functions help you evaluate the contents of cells and help to determine how to work with cell contents or formula results.

AND	Returns boolean value TRUE if all arguments are true, and the boolean value FALSE otherwise.
FALSE	Returns the boolean value FALSE. This function is included for compatibility with tables imported from other spreadsheet apps.
IF	Returns one of two values, depending on whether a specified expression evaluates to a boolean value of TRUE or FALSE.
IFERROR	Returns a value that you specify if a given value evaluates to an error; otherwise it returns the given value.
ISBLANK	Returns the boolean value TRUE if the specified cell is empty and the boolean value FALSE otherwise.
ISERROR	Returns the boolean value TRUE if a given expression evaluates to an error and the boolean value FALSE otherwise.
ISEVEN	Returns the boolean value TRUE if the given number is even (leaves no remainder when divided by 2); otherwise it returns the boolean value FALSE.
ISODD	Returns the boolean value TRUE if the given number is odd (leaves a remainder when divided by 2); otherwise it returns the boolean value FALSE.
NOT	Returns the opposite of the boolean value of a specified expression.
OR	Returns the boolean value TRUE if any argument is true; otherwise it returns the boolean value FALSE.
TRUE	Returns the boolean value TRUE. This function is included for compatibility with tables imported from other spreadsheet apps.

Numeric functions

The numeric functions help you calculate commonly used mathematical values.

ABS	Returns the absolute value of a number or duration.
CEILING	Rounds a number away from zero to the nearest multiple of the specified factor.
COMBIN	Returns the number of different ways you can combine a number of items into groups of a specific size, ignoring the order within the groups.
EVEN	Rounds a number away from zero to the next even number.
EXP	Returns e (the base of natural logarithms) raised to the specified power.
FACT	Returns the factorial of a number.
FACTDOUBLE	Returns the double factorial of a number.
FLOOR	Rounds a number toward zero to the nearest multiple of the specified factor.
GCD	Returns the greatest common divisor of a collection of number values.
INT	Returns the nearest integer that is less than or equal to the number.
LCM	Returns the least common multiple of a collection of number values.
LN	Returns the natural logarithm of a number (the power to which e must be raised to result in the number).
LOG	Returns the logarithm of a number using a specified base.
LOG10	Returns the base-10 logarithm of a number.
MOD	Returns the remainder from a division.
MROUND	Rounds a number to the nearest multiple of a specified factor.
MULTINOMIAL	Returns the closed form of the multinomial coefficient of the given numbers.
ODD	Rounds a number away from zero to the next odd number.
PI	Returns the approximate value of π (pi), the ratio of a circle’s circumference to its diameter.
POLYNOMIAL	Evaluates a polynomial at a given point.
POWER	Returns a number raised to a power.
PRODUCT	Returns the product of a collection of number values.
QUOTIENT	Returns the integer quotient of two numbers.
RAND	Returns a random number that is greater than or equal to 0 and less than 1.
RANDBETWEEN	Returns a random integer within the specified range.
ROMAN	Converts a number to Roman numerals.
ROUND	Returns a number rounded to the specified number of places.
ROUNDDOWN	Returns a number rounded toward zero (rounded down) to the specified number of places.
ROUNDUP	Returns a number rounded away from zero (rounded up) to the specified number of places.
SERIESSUM	Computes and returns the sum of a power series. The coefficients are for successive powers of a given value incremented by a given step value.
SIGN	Returns 1 when a given number is positive, –1 when it is negative, and 0 when it is zero.
SQRT	Returns the square root of a number value.
SQRTPI	Returns the square root of a number value after it has been multiplied by π (pi).
SUM	Returns the sum of a collection of numbers.
SUMIF	Returns the sum of a collection of numbers, including only numbers that satisfy a specified condition.
SUMIFS	Returns the sum of the cells in a collection where the test values meet the given conditions.
SUMPRODUCT	Returns the sum of the products of corresponding numbers in one or more collections.
SUMSQ	Returns the sum of the squares of a collection of number values.
SUMX2MY2	Returns the sum of the difference of the squares of corresponding values in two collections.
SUMX2PY2	Returns the sum of the squares of corresponding values in two collections.
SUMXMY2	Returns the sum of the squares of the differences between corresponding values in two collections.
TRUNC	Truncates a number to the specified number of digits.

Reference functions

The reference functions help you find data within tables and retrieve data from cells.

ADDRESS	Constructs a cell address string from separate row, column, and table identifiers.
AREAS	Returns the number of ranges the function references.
CHOOSE	Returns a value from a collection of values based on a specified index value.
COLUMN	Returns the column number of the column containing a specified cell.
COLUMNS	Returns the number of columns included in a specified collection of cells.
HLOOKUP	Returns a value from a collection of rows by using the top row of values to pick a column and a row number to pick a row within that column.
HYPERLINK	Creates a clickable link that opens a webpage or new email message.
INDEX	Returns the value in the cell located at the intersection of the specified row and column within a collection of cells or from an array returned by an array function.
INDIRECT	Returns the contents of a cell or range referenced by an address specified as a string value.
INTERSECT.RANGES	Returns a single value or an array of values contained at the intersection of the specified collections.
LOOKUP	Finds a match for a given search value in one collection, and then returns the value in the cell with the same relative position in a second collection.
MATCH	Returns the position of a value within a collection.
OFFSET	Returns an array of cells that is the specified number of rows and columns away from the specified base cell.
ROW	Returns the row number of the row containing a specified cell.
ROWS	Returns the number of rows included in a specified collection of cells.
TRANSPOSE	Returns a vertical collection of cells as a horizontal array of cells, or vice versa.
UNION.RANGES	Returns an array that represents a collection representing the union of the specified ranges.
VLOOKUP	Returns a value from a collection of columns by using the left column of values to pick a row and a column number to pick a column in that row.

Statistical functions

The statistical functions help you manipulate and analyze collections of data using a variety of measures and statistical techniques.

AVEDEV	Returns the average of the difference of a set of numbers from their average (arithmetic mean).
AVERAGE	Returns the average (arithmetic mean) of a set of numbers.
AVERAGEA	Returns the average (arithmetic mean) of a set containing any value.
AVERAGEIF	Returns the average (arithmetic mean) of the cells in a set that meet a given condition.
AVERAGEIFS	Returns the average (arithmetic mean) of the cells in a given set where one or more sets meet one or more related conditions.
BETADIST	Returns the cumulative beta distribution probability value.
BETAINV	Returns the inverse of the given cumulative beta distribution probability value.
BINOMDIST	Returns the individual term binomial distribution probability of the specified form.
CHIDIST	Returns the one-tailed probability of the chi-square distribution.
CHIINV	Returns the inverse of the one-tailed probability of the chi-square distribution.
CHITEST	Returns the value from the chi-square distribution for the given data.
CONFIDENCE	Returns a value for creating a statistical confidence interval for a sample from a population with a known standard deviation.
CORREL	Returns the correlation between two sets using linear regression analysis.
COUNT	Returns the number of its arguments that contain numbers, numeric expressions, or dates.
COUNTA	Returns the number of its arguments that are not empty.
COUNTBLANK	Returns the number of cells in a collection that are empty.
COUNTIF	Returns the number of cells in a collection that satisfy a given condition.
COUNTIFS	Returns the number of cells in one or more collections that satisfy given conditions (one condition per collection).
COVAR	Returns the covariance of two sets of number values.
CRITBINOM	Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a given value.
DEVSQ	Returns the sum of the squares of deviations of a set of numbers from their average (arithmetic mean).
EXPONDIST	Returns the exponential distribution of the specified form.
FDIST	Returns the F probability distribution.
FINV	Returns the inverse of the F probability distribution.
FORECAST	Returns the forecasted y value for a given x based on sample values using linear regression analysis.
FREQUENCY	Returns an array of how often data values occur within a collection of interval values.
GAMMADIST	Returns the gamma distribution in the specified form.
GAMMAINV	Returns the inverse gamma cumulative distribution.
GAMMALN	Returns the natural logarithm of the gamma function, G(x).
GEOMEAN	Returns the geometric mean.
HARMEAN	Returns the harmonic mean.
INTERCEPT	Returns the y-intercept of the best-fit line for the collection using linear regression analysis.
LARGE	Returns the n^th-largest value within a collection. The largest value is ranked number 1.
LINEST	Returns an array of the statistics for a straight line that best fits the given data using the “least squares” method.
LOGINV	Returns the inverse of the log-normal cumulative distribution function of x.
LOGNORMDIST	Returns the log-normal distribution.
MAX	Returns the largest numeric value in a set of number values, date/time values, or duration values.
MAXA	Returns the largest number in a set containing any values.
MEDIAN	Returns the median value of a set of numeric values. The median is the value where half the values in the set are less than the median and half are greater.
MIN	Returns the smallest numeric value in a set of number values, date/time values, or duration values.
MINA	Returns the smallest numeric value in a set containing any values.
MODE	Returns the most frequently occurring value in a set of numeric values.
NEGBINOMDIST	Returns the negative binomial distribution.
NORMDIST	Returns the normal distribution of the specified function form.
NORMINV	Returns the inverse of the cumulative normal distribution.
NORMSDIST	Returns the standard normal distribution.
NORMSINV	Returns the inverse of the cumulative standard normal distribution.
PERCENTILE	Returns the value within a set of numeric values that corresponds to a particular percentile.
PERCENTRANK	Returns the rank of a value within a set of numeric values as a percentage of the collection.
PERMUT	Returns the number of permutations for a given number of objects that can be selected from a total number of objects.
POISSON	Returns the probability that a specific number of events will occur using the Poisson distribution.
PROB	Returns the probability of a collection of values if you know the probabilities of the individual values.
QUARTILE	Returns the value for the specified quartile from a given set of numbers.
RANK	Returns the rank of a number within a set of numeric values.
SLOPE	Returns the slope of the best-fit line for the set using linear regression analysis.
SMALL	Returns the n^th-smallest value within a set of values. The smallest value is ranked number 1.
STANDARDIZE	Returns a normalized value from a distribution characterized by a given mean and standard deviation.
STDEV	Returns the standard deviation—a measure of dispersion—of a set of numeric values based on their sample (unbiased) variance.
STDEVA	Returns the standard deviation—a measure of dispersion—of a set of any values, based on the sample (unbiased) variance.
STDEVP	Returns the standard deviation—a measure of dispersion—of a set of numeric values based on their population (true) variance.
STDEVPA	Returns the standard deviation—a measure of dispersion—of a set of any values, based on the population (true) variance.
TDIST	Returns the probability from the student’s t-distribution.
TINV	Returns the t value (a function of the probability and degrees of freedom) from the student’s t-distribution.
TTEST	Returns the probability associated with a student’s t-test, based on the t-distribution function.
VAR	Returns the sample (unbiased) variance—a measure of dispersion—of a set of numeric values.
VARA	Returns the sample (unbiased) variance—a measure of dispersion—of a set of any values.
VARP	Returns the population (true) variance—a measure of dispersion—of a set of numeric values.
VARPA	Returns the sample (unbiased) variance—a measure of dispersion—of a set of any values.
WEIBULL	Returns the Weibull distribution. The Weibull distribution is one of the continuous probability distributions.
ZTEST	Returns the one-tailed probability value of the Z-test.

Text functions

The text functions help you work with strings of characters.

CHAR	Returns the character that corresponds to a decimal Unicode character code.
CLEAN	Removes most common nonprinting characters (Unicode character codes 0–31) from text.
CODE	Returns the decimal Unicode number of the first character in a specified string.
CONCATENATE	Joins (concatenates) strings.
DOLLAR	Returns a string formatted as a dollar amount for a given number value.
EXACT	Returns TRUE if the argument strings are identical in case and content.
FIND	Returns the starting position of one string within another.
FIXED	Rounds a number value to the specified number of decimal places and then returns the result as a string value.
LEFT	Returns a string value consisting of the specified number of characters from the left end of a given string value.
LEN	Returns the number of characters in a string value.
LOWER	Returns a string value that is entirely lowercase, regardless of the case of the characters in the specified string value.
MID	Returns a string value consisting of the given number of characters from a string value starting at the specified position.
PLAINTEXT	Returns a string value stripped of any rich text attributes in the input value.
PROPER	Returns a string value where the first letter of each word is uppercase and all remaining characters are lowercase, regardless of the case of the characters in the specified string value.
REPLACE	Returns a string value where a specified number of characters of a given string value have been replaced with a new string value.
REPT	Returns a string value that contains a given string value repeated a specified number of times.
RIGHT	Returns a string value consisting of the specified number of characters from the right end of a given string value.
SEARCH	Returns the starting position of one string value within another, ignoring case and allowing wildcards.
SUBSTITUTE	Returns a string value where the specified characters of a given string value have been replaced with a new string value.
T	Returns the text contained in a cell. This function is included for compatibility with tables imported from other spreadsheet apps.
TRIM	Returns a string value based on a given string value, after removing extra spaces.
UPPER	Returns a string value that is entirely uppercase, regardless of the case of the characters in the specified string value.
VALUE	Returns a number value, even if the argument is formatted as text. This function is included for compatibility with tables imported from other spreadsheet apps.

ACOS	Returns the inverse cosine (arccosine) of a number.
ACOSH	Returns the inverse hyperbolic cosine (hyperbolic arccosine) of a number.
ASIN	Returns the arcsine (the inverse sine) of a number.
ASINH	Returns the inverse hyperbolic sine of a number.
ATAN	Returns the inverse tangent (arctangent) of a number.
ATAN2	Returns the angle, relative to the positive x-axis, of the line passing through the origin and the specified point.
ATANH	Returns the inverse hyperbolic tangent of a number.
COS	Returns the cosine of an angle that is expressed in radians.
COSH	Returns the hyperbolic cosine of a number.
DEGREES	Returns the number of degrees in an angle expressed in radians.
RADIANS	Returns the number of radians in an angle expressed in degrees.
SIN	Returns the sine of an angle that is expressed in radians.
SINH	Returns the hyperbolic sine of the specified number.
TAN	Returns the tangent of an angle that is expressed in radians.
TANH	Returns the hyperbolic tangent of the specified number.