Formula results can be calculated in the browser and upon data export. Formulas can apply to resources and properties. Results from formulas produce single-valued results only, and they use the mean value of multi-valued properties. Formulas can be generated for actual or planned values as detailed in the articles on calculated properties and resource names.
ABS | Returns the absolute value of a number. The absolute value of a number is the number without its sign. |
ACCRINT | Returns the accrued interest for a security that pays periodic interest |
ACOS | Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. |
ACOSH | Returns the inverse hyperbolic cosine of a number. The number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is number, so ACOSH(COSH(number)) equals number. |
ACOT | Returns the principal value of the arccotangent, or inverse cotangent, of a number. |
ACOTH | Returns the inverse hyperbolic cotangent of a number. |
ADD | Although Excel includes a multitude of built-in worksheet functions, chances are it doesn’t have a function for every type of calculation you perform. The designers of Excel couldn’t possibly anticipate every user's calculation needs. Instead, Excel provides you with the ability to create custom functions, which are explained in this article. |
AGGREGATE | Returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values. |
AND | Use the AND function, one of the logical functions, to determine if all conditions in a test are TRUE. |
ARABIC | Converts a Roman numeral to an Arabic numeral. |
ARGS2ARRAY | Returns the column number of the given cell reference. For example, the formula =COLUMN(D10) returns 4, because column D is the fourth column. |
ASIN | Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2. |
ASINH | Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number, so ASINH(SINH(number)) equals number. |
ATAN | Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2. |
ATAN2 | Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -pi and pi, excluding -pi. |
ATANH | Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1). The inverse hyperbolic tangent is the value whose hyperbolic tangent is number, so ATANH(TANH(number)) equals number. |
AVEDEV | Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set. |
AVERAGE | Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers. |
AVERAGEA | Calculates the average (arithmetic mean) of the values in the list of arguments. |
AVERAGEIF | Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. |
AVERAGEIFS | Returns the average (arithmetic mean) of all cells that meet multiple criteria. |
BASE | Converts a number into a text representation with the given radix (base). |
BESSELI | Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments. |
BESSELJ | Returns the Bessel function. |
BESSELK | Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments. |
BESSELY | Returns the Bessel function, which is also called the Weber function or the Neumann function. |
BETA.DIST | Returns the beta distribution. |
BETA.INV | Returns the inverse of the beta cumulative probability density function (BETA.DIST). |
BETADIST | Returns the cumulative beta probability density function. The beta distribution is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television. |
BETAINV | Returns the inverse of the cumulative beta probability density function for a specified beta distribution. That is, if probability = BETADIST(x,...), then BETAINV(probability,...) = x. The beta distribution can be used in project planning to model probable completion times given an expected completion time and variability. |
BIN2DEC | Converts a binary number to decimal. |
BIN2HEX | Converts a binary number to hexadecimal. |
BIN2OCT | Converts a binary number to octal. |
BINOM.DIST | Returns the individual term binomial distribution probability. Use BINOM.DIST in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. For example, BINOM.DIST can calculate the probability that two of the next three babies born are male. |
BINOM.DIST.RANGE | Returns the probability of a trial result using a binomial distribution. |
BINOM.INV | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. |
BINOMDIST | Returns the individual term binomial distribution probability. Use BINOMDIST in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. For example, BINOMDIST can calculate the probability that two of the next three babies born are male. |
BITAND | Returns a bitwise 'AND' of two numbers. |
BITLSHIFT | Returns a number shifted left by the specified number of bits. |
BITOR | Returns a bitwise 'OR' of two numbers. |
BITRSHIFT | Returns a number shifted right by the specified number of bits. |
BITXOR | Returns a bitwise 'XOR' of two numbers. |
CEILING | Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel. |
CEILINGMATH | |
CEILINGPRECISE | Click a letter to go to functions that start with it. Or press Ctrl+F to find a function by typing the first few letters or a descriptive word. To get detailed information about a function, click its name in the first column. |
CHAR | Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. |
CHISQ.DIST | Returns the chi-squared distribution. |
CHISQ.DIST.RT | Returns the right-tailed probability of the chi-squared distribution. |
CHISQ.INV | This article describes the formula syntax and usage of the CHISQ.INV function in Microsoft Excel. |
CHISQ.INV.RT | Returns the inverse of the right-tailed probability of the chi-squared distribution. |
CHOOSE | Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num. |
CLEAN | Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed. |
CODE | Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. |
COLUMN | Returns the column number of the given cell reference. For example, the formula =COLUMN(D10) returns 4, because column D is the fourth column. |
COLUMNS | Returns the number of columns in an array or reference. |
COMBIN | Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items. |
COMBINA | Returns the number of combinations (with repetitions) for a given number of items. |
COMPLEX | Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj. |
or or |
Use CONCATENATE, one of the text functions, to join two or more text strings into one string. |
CONFIDENCE | Returns the confidence interval for a population mean, using a normal distribution. |
CONFIDENCE.NORM | The confidence interval is a range of values. Your sample mean, x, is at the center of this range and the range is x ± CONFIDENCE.NORM. For example, if x is the sample mean of delivery times for products ordered through the mail, x ± CONFIDENCE.NORM is a range of population means. For any population mean, μ0, in this range, the probability of obtaining a sample mean further from μ0 than x is greater than alpha; for any population mean, μ0, not in this range, the probability of obtaining a sample mean further from μ0 than x is less than alpha. In other words, assume that we use x, standard_dev, and size to construct a two-tailed test at significance level alpha of the hypothesis that the population mean is μ0. Then we will not reject that hypothesis if μ0 is in the confidence interval and will reject that hypothesis if μ0 is not in the confidence interval. The confidence interval does not allow us to infer that there is probability 1 – alpha that our next package will take a delivery time that is in the confidence interval. |
CONFIDENCE.T | This article describes the formula syntax and usage of the CONFIDENCE.T function in Microsoft Excel. |
CONVERT | Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers. |
CORREL | Returns the correlation coefficient of the Array1 and Array2 cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners. |
COS | Returns the cosine of the given angle. |
COSH | Returns the hyperbolic cosine of a number. |
COT | Return the cotangent of an angle specified in radians. |
COTH | Return the hyperbolic cotangent of a hyperbolic angle. |
COUNT | The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5. |
COUNTA | The COUNTA function counts the number of cells that are not empty in a range. |
COUNTBLANK | Counting is an integral part of data analysis, whether you are tallying the head count of a department in your organization or the number of units that were sold quarter-by-quarter. Excel provides multiple techniques that you can use to count cells, rows, or columns of data. |
COUNTIF | Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list. |
COUNTIFS | The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met. |
COUNTIN | The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5. |
COUNTUNIQUE | Let's say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains: |
COVARIANCE.P | This article describes the formula syntax and usage of the COVARIANCE.P function in Microsoft Excel. |
COVARIANCE.S | This article describes the formula syntax and usage of the COVARIANCE.S function in Microsoft Excel. |
CSC | Returns the cosecant of an angle specified in radians. |
CSCH | Return the hyperbolic cosecant of an angle specified in radians. |
CUMIPMT | Returns the cumulative interest paid on a loan between start_period and end_period. |
CUMPRINC | Returns the cumulative principal paid on a loan between start_period and end_period. |
DATE | Use Excel's DATE function when you need to take three separate values and combine them to form a date. |
DATEVALUE | Occasionally, dates may become formatted and stored in cells as text. For example, you may have entered a date in a cell that was formatted as text, or the data might have been imported or pasted from an external data source as text. |
DAY | Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. |
DAYS | Returns the number of days between two dates. |
DAYS360 | The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months. |
DB | Returns the depreciation of an asset for a specified period using the fixed-declining balance method. |
DDB | Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. |
DEC2BIN | Converts a decimal number to binary. |
DEC2HEX | Converts a decimal number to hexadecimal. |
DEC2OCT | Converts a decimal number to octal. |
DECIMAL | Converts a text representation of a number in a given base into a decimal number. |
DEGREES | Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. |
DELTA | Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise. Use this function to filter a set of values. For example, by summing several DELTA functions you calculate the count of equal pairs. This function is also known as the Kronecker Delta function. |
DEVSQ | Returns the sum of squares of deviations of data points from their sample mean. |
DIVIDE | Let's say you want to find out how many person hours it took to finish a project (total project hours ÷ total people on project) or the actual miles per gallon rate for your recent cross-country trip (total miles ÷ total gallons). Excel gives you several ways to divide numbers. |
DOLLAR | The function described in this Help topic converts a number to text format and applies a currency symbol. The name of the function (and the symbol that it applies) depends upon your language settings. |
DOLLARDE | Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number. Fractional dollar numbers are sometimes used for security prices. |
DOLLARFR | Use DOLLARFR to convert decimal numbers to fractional dollar numbers, such as securities prices. |
E | |
EDATE | Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. |
EFFECT | Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. |
EOMONTH | Use Excel's DATE function when you need to take three separate values and combine them to form a date. |
EQ | Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. |
ERF | Returns the error function integrated between lower_limit and upper_limit. |
ERFC | Returns the complementary ERF function integrated between x and infinity. |
EVEN | Returns number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing crate accepts rows of one or two items. The crate is full when the number of items, rounded up to the nearest two, matches the crate's capacity. |
EXACT | Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document. |
EXP | Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm. |
EXPON.DIST | This article describes the formula syntax and usage of the EXPON.DIST function in Microsoft Excel. |
EXPONDIST | This article describes the formula syntax and usage of the EXPONDIST function in Microsoft Excel. |
F.DIST | Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets. You can use this function to determine whether two data sets have different degrees of diversity. For example, you can examine the test scores of men and women entering high school and determine if the variability in the females is different from that found in the males. |
F.DIST.RT | This article describes the formula syntax and usage of the F.DIST.RT function in Microsoft Excel. |
F.INV | Returns the inverse of the F probability distribution. If p = F.DIST(x,...), then F.INV(p,...) = x. The F distribution can be used in an F-test that compares the degree of variability in two data sets. For example, you can analyze income distributions in the United States and Canada to determine whether the two countries have a similar degree of income diversity. |
F.INV.RT | This article describes the formula syntax and usage of the F.INV.RT function in Microsoft Excel. |
FACT | Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number. |
FACTDOUBLE | Returns the double factorial of a number. |
FALSE | Returns the logical value FALSE. |
FDIST | Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets. You can use this function to determine whether two data sets have different degrees of diversity. For example, you can examine the test scores of men and women entering high school and determine if the variability in the females is different from that found in the males. |
FDISTRT | |
FIND | Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. You can also use SEARCH to find one text string within another, but unlike SEARCH, FIND is case sensitive and doesn't allow wildcard characters. |
FINV | Returns the inverse of the (right-tailed) F probability distribution. If p = FDIST(x,...), then FINV(p,...) = x. |
FINVRT | New in Excel 2010, a sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values. Position a sparkline near its data for greatest impact. |
FISHER | Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed. Use this function to perform hypothesis testing on the correlation coefficient. |
FISHERINV | Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then FISHERINV(y) = x. |
FIXED | Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. |
FLATTEN | This article describes how to improve page load times for SharePoint Online by using structural navigation and search-driven navigation. |
FLOOR | Rounds number down, toward zero, to the nearest multiple of significance. |
FORECAST | Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends. |
FREQUENCY | Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula. |
FV | FV, one of the financial functions, calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment. |
FVSCHEDULE | Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate. |
GAMMA | Return the gamma function value. |
GAMMA.DIST | This article describes the formula syntax and usage of the GAMMA.DIST function in Microsoft Excel. |
GAMMA.INV | This article describes the formula syntax and usage of the GAMMA.INV function in Microsoft Excel. |
GAMMADIST | Returns the gamma distribution. You can use this function to study variables that may have a skewed distribution. The gamma distribution is commonly used in queuing analysis. |
GAMMAINV | This article describes the formula syntax and usage of the GAMMAINV function in Microsoft Excel. |
GAMMALN | Returns the natural logarithm of the gamma function, Γ(x). |
GAMMALN.PRECISE | Returns the natural logarithm of the gamma function, Γ(x). |
GAUSS | Calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean. |
GCD | Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder. |
GEOMEAN | Returns the geometric mean of an array or range of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates. |
GESTEP | Returns 1 if number ≥ step; returns 0 (zero) otherwise. Use this function to filter a set of values. For example, by summing several GESTEP functions you calculate the count of values that exceed a threshold. |
GROWTH | Calculates predicted exponential growth by using existing data. GROWTH returns the y-values for a series of new x-values that you specify by using existing x-values and y-values. You can also use the GROWTH worksheet function to fit an exponential curve to existing x-values and y-values. |
GTE | Returns a formula as a string. |
HARMEAN | Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals. |
HEX2BIN | Converts a hexadecimal number to binary. |
HEX2DEC | Converts a hexadecimal number to decimal. |
HEX2OCT | Converts a hexadecimal number to octal. |
HOUR | Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). |
HTML2TEXT | |
HYPGEOM.DIST | This article describes the formula syntax and usage of the HYPGEOM.DIST function in Microsoft Excel. |
HYPGEOMDIST | Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of sample successes, given the sample size, population successes, and population size. Use HYPGEOMDIST for problems with a finite population, where each observation is either a success or a failure, and where each subset of a given size is chosen with equal likelihood. |
IF | The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False. |
IMABS | Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format. |
IMAGINARY | Returns the imaginary coefficient of a complex number in x + yi or x + yj text format. |
IMARGUMENT | Returns the argument \r\n\r\n(theta), an angle expressed in radians, such that: |
IMCONJUGATE | Returns the complex conjugate of a complex number in x + yi or x + yj text format. |
IMCOS | Returns the cosine of a complex number in x + yi or x + yj text format. |
IMCOSH | Returns the hyperbolic cosine of a complex number in x+yi or x+yj text format. |
IMCOT | Returns the cotangent of a complex number in x+yi or x+yj text format. |
IMCSC | Returns the cosecant of a complex number in x+yi or x+yj text format. |
IMCSCH | Returns the hyperbolic cosecant of a complex number in x+yi or x+yj text format. |
IMDIV | Returns the quotient of two complex numbers in x + yi or x + yj text format. |
IMEXP | Returns the exponential of a complex number in x + yi or x + yj text format. |
IMLN | Returns the natural logarithm of a complex number in x + yi or x + yj text format. |
IMLOG10 | Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format. |
IMLOG2 | Returns the base-2 logarithm of a complex number in x + yi or x + yj text format. |
IMPOWER | Returns a complex number in x + yi or x + yj text format raised to a power. |
IMPRODUCT | Returns the product of 1 to 255 complex numbers in x + yi or x + yj text format. |
IMREAL | Returns the real coefficient of a complex number in x + yi or x + yj text format. |
IMSEC | Returns the secant of a complex number in x+yi or x+yj text format. |
IMSECH | Returns the hyperbolic secant of a complex number in x+yi or x+yj text format. |
IMSIN | Returns the sine of a complex number in x + yi or x + yj text format. |
IMSINH | Returns the hyperbolic sine of a complex number in x+yi or x+yj text format. |
IMSQRT | Returns the square root of a complex number in x + yi or x + yj text format. |
IMSUB | Returns the difference of two complex numbers in x + yi or x + yj text format. |
IMSUM | Returns the sum of two or more complex numbers in x + yi or x + yj text format. |
IMTAN | Returns the tangent of a complex number in x+yi or x+yj text format. |
INT | Rounds a number down to the nearest integer. |
INTERCEPT | Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values. Use the INTERCEPT function when you want to determine the value of the dependent variable when the independent variable is 0 (zero). For example, you can use the INTERCEPT function to predict a metal's electrical resistance at 0°C when your data points were taken at room temperature and higher. |
INTERVAL | Returns the confidence interval for a population mean, using a normal distribution. |
IPMT | Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. |
IRR | Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods. |
ISBINARY | Returns a bitwise 'AND' of two numbers. |
ISBLANK | Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE. |
ISEVEN | Returns TRUE if number is even, or FALSE if number is odd. |
ISLOGICAL | To get detailed information about a function, click its name in the first column. |
ISNONTEXT | |
ISNUMBER | Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE. |
ISODD | Returns TRUE if number is odd, or FALSE if number is even. |
ISOWEEKNUM | Returns number of the ISO week number of the year for a given date. |
ISPMT | Calculates the interest paid during a specific period of an investment. |
ISTEXT | Let's say you want to ensure that a column contains text and not numbers or you want to find all orders by a particular salesperson. There are several ways to check if a cell contains text but the case of the text doesn't matter to you. |
JOIN | Returns a string created by joining a number of substrings contained in an array. |
KURT | Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution. |
LARGE | Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score. |
LCM | Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments number1, number2, and so on. Use LCM to add fractions with different denominators. |
LEFT | LEFT returns the first character or characters in a text string, based on the number of characters you specify. |
LEN | When you need to count the characters in cells, use the LEN function. The function counts letters, numbers, characters, and all spaces. For example, the length of "It's 98 degrees today, so I'll go swimming" (excluding the quotes) is 42 characters—31 letters, 2 numbers, 8 spaces, a comma, and 2 apostrophes. |
LINEST | The LINEST function calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and then returns an array that describes the line. You can also combine LINEST with other functions to calculate the statistics for other types of models that are linear in the unknown parameters, including polynomial, logarithmic, exponential, and power series. Because this function returns an array of values, it must be entered as an array formula. Instructions follow the examples in this article. |
LN | Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). |
LOG | Returns the logarithm of a number to the base you specify. |
LOG10 | |
LOGEST | In Excel Online, you can project values in a series by using worksheet functions, or you can click and drag the fill handle to create a linear trend of numbers. But you can’t create a growth trend by using the fill handle. |
LOGNORM.DIST | Returns the lognormal distribution of x, where ln(x) is normally distributed with parameters Mean and Standard_dev. |
LOGNORM.INV | Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters Mean and Standard_dev. If p = LOGNORM.DIST(x,...) then LOGNORM.INV(p,...) = x. |
LOGNORMDIST | Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev. Use this function to analyze data that has been logarithmically transformed. |
LOGNORMINV | Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev. If p = LOGNORMDIST(x,...) then LOGINV(p,...) = x. |
LOWER | Converts all uppercase letters in a text string to lowercase. |
LT | Returns the Student's left-tailed t-distribution. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution. |
LTE | Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula. |
MATCH | The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range. |
MAX | Returns the largest value in a set of values. |
MAXA | Returns the largest value in a list of arguments. |
MEDIAN | Returns the median of the given numbers. The median is the number in the middle of a set of numbers. |
MID | MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. |
MIN | Formulas are equations that perform calculations on values in your sheet. All formulas begin with an equal sign (=). You can create a simple formula by using constant and calculation operator. For example, the formula =5+2*3, multiplies two numbers and then adds a number to the result. |
MINA | Returns the smallest value in the list of arguments. |
MINUS | Important: The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences. |
MINUTE | Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59. |
MIRR | Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash. |
MOD | Returns the remainder after number is divided by divisor. The result has the same sign as divisor. |
MODE.MULT | Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. For horizontal arrays, use TRANSPOSE(MODE.MULT(number1,number2,...)). |
MODE.SNGL | Returns the most frequently occurring, or repetitive, value in an array or range of data. |
MODEMULT | Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. For horizontal arrays, use TRANSPOSE(MODE.MULT(number1,number2,...)). |
MODESNGL | |
MONTH | Suppose you want to adjust a project's schedule date by adding two weeks to see what the new completion date will be, or you want to determine how long a single activity will take to complete in a list of project tasks. You can add or subtract a number of days to or from a date by using a simple formula, or you can use worksheet functions that are designed to work specifically with dates in Excel. |
MROUND | Returns a number rounded to the desired multiple. |
MULTINOMIAL | Returns the ratio of the factorial of a sum of values to the product of factorials. |
MULTIPLY | Multiplying and dividing in Excel is easy, but you need to create a simple formula to do it. Just remember that all formulas in Excel begin with an equal sign (=), and you can use the formula bar to create them. |
NE | Important: The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences. |
NEGBINOM.DIST | Returns the negative binomial distribution, the probability that there will be Number_f failures before the Number_s-th success, with Probability_s probability of a success. |
NEGBINOMDIST | Returns the negative binomial distribution. NEGBINOMDIST returns the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s. This function is similar to the binomial distribution, except that the number of successes is fixed, and the number of trials is variable. Like the binomial, trials are assumed to be independent. |
NETWORKDAYS | Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term. |
NOMINAL | Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year. |
NORM.DIST | Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing. |
NORM.INV | Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. |
NORM.S.DIST | Returns the standard normal distribution (has a mean of zero and a standard deviation of one). |
NORM.S.INV | Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one. |
NORMDIST | Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing. |
NORMINV | |
NORMSDIST | Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas. |
NORMSINV | Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one. |
NOT | Use the NOT function, one of the logical functions, when you want to make sure one value is not equal to another. |
NOW | Let's say that you want to easily enter the current date and time while making a time log of activities. Or perhaps you want to display the current date and time automatically in a cell every time formulas are recalculated. There are several ways to insert the current date and time in a cell. |
NPER | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. |
NPV | Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). |
NUMBERS | Unlike other Microsoft Office programs, Excel does not provide a button to number data automatically. But, you can easily add sequential numbers to rows of data by dragging the fill handle to fill a column with a series of numbers or by using the ROW function. |
NUMERAL | Converts an arabic numeral to roman, as text. |
OCT2BIN | Converts an octal number to binary. |
OCT2DEC | Converts an octal number to decimal. |
OCT2HEX | Converts an octal number to hexadecimal. |
ODD | Returns number rounded up to the nearest odd integer. |
OR | Use the OR function, one of the logical functions, to determine if any conditions in a test are TRUE. |
PDURATION | Returns the number of periods required by an investment to reach a specified value. |
PEARSON | Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets. |
PERCENTILEEXC | |
PERCENTILEINC | Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. |
PERCENTRANKEXC | |
PERCENTRANKINC | |
PERMUT | Returns the number of permutations for a given number of objects that can be selected from number objects. A permutation is any set or subset of objects or events where internal order is significant. Permutations are different from combinations, for which the internal order is not significant. Use this function for lottery-style probability calculations. |
PERMUTATIONA | Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects. |
PHI | Returns the value of the density function for a standard normal distribution. |
PI | Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits. |
PMT | PMT, one of the financial functions, calculates the payment for a loan based on constant payments and a constant interest rate. |
POISSON.DIST | Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute. |
POISSONDIST | Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute. |
POW | Returns the result of a number raised to a power. |
POWER | Returns the result of a number raised to a power. |
PPMT | Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate. |
PROB | Returns the probability that values in a range are between two limits. If upper_limit is not supplied, returns the probability that values in x_range are equal to lower_limit. |
PRODUCT | The PRODUCT function multiplies all the numbers given as arguments and returns the product. For example, if cells A1 and A2 contain numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2. |
PROPER | Unlike Microsoft Word, Microsoft Excel doesn't have a Change Case button for changing capitalization. However, you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case. Functions are just built-in formulas that are designed to accomplish specific tasks—in this case, converting text case. |
PV | PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that's your investment goal. |
QUARTILE.EXC | Returns the quartile of the data set, based on percentile values from 0..1, exclusive. |
QUARTILE.INC | Returns the quartile of a data set, based on percentile values from 0..1, inclusive. |
QUARTILEEXC | Returns the quartile of a data set. Quartiles often are used in sales and survey data to divide populations into groups. For example, you can use QUARTILE to find the top 25 percent of incomes in a population. |
QUARTILEINC | |
QUOTIENT | Returns the integer portion of a division. Use this function when you want to discard the remainder of a division. |
RADIANS | Converts degrees to radians. |
RAND | Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated. |
RANDBETWEEN | Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated. |
RANK.AVG | Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned. |
RANK.EQ | Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. |
RANKAVG | |
RANKEQ | Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. |
RATE | Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. |
REFERENCE | Quickly find the difference between two times, control the format of the result, and extend the calculation to a list of times. For example, you can quickly subtract one time from another time, or calculate the duration of agenda items for a meeting. |
REGEXEXTRACT | |
REGEXMATCH | Data Analysis Expressions (DAX) sounds a little intimidating at first, but don’t let the name fool you. DAX basics are really quite easy to understand. First things first - DAX is NOT a programming language. DAX is a formula language. You can use DAX to define custom calculations for Calculated Columns and for Measures (also known as calculated fields). DAX includes some of the functions used in Excel formulas, and additional functions designed to work with relational data and perform dynamic aggregation. |
REGEXREPLACE | Word offers several options for locating specific content in your document. You can search for and replace items such as text, images, captions, bookmarks, or certain types of formatting such as paragraphs or page breaks. You can use the Go To command to go to a particular instance of content in your document, and you can also extend your search by using wildcards, codes, or regular expressions to find words or phrases that contain specific characters or combinations of characters. |
REPLACE | REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. |
REPT | Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. |
RIGHT | RIGHT returns the last character or characters in a text string, based on the number of characters you specify. |
ROMAN | Converts an arabic numeral to roman, as text. |
ROUND | The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula: |
ROUNDDOWN | Rounds a number down, toward zero. |
ROUNDUP | Rounds a number up, away from 0 (zero). |
ROW | Returns the row number of a reference. |
ROWS | Returns the number of rows in a reference or array. |
RRI | Returns an equivalent interest rate for the growth of an investment. |
RSQ | Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. For more information, see the PEARSON function. The r-squared value can be interpreted as the proportion of the variance in y attributable to the variance in x. |
SEARCH | The SEARCH and SEARCHB functions locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. For example, to find the position of the letter "n" in the word "printer", you can use the following function: |
SEC | Returns the secant of an angle. |
SECH | Returns the hyperbolic secant of an angle. |
SECOND | Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59. |
SERIESSUM | Many functions can be approximated by a power series expansion. |
SIGN | Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative. |
SIN | Returns the sine of the given angle. |
SINH | Returns the hyperbolic sine of a number. |
SKEW | Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean. Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values. Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values. |
SKEW.P | Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean. |
SKEWP | Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean. |
SLN | Returns the straight-line depreciation of an asset for one period. |
SLOPE | To get detailed information about a function, click its name in the first column. |
SMALL | Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set. |
SPLIT | Returns a zero-based, one-dimensional array containing a specified number of substrings. |
SQRT | Returns a positive square root. |
SQRTPI | Returns the square root of (number * pi). |
STANDARDIZE | Returns a normalized value from a distribution characterized by mean and standard_dev. |
STDEV.P | Calculates standard deviation based on the entire population given as arguments (ignores logical values and text). |
STDEV.S | Estimates standard deviation based on a sample (ignores logical values and text in the sample). |
STDEVA | Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). |
STDEVP | Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). |
STDEVPA | Calculates standard deviation based on the entire population given as arguments, including text and logical values. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). |
STDEVS | Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). |
STEYX | Returns the standard error of the predicted y-value for each x in the regression. The standard error is a measure of the amount of error in the prediction of y for an individual x. |
SUBSTITUTE | Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. |
SUBTOTAL | Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function. |
SUM | Summing (adding values) is an integral part of data analysis, whether you are subtotaling sales in the Northwest region or doing a running total of weekly receipts. Excel provides multiple techniques that you can use to sum data. |
SUMIF | You use the SUMIF function to sum the values in a range that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula: =SUMIF(B2:B25,">5") |
SUMIFS | This topic lists the more common causes of the #VALUE! error in the SUMIF and SUMIFS functions and how to resolve them. |
SUMPRODUCT | Multiplies corresponding components in the given arrays, and returns the sum of those products. |
SUMSQ | Returns the sum of the squares of the arguments. |
SUMX2MY2 | Returns the sum of the difference of squares of corresponding values in two arrays. |
SUMX2PY2 | Returns the sum of the sum of squares of corresponding values in two arrays. The sum of the sum of squares is a common term in many statistical calculations. |
SUMXMY2 | Returns the sum of squares of differences of corresponding values in two arrays. |
SWITCH | The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. |
SYD | Returns the sum-of-years' digits depreciation of an asset for a specified period. |
T | Returns the text referred to by value. |
T.DIST | Returns the Student's left-tailed t-distribution. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution. |
T.DIST.2T | Returns the two-tailed Student's t-distribution. |
T.DIST.RT | Returns the right-tailed Student's t-distribution. |
T.INV | This article describes the formula syntax and usage of the T.INV function in Microsoft Excel. |
T.INV.2T | Returns the two-tailed inverse of the Student's t-distribution. |
TAN | Returns the tangent of the given angle. |
TANH | Returns the hyperbolic tangent of a number. |
TBILLEQ | Returns the bond-equivalent yield for a Treasury bill. |
TBILLPRICE | Returns the price per $100 face value for a Treasury bill. |
TBILLYIELD | Returns the yield for a Treasury bill. |
TDIST | Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution. |
TDIST2T | |
TDISTRT | Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution. |
TEXT |
The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. Additional details on how dates and other strings can be parsed using format tokens is available here. These format tokens can be used in addition to the format tokens listed in the excel help here. |
TIME | Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date. |
TIMEVALUE | Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). |
TINV | Returns the two-tailed inverse of the Student's t-distribution. |
TINV2T | Returns the two-tailed inverse of the Student's t-distribution. |
TODAY | Returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations. If the cell format was General before the function was entered, Excel changes the cell format to Date. If you want to view the serial number, you must change the cell format to General or Number. |
TRANSPOSE | Sometimes you need to switch or rotate cells. You can do this by copying, pasting, and using the Transpose option. But doing that creates duplicated data. If you don't want that, you can type a formula instead using the TRANSPOSE function. For example, in the following picture the formula =TRANSPOSE(A1:B4) takes the cells A1 through B4 and arranges them horizontally. |
TREND | Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify. |
TRIM | Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing. |
TRIMMEAN | Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis. |
TRUE | Returns the logical value TRUE. You can use this function when you want to return the value TRUE based on a condition. For example: |
TRUNC | Truncates a number to an integer by removing the fractional part of the number. |
UNICHAR | Returns the Unicode character that is referenced by the given numeric value. |
UNICODE | Returns the number (code point) corresponding to the first character of the text. |
UNIQUE | Let's say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains: |
UPPER | Converts text to uppercase. |
VALUE | Converts a text string that represents a number to a number. |
VAR.P | Calculates variance based on the entire population (ignores logical values and text in the population). |
VAR.S | Estimates variance based on a sample (ignores logical values and text in the sample). |
VARA | Estimates variance based on a sample. |
VARP | Calculates variance based on the entire population. |
VARPA | Calculates variance based on the entire population. |
VARS | Estimates variance based on a sample. |
WEEKDAY | Returns a Variant (Integer) containing a whole number representing the day of the week. |
WEEKNUM | Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1. |
WEIBULL.DIST | This article describes the formula syntax and usage of the WEIBULL.DIST function in Microsoft Excel. |
WEIBULLDIST | This article describes the formula syntax and usage of the WEIBULL.DIST function in Microsoft Excel. |
WORKDAY | Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. |
XIRR | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function. |
XNPV | Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function. |
XOR | Returns a logical Exclusive Or of all arguments. |
YEAR | Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999. |
YEARFRAC | Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. |