— No function —
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.
ACCRINTM — Returns the accrued interest for a security that pays interest at maturity.
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.
ADDRESS — You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers. For example, ADDRESS(2,3) returns $C$2. As another example, ADDRESS(77,300) returns $KN$77. You can use other functions, such as the ROW and COLUMN functions, to provide the row and column number arguments for the ADDRESS function.
AGGREGATE — The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.
AMORDEGRC — Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account. The function is similar to AMORLINC, except that a depreciation coefficient is applied in the calculation depending on the life of the assets.
AMORLINC — Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account.
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.
AREAS — Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell.
ARRAYTOTEXT — Accepts any array and returns the text representations of the array values.
ASC — For Double-byte character set (DBCS) languages, the function changes full-width (double-byte) characters to half-width (single-byte) characters.
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.
BAHTTEXT — Converts a number to Thai text and adds a suffix of "Baht." You can change the Baht format to a different style in the Excel desktop application by using Regional and Language Options (Windows Start menu, Control Panel).
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 — .DIST(x,alpha,beta,cumulative,[A],[B])
BETA.INV — .INV(probability,alpha,beta,[A],[B])
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 — The BETAINV functionr eturns the inverse of the cumulative beta probability density function for a specified beta distribution.
BIN2DEC — Converts a binary number to decimal.
BIN2HEX — Converts a binary number to hexadecimal.
BIN2OCT — Converts a binary number to octal.
BINOM.DIST — BINOM.DIST returns the individual term binomial distribution probability.
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.
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.
BYCOL — Applies a LAMBDA function to each column and returns an array of the results
BYROW — Applies a LAMBDA function to each row and returns an array of the results
CALL — value returned by a previously executed REGISTER or REGISTER.ID function.
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.
CEILING.MATH — The CEILING.MATH function rounds a number up to the nearest integer or, optionally, to the nearest multiple of significance.
CEILING.PRECISE — Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, zero is returned.
CELL — The CELL function returns information about the formatting, location, or contents of a cell. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can use the following formula:
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.
CHIDIST — Returns the right-tailed probability of the chi-squared distribution. The χ2 distribution is associated with a χ2 test. Use the χ2 test to compare observed and expected values. For example, a genetic experiment might hypothesize that the next generation of plants will exhibit a certain set of colors. By comparing the observed results with the expected ones, you can decide whether your original hypothesis is valid. This function is provided for backward compatibility; consider using the CHISQ.DIST.RT function instead.
CHIINV — Returns the inverse of the right-tailed probability of the chi-squared distribution. If probability = CHIDIST(x,...), then CHIINV(probability,...) = x. Use this function to compare observed results with expected ones in order to decide whether your original hypothesis is valid.
CHISQ.DIST — Returns the chi-squared distribution. The chi-squared 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.
CHISQ.DIST.RT — Returns the right-tailed probability of the chi-squared distribution. The χ2 distribution is associated with a χ2 test. Use the χ2 test to compare observed and expected values. For example, a genetic experiment might hypothesize that the next generation of plants will exhibit a certain set of colors. By comparing the observed results with the expected ones, you can decide whether your original hypothesis is valid.
CHISQ.INV — Returns the inverse of the left-tailed probability of the chi-squared distribution. The chi-squared 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.
CHISQ.INV.RT — Returns the inverse of the right-tailed probability of the chi-squared distribution. If probability = CHISQ.DIST.RT(x,...), then CHISQ.INV.RT(probability,...) = x. Use this function to compare observed results with expected ones in order to decide whether your original hypothesis is valid.
CHISQ.TEST — The CHISQ.TEXT function returns the test for independence. It returns the value from the chi-squared (χ2) distribution for the statistic and the appropriate degrees of freedom. You can use χ2 tests to determine whether hypothesized results are verified by an experiment.
CHITEST — Returns the value from the chi-squared (χ2) distribution for the statistic and the appropriate degrees of freedom. You can use χ2 tests to determine whether hypothesized results are verified by an experiment.
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.
CHOOSECOLS — CHOOSECOLS function
CHOOSEROWS — CHOOSEROWS function
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 — Syntax: COLUMN([reference])
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.
CONCAT — The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments.
CONCATENATE — Learn how to concatenate text strings in Excel using the CONCATENATE function. Our step-by-step guide makes it easy to join two or more text strings into one.
CONFIDENCE — Returns the confidence interval for a population mean, using a normal distribution. 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. For example, if x is the sample mean of delivery times for products ordered through the mail, x ± CONFIDENCE 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. For more information about the new functions, see CONFIDENCE.NORM function and CONFIDENCE.T function.
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 — Returns the confidence interval for a population mean, using a Student's t distribution.
CONVERT — units for the result. CONVERT accepts the following text values (in quotation marks) for from_unit and to_unit.
COPILOT — COPILOT Function
CORREL — The CORREL function returns the correlation coefficient of two 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 — How to use the COUNT function in Excel to count the number of cells that contain numbers, and to count numbers within a list of arguments.
COUNTA — The COUNTA function counts the number of cells that are not empty in a range.
COUNTBLANK — Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.
COUNTIF — Conditional count (a single condition)
COUNTIFS — How to use the COUNTIFS function in Excel to apply criteria to cells, across multiple ranges, and count the number of times all criteria are met.
COUPDAYBS — The COUPDAYBS function returns the number of days from the beginning of a coupon period until its settlement date.
COUPDAYS — Returns the number of days in the coupon period that contains the settlement date.
COUPDAYSNC — Returns the number of days from the settlement date to the next coupon date.
COUPNCD — Returns a number that represents the next coupon date after the settlement date.
COUPNUM — Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.
COUPPCD — Returns a number that represents the previous coupon date before the settlement date.
COVAR — Returns covariance, the average of the products of deviations for each data point pair in two data sets. Use covariance to determine the relationship between two data sets. For example, you can examine whether greater income accompanies greater levels of education.
COVARIANCE.P — Returns population covariance, the average of the products of deviations for each data point pair in two data sets. Use covariance to determine the relationship between two data sets.
COVARIANCE.S — Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets.
CRITBINOM — CRITBINOM function
CSC — Returns the cosecant of an angle specified in radians.
CSCH — Return the hyperbolic cosecant of an angle specified in radians.
CUBEKPIMEMBER — Returns a key performance indicator (KPI) property and displays the KPI name in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, that is used to monitor an organization's performance.
CUBEMEMBER — Returns a member or tuple from the cube. Use to validate that the member or tuple exists in the cube.
CUBEMEMBERPROPERTY — The CUBEMEMBERPROPERTY function returns the value of a member property from a cube. Use it to validate that a member name exists within the cube, and to return the specified property for this member.
CUBERANKEDMEMBER — Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students.
CUBESET — Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Excel.
CUBESETCOUNT — Returns the number of items in a set.
CUBEVALUE — Returns an aggregated value from the cube.
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.
DATEDIF — Calculates the number of days, months, or years between two dates.
DATEVALUE — The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may cause the results of a DATEVALUE function to vary from this example The DATEVALUE function is helpful in cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations. To view a date serial number as a date, you must apply a date format to the cell. Find links to more information about displaying numbers as dates in the See Also section.
DAVERAGE — Averages the values in a field (column) of records in a list or database that match conditions you specify.
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.
DBCS — The function described in this Help topic converts half-width (single-byte) letters within a character string to full-width (double-byte) characters. The name of the function (and the characters that it converts) depends upon your language settings. For Japanese, this function changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters.
DCOUNT — Counts the cells that contain numbers in a field (column) of records in a list or database that match conditions that you specify. The field argument is optional. If field is omitted, DCOUNT counts all records in the database that match the criteria.
DCOUNTA — Counts the nonblank cells in a field (column) of records in a list or database that match conditions that you specify. The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria.
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 — Converts radians into degrees.
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.
DETECTLANGUAGE — With the DETECTLANGUAGE() function, you can easily identify the language used in a specified text or range. This function uses Microsoft Translation Services.
DEVSQ — Returns the sum of squares of deviations of data points from their sample mean.
DGET — Extracts a single value from a column of a list or database that matches conditions that you specify.
DISC — Returns the discount rate for a security.
DMAX — Returns the largest number in a field (column) of records in a list or database that matches conditions you that specify.
DMIN — Returns the smallest number in a field (column) of records in a list or database that matches conditions that you specify.
DOLLAR — The DOLLAR function, one of the TEXT functions, converts a number to text using currency format, with the decimals rounded to the number of places you specify. DOLLAR uses the $#,##0.00_);($#,##0.00) number format, although the currency symbol that is applied depends on your local 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. The fraction part of the value is divided by an integer that you specify. For example, if you want your price to be expressed to a precision of 1/16 of a dollar, you divide the fraction part by 16. In this case, 1.02 represents $1.125 ($1 + 2/16 = $1.125).
DOLLARFR — Use DOLLARFR to convert decimal numbers to fractional dollar numbers, such as securities prices.
DPRODUCT — Multiplies the values in a field (column) of records in a list or database that match conditions that you specify.
DROP — DROP function
DSTDEV — Estimates the standard deviation of a population based on a sample by using the numbers in a field (column) of records in a list or database that match conditions that you specify.
DSTDEVP — Calculates the standard deviation of a population based on the entire population by using the numbers in a field (column) of records in a list or database that match conditions that you specify.
DSUM — In a list or database, DSUM provides the sum of the numbers in fields (columns) of records that match your specified conditions.
DURATION — Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of cash flows, and is used as a measure of a bond price's response to changes in yield.
DVAR — Estimates the variance of a population based on a sample by using the numbers in a field (column) of records in a list or database that match conditions that you specify.
DVARP — Calculates the variance of a population based on the entire population by using the numbers in a field (column) of records in a list or database that match conditions that you specify.
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.
ENCODEURL — The ENCODEURL function returns a URL-encoded string, replacing certain non-alphanumeric characters with the percentage symbol (%) and a hexadecimal number.
EOMONTH — Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
ERF — Returns the error function integrated between lower_limit and upper_limit.
ERF.PRECISE — Returns the error function.
ERFC — Returns the complementary ERF function integrated between x and infinity.
ERFC.PRECISE — Returns the complementary ERF function integrated between x and infinity.
ERROR.TYPE — Returns a number corresponding to one of the error values in Microsoft Excel or returns the #N/A error if no error exists. You can use ERROR.TYPE in an IF function to test for an error value and return a text string, such as a message, instead of the error value.
EUROCONVERT — Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation). The currencies available for conversion are those of European Union (EU) members that have adopted the euro. The function uses fixed conversion rates that are established by the EU. If this function is not available, and returns the #NAME? error, install and load the Euro Currency Tools Add-in. Install and load the Euro Currency Tools Add-in add-in
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.
EXPAND — EXPAND Function
EXPON.DIST — Returns the exponential distribution.
EXPONDIST — Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPONDIST to determine the probability that the process takes at most 1 minute.
F.DIST — Returns the F probability distribution. 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 — rticle describes the formula syntax and usage of the F.DIST.RT function in Microsoft Excel. 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.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 — Returns the inverse of the (right-tailed) F probability distribution. If p = F.DIST.RT(x,...), then F.INV.RT(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.TEST — Use this function to determine whether two samples have different variances.
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 — The FDIST function 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.
FIELDVALUE — The FIELDVALUE function can be used inside other formulas to extract field data from linked records. This article explains how.
FILTER — How to use the FILTER function in Excel to filter a range of data based on criteria you define.
FILTERXML — The FILTERXML function returns specific data from XML content by using the specified xpath.
FIND — Finds the position of a text inside another text (case-sensitive)
FINDB — Returns the position of a text in another text in bytes (DBCS, for double-byte languages) — like FIND but byte-based
FINV — Returns the inverse of the (right-tailed) F probability distribution. If p = FDIST(x,...), then FINV(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.
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.
FLOOR — The FLOOR function in Excel rounds a specified number down to the nearest specified multiple of significance. Negative numbers are rounded down (further negative) to nearest whole multiple below zero.
FLOOR.MATH — Round a number down to the nearest integer or to the nearest multiple of significance.
FLOOR.PRECISE — Returns a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down. However, if the number or the significance is zero, zero is returned.
FORECAST — Predicts a future value based on a linear trend (the legacy FORECAST.LINEAR)
FORECAST.ETS — Calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline. You can use this function to predict future sales, inventory requirements, or consumer trends. This function requires the timeline to be organized with a constant step between the different points.
FORECAST.ETS.CONFINT — Returns a confidence interval for the forecast value at the specified target date.
FORECAST.ETS.SEASONALITY — Returns the length of the repetitive pattern Excel detects for the specified time series.
FORECAST.ETS.STAT — Returns a statistical value as a result of time series forecasting.
FORECAST.LINEAR — Predicts a future value based on the linear trend of past data
FORMULATEXT — Returns a formula as a string.
FREQUENCY — The FREQUENCY function 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.
FTEST — Returns the result of an F-test. An F-test returns the two-tailed probability that the variances in array1 and array2 are not significantly different. Use this function to determine whether two samples have different variances.
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 — 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.
GAMMA.INV — Returns the inverse of the gamma cumulative distribution. If p = GAMMA.DIST(x,...), then GAMMA.INV(p,...) = x. You can use this function to study a variable whose distribution may be skewed.
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. This function is provided for backward compatibility; consider using the GAMMA.DIST function instead.
GAMMAINV — Returns the inverse of the gamma cumulative distribution. If p = GAMMADIST(x,...), then GAMMAINV(p,...) = x. You can use this function to study a variable whose distribution may be skewed.
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.
GETPIVOTDATA — The GETPIVOTDATA function returns visible data from a PivotTable.
GROUPBY — The GROUPBY function in Excel allows you to create a summary of your data via a formula.
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.
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.
HIDDEN — Hidden function
HLOOKUP — Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The H in HLOOKUP stands for "Horizontal."
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.).
HSTACK — The arrays to append.
HYPERLINK — The HYPERLINK function creates a shortcut that jumps to another location in the current workbook, or opens a document stored on a network server, an intranet, or the Internet. When you click a cell that contains a HYPERLINK function, Excel jumps to the location listed, or opens the document you specified.
HYPGEOM.DIST — Returns the probability of a given number of sample successes, given the sample size, population successes, and population size.
HYPGEOMDIST — 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 — How to use the IF function in Excel to make logical comparisons between data values.
IFERROR — How to use the IFERROR function in Excel to trap and handle errors in a formula.
IFNA — The IFNA function returns the value you specify if a formula returns the #N/A error value; otherwise it returns the result of the formula.
IFS — The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS will allow you to include up to 127 pairs of conditions (the test if something is True and the result if something is true). IFS can take the place of multiple nested IF statements.
IMABS — Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format.
IMAGE — The custom width of the image in pixels.
IMAGINARY — Returns the imaginary coefficient of a complex number in x + yi or x + yj text format.
IMARGUMENT — Returns the argument (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.
IMPORTCSV — IMPORTCSV function
IMPORTTEXT — IMPORTTEXT function
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 — The IMSINH function 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. If you just want to subtract two numbers that are not complex, see Subtract numbers.
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.
INDEX — Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array form if the first argument to INDEX is an array constant. Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of non-adjacent selections, you can pick the selection to look in.
INDIRECT — Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
INFO — Returns information about the current operating environment.
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.
INTRATE — Returns the interest rate for a fully invested security.
IPMT — Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
IRR — A number that you guess is close to the result of IRR.
ISBLANK — Checks whether a cell is empty or not?
ISERR — Whether a formula is an error or not
ISERROR — Checks whether a value is an error or not?
ISEVEN — Returns TRUE if number is even, or FALSE if number is odd.
ISFORMULA — Checks whether there is a reference to a cell that contains a formula, and returns TRUE or FALSE.
ISLOGICAL — Checks whether a value is logical (TRUE or FALSE) or not?
ISNA — Whether it is the #N/A error or not
ISNONTEXT — Checks that a value is not text
ISNUMBER — Checks whether a value is numeric or not?
ISO.CEILING — ISO.CEILING returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, zero is returned.
ISODD — Returns TRUE if number is odd, or FALSE if number is even.
ISOMITTED — The value you want to test, such as a LAMBDA parameter.
ISOWEEKNUM — Returns number of the ISO week number of the year for a given date.
ISPMT — Calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments.
ISREF — Checks whether a value is a valid reference to a cell or range or not
ISTEXT — Checks whether an input value is text or not?
JIS — The function described in this Help topic converts half-width (single-byte) letters within a character string to full-width (double-byte) characters. The name of the function (and the characters that it converts) depends upon your language settings. For Japanese, this function changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters.
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.
LAMBDA — LAMBDA function
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.
LEFTB — Returns a specified number of characters from the beginning of a text in bytes
LEN — LEN returns the number of characters in a text string.
LENB — Returns the length of a text in bytes — each double-byte character counts as 2
LET — LET function
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. The equation for the line is: y = mx + b –or– y = m1x1 + m2x2 + ... + b if there are multiple ranges of x-values, where the dependent y-values are a function of the independent x-values. The m-values are coefficients corresponding to each x-value, and b is a constant value. Note that y, x, and m can be vectors. The array that the LINEST function returns is {mn,mn-1,...,m1,b}. LINEST can also return additional regression statistics.
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 — Returns the base-10 logarithm of a number.
LOGEST — The equation for the curve is: y = b*m^x or y = (b*(m1^x1)*(m2^x2)*_) if there are multiple x-values, where the dependent y-value is a function of the independent x-values. The m-values are bases corresponding to each exponent x-value, and b is a constant value. Note that y, x, and m can be vectors. The array that LOGEST returns is {mn,mn-1,...,m1,b}.
LOGINV — 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. Use the lognormal distribution to analyze logarithmically transformed data. For more information about the new function, see LOGNORM.INV function.
LOGNORM.DIST — Returns the 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.
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. Use the lognormal distribution to analyze logarithmically transformed data.
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. For more information about the new function, see LOGNORM.DIST function.
LOOKUP — How to use the LOOKUP function in Excel to search in a single row or column and find a value from the same position in a second row or column.
LOWER — Converts all uppercase letters in a text string to lowercase.
MAKEARRAY — Returns a calculated array of a specified row and column size, by applying a LAMBDA function
MAP — Returns an array formed by 'mapping' each value in the array(s) to a new value by applying a lambda to create a new value
MATCH — How to use the MATCH function in Excel to search for a specified item in a range of cells, returning the relative position of that item in the range.
MAX — Returns the largest value in a set of values.
MAXA — Returns the largest value in a list of arguments. MAXA is similar to MINA. For more information, see the examples for the MINA function.
MAXIFS — The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria: numbers, dates, text, etc. This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.
MDETERM — Returns the matrix determinant of an array.
MDURATION — Returns the modified Macauley duration for a security with an assumed par value of $100.
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.
MIDB — Returns a portion of text starting at a specified position based on bytes
MIN — Returns the smallest number in a set of values.
MINA — Returns the smallest value in the list of arguments.
MINIFS — The MINIFS function returns the minimum value among cells specified by a given set of conditions or criteria.
MINUTE — Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.
MINVERSE — The MINVERSE function returns the inverse matrix for a matrix stored in an array. Array can be given as a cell range, such as A1:C3; as an array constant, such as {1,2,3;4,5,6;7,8,9}; or as a name for either of these. Inverse matrices, like determinants, are generally used for solving systems of mathematical equations involving several variables. The product of a matrix and its inverse is the identity matrix — the square array in which the diagonal values equal 1, and all other values equal 0.
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.
MMULT — The MMULT function returns the matrix product or multiplication of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2. The way you enter the formula depends on which version of Office 365 you are using.
MOD — Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
MODE — The MODE function returns the most frequently occurring, or repetitive, value in an array or range of data.
MODE.MULT — Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data.
MODE.SNGL — Returns the most frequently occurring, or repetitive, value in an array or range of data.
MONTH — Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).
MROUND — MROUND returns a number rounded to the desired multiple. MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple. The Number and Multiple arguments must have the same sign. If not, a #NUM error is returned.
MULTINOMIAL — Returns the ratio of the factorial of a sum of values to the product of factorials.
MUNIT — The MUNIT function returns the unit matrix for the specified dimension.
N — Returns a value converted to a number.
NA — Returns the error value #N/A. #N/A is the error value that means "no value is available." Use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations. (When a formula refers to a cell containing #N/A, the formula returns the #N/A error value.)
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. 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. For example, you need to find 10 people with excellent reflexes, and you know the probability that a candidate has these qualifications is 0.3. NEGBINOM.DIST calculates the probability that you will interview a certain number of unqualified candidates before finding all 10 qualified candidates.
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. For example, you need to find 10 people with excellent reflexes, and you know the probability that a candidate has these qualifications is 0.3. NEGBINOMDIST calculates the probability that you will interview a certain number of unqualified candidates before finding all 10 qualified candidates. For more information about the new function, see NEGBINOM.DIST function.
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.
NETWORKDAYS.INTL — string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.
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. If mean or standard_dev is nonnumeric, NORM.DIST returns the #VALUE! error value.
NORM.INV — Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
NORM.S.DIST — NORM.S.DIST returns the standard normal distribution (has a mean of zero and a standard deviation of one). Use this function in place of a table of standard normal curve areas.
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 — This function 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 — Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. This function is provided for backward compatibility; consider using the NORM.INV function instead.
NORMSDIST — Returns the standard normal cumulative distribution function.
NORMSINV — Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one. This function is provided for backward compatibility; consider using the NORM.S.INV function instead.
NOT — Use the NOT function, one of the logical functions, when you want to make sure one value is not equal to another. One common use for the NOT function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the NOT function as the logical_test argument of the IF function, you can test many different conditions instead of just one.
NOW — Returns the serial number of the current date and time. If the cell format was General before the function was entered, Excel changes the cell format so that it matches the date and time format of your regional settings. You can change the date and time format for the cell by using the commands in the Number group of the Home tab on the Ribbon. The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
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).
NUMBERVALUE — Converts text to a number, in a locale-independent way.
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.
ODDFPRICE — Returns the price per $100 face value of a security having an odd (short or long) first period.
ODDFYIELD — Returns the yield of a security that has an odd (short or long) first period.
ODDLPRICE — Returns the price per $100 face value of a security having an odd (short or long) last coupon period.
ODDLYIELD — Returns the yield of a security that has an odd (short or long) last period.
OFFSET — Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
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.
PERCENTILE — Returns the k-th percentile of values in a range. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile. If k is non-numeric, PERCENTILE returns the #VALUE! error value. This function has been replaced with one or more new functions that may provide improved accuracy. Although this function is still available for backward compatibility, this function may not be available in future versions of Excel.
PERCENTILE.EXC — The PERCENTILE.EXC function returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.
PERCENTILE.INC — PERCENTILE.INC returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. You can use this function to establish a threshold of acceptance.
PERCENTOF — PERCENTOF function
PERCENTRANK — Returns the rank of a value in a data set as a percentage of the data set. This function can be used to evaluate the relative standing of a value within a data set. For example, you can use PERCENTRANK to evaluate the standing of an aptitude test score among all scores for the test. This function has been replaced with one or more new functions that may provide improved accuracy. Although this function is still available for backward compatibility, this function may not be available in future versions of Excel.
PERCENTRANK.EXC — Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set.
PERCENTRANK.INC — Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set.
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.
PHONETIC — Extracts the phonetic (furigana) characters from a text string.
PI — Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
PIVOTBY — PIVOTBY function
PMT — How to use the PMT function in Excel to calculate monthly loan payments based on constant payments and a constant interest rate.
POISSON — 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. This function is provided for backward compatibility; consider using the POISSON.DIST function instead.
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.
POWER — (number, 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.
PRICE — Returns the price per $100 face value of a security that pays periodic interest.
PRICEDISC — Returns the price per $100 face value of a discounted security.
PRICEMAT — Returns the price per $100 face value of a security that pays interest at maturity.
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. The PRODUCT function is useful when you need to multiply many cells together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3.
PROPER — Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
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.
PY — PY function
QUARTILE — 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. This function has been replaced with one or more new functions that may provide improved accuracy. Although this function is still available for backward compatibility, you should consider using the new functions from now on, it may not be available in future versions of Excel.
QUARTILE.EXC — QUARTILE.EXC returns the quartile of the data set, based on percentile values from 0..1, exclusive.
QUARTILE.INC — QUARTLE.INC returns the quartile of a data set, based on percentile values from 0..1, inclusive. Quartiles often are used in sales and survey data to divide populations into groups.
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 — 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.
RANDARRAY — Learn about the RANDARRAY function for creating arrays of random numbers between 0 and 1. RANDARRAY is in a class of functions called dynamic arrays.
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 — Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.) For more information about the new functions, see RANK.AVG function and RANK.EQ function.
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. If you were to sort the list, the rank of the number would be its position.
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.
RECEIVED — Returns the amount received at maturity for a fully invested security.
REDUCE — Reduces an array to an accumulated value by applying a LAMBDA function to each value and returning the total value in the accumulator
REGEXEXTRACT — This article shows you how REGEXEXTRACT function allows you to extract text from a string based on a supplied regular expression.
REGEXREPLACE — REGEXREPLACE Function
REGEXTEST — REGEXTEST Function
REGISTER.ID — Returns the register ID of the specified dynamic link library (DLL) or code resource that has been previously registered. If the DLL or code resource has not been registered, this function registers the DLL or code resource and then returns the register ID. REGISTER.ID can be used on worksheets (unlike REGISTER), but you cannot specify a function name and argument names with REGISTER.ID. For more information about DLLs and code resources and data types, see Using the CALL and REGISTER functions.
REPLACE — REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
REPLACEB — Replaces part of text by bytes (DBCS)
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.
RIGHTB — Returns a specified number of characters from the end of text based on bytes
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: =ROUND(A1, 2) The result of this function is 23.78.
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.
RTD — Retrieves real-time data from a program that supports COM automation.
SCAN — Scans an array by applying a LAMBDA function to each value and returns an array that has each intermediate value
SEARCH — The SEARCH function 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: =SEARCH("n","printer") This function returns 4 because "n" is the fourth character in the word "printer." You can also search for words within other words. For example, the function =SEARCH("base","database") returns 5, because the word "base" begins at the fifth character of the word "database". You can use the SEARCH to determine the location of a character or text string within another text string, and then use the MID to return the text, or use the REPLACE to change the text. These functions are demonstrated in Example 1 in this article.
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.
SEQUENCE — Learn more about the SEQUENCE function, which allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4. SEQUENCE is in a class of functions called dynamic arrays.
SERIESSUM — Many functions can be approximated by a power series expansion. Returns the sum of a power series based on the formula:
SHEET — The SHEET function returns the sheet number of the specified sheet or other reference.
SHEETS — Returns the number of sheets in a reference.
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 — 1, number 2,… Number 1 is , subsequent numbers are Number 1, number 2,… are 1 to 254 numbers or names, arrays, or reference that contain numbers for which you want the population skewness.
SLN — Returns the straight-line depreciation of an asset for one period.
SLOPE — Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.
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.
SORT — Learn about the SORT function, which sorts the contents of a range or array. SORT is in a class of functions called dynamic arrays.
SORTBY — Learn about the SORTBY function, which sorts the contents of a range or array based on the values in a corresponding range or array. SORTBY is in a class of functions called dynamic arrays.
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 — 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).
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 — The STDEVP function 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).
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.
STOCKHISTORY — STOCKHISTORY function
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 — How to use the SUM function in Excel to add individual values, cell references, ranges, or a mix of all three.
SUMIF — How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.
SUMIFS — How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.
SUMPRODUCT — How to use the SUMPRODUCT function in Excel, one of Excel’s math and trig functions, using multiplication, addition, subtraction, and/or division to return the sum of the products of corresponding ranges or arrays.
SUMSQ — Returns the sum of the squares of the arguments.
SUMX2MY2 — This Excel function returns the sum of the difference of the 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 — The SUMXMY2 function 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. If there are no matching values, and no default argument is supplied, the SWITCH function returns the #N/A! error.
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. The Student's 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.RT — Returns the right-tailed Student's 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.INV — Returns the left-tailed inverse of the Student's t-distribution.
T.INV.2T — Returns the two-tailed inverse of the Student's t-distribution.
T.TEST — Returns the probability associated with a Student's t-Test. Use T.TEST to determine whether two samples are likely to have come from the same two underlying populations that have the same mean. Results from the test shows if the difference is statistically significant or from chance.
TAKE — TAKE function
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. For more information about the new functions, see T.DIST.2T function and T.DIST.RT function.
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.
TEXTAFTER — Value returned if no match is found. By default, #N/A is returned.
TEXTBEFORE — Value returned if no match is found. By default, #N/A is returned.
TEXTJOIN — The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT — TEXTSPLIT function
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. The decimal number returned by TIME 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.).
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. This function is provided for backward compatibility; consider using the T.INV.2T function instead.
TOCOL — Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column.
TODAY — The TODAY function 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. The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals. For example, if you know that someone was born in 1963, you might use the following formula to find that person's age as of this year's birthday: = YEAR( TODAY())-1963 This formula uses the TODAY function as an argument for the YEAR function to obtain the current year, and then subtracts 1963, returning the person's age.
TOROW — Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column.
TRANSLATE — The TRANSLATE() function allows you to translate text from one language to another in Microsoft Excel by using Microsoft Translation Services.
TRANSPOSE — How to use the TRANSPOSE function in Excel to switch or rotate cells.
TREND — The TREND function returns values along a linear trend. It fits a straight line (using the method of least squares) to the array's known_y's and known_x's. TREND 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.
TRIMRANGE — The TRIMRANGE function excludes all empty rows and/or columns from the outer edges of a range or array.
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: =IF(A1=1,TRUE()) You can also enter the value TRUE directly into cells and formulas without using this function. For example: =IF(A1=1,TRUE) If the condition is met, Excel returns TRUE in both examples. If the condition is not met, Excel returns FALSE in both examples.
TRUNC — The TRUNC functions truncates a number to an integer by removing the fractional part of the number.
TTEST — Returns the probability associated with a Student's t-Test. Use T.TEST to determine whether two samples are likely to have come from the same two underlying populations that have the same mean. Results from the test shows if the difference is statistically significant or from chance. This function is provided for backward compatibility; consider using the T.TEST function instead.
TYPE — Returns the type of value. Use TYPE when the behavior of another function depends on the type of value in a particular cell.
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 — Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.
UPPER — Converts text to uppercase.
VALUE — Converts a text string that represents a number to a number.
VALUETOTEXT — VALUETOTEXT function
VAR — Estimates variance based on a sample. VAR assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the variance by using VARP. Arguments can either be numbers or names, arrays, or references that contain numbers. This function has been replaced with one or more new functions. Although this function is still available for backward compatibility, this function may not be available in future versions of Excel.
VAR.P — Calculates variance based on the entire population (ignores logical values and text in the population).
VAR.S — The VAR.S function 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.
VDB — Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance.
VLOOKUP — Learn how to use function VLOOKUP in Excel to find data in a table or range by row. Our step-by-step guide makes vlookup in excel easy and efficient.
VSTACK — The arrays to append.
WEBSERVICE — The WEBSERVICE function returns data from a web service on the Internet or Intranet.
WEEKDAY — Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
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. There are two systems used for this function:
WEIBULL — Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure. This function is provided for backward compatibility; consider using the WEIBULL.DIST function instead.
WEIBULL.DIST — Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.
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.
WORKDAY.INTL — string values are seven characters long. Each character in the string represents a day of the week, starting with Monday. The character 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Also, 1111111 is an invalid string.
WRAPCOLS — WRAPCOLS function
WRAPROWS — WRAPROWS function
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.
XLOOKUP — Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.
XMATCH — The XMATCH function searches for a specified item in an array or range of cells, and then returns the relative position of the first match.
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 — The XOR function 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 — YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term.
YIELD — Returns the yield on a security that pays periodic interest. Use YIELD to calculate bond yield.
YIELDDISC — Returns the annual yield for a discounted security.
YIELDMAT — Returns the annual yield of a security that pays interest at maturity.
Z.TEST — This article describes the formula syntax and usage of the Z.TEST, which returns the one-tailed P-value of a z-test.
ZTEST — Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, μ0, ZTEST returns the probability that the sample mean would be greater than the average of observations in the data set (array) — that is, the observed sample mean.
▶ Run
Clear
Share ↗
Fill sample
Presets:
DURATION — Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of cash flows, and is used as a measure of a bond price's response to changes in yield.
=DURATION(settlement, maturity, coupon, yld, frequency, [basis])