Formula Syntax Guide

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.
CONCATENATE 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.
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.
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request