Excel Functions

Excel functions list

Below are over 200 Excel functions to help you become a world-class financial analyst.

Date and Time

 DATE Create a valid date from year, month, and day DATEDIF Get days, months, or years between two dates DATEVALUE Convert a date in text format to a valid date DAY Get the day as a number (1-31) from a date DAYS Get days between dates DAYS360 Get days between 2 dates in a 360-day year EDATE Get the same date in future or past months EOMONTH Get the last day of the month in future or past months HOUR Get the hour as a number (0-23) from a time ISOWEEKNUM Get ISO week number for a given date MINUTE Get the minute as a number (0-59) from a time MONTH Get the month as a number (1-12) from a date NETWORKDAYS Get the number of working days between two dates NETWORKDAYS.INTL Get work days between two dates NOW Get the current date and time SECOND Get the second as a number (0-59) from a time TIME Create a time with hours, minutes, and seconds TIMEVALUE Get a valid time from a text string TODAY Get the current date WEEKDAY Get the day of the week as a number WEEKNUM Get the week number for a given date WORKDAY Get a date n working days in the future or past WORKDAY.INTL Get date n working days in future or past YEAR Get the year from a date YEARFRAC Get the fraction of a year between two dates

Financial

 ACCRINT Get the accrued interest for a security that pays interest on a periodic basis ACCRINTM Get the accrued interest for a security that pays interest on maturity CUMIPMT Get the cumulative interest paid on a loan CUMPRINC Get the cumulative principal paid on a loan DB Calculate the depreciation of an asset DISC Calculate the rate of discount for a bond DOLLARDE Converts a dollar value in fractional notation into a dollar value expressed in decimal notation DOLLARFR Convert a dollar value in decimal notation into a dollar value in fractional notation DURATION Get the duration of a security EFFECT Get the annual interest rate and the number of compounding periods per year FV Get the future value of an investment FVSCHEDULE Get the future value of an investment with a variable interest rate INTRATE Get the interest rate for a security IPMT Get principal for given period IRR Get the Internal Rate of Return for a given cash flow ISPMT Get the interest paid during a specific period of Investment MDURATION Get the duration of a security using the Modified Macauley method MIRR Get he modified internal rate of return for given periodic cash flows NOMINAL Get the nominal annual interest rate NPER Get number of periods for loan or investment NPV Get the Net Present Value (NPV) for periodic cash flows PDURATION Get the time or specific number of periods required for an investment to reach a particular value PMT Get the periodic payment for a loan PPMT Get the payment on the principal for an investment or a loan PRICE Get the price of a bond per \$100 face value PRICEDISC Get the price of a bond per \$100 face value of a discounted security PRICEMAT Get the price of a bond that pays interest at maturity per \$100 face value PV Get the present value of an investment RATE Get the interest rate per period of an annuity RECEIVED Get the amount received at maturity for a fully invested security SLN Get the straight-line depreciation of an asset SYD Get the sum-of-years' digits depreciation of an asset TBILLPRICE Get the fair market value of a Treasury bill TBILLEQ Get the bond-equivalent yield of a Treasury bill TBILLYIELD Get the yield on a Treasury bill VDB Get the depreciation of an asset using the Double Declining Balance method XIRR Get the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic XNPV Get the Net Present Value (NPV) for a series of cash flows that may not be periodic YIELD Get the yield on a security YIELDDISC Get the annual yield for a discounted security YIELDMAT Get the annual yield on a security

Engineering

 CONVERT Convert measurement units

Information

 CELL Get information about a cell ERROR.TYPE Test for a specific error value INFO Get information about current environment ISBLANK Test if a cell is blank ISERR Test for any error but #N/A ISERROR Test for any error ISEVEN Test if a value is even ISFORMULA Test if a cell contains a formula ISLOGICAL Test if a value is logical ISNA Test for the #N/A error ISNONTEXT Test for a non-text value ISNUMBER Test for numeric value ISODD Test if a value is odd ISREF Test for a reference ISTEXT Test for a text value N Convert a value to a number NA Create an #N/A error TYPE Get the type of value in a cell

Logical

 AND Test multiple conditions with AND FALSE Generate the logical value FALSE IF Test for a specific condition IFERROR Trap and handle errors IFNA Returns a specified alternate value if a formula results in an #N/A error IFS Test multiple conditions, return first true NOT Reverse arguments or results OR Test multiple conditions with OR SWITCH Evaluates a given expression and returns a result corresponding to the first matching value TRUE Generate the logical value TRUE XOR Perform exclusive OR

Lookup and Reference

 ADDRESS Create a cell address from a given row and column AREAS Get the number of areas in a reference CHOOSE Get a value from a list based on position COLUMN Get the column number of a reference COLUMNS Get the number of columns in an array or reference FORMULATEXT Get the formula in a cell GETPIVOTDATA Get data from a pivot table in a formula HLOOKUP Look up a value in a table by matching on the first row HYPERLINK Create a clickable link INDEX Get a value in a list or table based on location INDIRECT Create a reference from text LOOKUP Look up a value in a one-column range MATCH Get the position of an item in an array OFFSET Create a reference offset from given starting point ROW Get the row number of a reference ROWS Get the number of rows in an array or reference RTD Get real-time data from a COM automation server TRANSPOSE Flip the orientation of a range of cells VLOOKUP Lookup a value in a table by matching on the first column

Math

 ABS Find the absolute value of a number ARABIC Convert a Roman numeral to an Arabic numerals BASE Converts a number into the supplied base CEILING Round a number up to the nearest specified multiple CEILING.MATH Round a number to the nearest integer or multiple of significance CEILING.PRECISE Round up a number to the nearest integer or multiple of significance COMBIN Calculate the total number of combinations COMBINA Calculate the total number of combinations along with repetitions DEGREES Convert radians to degrees EVEN Round a number up to the next even integer ISO.CEILING Get a number rounded up to the nearest integer or multiple of significance MOD Get the remainder from division MROUND Round a number to the nearest specified multiple ODD Round a number up to the next odd integer PI Get the value of π RAND Get a random number between 0 and 1 RANDBETWEEN Get a random integer between two values ROUND Round a number to a given number of digits ROUNDDOWN Round a number down to a given number of digits ROUNDUP Round a number up to a given number of digits SQRT Find the positive square root of a number SUBTOTAL Get a subtotal in a list or database SUM Add numbers together SUMIF Sum numbers in a range that meet supplied criteria SUMIFS Sum cells that match multiple criteria SUMPRODUCT Multiply, then sum arrays TRUNC Truncate a number to a given precision

Statistical

 AVERAGE Get the average of a group of numbers AVERAGEA Get the average of a group of numbers and text AVERAGEIF Get the average of numbers that meet criteria AVERAGEIFS Average cells that match multiple criteria BINOM.DIST Get the binomial distribution probability for the number of successes from the trials BINOM.DIST.RANGE Get the binomial distribution probability for a given number of successes CHISQ.DIST Get the chi-square distribution CHISQ.DIST.RT Get the right-tailed probability of a chi-square distribution CHISQ.INV Get the inverse of the left-tailed probability of the chi-square distribution CHISQ.INV.RT Get the inverse of the right-tailed probability of a chi-square distribution CHISQ.TEST Get the chi-square distribution of two provided datasets CONFIDENCE Get the confidence interval for a population mean CONFIDENCE.NORM Get the confidence interval for a population mean CONFIDENCE.T Get the confidence value for the confidence interval of a population mean CORREL Calculate the correlation coefficient between two variables COUNT Count numbers COUNTA Count the number of non-blank cells COUNTBLANK Count cells that are blank COUNTIF Count cells that match criteria COUNTIFS Count cells that match multiple criteria COVAR Get the covariance of two sets of values COVARIANCE.P Get the covariance of two given set of values COVARIANCE.S Get the sample covariance for two sets of values CRITBINOM Get the smallest value that is greater than or equal to the criterion value in a Cumulative Binomial Distribution DEVSQ Get the sum of the squared deviations from the sample mean F.DIST Calculate the probability density function F.DIST.RT Calculate the (right-tailed) F Probability Distribution F.INV Calculate the inverse of the Cumulative F Distribution for a supplied probability F.INV.RT Calculate the inverse of the (right-tailed) F probability distribution FORECAST Get the future value using existing values FORECAST.LINEAR Get the future value using linear progression FREQUENCY Get the frequency of values in a data set F.TEST Get the result of an F-test for two given arrays or ranges GAUSS Get the probability that a member of a standard normal population will be between the mean and a specified number of standard deviations GROWTH Get the predicted exponential growth using existing data LARGE Get the nth largest value LOGNORM.DIST Get the cumulative log-normal distribution function at a given value of x LOGNORM.INV Get the inverse of the lognormal cumulative lognormal distribution function at a given value of x MAX Get the largest value MAXA Get the largest value MAXIFS Get maximum value with criteria MEDIAN Get the median of a group of numbers MIN Get the smallest value MINA Get the smallest value MINIFS Get minimum value with criteria MODE Get the mode of a group of numbers MODE.MULT Get a vertical array of statistical modes within a list of given numbers MODE.SNGL Get the most frequently occurring number in a numeric data set NEGBINOM.DIST Get the negative binomial distribution probability NORMDIST Get the normal distribution for a stated mean and standard distribution NORM.DIST Get the probability that variable x falls below or at a specified value NORMINV Get the inverse of a normal cumulative distribution NORM.INV Get the inverse of a normal cumulative distribution NORM.S.DIST Get the standard normal distribution function for a given value NORM.S.INV Get the inverse of the normal cumulative distribution for a given probability value PEARSON Get the Pearson Product-Moment Correlation Coefficient for two sets of values PERCENTILE Get the k-th percentile of the values in a range PERCENTILERANK Get the rank of a value in a data set PERMUT Get the total number of permutations PERMUTATIONA Get the total number of permutations, with repetitions PHI Get the value of the density function for a standard normal distribution POISSON.DIST Get the Poisson probability mass function PROB Get he probability that is associated with a given range QUARTILE Get the quartile in a data set QUARTILE.EXC Get the quartile of a given data set based on percentile values from 0.1 exclusive QUARTILE.INC Get the quartile of a given data set based on percentile values from 0.1 inclusive RANK Rank a number against a range of numbers SLOPE Get the slope of a linear regression line SMALL Get the nth smallest value STANDARDIZE Get a normalized value (z-score) based on the mean and standard deviation STDEV Get the standard deviation in a sample STDEVA Get the standard deviation in a sample STDEVPA Get the standard deviation of an entire population given as arguments STDEV.P Get the standard deviation in a population STDEV.S Get the standard deviation in a sample T.DIST Get the student's t-distribution T.DIST.RT Get the right-tailed student's t-distribution T.INV Get the left-tailed student's t-distribution T.INV.2T Get the two-tailed Student's T-Distribution T.TEST Get the probability associated with a student’s t-test TREND Get the linear trend line to the arrays of known_y’s and known_x’s VARA Get the sample variance of a set of values VAR.P Get the variance of a set of values (population) VAR.S Get the variance of a sample WEIBULL.DIST Get the Weibull distribution for a supplied set of parameters Z.TEST Get the one-tailed P-value of a Z-test

Text

 CHAR Get a character from a number CLEAN Strip non-printable characters from text CODE Get the code for a character CONCAT Join text values without delimiter CONCATENATE Join text together DOLLARDE Converts a dollar value in fractional notation into a dollar value in decimal notation EXACT Compare two text strings FIND Get the location of text in a string FIXED Rounds and converts a supplied number to text LEFT Extract text from the left of a string LEN Get the length of text LOWER Convert text to lower case MID Extract text from inside a string NUMBERVALUE Converts a text string into a number in a locale-independent manner PROPER Capitalize the first letter in each word REPLACE Replace text based on location REPT Repeat text as specified RIGHT Extract text from the right of a string SEARCH Get the location of text in a string SUBSTITUTE Replace text based on content T Tests if the value provided is a text or not TEXT Convert a number to text in a number format TEXTJOIN Join text values with a delimiter TRIM Remove extra spaces from text UNICHAR Get the Unicode character for a given number UNICODE Get the code point for a given character UPPER Convert text to upper case VALUE Convert text to a number