All Formulas and Functions in Excel
- Date and Time Functions
- Lookup and reference function
- Engineering Functions
- Financial Functions
- Logical Functions
- Text Functions
- Math and trigonometry functions
- Statistical functions
- Database function
- Information functions
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. Here a good example to find the number of days in a month.
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. Second example of CONCATENATE in Excel is here.
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). Here another example.
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(): The left most characters from a text value. Here some more on LEFT, LEFTB
LEN function: The number of characters in a text string, some other example of the LEN, LENB
LOWER: Converts text to lowercase. Another example for change to Lowercase is here.
MID, MIDB: A specific number of characters from a text string starting at the position you specify. Here another example of MID, MIDB
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(): The rightmost characters from a text value: another example of RIGHT, RIGHTB
SEARCH, SEARCHB: Finds one text value within another (not case-sensitive). Another example here of the Search() function.
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. Another example for changing to upper case in Excel is here.
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
ACOT: inverse of the COT function
ACOTH: inverse hyperbolic cotangent 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
CSCH: Hyperbolic cosine of the 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 an angle
COSH: The hyperbolic cosine of an angle
COT: returns the Cotangent of the angle
COTH: Hyperbolic cotangent of a number
CSC: Cosecant of an angle
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
ISEVEN: boolean that determines if a number is even or odd
ISODD: boolean that determine if a number is odd (1, true) or even (0, false)
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. Used as 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
SEC: calculate the secant of an angle
SECH: Hyperbolic secant of the number
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 an angle
SQRT: A positive square root
SQRTPI: The square root of (number * pi)
SUBTOTAL: A subtotal in a list or database. Another example of Subtotal in Excel is here.
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 an angle
TANH: The hyperbolic tangent of an angle
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). A second example here.
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. Very useful for basic school statistic
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 based on mean and std
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
IMCOSH: The hyperbolic cosine of a complex number
IMCOT: the cotangent of an imaginary number or complex number
IMCSC: the cosecant of an imaginary number
IMCSCH: hyperbolic cosecant of an imaginary 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
IMSEC: the Secant of an imaginary number
IMSECH: the hyperbolic secant of an imaginary number or complex number
IMSIN: The sine of a complex number
IMSINH: the hyperbolic sinus of an complex number
IMSQRT: The square root of a complex number
IMSUB: The difference between two complex numbers
IMSUM: The sum of complex numbers
IMTAN: the tangent of an imaginary number
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
ISFORMULA: checks if a cell contains a formula
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