Excel Formulas Cheat Sheet

Excel Formulas Cheat Sheet
Excel Formulas Cheat Sheet

${tocify}

Database Functions

  • DAVERAGE This function will return the average of selected database entries
  • DCOUNT This function will count the cells that contain numbers in a database
  • DCOUNTA This function will count the nonblank cells in a database
  • DGET This function will extract from a database, a single record that matches the specified criteria
  • DMAX This function will return the maximum value from selected database entries
  • DMIN This function will return the minimum value from selected database entries
  • DSTDEV This function will estimate the standard deviation based on a sample of selected database entries
  • DPRODUCT This function will multiply the values in a particular field of records that match the criteria in a database
  • DSTDEVP This function will calculate the standard deviation based on the entire population of selected database entries
  • DSUM This function will add the numbers in the field column of records in the database that match the criteria
  • DVAR This function will estimate the variance based on a sample from selected database entries
  • DVARP This function will calculate the variance based on the entire population of selected database entries

Date and Time Functions

  • DATE This function will return the serial number of a particular date
  • DATEVALUE This function will convert a date in the form of text to a serial number
  • DAY This function will convert a serial number to a day of the month
  • DAYS360 This function will calculate the number of days between two dates based on a 360-day year
  • EDATE This function will return the serial number of the date that is the indicated number of months before or after the start date
  • EOMONTH This function will return the serial number of the last day of the month before or after a specified number of months
  • HOUR This function will convert a serial number to an hour
  • MINUTE This function will convert a serial number to a minute
  • MONTH This function will convert a serial number to a month
  • NETWORKDAYS This function will return the number of whole workdays between two dates
  • NOW This function will return the serial number of the current date and time
  • SECOND This function will convert a serial number to a second
  • TIME This function will return the serial number of a particular time
  • TIMEVALUE This function will convert a time in the form of text to a serial number
  • TODAY This function will return the serial number of today's date
  • WEEKDAY This function will Convert a serial number to a day of the week
  • WEEKNUM This function will convert a serial number to a number representing where the
  • week falls numerically with a year
  • WORKDAY This function will return the serial number of the date before or after a specified number of workdays
  • YEAR This function will convert a serial number to a year
  • YEARFRAC This function will return the year fraction representing the number of whole
  • days between start_date and end_date

Engineering Functions

  • BESSELI This function will return the modified Bessel function In(x)
  • BESSELJ This function will return the Bessel function Jn(x)
  • BESSELK This function will return the modified Bessel function Kn(x)
  • BESSELY This function will return the Bessel function Yn(x)
  • BIN2DEC This function will convert a binary number to decimal
  • BIN2HEX This function will converts a binary number to hexadecimal
  • BIN2OCT This function will convert a binary number to octal
  • COMPLEX This function will convert real and  imaginary coefficients into a complex number
  • CONVERT This function will convert a number from one measurement system to another
  • DEC2BIN This function will convert a decimal number to binary
  • DEC2HEX This function will convert a decimal number to hexadecimal
  • DEC2OCT This function will convert a decimal number to octal
  • DELTA This function will Test whether two values are equal
  • ERF This function will return the error function
  • ERFC This function will return the complementary error function
  • GESTEP This function will test whether a number is greater than a threshold value
  • HEX2BIN This function will convert a hexadecimal number to binary
  • HEX2DEC This function will convert a hexadecimal number to decimal
  • HEX2OCT This function will convert a hexadecimal number to octal
  • IMABS This function will return the absolute value (modulus) of a complex number
  • IMAGINARY This function will return the imaginary coefficient of a complex number
  • IMARGUMENT This function will return the argument theta, an angle expressed in radians
  • IMCONJUGATE This function will return the complex conjugate of a complex number
  • IMCOS This function will return the cosine of a complex number
  • IMDIV This function will return the quotient of two complex numbers
  • IMEXP This function will return the exponential of a complex number
  • IMLN This function will return the natural logarithm of a complex number
  • IMLOG10 This function will return the base10 logarithm of a complex number
  • IMLOG2 This function will return the base-2 logarithm of a complex number
  • IMPOWER This function will return a complex number raised to an integer power
  • IMPRODUCT This function will return the product of from 2 to 29 complex numbers
  • IMREAL This function will return the real coefficient of a complex number
  • IMSIN This function will return the sine of a complex number
  • IMSQRT This function will return the square root of a complex number
  • IMSUB This function will return the difference between two complex numbers
  • MSUM This function will return the sum of complex numbers
  • OCT2BIN This function will convert an octal number to binary
  • OCT2DEC This function will convert an octal number to decimal
  • OCT2HEX This function will convert an octal number to hexadecimal

Financial Functions

  • ACCRINT This function will return the accrued interest for a security that pays periodic interest
  • ACCRINTM This function will return the accrued interest for a security that pays interest at maturity
  • AMORDEGRC This function will return the depreciation for each accounting period by using a depreciation coefficient
  • AMORLINC This function will return the depreciation for each accounting period
  • COUPDAYBS This function will return the number of days from the beginning of the coupon period to the settlement date 
  • COUPDAYS This function will return the number of days in the coupon period that  contains the settlement date
  • COUPDAYSNC This function will return the number of days from the settlement date to the next coupon date
  • COUPNCD This function will return the next coupon date after the settlement date
  • COUPNUM This function will return the number of coupons payable between the settlement date and maturity date
  • COUPPCD This function will return the previous coupon date before the settlement date
  • CUMIPMT This function will return the cumulative interest paid between two periods
  • CUMPRINC This function will return the cumulative principal paid on a loan between two periods
  • DB This function will return the depreciation of an asset for a specified period by using the fixed-declining balance method
  • DDB This function will return the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify
  • DISC This function will return the discount rate for a security
  • DOLLARDE This function will convert a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
  • DOLLARFR This function will convert a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
  • DURATION This function will return the annual duration of a security with periodic interest payments
  • EFFECT This function will return the effective annual interest rate
  • FV This function will return the future value of an investment
  • FVSCHEDULE This function will return the future value of an initial principal after applying a series of compound interest rates
  • INTRATE This function will return the interest rate for a fully invested security
  • IPMT This function will return the interest payment for an investment for a given period
  • IRR This function will return the internal rate of return for a series of cash flows
  • ISPMT This function will calculate the interest paid during a specific period of an investment
  • MDURATION This function will return the Macauley modified duration for a security with an assumed par value of $100
  • MIRR This function will return the internal rate of return where positive and negative cash flows are financed at different rates
  • NOMINAL This function will return the annual nominal interest rate
  • NPER This function will return the number of periods for an investment
  • NPV This function will return the net present value of an investment based on a series of periodic cash flows and a discount rate
  • ODDFPRICE This function will return the price per $100 face value of a security with  an odd first period
  • ODDFYIELD This function will return the yield of a security with an odd first period
  • ODDLPRICE This function will return the price per $100 face value of a security with an odd last period
  • ODDLYIELD This function will return the yield of a security with an odd last period 
  • PMT This function will return the periodic payment for an annuity
  • PPMT This function will return the payment on the principal for an investment for a given period
  • PRICE This function will return the price per $100 face value of a security that pays periodic interest
  • PRICEDISC This function will return the price per $100 face value of a discounted security
  • PRICEMAT This function will return the price per $100 face value of a security that pays interest at maturity
  • PV This function will return the present value of an investment
  • RATE This function will return the interest rate per period of an annuity
  • RECEIVED This function will return the amount received at maturity for a fully invested security
  • SLN This function will return the straight-line depreciation of an asset for one period
  • SYD This function will return the sum-ofyears' digits depreciation of an asset for a specified period
  • TBILLEQ This function will return the bondequivalent yield for a Treasury bill
  • TBILLPRICE This function will return the price per $100 face value for a Treasury bill
  • TBILLYIELD This function will return the yield for a Treasury bill
  • VDB This function will return the depreciation of an asset for a specified or partial period by using a declining balance method
  • XIRR This function will return the internal rate of return for a schedule of cash flows that is not necessarily periodic
  • XNPV This function will return the net present value for a schedule of cash flows that is not necessarily periodic
  • YIELD This function will Return the yield on  a security that pays periodic interest
  • YIELDDISC This function will return the annual yield for a discounted security; for example, a Treasury bill
  • YIELDMAT This function will return the annual yield of a security that pays interest at maturity

Information Functions

  • CELL This function will return information about the formatting, location, or contents of a cell
  • ERROR.TYPE This function will return a number corresponding to an error type 
  • INFO This function will return information about the current operating environment
  • ISBLANK This function will return TRUE if the value is blank
  • ISERR This function will return TRUE if the value is any error value except #N/A
  • ISERROR This function will return TRUE if the value is any error value
  • ISEVEN This function will return TRUE if the number is even
  • ISLOGICAL This function will return TRUE if the value is a logical value
  • ISNA This function will return TRUE if the value is the #N/A error value
  • ISNON T This function will return TRUE if the value is not text
  • ISNUMBER This function will return TRUE if the value is a number
  • ISODD This function will return TRUE if the number is odd
  • ISREF This function will return TRUE if the value is a reference
  • ISTEXT This function will return TRUE if the value is text
  • N This function will return a value converted to a number
  • NA This function will return the error value #N/A
  • TYPE This function will return a number indicating the data type of a value

Logical Functions

  • AND This function will return TRUE if all of its arguments are TRUE
  • FALSE This function will return the logical value FALSE
  • IF This function will specify a logical test to perform
  • NOT This function will reverse the logic of its argument
  • OR This function will return TRUE if any argument is TRUE
  • TRUE This function will return the logical value TRUE

Lookup and Reference Functions

  • ADDRESS This function will return a reference as text to a single cell in a worksheet
  • AREAS This function will return the number of areas in a reference
  • CHOOSE This function will choose a value from a list of values
  • COLUMN This function will return the column number of a reference
  • COLUMNS This function will return the number of columns in a reference
  • GETPIVOTDATA This function will return data stored in a PivotTable
  • HLOOKUP This function will look in the top row of an array and returns the value of the
  • indicated cell
  • HYPERLINK This function will create a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
  • INDEX This function will use an index to choose a value from a reference or array
  • INDIRECT This function will return a reference indicated by a text value
  • LOOKUP This function will look up values in a vector or array
  • MATCH This function will look up values in a reference or array
  • OFFSET This function will return a reference offset from a given reference
  • OW This function will return the row number of a reference
  • ROWS This function will return the number of rows in a reference
  • RTD This function will retrieve real-time data from a program that supports COM automation
  • TRANSPOSE This function will return the transpose of an array
  • VLOOKUP This function will look in the first column of an array and moves across the row to return the value of a cell

Math and Trigonometry Functions

  • ABS This function will return the absolute value of a number
  • ACOS This function will return the arccosine of a number
  • ACOSH This function will return the inverse hyperbolic cosine of a number
  • ASIN This function will return the arcsine of a number
  • ASINH This function will return the inverse hyperbolic sine of a number
  • ATAN This function will return the arctangent of a number
  • ATAN2 This function will return the arctangent from x- and y-coordinates
  • ATANH This function will return the inverse hyperbolic tangent of a number
  • CEILING This function will round a number to the nearest integer or to the nearest multiple of significance
  • COMBIN This function will return the number of combinations for a given number of objects
  • COS This function will return the cosine of a number
  • COSH This function will return the hyperbolic cosine of a number
  • DEGREES This function will convert radians to degrees
  • EVEN This function will round a number up to the nearest even integer
  • EXP This function will return e raised to the power of a given number
  • FACT This function will return the factorial of a number
  • FACTDOUBLE This function will return the double factorial of a number
  • FLOOR This function will round a number down, toward zero
  • GCD This function will return the greatest common divisor
  • INT This function will round a number down to the nearest integer
  • LCM This function will return the least common multiple
  • LN This function will return the natural logarithm of a number
  • LOG This function will return the logarithm of a number to a specified base
  • LOG10 This function will return the base-10 logarithm of a number
  • MDETERM This function will return the matrix determinant of an array
  • MINVERSE This function will return the matrix inverse of an array
  • MMULT This function will return the matrix product of two arrays
  • MOD This function will return the remainder from division
  • MROUND This function will return a number rounded to the desired multiple
  • MULTINOMIAL This function will return the multinomial of a set of numbers
  • ODD This function will round a number up to the nearest odd integer
  • PI This function will return the value of pi 
  • POWER This function will return the result of a number raised to a power
  • PRODUCT This function will multiply its arguments
  • QUOTIENT This function will return the integer portion of a division
  • RADIANS This function will convert degrees to radians
  • RAND This function will return a random number between 0 and 1
  • RANDBETWEEN This function will return a random number between the numbers you specify
  • ROMAN This function will convert an arabic numeral to roman, as text
  • ROUND This function will round a number to a specified number of digits
  • ROUNDDOWN This function will round a number down, toward zero 
  • ROUNDUP This function will round a number up, away from zero
  • SERIESSUM This function will return the sum of a power series based on the formula
  • SIGN This function will return the sign of a number
  • SIN This function will return the sine of the given angle
  • SINH This function will return the hyperbolic sine of a number
  • SQRT This function will return a positive square root
  • SQRTPI This function will return the square root of (number * pi)
  • SUBTOTAL This function will return a subtotal in a list or database
  • SUM This function will add its arguments
  • SUMIF Adds the cells specified by a given criteria
  • SUMPRODUCT This function will return the sum of the products of corresponding array components
  • SUMSQ This function will return the sum of the squares of the arguments
  • SUMX2MY2 Returns the sum of the difference of squares of corresponding values in two arrays
  • SUMX2PY2 This function will return the sum of the sum of squares of corresponding values in two arrays
  • SUMXMY2 This function will return the sum of squares of differences of corresponding values in two arrays
  • TAN This function will return the tangent of a number
  • TANH This function will return the hyperbolic tangent of a number
  • TRUNC This function will truncate a number to an integer

Statistical Functions

  • AVEDEV This function will return the average of the absolute deviations of data points from their mean
  • AVERAGE This function will return the average of its arguments
  • AVERAGEA This function will return the average of its arguments, including numbers, text, and logical values
  • BETADIST This function will return the beta cumulative distribution function
  • BETAINV This function will return the inverse of the cumulative distribution function for a specified beta distribution
  • BINOMDIST This function will return the individual term binomial distribution probability
  • CHIDIST This function will return the onetailed probability of the chi-squared distribution
  • CHIINV This function will return the inverse of the one-tailed probability of the chisquared distribution
  • CHITEST This function will return the test for independence
  • CONFIDENCE This function will return the confidence interval for a population mean
  • CORREL This function will return the correlation coefficient between two data sets
  • COUNT This function will count how many numbers are in the list of arguments
  • COUNTA This function will count how many values are in the list of arguments
  • COUNTBLANK This function will count the number of blank cells within a range
  • COUNTIF This function will count the number of nonblank cells within a range that meet the given criteria
  • COVAR This function will return covariance, the average of the products of paired deviations
  • CRITBINOM This function will return the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
  • DEVSQ This function will return the sum of squares of deviations
  • EXPONDIST This function will return the exponential distribution
  • FDIST This function will return the F probability distribution
  • FINV This function will return the inverse of the F probability distribution
  • FISHER This function will return the Fisher transformation
  • FISHERINV This function will return the inverse of the Fisher transformation
  • FORECAST This function will return a value along a linear trend
  • FREQUENCY This function will return a frequency distribution as a vertical array
  • FTEST This function will return the result of an F-test
  • GAMMADIST This function will return the gamma distribution
  • GAMMAINV This function will return the inverse of the gamma cumulative distribution
  • GAMMALN This function will return the natural logarithm of the gamma function, Γ (x)
  • GEOMEAN This function will return the geometric mean
  • GROWTH This function will return values along an exponential trend
  • HARMEAN This function will return the harmonic mean
  • HYPGEOMDIST This function will return the hypergeometric distribution
  • INTERCEPT This function will return the intercept of the linear regression line
  • KURT This function will return the kurtosis of a data set
  • LARGE This function will return the k-th largest value in a data set
  • LINEST This function will return the parameters of a linear trend
  • LOGEST This function will return the parameters of an exponential trend
  • LOGINV This function will return the inverse of the lognormal distribution
  • LOGNORMDIST This function will return the cumulative lognormal distribution
  • MAX This function will return the maximum value in a list of arguments
  • MAXA This function will return the maximum value in a list of arguments, including numbers, text, and logical values
  • MEDIAN This function will return the median of the given numbers
  • MIN This function will return the minimum value in a list of arguments
  • MINA This function will return the smallest value in a list of arguments, including numbers, text, and logical values
  • MODE This function will return the most common value in a data set
  • NEGBINOMDIST return the negative binomial distribution
  • NORMDIST This function will return the normal cumulative distribution
  • NORMINV This function will return the inverse of the normal cumulative distribution
  • NORMSDIST This function will return the standard normal cumulative distribution
  • NORMSINV This function will return the inverse of the standard normal cumulative distribution
  • PEARSON This function will return the Pearson product moment correlation coefficient
  • PERCENTILE This function will return the kth percentile of values in a range
  • PERCENTRANK This function will return the percentage rank of a value in a data set
  • PERMUT This function will return the number of permutations for a given number of objects
  • POISSON This function will return the Poisson distribution
  • PROB This function will return the probability that values in a range are between two limits
  • QUARTILE This function will return the quartile of a data set
  • RANK This function will return the rank of a number in a list of numbers
  • RSQ This function will return the square of the Pearson product moment correlation coefficient
  • SKEW This function will return the skewness of a distribution
  • SLOPE This function will return the slope of the linear regression line
  • SMALL This function will return the k-th smallest value in a data set
  • STANDARDIZE This function will return a normalized value
  • STDEV This function will estimate standard deviation based on a sample
  • STDEVA This function will estimate standard deviation based on a sample, including numbers, text, and logical values
  • STDEVP This function will calculate standard deviation based on the entire population
  • STDEVPA This function will calculate standard deviation based on the entire population, including numbers, text, and logical values
  • STEYX This function will return the standard error of the predicted y-value for each x in the regression
  • TDIST This function will return the Student's t-distribution
  • TINV This function will return the inverse of the Student's t-distribution
  • TREND This function will return values along a linear trend
  • TRIMMEAN This function will return the mean of the interior of a data set
  • TTEST This function will return the probability associated with a Student's t-test
  • VAR This function will estimate variance based on a sample
  • VARA This function will estimate variance based on a sample, including numbers, text,
  • and logical values
  • VARP This function will calculate variance based on the entire population
  • VARPA This function will calculate variance based on the entire population, including numbers, text, and logical values
  • WEIBULL This function will return the Weibull distribution
  • ZTEST This function will return the one tailed probability-value of a z-test

Text Functions

  • ASC This function will change full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
  • BAHTTEXT This function will converts a number to text, using the ß (baht) currency format
  • CHAR This function will return the character specified by the code number
  • CLEAN This function will remove all nonprintable characters from text
  • CODE This function will return a numeric code for the first character in a text string
  • CONCATENATE This function will join several text items into one text item
  • DOLLAR This function will convert a number to text, using the $ (dollar) currency format
  • EXACT This function will check to see if two text values are identical
  • FIND, FINDB This function will find one text value within another (case-sensitive)
  • FIXED This function will format a number as text with a fixed number of decimals
  • JIS This function will change half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
  • LEFT, LEFTB This function will return the leftmost characters from a text value
  • LEN, LENB This function will return the number of characters in a text string
  • LOWER This function will convert text to lowercase
  • MID, MIDB This function will return a specific number of characters from a text string starting at the position you specify
  • PHONETIC This function will extract the phonetic (furigana) characters from a text string
  • PROPER This function will capitalize the first letter in each word of a text value
  • REPLACE, REPLACEB This function will replace characters within text
  • REPT This function will repeat text a given number of times
  • RIGHT, RIGHTB This function will return the rightmost characters from a text value
  • SEARCH, SEARCHB This function will find one text value within another (not casesensitive)
  • SUBSTITUTE This function will substitute new text for old text in a text string
  • T This function will convert its arguments to text
  • TEXT This function will format a number and converts it to text
  • TRIM This function will remove spaces from text
  • UPPER This function will convert text to uppercase
  • VALUE Converts a text argument to a number

Post a Comment

Previous Post Next Post