All Formulas and Functions in Excel

This is the list of all functions in Excel with a definition of what they do.

The basics can be found here for function on numbers, here for functions on words.

A Formula is an expression that you create and which calculates the value of a cell. 

A Function is a predefined formula and is already available in Excel (like Sinus, Logarithm, PMT, ...)  All the functions are listed here under.

In complex formulas, naming the cells is essential. Look here how this is done. How to use the name manager and have a readable spreadsheet.

 

Date and time functions

Date and Time functions help you deal with dates and durations, and birthdays and days until birthday, etc….. Beware it works only from 1 January 1900….. if you want to deal with the real past, then special tricks and add in are needed.

 

DATE: 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: The serial number of the date that is the indicated number of months before or after the start date

EOMONTH: 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: The number of whole workdays between two dates

NOW: The serial number of the current date and time

SECOND: Converts a serial number to a second

TIME: The serial number of a particular time

TIMEVALUE: Converts a time in the form of text to a serial number

TODAY: 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: The serial number of the date before or after a specified number of workdays

YEAR: Converts a serial number to a year

YEARFRAC: The year fraction representing the number of whole days between start_date and end_date

 

Lookup and reference function

In these functions, the most useful are VLookup and HLookup which allow you to find items in rows or columns.

 

ADDRESS: A reference as text to a single cell in a worksheet:

AREAS: The number of areas in a reference (not so used)

CHOOSE: Chooses a value from a list of values

COLUMN: The column number of a reference

COLUMNS: The number of columns in a reference

GETPIVOTDATA: 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: A reference indicated by a text value. Find the value of a cell by indicating its address in text form. Extremely useful.

LOOKUP: Looks up values in a vector or array

MATCH: Looks up values in a reference or array

OFFSET: A reference offset from a given reference

ROW: The row number of a reference

ROWS: The number of rows in a reference

RTD: Retrieves real-time data from a program that supports COM automation

TRANSPOSE: 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

 

 

Logical functions or Boolean functions

Logical Functions are here to test statements like is this AND that both TRUE and if this is the case then DO something…

 

AND: TRUE if all of its arguments are TRUE

FALSE: The logical value FALSE

IF: Specifies a logical test to perform

IFERROR: Handle if there is an error

NOT: Reverses the logic of its argument

OR: TRUE if any argument is TRUE

TRUE: The logical value TRUE

 

 

Text functions

These functions allow you to juggle with text. To convert, to extract, to concatenate, to add and remove text from sentences, etc....Extremely useful.

 

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: The character specified by the code number

CLEAN: Removes all nonprintable characters from text

CODE: 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: 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: The leftmost characters from a text value

LEN, LENB: The number of characters in a text string

LOWER: Converts text to lowercase

MID, MIDB: 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

REPLACE, REPLACEB: Replaces characters within text

REPT: Repeats text a given number of times

RIGHT, RIGHTB: 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

 

 

Math and trigonometry functions

The math and trigonometry function are useful from early age math classes for every student.... so use them wisely. You can trig, log, factorise and inverse plus many more....

 

ABS: The absolute value of a number

ACOS: The arccosine of a number

ACOSH: The inverse hyperbolic cosine of a number

ASIN: The arcsine of a number

ASINH: The inverse hyperbolic sine of a number

ATAN: The arctangent of a number

ATAN2: The arctangent from x- and y-coordinates

ATANH: The inverse hyperbolic tangent of a number

CEILING: Rounds a number to the nearest integer or to the nearest multiple of significance

COMBIN: The number of combinations for a given number of objects

COS: The cosine of a number

COSH: The hyperbolic cosine of a number

DEGREES: Converts radians to degrees

EVEN: Rounds a number up to the nearest even integer

EXP: e raised to the power of a given number

FACT: The factorial of a number

FACTDOUBLE: The double factorial of a number

FLOOR: Rounds a number down, toward zero

GCD: The greatest common divisor

INT: Rounds a number down to the nearest integer

LCM: The least common multiple

LN: The natural logarithm of a number

LOG: The logarithm of a number to a specified base

LOG10: The base-10 logarithm of a number

MDETERM: The matrix determinant of an array

MINVERSE: The matrix inverse of an array

MMULT: The matrix product of two arrays

MOD: The remainder from division

MROUND: A number rounded to the desired multiple

MULTINOMIAL: The multinomial of a set of numbers

ODD: Rounds a number up to the nearest odd integer

PI: The value of pi

POWER: The result of a number raised to a power

PRODUCT: Multiplies its arguments

QUOTIENT: The integer portion of a division

RADIANS: Converts degrees to radians

RAND: A random number between 0 and 1

RANDBETWEEN: 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: The sum of a power series based on the formula

SIGN: The sign of a number

SIN: The sine of the given angle

SINH: The hyperbolic sine of a number

SQRT: A positive square root

SQRTPI: The square root of (number * pi)

SUBTOTAL: A subtotal in a list or database

SUM: Adds its arguments

SUMIF: Adds the cells specified by a given criteria

SUMIFS: Adds the cells specified by more than one given criteria

SUMPRODUCT: The sum of the products of corresponding array components

SUMSQ: The sum of the squares of the arguments

SUMX2MY2: The sum of the difference of squares of corresponding values in two arrays

SUMX2PY2: The sum of the sum of squares of corresponding values in two arrays

SUMXMY2: The sum of squares of differences of corresponding values in two arrays

TAN: The tangent of a number

TANH: The hyperbolic tangent of a number

TRUNC: Truncates a number to an integer

 

 

Statistical functions

Statistical function allow engineers, statistitian to calculate probabilities of things happening or not..... you could use it to calculate the next loto numbers.....

 

AVEDEV: The average of the absolute deviations of data points from their mean

AVERAGE: The average of its arguments

AVERAGEA: The average of its arguments, including numbers, text, and logical values

BETADIST: The beta cumulative distribution function

BETAINV: The inverse of the cumulative distribution function for a specified beta distribution

BINOMDIST: The individual term binomial distribution probability

CHIDIST: The one-tailed probability of the chi-squared distribution

CHIINV: The inverse of the one-tailed probability of the chi-squared distribution

CHITEST: The test for independence

CONFIDENCE: The confidence interval for a population mean

CORREL: 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 (does not count the empty cells)

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 (one only)

COUNTIFS: Counts the number of nonblank cells within a range that meet multiple given criteria

COVAR: covariance, the average of the products of paired deviations

CRITBINOM: The smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

DEVSQ: The sum of squares of deviations

EXPONDIST: The exponential distribution

FDIST: The F probability distribution

FINV: The inverse of the F probability distribution

FISHER: The Fisher transformation

FISHERINV: The inverse of the Fisher transformation

FORECAST: A value along a linear trend

FREQUENCY: A frequency distribution as a vertical array

FTEST: The result of an F-test

GAMMADIST: The gamma distribution

GAMMAINV: The inverse of the gamma cumulative distribution

GAMMALN: The natural logarithm of the gamma function, Γ(x)

GEOMEAN: The geometric mean

GROWTH: Values along an exponential trend

HARMEAN: The harmonic mean

HYPGEOMDIST: The hypergeometric distribution

INTERCEPT: The intercept of the linear regression line

KURT: The kurtosis of a data set

LARGE: The k-th largest value in a data set

LINEST: The parameters of a linear trend

LOGEST: The parameters of an exponential trend

LOGINV: The inverse of the lognormal distribution

LOGNORMDIST: The cumulative lognormal distribution

MAX: The maximum value in a list of arguments

MAXA: The maximum value in a list of arguments, including numbers, text, and logical values

MEDIAN: The median of the given numbers

MIN: The minimum value in a list of arguments

MINA: The smallest value in a list of arguments, including numbers, text, and logical values

MODE: The most common value in a data set

NEGBINOMDIST: The negative binomial distribution

NORMDIST: The normal cumulative distribution

NORMINV: The inverse of the normal cumulative distribution

NORMSDIST: The standard normal cumulative distribution

NORMSINV: The inverse of the standard normal cumulative distribution

PEARSON: The Pearson product moment correlation coefficient

PERCENTILE: The k-th percentile of values in a range

PERCENTRANK: The percentage rank of a value in a data set

PERMUT: The number of permutations for a given number of objects

POISSON: The Poisson distribution

PROB: The probability that values in a range are between two limits

QUARTILE: The quartile of a data set

RANK: The rank of a number in a list of numbers

RSQ: The square of the Pearson product moment correlation coefficient

SKEW: The skewness of a distribution

SLOPE: The slope of the linear regression line

SMALL: The k-th smallest value in a data set

STANDARDIZE: 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: The standard error of the predicted y-value for each x in the regression

TDIST: The Student's t-distribution

TINV: The inverse of the Student's t-distribution

TREND: Values along a linear trend

TRIMMEAN: The mean of the interior of a data set

TTEST: 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: The Weibull distribution

ZTEST: The one-tailed probability-value of a z-test

 

 

Engineering functions

Engineering functions deal with tough stuff only engineers grasp (;-)…. Like complex number, binary numbers, conversions from binary to hexadecimal (the base of computing)….

 

BESSELI: The modified Bessel function In(x)

BESSELJ: The Bessel function Jn(x)

BESSELK: The modified Bessel function Kn(x)

BESSELY: 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: The error function

ERFC: 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: The absolute value (modulus) of a complex number

IMAGINARY: The imaginary coefficient of a complex number

IMARGUMENT: The argument theta, an angle expressed in radians

IMCONJUGATE: The complex conjugate of a complex number

IMCOS: The cosine of a complex number

IMDIV: The quotient of two complex numbers

IMEXP: The exponential of a complex number

IMLN: The natural logarithm of a complex number

IMLOG10: The base-10 logarithm of a complex number

IMLOG2: The base-2 logarithm of a complex number

IMPOWER: A complex number raised to an integer power

IMPRODUCT: The product of from 2 to 29 complex numbers

IMREAL: The real coefficient of a complex number

IMSIN: The sine of a complex number

IMSQRT: The square root of a complex number

IMSUB: The difference between two complex numbers

IMSUM: 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

Financial functions help you deal with investment, budgets, interest, loans….

 

ACCRINT : the accrued interest for a security that pays periodic interest

ACCRINTM : the accrued interest for a security that pays interest at maturity

AMORDEGRC : the depreciation for each accounting period by using a depreciation coefficient

AMORLINC : the depreciation for each accounting period

COUPDAYBS : the number of days from the beginning of the coupon period to the settlement date

COUPDAYS : the number of days in the coupon period that contains the settlement date

COUPDAYSNC : the number of days from the settlement date to the next coupon date

COUPNCD : the next coupon date after the settlement date

COUPNUM : the number of coupons payable between the settlement date and maturity date

COUPPCD : the previous coupon date before the settlement date

CUMIPMT : the cumulative interest paid between two periods

CUMPRINC : the cumulative principal paid on a loan between two periods

DB : the depreciation of an asset for a specified period by using the fixed-declining balance method

DDB : the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify

DISC : 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 : the annual duration of a security with periodic interest payments

EFFECT : the effective annual interest rate

FV : the future value of an investment

FVSCHEDULE : the future value of an initial principal after applying a series of compound interest rates

INTRATE : the interest rate for a fully invested security

IPMT : the interest payment for an investment for a given period

IRR : the internal rate of return for a series of cash flows

ISPMT : Calculates the interest paid during a specific period of an investment

MDURATION : the Macauley modified duration for a security with an assumed par value of $100

MIRR : the internal rate of return where positive and negative cash flows are financed at different rates

NOMINAL : the annual nominal interest rate

NPER : the number of periods for an investment

NPV : the net present value of an investment based on a series of periodic cash flows and a discount rate

ODDFPRICE : the price per $100 face value of a security with an odd first period

ODDFYIELD : the yield of a security with an odd first period

ODDLPRICE : the price per $100 face value of a security with an odd last period

ODDLYIELD : the yield of a security with an odd last period

PMT : the periodic payment for an annuity

PPMT : the payment on the principal for an investment for a given period

PRICE : the price per $100 face value of a security that pays periodic interest

PRICEDISC : the price per $100 face value of a discounted security

PRICEMAT : the price per $100 face value of a security that pays interest at maturity

PV : the present value of an investment

RATE : the interest rate per period of an annuity

RECEIVED : the amount received at maturity for a fully invested security

SLN : the straight-line depreciation of an asset for one period

SYD : the sum-of-years' digits depreciation of an asset for a specified period

TBILLEQ : the bond-equivalent yield for a Treasury bill

TBILLPRICE : the price per $100 face value for a Treasury bill

TBILLYIELD : the yield for a Treasury bill

VDB : the depreciation of an asset for a specified or partial period by using a declining balance method

XIRR : the internal rate of return for a schedule of cash flows that is not necessarily periodic

XNPV : the net present value for a schedule of cash flows that is not necessarily periodic

YIELD : the yield on a security that pays periodic interest

YIELDDISC : the annual yield for a discounted security

YIELDMAT : the annual yield of a security that pays interest at maturity

 

 

Database functions

Database in excel functions in Excel allow you to work on table and find out some basic informations about the data entered.

 

DAVERAGE: 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: The maximum value from selected database entries

DMIN: 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

 

Information functions

Information functions give you INFORMATION about the content of cells. Is this a number, is there an error, etc….typically you can test for errors and insteaf of having these errors like #NAME that have no meaning for the user, then you can replace them with a text message.

 

CELL: Information about the formatting, location, or contents of a cell

ERROR.TYPE: A number corresponding to an error type

INFO: Information about the current operating environment

ISBLANK: TRUE if the value is blank

ISERR: TRUE if the value is any error value except #N/A

ISERROR: TRUE if the value is any error value

ISEVEN: TRUE if the number is even

ISLOGICAL: TRUE if the value is a logical value

ISNA: TRUE if the value is the #N/A error value

ISNONTEXT: TRUE if the value is not text

ISNUMBER: TRUE if the value is a number

ISODD: TRUE if the number is odd

ISREF: TRUE if the value is a reference

ISTEXT: TRUE if the value is text

N: A value converted to a number

NA: The error value #N/A

TYPE: A number indicating the data type of a value

  

 

So that's it for the functions in Excel. 

These were only some basics. Now you will learn by doing.