Built-In Excel Functions List

Home » Excel-Functions

This page lists the current, built-in Excel Functions, grouped by category. If you know the name of the function you are interested in, you may prefer to select from the alphabetical functions list. Alternatively, select from the groups of functions below, to go to a full function description, including examples of use, related tips & tricks and common errors.

Excel Built-In Function Categories:
Text Functions	Logical Functions
Information Functions	Date and Time Functions
Lookup and Reference Functions	Math and Trig Functions
Statistical Functions	Database Functions
Financial Functions	Engineering Functions
Cube Functions	Excel Operators

Excel Text Functions

Functions to Remove Extra Characters CLEAN Removes all non-printable characters from a supplied text string TRIM Removes duplicate spaces, and spaces at the start and end of a text string Functions to Convert Excel Data Types BAHTTEXT Converts a number, plus the suffix "Baht" into Thai text DOLLAR Converts a supplied number into text, using a currency format FIXED Rounds a supplied number to a specified number of decimal places, and then converts this into text TEXT Converts a supplied value into text, using a user-specified format VALUE Converts a text string into a numeric value Functions to Convert Between Upper & Lower Case LOWER Converts all characters in a supplied text string to lower case PROPER Converts all characters in a supplied text string to proper case (i.e. letters that do not follow another letter are upper case and all other characters are lower case) UPPER Converts all characters in a supplied text string to upper case Converting Between Characters & Numeric Codes CHAR Returns the character that corresponds to a supplied numeric value CODE Returns the numeric code for the first character of a supplied string

Replacing / Substituting Parts of a Text String REPLACE Replaces all or part of a text string with another string (from a user supplied position) SUBSTITUTE Substitutes all occurrences of a search text string, within an original text string, with the supplied replacement text Cutting Up & Piecing Together Text Strings CONCATENATE Joins together two or more text strings LEFT Returns a specified number of characters from the start of a supplied text string MID Returns a specified number of characters from the middle of a supplied text string RIGHT Returns a specified number of characters from the end of a supplied text string REPT Returns a string consisting of a supplied text string, repeated a specified number of times Information Functions LEN Returns the length of a supplied text string FIND Returns the position of a supplied character or text string from within a supplied text string (case-sensitive) SEARCH Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive) EXACT Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive) T Tests whether a supplied value is text and if so, returns the supplied text; If not, returns an empty text string.

Excel Logical Functions

Boolean Operator Functions AND Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise OR Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise NOT Returns a logical value that is the opposite of a user supplied logical value or expression (ie. returns FALSE is the supplied argument is TRUE and returns TRUE if the supplied argument is FALSE)

Conditional Functions IF Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE IFERROR Tests if an initial supplied value (or expression) returns an error, and if so the function returns a supplied value; Otherwise the function returns the initial value. (New in Excel 2007) Functions Returning Constant Values TRUE Simply returns the logical value TRUE FALSE Simply returns the logical value FALSE

Excel Information Functions

Error Information Functions ISERROR Tests if an initial supplied value (or expression) returns an error and if so, returns TRUE; Otherwise returns FALSE ISERR Tests if an initial supplied value (or expression) returns an error (EXCEPT for the #N/A error) and if so, returns TRUE; Otherwise returns FALSE ISNA Tests if an initial supplied value (or expression) returns the Excel #N/A error and if so, returns TRUE; Otherwise returns FALSE ERROR.TYPE Tests a supplied value and returns an integer relating to the supplied value's error type Numerical Information Functions ISNUMBER Tests if a supplied value is a number, and if so, returns TRUE; Otherwise, returns FALSE. ISEVEN Tests if a supplied number (or expression) is an even number, and if so, returns TRUE; Otherwise, returns FALSE. ISODD Tests if a supplied number (or expression) is an odd number, and if so, returns TRUE; Otherwise, returns FALSE. N Converts a non-number value to a number, a date to a serial number, the logical value TRUE to 1 and all other values to 0

Other Data Type Functions ISBLANK Tests if a supplied cell is blank (empty), and if so, returns TRUE; Otherwise, returns FALSE ISLOGICAL Tests if a supplied value is a logical value, and if so, returns TRUE; Otherwise, returns FALSE ISTEXT Tests if a supplied value is text, and if so, returns TRUE; Otherwise, returns FALSE ISNONTEXT Tests if a supplied value is text, and if it is NOT, returns TRUE; Otherwise, returns FALSE ISREF Tests if a supplied value is a reference, and if so, returns TRUE; Otherwise, returns FALSE TYPE Returns information about the data type of a supplied value General Information Functions CELL Returns information about the contents, formatting or location of a given cell INFO Returns information about the the current operating environment Function Returning a Constant Value NA Returns the Excel #N/A error

Excel Date and Time Functions

Before using the Date and Time Excel Functions, it is advised that you ensure you have a clear understanding of the way Excel stores Dates and Times - For details, see the Excel Dates and Times pages.

Creating Dates & Times DATE Returns a date, from a user-supplied year, month and day TIME Returns a time, from a user-supplied hour, minute and second DATEVALUE Converts a text string showing a date, to an integer that represents the date in Excel's date-time code TIMEVALUE Converts a text string showing a time, to a decimal that represents the time in Excel Current Date & Time NOW Returns the current date & time TODAY Returns today's date Extracting The Components of a Time HOUR Returns the hour part of a user-supplied time MINUTE Returns the minute part of a user-supplied time SECOND Returns the seconds part of a user-supplied time Extracting The Components of a Date DAY Returns the day (of the month) from a user-supplied date MONTH Returns the month from a user-supplied date YEAR Returns the year from a user-supplied date WEEKNUM Returns an integer representing the week number (from 1 to 53) of the year from a user-supplied date WEEKDAY Returns an integer representing the day of the week for a supplied date

Performing Calculations with Dates EDATE Returns a date that is the specified number of months before or after an initial supplied start date EOMONTH Returns a date that is the last day of the month which is a specified number of months before or after an initial supplied start date WORKDAY Returns a date that is a supplied number of working days (excluding weekends & holidays) ahead of a given start date WORKDAY.INTL Returns a date that is a supplied number of working days (excluding weekends & holidays) ahead of a given start date, using supplied parameters to specify weekend days (New in Excel 2010) DAYS360 Calculates the number of days between 2 dates, based on a 360-day year (12 x 30 months) NETWORKDAYS Returns the number of whole networkdays (excluding weekends & holidays), between two supplied dates NETWORKDAYS.INTL Returns the number of whole networkdays (excluding weekends & holidays), between two supplied dates, using parameters to specify weekend days (New in Excel 2010) YEARFRAC Calculates the fraction of the year represented by the number of whole days between two dates

Excel Lookup and Reference Functions

Functions To Return References to Cell Ranges ADDRESS Returns a reference, in text format, for a supplied row and column number INDEX Returns a reference to a cell (or range of cells) for requested rows and columns within a supplied range INDIRECT Returns a cell or range reference that is represented by a supplied text string OFFSET Returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range Row / Column / Area Information ROW Returns the row number of a supplied range, or of the current cell COLUMN Returns the column number of a supplied range, or of the current cell ROWS Returns the number of rows in a supplied range COLUMNS Returns the number of columns in a supplied range AREAS Returns the number of areas in a supplied range

Data Lookup Functions HLOOKUP Looks up a supplied value in the first row of a table, and returns the corresponding value from another row VLOOKUP Looks up a supplied value in the first column of a table, and returns the corresponding value from another column LOOKUP Searches for a specific value in one data vector, and returns a value from the corresponding position of a second data vector GETPIVOTDATA Extracts data stored in a Pivot Table CHOOSE Returns one of a list of values, depending on the value of a supplied index number MATCH Finds the relative position of a value in a supplied array Other HYPERLINK Creates a hyperlink to a document in a supplied location. TRANSPOSE Performs a transpose transformation on a range of cells (ie. transforms a horizontal range of cells into a vertical range and vice versa) RTD Retrieves real-time data from a program that supports COM automation

Excel Math and Trig Functions

Basic Numeric Information ABS Returns the absolute value (ie. the modulus) of a supplied number SIGN Returns the sign (+1, -1 or 0) of a supplied number GCD Returns the Greatest Common Divisor of two or more supplied numbers LCM Returns the Least Common Multiple of two or more supplied numbers Basic Mathematical Operations SUM Returns the sum of a supplied list of numbers PRODUCT Returns the product of a supplied list of numbers POWER Returns the result of a given number raised to a supplied power SQRT Returns the positive square root of a given number QUOTIENT Returns the integer portion of a division between two supplied numbers MOD Returns the remainder from a division between two supplied numbers AGGREGATE Performs a specified calculation (eg. the sum, product, average, etc.) for a list or database, with the option to ignore hidden rows and error values (New in Excel 2010) SUBTOTAL Performs a specified calculation (eg. the sum, product, average, etc.) for a supplied set of values Rounding Functions CEILING Rounds a number away from zero (ie. rounds a positive number up and a negative number down), to a multiple of significance CEILING.PRECISE Rounds a number up, regardless of the sign of the number, to a multiple of significance (New in Excel 2010) ISO.CEILING Rounds a number up, regardless of the sign of the number, to a multiple of significance. (New in Excel 2010) EVEN Rounds a number away from zero (ie. rounds a positive number up and a negative number down), to the next even number FLOOR Rounds a number towards zero, (ie. rounds a positive number down and a negative number up), to a multiple of significance FLOOR.PRECISE Rounds a number down, regardless of the sign of the number, to a multiple of significance (New in Excel 2010) INT Rounds a number down to the next integer MROUND Rounds a number up or down, to the nearest multiple of significance ODD Rounds a number away from zero (ie. rounds a positive number up and a negative number down), to the next odd number ROUND Rounds a number up or down, to a given number of digits ROUNDDOWN Rounds a number towards zero, (ie. rounds a positive number down and a negative number up), to a given number of digits ROUNDUP Rounds a number away from zero (ie. rounds a positive number up and a negative number down), to a given number of digits TRUNC Truncates a number towards zero (ie. rounds a positive number down and a negative number up), to the next integer. Exponentials & Logarithms EXP Returns e raised to a given power LN Returns the natural logarithm of a given number LOG Returns the logarithm of a given number, to a specified base LOG10 Returns the base 10 logarithm of a given number

Conditional Sums SUMIF Adds the cells in a supplied range, that satisfy a given criteria SUMIFS Adds the cells in a supplied range, that satisfy multiple criteria (New in Excel 2007) Advanced Mathematical Operations SUMPRODUCT Returns the sum of the products of corresponding values in two or more supplied arrays SUMSQ Returns the sum of the squares of a supplied list of numbers SUMX2MY2 Returns the sum of the difference of squares of corresponding values in two supplied arrays SUMX2PY2 Returns the sum of the sum of squares of corresponding values in two supplied arrays SUMXMY2 Returns the sum of squares of differences of corresponding values in two supplied arrays SERIESSUM Returns the sum of a power series Trigonometry Functions PI Returns the constant value of pi SQRTPI Returns the square root of a supplied number multiplied by pi DEGREES Converts Radians to Degrees RADIANS Converts Degrees to Radians COS Returns the Cosine of a given angle ACOS Returns the Arccosine of a number COSH Returns the hyperbolic cosine of a number ACOSH Returns the inverse hyperbolic cosine of a number SIN Returns the Sine of a given angle ASIN Returns the Arcsine of a number SINH Returns the Hyperbolic Sine of a number ASINH Returns the Inverse Hyperbolic Sine of a number TAN Returns the Tangent of a given angle ATAN Returns the Arctangent of a given number ATAN2 Returns the Arctangent of a given pair of x and y coordinates TANH Returns the Hyperbolic Tangent of a given number ATANH Returns the Inverse Hyperbolic Tangent of a given number Matrix Functions MDETERM Returns the matrix determinant of a supplied array MINVERSE Returns the matrix inverse of a supplied array MMULT Returns the matrix product of two supplied arrays Factorials FACT Returns the Factorial of a given number FACTDOUBLE Returns the Double Factorial of a given number MULTINOMIAL Returns the Multinomial of a given set of numbers Random Numbers RAND Returns a random number between 0 and 1 RANDBETWEEN Returns a random number between two given integers Miscellaneous COMBIN Returns the number of combinations for a given number of objects ROMAN Returns a text string depicting the roman numeral for a given number

Excel Statistical Functions

Finding the Largest & Smallest Values MAX Returns the largest value from a list of supplied numbers MAXA Returns the largest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 MIN Returns the smallest value from a list of supplied numbers MINA Returns the smallest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 LARGE Returns the Kth LARGEST value from a list of supplied numbers, for a given value K SMALL Returns the Kth SMALLEST value from a list of supplied numbers, for a given value K Averages, Frequency & Rank AVERAGE Returns the Average of a list of supplied numbers AVERAGEA Returns the Average of a list of supplied numbers, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 AVERAGEIF Calculates the Average of the cells in a supplied range, that satisfy a given criteria (New in Excel 2007) AVERAGEIFS Calculates the Average of the cells in a supplied range, that satisfy multiple criteria (New in Excel 2007) MEDIAN Returns the Median (the middle value) of a list of supplied numbers MODE Returns the Mode (the most frequently occurring value) of a list of supplied numbers (Replaced by MODE.SNGL function in Excel 2010) MODE.SNGL Returns the Mode (the most frequently occurring value) of a list of supplied numbers (New in Excel 2010 - replaces MODE function) MODE.MULT Returns a vertical array of the most frequently occurring values in an array or range of data (New in Excel 2010) GEOMEAN Returns the geometric mean of a set of supplied numbers HARMEAN Returns the harmonic mean of a set of supplied numbers TRIMMEAN Returns the mean of the interior of a supplied set of values FREQUENCY Returns an array showing the number of values from a supplied array, which fall into specified ranges RANK Returns the statistical rank of a given value, within a supplied array of values (Replaced by RANK.EQ function in Excel 2010) RANK.EQ Returns the Mode (the most frequently occurring value) of a list of supplied numbers (if more than one value has same rank, the top rank of that set is returned) (New in Excel 2010 - replaces RANK function) RANK.AVG Returns the statistical rank of a given value, within a supplied array of values (if more than one value has same rank, the average rank is returned) (New in Excel 2010) KURT Returns the kurtosis of a data set PERCENTILE Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (inclusive) (Replaced by PERCENTILE.INC function in Excel 2010) PERCENTILE.INC Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (inclusive) (New in Excel 2010 - replaces PERCENTILE function) PERCENTILE.EXC Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (exclusive) (New in Excel 2010) PERCENTRANK Returns the rank of a value in a data set, as a percentage (0 - 1 inclusive) (Replaced by PERCENTRANK.INC function in Excel 2010) PERCENTRANK.INC Returns the rank of a value in a data set, as a percentage (0 - 1 inclusive) (New in Excel 2010 - replaces PERCENTRANK function) PERCENTRANK.EXC Returns the rank of a value in a data set, as a percentage (0 - 1 exclusive) (New in Excel 2010) QUARTILE Returns the specified quartile of a set of supplied numbers, based on percentile value 0 - 1 (inclusive) (Replaced by QUARTILE.INC function in Excel 2010) QUARTILE.INC Returns the specified quartile of a set of supplied numbers, based on percentile value 0 - 1 (inclusive) (New in Excel 2010 - replaces QUARTILE function) QUARTILE.EXC Returns the specified quartile of a set of supplied numbers, based on percentile value 0 - 1 (exclusive) (New in Excel 2010) Deviation & Variance AVEDEV Returns the average of the absolute deviations of data points from their mean STDEV Returns the standard deviation of a supplied set of values (which represent a sample of a population) (Replaced by STDEV.S function in Excel 2010) STDEV.S Returns the standard deviation of a supplied set of values (which represent a sample of a population) (New in Excel 2010 - replaces STDEV function) STDEVA Returns the standard deviation of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 STDEVP Returns the standard deviation of a supplied set of values (which represent an entire population) (Replaced by STDEV.P function in Excel 2010) STDEV.P Returns the standard deviation of a supplied set of values (which represent an entire population) (New in Excel 2010 - replaces STDEVP function) STDEVPA Returns the standard deviation of a supplied set of values (which represent an entire population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 VAR Returns the variance of a supplied set of values (which represent a sample of a population) (Replaced by VAR.S function in Excel 2010) VAR.S Returns the variance of a supplied set of values (which represent a sample of a population) (New in Excel 2010 - replaces VAR function) VARA Returns the variance of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 VARP Returns the variance of a supplied set of values (which represent an entire population) (Replaced by VAR.P function in Excel 2010) VAR.P Returns the variance of a supplied set of values (which represent an entire population) (New in Excel 2010 - replaces VARP function) VARPA Returns the variance of a supplied set of values (which represent an entire population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 COVAR Returns population covariance (ie. the average of the products of deviations for each pair within two supplied data sets) (Replaced by COVARIANCE.P function in Excel 2010) COVARIANCE.P Returns population covariance (ie. the average of the products of deviations for each pair within two supplied data sets) (New in Excel 2010 - replaces COVAR function) COVARIANCE.S Returns sample covariance (ie. the average of the products of deviations for each pair within two supplied data sets) (New in Excel 2010) CONFIDENCE Returns the confidence interval for a population mean, using a normal distribution (Replaced by CONFIDENCE.NORM function in Excel 2010) CONFIDENCE.NORM Returns the confidence interval for a population mean, using a normal distribution (New in Excel 2010 - replaces CONFIDENCE function) CONFIDENCE.T Returns the confidence interval for a population mean, using a Student's t distribution (New in Excel 2010) DEVSQ Returns the sum of the squares of the deviations of a set of data points from their sample mean Trend Line Functions FORECAST Predicts a future point on a linear trend line fitted to a supplied set of x- and y- values INTERCEPT Calculates the best fit regression line, through a supplied series of x- and y- values and returns the value at which this line intercepts the y-axis LINEST Returns statistical information describing the trend of the line of best fit, through a supplied series of x- and y- values SLOPE Returns the slope of the linear regression line through a supplied series of x- and y- values TREND Calculates the trend line through a given set of y-values and returns additional y-values for a supplied set of new x-values GROWTH Returns numbers in a exponential growth trend, based on a set of supplied x- and y- values LOGEST Returns the parameters of an exponential trend for a supplied set of x- and y- values STEYX Returns the standard error of the predicted y-value for each x in the regression line for a set of supplied x- and y- values Miscellaneous PERMUT Returns the number of permutations for a given number of objects

Counting Cells COUNT Returns the number of numerical values in a supplied set of cells or values COUNTA Returns the number of non-blanks in a supplied set of cells or values COUNTBLANK Returns the number of blank cells in a supplied range COUNTIF Returns the number of cells (of a supplied range), that satisfy a given criteria COUNTIFS Returns the number of cells (of a supplied range), that satisfy a set of given criteria (New in Excel 2007) Distribution & Tests of Probability BETADIST Returns the cumulative beta probability density function (Replaced by BETA.DIST function in Excel 2010) BETA.DIST Returns the cumulative beta distribution function or the beta probability density function (New in Excel 2010 - replaces BETADIST function) BETAINV Returns the inverse of the cumulative beta probability density function (Replaced by BETA.INV function in Excel 2010) BETA.INV Returns the inverse of the cumulative beta probability density function (New in Excel 2010 - replaces BETAINV function) BINOMDIST Returns the individual term binomial distribution probability (Replaced by BINOM.DIST function in Excel 2010) BINOM.DIST Returns the individual term binomial distribution probability (New in Excel 2010 - replaces BINOMDIST function) NEGBINOMDIST Returns the negative binomial distribution (Replaced by NEGBINOM.DIST function in Excel 2010) NEGBINOM.DIST Returns the negative binomial distribution (New in Excel 2010 - replaces NEGBINOMDIST function) CRITBINOM Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value (Replaced by BINOM.INV function in Excel 2010) BINOM.INV Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value (New in Excel 2010 - replaces CRITBINOM function) CHIDIST Returns the right-tailed probability of the chi-squared distribution (Replaced by CHISQ.DIST.RT function in Excel 2010) CHISQ.DIST.RT Returns the right-tailed probability of the chi-squared distribution (New in Excel 2010 - replaces CHIDIST function) CHISQ.DIST Returns the chi-squared distribution (probability density or cumulative distribution function) (New in Excel 2010) CHIINV Returns the inverse of the right-tailed probability of the chi-squared distribution (Replaced by CHISQ.INV.RT function in Excel 2010) CHISQ.INV.RT Returns the inverse of the right-tailed probability of the chi-squared distribution (New in Excel 2010 - replaces CHIINV function) CHISQ.INV Returns the inverse of the left-tailed probability of the chi-squared distribution (New in Excel 2010) CHITEST Returns the chi-squared statistical test for independence (Replaced by CHISQ.TEST function in Excel 2010) CHISQ.TEST Returns the chi-squared statistical test for independence (New in Excel 2010 - replaces CHITEST function) CORREL Returns the correlation coefficient between two sets of values EXPONDIST Returns the exponential distribution (Replaced by EXPON.DIST function in Excel 2010) EXPON.DIST Returns the exponential distribution (New in Excel 2010 - replaces EXPONDIST function) FDIST Returns the right-tailed F probability distribution for two data sets (Replaced by F.DIST.RT function in Excel 2010) F.DIST.RT Returns the right-tailed F probability distribution for two data sets (New in Excel 2010 - replaces FDIST function) F.DIST Returns the F probability distribution (probability density or cumulative distribution function) (New in Excel 2010) FINV Returns the inverse of the right-tailed F probability distribution for two data sets (Replaced by F.INV.RT function in Excel 2010) F.INV.RT Returns the inverse of the right-tailed F probability distribution for two data sets (New in Excel 2010 - replaces FINV function) F.INV Returns the inverse of the Cumulative F distribution (New in Excel 2010) FISHER Returns the Fisher transformation FISHERINV Returns the inverse of the Fisher transformation FTEST Returns the result of an F-Test for 2 supplied data sets (Replaced by F.TEST function in Excel 2010) F.TEST Returns the result of an F-Test for 2 supplied data sets (New in Excel 2010 - replaces FTEST function) GAMMADIST Returns the gamma distribution (Replaced by GAMMA.DIST function in Excel 2010) GAMMA.DIST Returns the gamma distribution (New in Excel 2010 - replaces GAMMADIST function) GAMMAINV Returns the inverse gamma cumulative distribution (Replaced by GAMMA.INV function in Excel 2010) GAMMA.INV Returns the inverse gamma cumulative distribution (New in Excel 2010 - replaces GAMMAINV function) GAMMALN Calculates the natural logarithm of the gamma function for a supplied value GAMMALN.PRECISE Returns the natural logarithm of the gamma function for a supplied value (New in Excel 2010) HYPGEOMDIST Returns the hypergeometric distribution (Replaced by HYPGEOM.DIST function in Excel 2010) HYPGEOM.DIST Returns the hypergeometric distribution (New in Excel 2010 - replaces HYPGEOMDIST function) LOGNORMDIST Returns the cumulative log-normal distribution (Replaced by LOGNORM.DIST function in Excel 2010) LOGNORM.DIST Returns the log-normal probability density function or the cumulative log- normal distribution (New in Excel 2010 - replaces LOGNORMDIST function) LOGINV Returns the inverse of the lognormal distribution (Replaced by LOGNORM.INV function in Excel 2010) LOGNORM.INV Returns the inverse of the lognormal distribution (New in Excel 2010 - replaces LOGINV function) NORMDIST Returns the normal cumulative distribution (Replaced by NORM.DIST function in Excel 2010) NORM.DIST Returns the normal cumulative distribution (New in Excel 2010 - replaces NORMDIST function) NORMINV Returns the inverse of the normal cumulative distribution (Replaced by NORM.INV function in Excel 2010) NORM.INV Returns the inverse of the normal cumulative distribution (New in Excel 2010 - replaces NORMINV function) NORMSDIST Returns the standard normal cumulative distribution (Replaced by NORM.S.DIST function in Excel 2010) NORM.S.DIST Returns the standard normal cumulative distribution (New in Excel 2010 - replaces NORMSDIST function) NORMSINV Returns the inverse of the standard normal cumulative distribution (Replaced by NORM.S.INV function in Excel 2010) NORM.S.INV Returns the inverse of the standard normal cumulative distribution (New in Excel 2010 - replaces NORMSINV function) PEARSON Returns the Pearson product moment correlation coefficient RSQ Returns the square of the Pearson product moment correlation coefficient POISSON Returns the Poisson distribution (Replaced by POISSON.DIST function in Excel 2010) POISSON.DIST Returns the Poisson distribution (New in Excel 2010 - replaces POISSON function) PROB Returns the probablity that values in a supplied range are within given limits SKEW Returns the skewness of a distribution STANDARDIZE Returns a normalized value TDIST Returns the Student's T-distribution (Replaced by T.DIST.2T & T.DIST.RT functions in Excel 2010) T.DIST.2T Returns the two-tailed Student's T-distribution (New in Excel 2010 - replaces TDIST function) T.DIST.RT Returns the right-tailed Student's T-distribution (New in Excel 2010 - replaces TDIST function) T.DIST Returns the Student's T-distribution (probability density or cumulative distribution function) (New in Excel 2010) TINV Returns the two-tailed inverse of the Student's T-distribution (Replaced by T.INV.2T function in Excel 2010) T.INV.2T Returns the two-tailed inverse of the Student's T-distribution (New in Excel 2010 - replaces TINV function) T.INV Returns the left-tailed inverse of the Student's T-distribution (New in Excel 2010) TTEST Returns the probability associated with a Student's T-Test (Replaced by T.TEST function in Excel 2010) T.TEST Returns the probability associated with a Student's T-Test (New in Excel 2010 - replaces TTEST function) WEIBULL Returns the Weibull distribution (Replaced by WEIBULL.DIST function in Excel 2010) WEIBULL.DIST Returns the Weibull distribution (New in Excel 2010 - replaces WEIBULL function) ZTEST Returns the one-tailed probability value of a z-test (Replaced by Z.TEST function in Excel 2010) Z.TEST Returns the one-tailed probability value of a z-test (New in Excel 2010 - replaces ZTEST function)

Excel Database Functions

Database Functions DAVERAGE Calculates the average of values in a field of a list or database, that satisfy specified conditions DCOUNT Returns the number of cells containing numbers in a field of a list or database that satisfy specified conditions DCOUNTA Returns the number of non-blank cells in a field of a list or database, that satisfy specified conditions DGET Returns a single value from a field of a list or database, that satisfy specified conditions DMAX Returns the maximum value from a field of a list or database, that satisfy specified conditions DMIN Returns the minimum value from a field of a list or database, that satisfy specified conditions

Database Functions DPRODUCT Calculates the product of values in a field of a list or database, that satisfy specified conditions DSTDEV Calculates the standard deviation (based on a sample of a population), of values in a field of a list or database, that satisfy specified conditions DSTDEVP Calculates the standard deviation (based on an entire population), of values in a field of a list or database, that satisfy specified conditions DSUM Calculates the sum of values in a field of a list or database, that satisfy specified conditions DVAR Calculates the variance (based on a sample of a population), of values in a field of a list or database, that satisfy specified conditions DVARP Calculates the variance (based on an entire population), of values in a field of a list or database, that satisfy specified conditions

Excel Financial Functions

Investment Value Functions FV Calculates the future value of an investment with periodic constant payments and a constant interest rate FVSCHEDULE Calculates the future value of an initial principal, after applying a series of compound interest rates NPV Calculates the net present value of an investment, based on a supplied discount rate, and a series of future payments and income PV Calculates the present value of an investment (ie. the total amount that a series of future payments is worth now) RECEIVED Calculates the amount received at maturity for a fully invested Security XNPV Calculates the net present value for a schedule of cash flows Payment Functions CUMIPMT Calculates the cumulative interest paid between two specified periods CUMPRINC Calculates the cumulative principal paid on a loan, between two specified periods IPMT Calculates the interest payment for a given period of an investment, with periodic constant payments and a constant interest rate ISPMT Returns the interest paid during a specified period of an investment PMT Calculates the payments required to reduce a loan, from a supplied present value to a specified future value PPMT Calculates the payment on the principal for a given investment, with periodic constant payments and a constant interest rate Duration Functions COUPDAYBS Calculates the number of days from the beginning of the coupon period to the settlement date COUPDAYS Calculates the number of days in the coupon period that contains the settlement date COUPDAYSNC Calculates 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 DURATION Calculates the Macauley duration of a security with an assumed par value of $100 MDURATION Calculates the Macauley modified duration for a security with an assumed par value of $100 NPER Returns the number of periods for an investment with periodic constant payments and a constant interest rate Yield Functions YIELD Calculates the yield of a security that pays periodic interest YIELDDISC Calculates the annual yield of a discounted security YIELDMAT Calculates the annual yield of a security that pays interest at maturity ODDFYIELD Calculates the yield of a security with an odd first period ODDLYIELD Calculates the yield of a security with an odd last period TBILLEQ Calculates the bond-equivalent yield for a treasury bill TBILLYIELD Calculates the yield for a treasury bill

Internal Rate of Return Functions IRR Calculates the internal rate of return for a series of cash flows MIRR Calculates the internal rate of return for a series of periodic cash flows, considering the cost of the investment and the interest on the reinvestment of cash XIRR Calculates the internal rate of return for a schedule of cash flows Asset Depreciation Functions AMORDEGRC Calculates the prorated linear depreciation of an asset for each accounting period (with depreciation coefficient applied, depending on the life of the asset) AMORLINC Calculates the prorated linear depreciation of an asset for each accounting period DB Calculates the depreciation of an asset for a specified period, using the fixed-declining balance method DDB Calculates the depreciation of an asset for a specified period, using the double-declining balance method, or some other user-specified method 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 VDB Returns the depreciation of an asset for a specified period, (including partial periods), using the double-declining balance method or another user-specified method Interest Rate Functions ACCRINT Calculates the accrued interest for a security that pays periodic interest ACCRINTM Calculates the accrued interest for a security that pays interest at maturity DISC Calculates the discount rate for a security EFFECT Calculates the effective annual interest rate INTRATE Calculates the interest rate for a fully invested security NOMINAL Calculates the annual nominal interest rate RATE Calculates the interest rate required to pay off a specified amount of a loan, or reach a target amount on an investment over a given period Dollar Conversion DOLLARDE Converts a dollar price expressed as a fraction, into a dollar price expressed as a decimal DOLLARFR Converts a dollar price expressed as a decimal, into a dollar price expressed as a fraction Price Functions PRICE Calculates the price per $100 face value of a security that pays periodic interest PRICEDISC Calculates the price per $100 face value of a discounted security PRICEMAT Calculates the price per $100 face value of a security that pays interest at maturity ODDFPRICE Calculates the price per $100 face value of a security with an odd first period ODDLPRICE Calculates the price per $100 face value of a security with an odd last period TBILLPRICE Calculates the price per $100 face value for a treasury bill

Excel Engineering Functions

Converting Between Units of Measurement CONVERT Converts a number from one measurement system to another Bessel Functions BESSELI Calculates the modified Bessel function In(x) BESSELJ Calculates the Bessel function Jn(x) BESSELK Calculates the modified Bessel function Kn(x) BESSELY Calculates the modified Bessel function Yn(x) Converting Between Bases BIN2DEC Converts a binary number to a decimal BIN2HEX Converts a binary number to hexadecimal BIN2OCT Converts a binary number to octal DEC2BIN Converts a decimal number to binary DEC2HEX Converts a decimal number to hexadecimal DEC2OCT Converts a decimal number to octal HEX2BIN Converts a hexadecimal number to binary HEX2DEC Converts a hexadecimal number to a decimal HEX2OCT Converts a hexadecimal number to octal OCT2BIN Converts octal number to binary OCT2DEC Converts octal number to a decimal OCT2HEX Converts octal number to hexadecimal The Error Function ERF Returns the error function integrated between two supplied limits ERF.PRECISE Returns the error function integrated between 0 and a supplied limit (New in Excel 2010) ERFC Returns the complementary error function integrated between a supplied lower limit and infinity ERFC.PRECISE Returns the complementary error function integrated between a supplied lower limit and infinity (New in Excel 2010)

Testing Numeric Values DELTA Tests whether two supplied numbers are equal GESTEP Tests whether a number is greater than a supplied threshold value Complex Numbers COMPLEX Converts user-supplied real and imaginary coefficients into a complex number IMABS Returns the absolute value (the modulus) of a complex number IMAGINARY Returns the imaginary coefficient of a complex number IMARGUMENT Returns the argument Θ (an angle expressed in radians) of a complex number IMCONJUGATE Returns the complex conjugate of a complex number IMCOS Returns the cosine of a complex number IMDIV Returns the quotient of two supplied 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 Calculates a complex number raised to a supplied power IMPRODUCT Returns the product of up to 255 supplied 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 Subtracts two complex numbers IMSUM Calculates the sum of two complex numbers

Excel Cube Functions

The Excel Cube functions perform calculations and extract data from a cube, which is stored on an external SQL server. These functions are only supported with a connection to Microsoft SQL Server 2005 Analysis Services or later data source.

Cube Functions CUBEKPIMEMBER Returns a Key Performance Indicator (KPI) property and displays the KPI name in the cell (New in Excel 2007) CUBEMEMBER Returns a member or tuple from the cube (New in Excel 2007) CUBEMEMBERPROPERTY Returns the value of a member property from the cube (New in Excel 2007) CUBERANKEDMEMBER Returns the nth, or ranked, member in a set (New in Excel 2007)

Cube Functions CUBESET Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel (New in Excel 2007) CUBESETCOUNT Returns the number of items in a set (New in Excel 2007) CUBEVALUE Returns an aggregated value from the cube (New in Excel 2007)

Excel Operators

Although the Excel Operators are not, strictly speaking, Excel Functions, a brief explanation of them is included in the following page:

Excel Operators

Excel Mathematical Operators - used to perform basic Mathematical operations, such as addition, subtraction, multiplication, division and raising to a power, OR the & Excel Operator is used to concatenate text strings

Home

Basic Excel

Built-In Functions

Text Functions

Logical Functions

Information Functions

Date & Time Functions

Lookup & Reference Functions

Math Functions

Statistical Functions

Database Functions

Financial Functions

Engineering Functions

Excel Vlookup Tutorial

Pivot Table Tutorial

Excel Formulas

Array Formulas

Tips & Tricks

Common Excel Errors

Excel Templates

Excel Macros

Excel VBA Tutorial

Excel 2003 vs 2007

Recommended Books

Search This Site