This appendix lists all the worksheet functions available in Microsoft Office Excel 2007 in alphabetical order. We include a description, the function syntax, and a description of each argument. Arguments that appear in bold text are required; arguments that do not appear in bold text are optional.
You must type all function arguments in the order shown, and you should not add any spaces between or within arguments. While Excel now generally accepts spaces in functions for readability, when you use spaces within a text argument or a reference argument you must still enclose the entire argument in quotation marks.
Function | Description |
---|---|
ABS | Returns the absolute value of a number and takes the form =ABS(number). If a number is negative, this function simply removes the sign, making it a positive number. |
ACCRINT | Returns the interest accrued by a security that pays interest on a periodic basis and takes the form =ACCRINT(issue, first interest, settlement, rate, par, frequency, basis), where issue is the issue date of the security; first interest is the date of the initial interest payment; settlement is the day you pay for the security; rate is the interest rate of the security at the issue date; par is the par value of the security; frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly); and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
ACCRINTM | Returns the interest accrued by a maturity security that pays interest at maturity and takes the form =ACCRINTM(issue, maturity, rate, par, basis), where issue is the issue date of the security; maturity is the security's maturity date; rate is the interest rate of the security at the issue date; par is the par value of the security; and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
ACOS | Returns the arccosine (inverse cosine) of a number in radians and takes the form =ACOS(number), in which number is the cosine of an angle. |
ACOSH | Returns the inverse hyperbolic cosine of a number and takes the form =ACOSH(number), in which number must be >=1. |
ADDRESS | Builds references from numbers and takes the form =ADDRESS(row_num, column_num, abs_num, a1, sheet_text), in which row_num and column_num designate the row and column values for the address; abs_num determines whether the resulting address uses absolute references (1), mixed (2 means absolute row, relative column, and 3 means relative row, absolute column), or relative (4); a1 is a logical value (if TRUE, the resulting address is in A1 format; if FALSE, the resulting address is in R1C1 format); and sheet_text specifies the name of the sheet. See "Using Selected Lookup and Reference Functions" on page 512. |
AMORDEGRC | Returns the depreciation for each accounting period (French accounting system only), including any partial period, and takes the form =AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, basis), where cost is the cost of the asset; date_purchased is the date of the purchase; first_period is the date of the end of the first period; salvage is the salvage value at the end of the life of the asset; period is the period for which you want to calculate depreciation; rate is the rate of depreciation, and basis is the year basis to be used (0 = 360 days, 1 = actual, 3 = 365 days, 4 = European 360 days). This function is similar to AMORLINC except a depreciation coefficient is applied, depending on the asset life (1.5 if 3–4 years, 2 if 5–6 years, 2.5 if greater than 6 years). |
AMORLINC | Returns the depreciation for each accounting period (French accounting system only), including any partial period. See AMORDEGRC for syntax and arguments. |
AND | Helps develop compound conditional test formulas in conjunction with the simple logical operators: =, >, <, >=, <=, and <>. The AND function can have as many as 30 arguments and takes the form =AND(logical1, logical2, . . .), where each logical can be conditional tests, arrays, or references to cells that contain logical values. See "Using Selected Logical Functions" on page 508. |
AREAS | Returns the number of areas in a reference (a cell or block of cells) and takes the form =AREAS(reference), where reference can be a cell reference, a range reference, or several range references enclosed in parentheses. See "Using Selected Lookup and Reference Functions" on page 512. |
ASC | Changes text in double-byte character set languages to single-byte characters and takes the form =ASC(text), where text is either text or a reference to a cell containing text. Has no effect on single-byte characters. |
ASIN | Returns the arcsine of a number in radians and takes the form =ASIN(number), where number is the sine of the angle you want and must be from –1 to 1. |
ASINH | ASINH Returns the inverse hyperbolic sine of a number and takes the form =ASINH(number). |
ATAN | Returns the arctangent of a number and takes the form =ATAN(number), where number is the tangent of an angle. |
ATAN2 | Returns the arctangent of the specified x- and y-coordinates, in radians, and takes the form =ATAN2(x_num, y_num), where x_num is the x-coordinate of the point, and y_num is the y-coordinate of the point. A positive result represents a counterclockwise angle from the x-axis; a negative result represents a clockwise angle. |
ATANH | Returns the inverse hyperbolic tangent of a number and takes the form =ATANH(number), where number must be between (not including) -1 and 1. |
AVEDEV | Returns the average of the absolute deviations of data points from their mean; takes the form =AVEDEV(number1, number2, . . .), where the numbers can be names, arrays, or references that resolve to numbers; and accepts up to 30 arguments. |
AVERAGE | Returns the arithmetic mean of the specified numbers and takes the form =AVERAGE(number1, number2, . . .), where the numbers can be names, arrays, or references that resolve to numbers. Cells containing text, logical values, or empty cells are ignored, but cells containing a zero value are included. See "Using Built-In Statistical Functions" on page 553. |
AVERAGEA | Acts like AVERAGE except text and logical values are included in the calculation. See "Using Built-In Statistical Functions" on page 553. |
AVERAGEIF | Finds the arithmetic mean cells in the specified range that meet a given criteria and takes the form =AVERAGEIF(range, criteria, average_range) where range is the cells to evaluate; criteria is an expression, cell reference, or number used to define which cells to average; and average_range is the actual cells to average. Excel uses the top-left cell of average_range as the beginning and uses the bottom-left cell of either average_range or range (whichever is larger) to determine the size of the cell range to be used. If average_range is omitted, range is used. |
AVERAGEIFS | Acts like AVERAGEIF but accepts multiple criteria; takes the form =AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, . . .). |
BAHTTEXT | Converts a number to Thai text and adds the suffix Baht, using the form =BAHTTEXT(number), where number can be a reference to a cell containing a number or a formula that resolves to a number. |
BESSELI | Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for imaginary arguments, and takes the form =BESSELI(x, n), where x is the value at which to evaluate the function, and n is the order of the Bessel function. |
BESSELJ | Returns the Bessel function, using the form =BESSELJ(x, n), where x is the value at which to evaluate the function, and n is the order of the Bessel function. |
BESSELK | Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for imaginary arguments, and takes the form =BESSELK(x, n), where x is the value at which to evaluate the function, and n is the order of the Bessel function. |
BESSELY | Returns the Bessel function (also called the Weber or Neumann function) and takes the form =BESSELY(x, n), where x is the value at which to evaluate the function, and n is the order of the function. |
BETADIST | Returns the cumulative beta probability density function and takes the form =BETADIST(x, alpha, beta, A, B), where x is the value between A and B at which to evaluate the function, alpha is a parameter to the distribution, beta is a parameter to the distribution, A is an optional lower bound to the interval of x, and B is an optional upper bound to the interval of x. |
BETAINV | Returns the inverse of the cumulative beta probability density function and takes the form =BETAINV(probability, alpha, beta, A, B), where probability is a probability associated with the beta distribution. For additional argument descriptions, see BETADIST. |
BIN2DEC | Converts a binary number to decimal and takes the form =BIN2DEC(number), where number is the binary integer you want to convert. |
BIN2HEX | Converts a binary number to hexadecimal and takes the form =BIN2HEX(number, places), where number is the binary integer you want to convert, and places is the number of characters to use. places is useful for padding the return value with leading zeros. |
BIN2OCT | Converts a binary number to octal and takes the form =BIN2DEC(number, places), where number is the binary integer you want to convert, and places is the number of characters to use. places is useful for padding the return value with leading zeros. |
BINOMDIST | Returns the individual term binomial distribution probability and takes the form =BINOMDIST(number_s, trials, probability_s, cumulative), where number_s is the number of successes in trials, trials is the number of independent trials, probability_s is the probability of success on each trial, and cumulative is a logical value that determines the form of the function. If TRUE, it returns the probability that there are at most number_s successes; if FALSE, it returns the probability that there are number_s successes. |
CEILING | Rounds a number up to the nearest given multiple and takes the form =CEILING(number, multiple), where number and multiple must be numeric and have the same sign. If they have different signs, Excel returns the #NUM! error value. See "Using the Rounding Functions" on page 500. |
CELL | Returns information about the contents, location, or formatting of a cell and takes the form =CELL(info_type, reference), where info_type specifies the type of information you want, and reference is the cell you want information about. info_type can be any of the following: address, col (column #), color, contents, filename, format, parentheses, prefix, protect, row, type, or width. See Help for a table of format codes returned. |
CHAR | Returns the character that corresponds to an ASCII code number and takes the form =CHAR(number), where number accepts ASCII codes with or without leading zeros. See "Using Selected Text Functions" on page 503. |
CHIDIST | Returns the one-tailed probability of the chi-squared distribution (used to compare observed vs. expected values) and takes the form =CHIDIST(x, degrees_freedom), where x is the value at which you want to evaluate the distribution, and degrees_freedom is the number of degrees of freedom. |
CHIINV | Returns the inverse of CHIDIST (one-tailed probability of the chi-squared distribution) and takes the form =CHIINV(probability, degrees_freedom), where probability is a probability associated with the chi-squared distribution, and degrees_freedom is the number of degrees of freedom. |
CHITEST | Returns the test for independence and takes the form =CHITEST(actual_range, expected_range), where actual_range is the range of data that contains observations to test against expected values, and expected_range is the range of data that contains the ratio of the product of row totals and column totals to the grand total. |
CHOOSE | Retrieves an item from a list of values and takes the form =CHOOSE(index_num, value1, value2, . . .), where index_num is the position in the list of the item you want to look up, and the value arguments are the elements of the list, which can be values or cell references. Returns the value of the element of the list that occupies the position indicated by index_num. See "Using Selected Lookup and Reference Functions" on page 512. |
CLEAN | Removes nonprintable characters such as tabs and program-specific codes from a string and takes the form =CLEAN(text). See "Using Selected Text Functions" on page 503. |
CODE | Returns the ASCII code number for the first character of its argument and takes the form =CODE(text). See "Using Selected Text Functions" on page 503. |
COLUMN | Returns the column number of the referenced cell or range and takes the form =COLUMN(reference). If reference is omitted, the result is the column number of the cell containing the function. If reference is a range or a name and the function is entered as an array (by pressing Ctrl+Shift+Enter), the result is an array of the numbers of each of the columns in the range. See "Using Selected Lookup and Reference Functions" on page 512. |
COLUMNS | Returns the number of columns in a reference or an array and takes the form =COLUMNS(array), where array is an array constant, a range reference, or a range name. See "Using Selected Lookup and Reference Functions" on page 512. |
COMBIN | Determines the number of possible group combinations that can be derived from a pool of items and takes the form =COMBIN(number, number_chosen), where number is the total items in the pool, and number_chosen is the number of items you want in each group. See "Using Selected Mathematical Functions" on page 498. |
COMPLEX | Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj and takes the form =COMPLEX(real_num, i_num, suffix), where real_num is the real coefficient of the complex number, i_num is the imaginary coefficient of the complex number, and suffix is the suffix for the imaginary component of the complex number. If omitted, suffix is assumed to be i. |
CONCATENATE | Assembles larger strings from smaller strings; takes the form =CONCATENATE(text1, text2, . . .); and accepts up to 30 arguments, which can be text, numbers, or cell references. See "Using the Substring Text Functions" on page 505. |
CONFIDENCE | Returns the confidence interval for a population mean and takes the form =CONFIDENCE(alpha, standard_dev, size), where alpha is the significance level used to compute the confidence level (an alpha of 0.1 indicates a 90 percent confidence level), standard_dev is the population standard deviation for the data range and is assumed to be known, and size is the sample size. |
CONVERT | Converts a number from one measurement system to another and takes the form =CONVERT(number, from_unit, to_unit), where number is the value to convert, from_unit is the units for number, and to_unit is the units for the result. See Help for a table of unit codes. |
CORREL | Returns the correlation coefficient of the array1 and array2 cell ranges and takes the form =CORREL(array1, array2), where arrays are ranges of cells containing values. |
COS | Returns the cosine of an angle and is the complement of the SIN function. It takes the form =COS(number), where number is the angle in radians. |
COSH | Returns the hyperbolic cosine of a number and takes the form =COSH(number), where number is any real number. |
COUNT | Tells you how many cells in a given range contain numbers, including dates and formulas, that evaluate to numbers; takes the form =COUNT(number1, number2, . . .); and accepts up to 30 arguments, ignoring text, error values, and logical values. See "Using Built-In Statistical Functions" on page 553. |
COUNTA | Acts like COUNT except text and logical values are included in the calculation. See "Using Built-In Statistical Functions" on page 553. |
COUNTBLANK | Counts empty cells in a specified range and takes the form =COUNTBLANK(range). See "Using Selected Lookup and Reference Functions" on page 512. |
COUNTIF | Counts only those cells that match specified criteria and takes the form =COUNTIF(range, criteria), where range is the range you want to test, and criteria is the logical test to be performed on each cell. See "Using Built-In Statistical Functions" on page 553. |
COUNTIES | COUNTIFS Acts like COUNTIF but accepts multiple criteria, taking the form =COUNTIFS(range1, criteria1, range2, criteria2, . . .). |
COUPDAYBS | Calculates the number of days from the beginning of the coupon period to the settlement date and takes the form =COUPDAYBS(settlement, maturity, frequency, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly); and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
COUPDAYS | Calculates the number of days in the coupon period that contains the settlement date and takes the form =COUPDAYS(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See "Analyzing Securities" on page 544. |
COUPDAYSNC | Calculates the number of days from the settlement date to the next coupon date and takes the form =COUPDAYSNC(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See "Analyzing Securities" on page 544. |
COUPNCD | Calculates the next coupon date after the settlement date and takes the form =COUPNCD(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See "Analyzing Securities" on page 544. |
COUPNUM | Calculates the number of coupons payable between the settlement date and the maturity date and rounds the result to the nearest whole coupon; takes the form =COUPNUM(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See "Analyzing Securities" on page 544. |
COUPPCD | Calculates the coupon date previous to the settlement date and takes the form =COUPPCD(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See "Analyzing Securities" on page 544. |
COVAR | Returns covariance, the average of the products of deviations for each data point pair, and takes the form =COVAR(array1, array2), where arrays are cell ranges containing integers. |
CRITBINOM | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value and takes the form =CRITBINOM(trials, probability_s, alpha), where trials is the number of Bernoulli trials, probability_s is the probability of a success on each trial, and alpha is the criterion value. |
CUBEKPIMEMBER | Returns a key performance indicator (KPI) property and returns the name of the KPI. This function takes the form =CUBEKPIMEMBER(connection, kpi_name, kpi_property, caption). connection is a text string indicating the name of the cube connection; kpi_name is the text name of the KPI; and kpi_property is the component of the KPI that is returned (one of KPIValue, KPIGoal, KPIStatus, KPITrend, KPIWeight, or KPICurrentTimeMemeber). This function is supported only when connected to a Microsoft SQL Server 2005 Analysis Services (or later) data source. Cube functions are used with online analytical processing (OLAP) databases, where data structures called cubes are used to draw multidimensional relationships among data sets. |
CUBEMEMBER | Returns a member or tuple from the cube and takes the form =CUBEMEMBER(connection, member_expression, caption). connection is a text string indicating the name of the cube connection, member_expression is a text string of a multidimensional expression (MDX) that evaluates to a unique number in the cube, and caption is a text string to display in the cell instead of the defined caption from the cube. Cube functions are used with online analytical processing (OLAP) databases, where data structures called cubes are used to draw multidimensional relationships among data sets. |
CUBEMEMBERPROPERTY | Returns the value of a member property from the cube and takes the form =CUBEMEMBERPROPERTY(connection, member_expression, property), where connection is a text string indicating the name of the cube connection, member_expression is a text string of a multidimensional expression (MDX) that evaluates to a unique number in the cube, and property is a text string of the property name or a reference to a cell containing a property name. Cube functions are used with online analytical processing (OLAP) databases, where data structures called cubes are used to draw multidimensional relationships among data sets. |
CUBERANKEDMEMBER | Returns the Nth (ranked) member in a set and takes the form =CUBERANKEDMEMBER(connection, set_expression, rank, caption). connection is a text string indicating the name of the cube connection; set_expression is a text string indicating a set expression, the CUBESET function, or a reference to a cell containing the CUBESET function; rank is an integer specifying the top value to return (1 = top value, 2 = second value, and so on); and caption is a text string to be displayed in the cell instead of the caption supplied by the cube. Cube functions are used with online analytical processing (OLAP) databases, where data structures called cubes are used to draw multidimensional relationships among data sets. |
CUBESET | Returns a calculated set of members or tuples from the cube database and takes the form =CUBESET(connection, set_expression, caption, sort_order, sort_by), where connection is a text string indicating the name of the cube connection; set_expression is a text string that returns a set of members or is a reference to a cell range containing a set or members or tuples; caption is a text string to be displayed in the cell instead of the caption supplied by the cube; sort_by is a text string indicating the value in the set by which you want to sort the results; and sort_order is a number specifying the type of sort (0 = none, 1 = ascending, 2 = descending, 3 = alpha ascending, 4 = alpha descending, 5 = natural ascending, and 6 = natural descending). Cube functions are used with online analytical processing (OLAP) databases, where data structures called cubes are used to draw multidimensional relationships among data sets. |
CUBESETCOUNT | Returns the number of items in a set and takes the form =CUBESETCOUNT(set), where set is a text string of an expression that evaluates to a set defined by the CUBESET function, the CUBESET function itself, or a reference to a cell containing a CUBESET function. Cube functions are used with online analytical processing (OLAP) databases, where data structures called cubes are used to draw multidimensional relationships among data sets. |
CUBEVALUE | Returns an aggregated value from a cube and takes the form =CUBEVALUE(connection, member_expression1, member_expression2, . . .). connection is a text string indicating the name of the cube connection, and member_expression is a text string of a multidimensional expression (MDX) that evaluates to a unique number in the cube. Cube functions are used with online analytical processing (OLAP) databases, where data structures called cubes are used to draw multidimensional relationships among data sets. |
CUMIPMT | Returns the cumulative interest paid on a loan between start_period and end_period and takes the form =CUMIPMT(rate, nper, pv, start_period, end_period, type), where rate is the interest rate, nper is the total number of payment periods, pv is the present value, and start_period is the first period in the calculation. Payment periods are numbered beginning with 1; end_period is the last period in the calculation, and type is the timing of the payment. |
CUMPRINC | Returns the cumulative principal paid on a loan between start_period and end_period and takes the form =CUMPRINC(rate, nper, pv, start_period, end_period, type). For argument descriptions, see CUMIPMT. |
DATE | Returns the serial number that represents a particular date and takes the form =DATE(year, month, day), where year can be one to four digits from 1 to 9999; month is a number representing the month of the year; and day is a number representing the day of the month. |
DATEVALUE | Translates a date into a serial value and takes the form =DATEVALUE(date_text), where date_text represents a date entered as text in quotation marks. See "Working with Date and Time Functions" on page 531. |
DAVERAGE | Averages the values in a column in a list or database that match conditions you specify and takes the form =DAVERAGE(database, field, criteria), where database is the range of cells that make up the list or database and the first row of the list contains labels for each column, field indicates which column is used in the function (by label name or by position), and criteria is the range of cells that contain the conditions you specify. |
DAY | Returns the value of the day portion of a serial date/time value and takes the form =DAY(serial_number), where serial_number can be a date value, a reference, or text in date format enclosed in quotation marks. See "Working with Date and Time Functions" on page 531. |
DAYS360 | Returns the number of days between two dates based on a 360-day year (12 months of 30 days each), which is used in some accounting calculations, and takes the form =DAYS360(start_date, end_date, method), where start_date and end_date are the two dates between which you want to know the number of days, and method is a logical value that specifies whether to use the U.S. or European method in the calculation. If FALSE or omitted, uses U.S. (NASD) method; if TRUE, uses the European method. |
DB | Computes fixed declining balance depreciation for a particular period in the asset's life and takes the form =DB(cost, salvage, life, period, month), where cost is the initial asset cost, salvage is the remaining value after the asset is fully depreciated, life is the length of depreciation time; period is the individual period to be computed, and month is the number of months depreciated in the first year (if omitted, it is assumed to be 12). See "Calculating Depreciation" on page 541. |
DCOUNT | Counts the cells that contain numbers in a column in a list or database that match conditions you specify and takes the form =DCOUNT(database, field, criteria), where database is the range of cells that make up the list or database, field indicates which column is used in the function, and criteria is the range of cells that contain the conditions you specify. |
DCOUNTA | Acts like DCOUNT except it also includes cells containing text, logical values, and error values. See DCOUNT for arguments. |
DDB | Computes double-declining balance depreciation and takes the form =DDB(cost, salvage, life, period, factor), where cost is the initial asset cost; salvage is the remaining value after the asset is fully depreciated; life is the length of depreciation time; period is the individual period to be computed; and factor indicates the method used (2 or omitted indicates double-declining balance, and 3 indicates triple-declining balance). See "Calculating Depreciation" on page 541. |
DEC2BIN | Converts a decimal number to binary and takes the form =DEC2BIN(number, places), where number is the decimal integer you want to convert, and places is the number of characters to use. places is useful for padding the return value with leading zeros. |
DEC2HEX | Converts a decimal number to hexadecimal and takes the same form and arguments as DEC2BIN. |
DEC2OCT | Converts a decimal number to octal and takes the same form and arguments as DEC2BIN. |
DEGREES | Converts radians to degrees and takes the form =DEGREES(angle), where angle represents an angle measured in radians. |
DELTA | Tests whether two values are equal and takes the form =DELTA(number1, number2), where number1 is the first number, and number2 is the second number (which, if omitted, is assumed to be zero). Returns 1 if number1 equals number2; otherwise, returns 0. |
DEVSQ | Returns the sum of squares of deviations of data points from their sample mean; takes the form =DEVSQ(number1, number2, . . .), where the numbers can be names, arrays, or references that resolve to numbers; and accepts up to 30 arguments. |
DGET | Extracts a single value from a column in a list or database that matches conditions you specify and takes the form =DGET(database, field, criteria), where database is the range of cells that make up the list or database, field indicates which column is used in the function, and criteria is the range of cells that contain the conditions you specify. |
DISC | Calculates the discount rate for a security and takes the form =DISC(settlement, maturity, price, redemption, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; price is the security's price per $100 face value; redemption is the value of the security at redemption; and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
DMAX | Returns the largest number in a column in a list or database that matches conditions you specify and takes the form =DMAX(database, field, criteria), where database is a range that makes up the list or database, field indicates which column is used in the function, and criteria is the range of cells that contain the conditions you specify. |
DMIN | Returns the smallest number in a column in a list or database that matches conditions you specify and takes the same form and arguments as DMAX. |
DOLLAR | Converts a number into a string formatted as currency with the specified number of decimal places and takes the form =DOLLAR(number, decimals). If you omit decimals, the result is rounded to two decimal places. If you use a negative number for decimals, the result is rounded to the left of the decimal point. See "Using Selected Text Functions" on page 503. |
DOLLARDE | Converts the familiar fractional pricing of securities to decimals and takes the form =DOLLARDE(fractional dollar, fraction), where fractional dollar is the value you want to convert expressed as an integer followed by a decimal point and the numerator of the fraction you want, and fraction is an integer indicating the denominator to be used. See "Analyzing Securities" on page 544. |
DOLLARFR | Converts a security price expressed in decimals to fractions and takes the form =DOLLARFR(decimal dollar, fraction), where decimal dollar is the value you want to convert expressed as a decimal, and fraction is an integer indicating the denominator of the fraction you want. See "Analyzing Securities" on page 544. |
DPRODUCT | Multiplies the values in a column in a list or database that match conditions you specify and takes the form =DPRODUCT(database, field, criteria), where database is a range that makes up the list or database, field indicates which column is used in the function, and criteria is the range of cells that contain the conditions you specify. |
DSTDEV | Estimates the standard deviation of a population based on a sample, using the numbers in a column in a list or database that match conditions you specify, and takes the same form and arguments as DPRODUCT. |
DSTDEVP | Calculates the standard deviation of a population based on the entire population, using the numbers in a column in a list or database that match conditions you specify, and takes the same form and arguments as DPRODUCT. |
DSUM | Adds the numbers in a column in a list or database that match conditions you specify and takes the same form and arguments as DPRODUCT. |
DURATION | Calculates the weighted average of the present value of the bond's cash flows for a security whose interest payments are made on a periodic basis and takes the form =DURATION(settlement, maturity, coupon, yield, frequency, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; coupon is the security's annual coupon rate; yield is the annual yield of the security; frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly); and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
DVAR | Estimates the variance of a population based on a sample, using the numbers in a column in a list or database that match conditions you specify, and takes the form =DVAR(database, field, criteria), where database is the range of cells that make up the list or database, field indicates which column is used in the function, and criteria is the range of cells that contain the conditions you specify. |
DVARP | Calculates the variance of a population based on the entire population, using the numbers in a column in a list or database that match conditions you specify, and takes the same form and arguments as DVAR. |
EDATE | Returns the exact date that falls an indicated number of months before or after a given date and takes the form =EDATE(start_date, months), where start_date is the date to calculate from, and months is the number of months before (negative) or after (positive) the start date. See "Working with Specialized Date Functions" on page 533. |
EFFECT | Returns the effective interest rate and takes the form =EFFECT(nominal_rate, npery). nominal_rate is the annual interest rate, and npery is the number of annual compounding periods. |
EOMONTH | Returns a date that falls on the last day of the month an indicated number of months before or after a given date and takes the form =EOMONTH(start_date, months), where start_date is the date to calculate from, and months is the number of months before (negative) or after (positive) the start date. See "Working with Specialized Date Functions" on page 533. |
ERF | Returns the error function integrated between lower_limit and upper_limit and takes the form =ERF(lower_limit, upper_limit), where lower_limit is the lower bound, and upper_limit is the upper bound. If omitted, ERF integrates between zero and lower_limit. |
ERFC | Returns the complementary ERF function integrated between x and infinity and takes the form =ERFC(x), where x is the lower bound for integrating ERF. |
ERROR. TYPE | Detects the type of error value in a referenced cell and takes the form =ERROR.TYPE(error_val). Returns a code designating the type of error value in the referenced cell: 1 (#NULL!), 2 (#DIV/0!), 3 (#VALUE!), 4 (#REF!), 5 (#NAME!), 6 ( #NUM!), and 7 (#N/A). Any other value in the referenced cell returns the error value #N/A. See "Using Selected Lookup and Reference Functions" on page 512. |
EUROCONVERT | Converts a number to euros-or converts any EU member currency to euros or any other member currency-and takes the form =EUROCONVERT(number, source, target, full_precision, triangulation_precision), where number is the value you want to convert, source is the ISO country code for the source currency, target is the ISO country code for the currency to which you want to convert, full_precision is a logical value that displays all significant digits when TRUE and that uses a currency-specific rounding factor when FALSE, and triangulation_precision is an integer equal to 3 or greater that specifies the number of significant digits to use when converting from one EU member currency to another. This function is installed with the Euro Currency Tools add-in. See Help for tables of ISO codes and rounding factors. |
EVEN | Rounds a number up to the nearest even integer and takes the form =EVEN(number). Negative numbers are correspondingly rounded down. See "Using the Rounding Functions" on page 500. |
EXACT | Determines whether two strings match exactly, including uppercase and lowercase letters, not including formatting differences, and takes the form =EXACT(text1, text2), where both arguments must be either literal strings enclosed in quotation marks or references to cells that contain text. See "Using Selected Text Functions" on page 503. |
EXP | Computes the value of the constant e (approx. 2.71828183) raised to the power specified by its argument and takes the form =EXP(number). The EXP function is the inverse of the LN function. |
EXPONDIST | Returns exponential distribution and takes the form =EXPONDIST(x, lambda, cumulative), where x is the value of the function; lambda is the parameter value; and cumulative is a logical value that indicates which form of the exponential function to provide (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function). |
FACT | Returns the factorial of a number and takes the form =FACT(number), where number is a positive integer. |
FACTDOUBLE | Returns the double factorial of a number and takes the form =FACT(number), where number is a positive integer. |
FALSE | Represents an alternative for the logical condition FALSE, which accepts no arguments and takes the form =FALSE( ). See "Using Selected Logical Functions" on page 508. |
FDIST | Returns the F probability distribution and takes the form =FDIST(x, degrees_freedom1, degrees_freedom2), where x is the value at which to evaluate the function, degrees_freedom1 is the numerator degrees of freedom, and degrees_freedom2 is the denominator. |
FIND | Returns the position of specified text within a string and takes the form =FIND(find_text, within_text, start_num), where find_text is the text you want to find (case sensitive), and within_text indicates where to look. Both arguments accept either literal text enclosed in quotation marks or cell references. Optional start_num specifies the character position in within_text where you want to begin the search. You get a #VALUE! error value if find_text isn't contained in within_text, if start_num isn't greater than zero, or if start_num is greater than the number of characters in within_text or greater than the position of the last occurrence of find_text. See "Using the Substring Text Functions" on page 505. |
FINDB | Returns the position of specified text within a string based on the number of bytes each character uses from the first character of within_text, takes the form =FINDB(find_text, within_text, start_num), and takes the same arguments as FIND. This function is for use with double-byte characters. |
FINV | Returns the inverse of the F probability distribution and takes the form =FINV(probability, degrees_freedom1, degrees_freedom2), where probability is a probability associated with the F cumulative distribution, degrees_freedom1 is the numerator degrees of freedom, and degrees_freedom2 is the denominator degrees of freedom. |
FISHER | Returns the Fisher transformation at x and takes the form =FISHER(x), where x is a value between -1 and 1 (not inclusive). |
FISHERINV | Returns the inverse of the Fisher transformation and takes the form =FISHERINV(y), where y is any numeric value. |
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. This function takes the form =FIXED(number, decimals, no_commas), where number is the number you want to round and convert to text; decimals is the number of digits to the right of the decimal point (assumes 2 if omitted); and no_commas is a logical value (if TRUE, prevents commas; if FALSE or omitted, includes commas). |
FLOOR | Rounds a number down to the nearest given multiple and takes the form =FLOOR(number, multiple), where number and multiple must be numeric and have the same sign. If they have different signs, Excel returns the #NUM! error value. See "Using the Rounding Functions" on page 500. |
FORECAST | Returns a single point along a trend line and takes the form =FORECAST(x, known_y's, known_x's). For arguments and usage details, see "The FORECAST Function" on page 564. |
FREQUENCY | Returns the number of times that values occur within a population and takes the form =FREQUENCY(data_array, bins_array). For usage and argument details, see "Analyzing Distribution with the FREQUENCY Function" on page 572. |
FTEST | Returns the result of an F-test, the one-tailed probability that the variances in array1 and array2 are not significantly different, and takes the form =FTEST(array1, array2). |
FV | Computes the value at a future date of an investment based on periodic, constant payments and a constant interest rate. Takes the form =FV(rate, nper, payment, pv, type), where rate is the interest rate, nper is the term (periods) of the investment, payment is the amount of each periodic payment when individual amounts are the same, pv is the investment value today, and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See "Calculating Investments" on page 535. |
FVSCHEDULE | Returns the future value of an initial principal after applying a series of variable compound interest rates and takes the form =FVSCHEDULE(principal, schedule), where principal is the present value, and schedule is an array of interest rates to apply. |
GAMMADIST | Returns the gamma distribution and takes the form =GAMMADIST(x, alpha, beta, cumulative), where x is the value at which you want to evaluate the distribution; alpha is a parameter to the distribution; beta is a parameter to the distribution; and cumulative is a logical value that determines the form of the function (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function). |
GAMMAINV | Returns the inverse of the gamma cumulative distribution and takes the form =GAMMAINV(probability, alpha, beta), where probability is the probability associated with the gamma distribution, alpha is a parameter to the distribution, and beta is a parameter to the distribution. |
GAMMALN | Returns the natural logarithm of the gamma function and takes the form =GAMMALN(x), where x is a positive value. |
GCD | Returns the greatest common divisor of two or more integers (the largest integer that divides both number1 and number2 without a remainder) and takes the form =GCD(number1, number2, . . .), where the numbers are 1 to 30 positive integer values. |
GEOMEAN | Returns the geometric mean of an array or range of positive data and takes the form =GEOMEAN(number1, number2, . . .), where the numbers are 1 to 30 positive integer values. |
GESTEP | Returns 1 if number is greater than or equal to step; otherwise, returns 0 (zero) and takes the form =GESTEP(number, step), where number is the value to test against step, and step is the threshold value (zero if omitted). |
GETPIVOTDATA | Returns data stored in a PivotTable report and takes the form =GETPIVOTDATA(data_field, pivot_table, field1, item1, field2, item2, . . .), where data_field is the name, in quotation marks, for the data field that contains the data you want retrieved; pivot_table is a reference to a cell in the PivotTable report that contains the data you want to retrieve; and fieldx and itemx are 1 to 14 pairs of field names and item names that describe the data you want to retrieve. |
GROWTH | Returns values of points that lie along an exponential growth trend line and takes the form =GROWTH(known_y's, known_x's, new_x's, const). For arguments and usage details, see "The GROWTH Function" on page 567. |
HARMEAN | Returns the harmonic mean of a data set and takes the form =HARMEAN(number1, number2, . . .), where the numbers are 1 to 30 positive values. |
HEX2BIN | Converts a hexadecimal number to binary and takes the form =HEX2BIN(number, places), where number is the hexadecimal number you want to convert, and places is the number of characters to use (useful for padding the return value with leading zeros). |
HEX2DEC | Converts a hexadecimal number to decimal and takes the form =HEX2DEC(number), where number is the hexadecimal number you want to convert. |
HEX2OCT | Converts a hexadecimal number to octal and takes the form =HEX2OCT(number, places), where number is the hexadecimal number you want to convert, and places is the number of characters to use (useful for padding the return value with leading zeros). |
HLOOKUP | Looks for a specified value in the top row in a table, returns the value in the same column and a specified row, and takes the form =HLOOKUP(lookup_value, table_array, row_index_num, range_lookup), where lookup_value is the value to look for; table_array is the range containing the lookup and result values sorted in alphabetical order by the top row; row_index_num is the row number containing the value you want to find; and range_lookup is a logical value, which, if FALSE, forces an exact match. See "Using Selected Lookup and Reference Functions" on page 512. |
HOUR | Returns the hour portion of a serial date/time value and takes the form =HOUR(serial_number), where serial_number can be a time/date value, a reference, or text in time/date format enclosed in quotation marks. See "Working with Date and Time Functions" on page 531. |
HYPERLINK | Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. When you click the cell that contains the HYPERLINK function, Excel opens the file stored at link_location. This function takes the form =HYPERLINK(link_location, friendly_name), where link_location is the path and file name to the document to be opened, and friendly_name is the jump text or numeric value that is displayed in the cell. |
HYPGEOMDIST | Returns the hypergeometric distribution (the probability of a given number of sample successes, given the size of the sample and population, and the number of population successes) and takes the form =HYPGEOMDIST(sample_s, number_sample, population_s, number_population), where sample_s is the number of successes in the sample, number_sample is the size of the sample, population_s is the number of successes in the population, and number_population is the population size. |
IF | Returns values based on supplied conditional tests and takes the form =IF(logical_test, value_if_true, value_if_false). You can nest up to seven additional functions within an IF function. If you use text arguments, the match must be exact except for case. See "Using Selected Logical Functions" on page 508. |
IFERROR | Returns a specified value when a formula evaluates to an error and takes the form =IFERROR(value, value_if_error), where value refers to the formula you want to check, and value_if_error is the value you want to display if value returns an error. |
IMABS | Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format and takes the form =IMABS(inumber), where inumber is a complex number for which you want the absolute value. |
IMAGINARY | Returns the imaginary coefficient of a complex number in x + yi or x + yj text format and takes the form =IMAGINARY(inumber), where inumber is a complex number for which you want the imaginary coefficient. |
IMARGUMENT | Returns the argument theta, an angle expressed in radians, and takes the form =IMARGUMENT(inumber), where inumber is a complex number for which you want the argument theta. |
IMCONJUGATE | Returns the complex conjugate of a complex number in x + yi or x + yj text format and takes the form =IMCONJUGATE(inumber), where inumber is a complex number for which you want the conjugate. |
IMCOS | Returns the cosine of a complex number in x + yi or x + yj text format and takes the form =IMCOS(inumber), where inumber is a complex number for which you want the cosine. |
IMDIV | Returns the quotient of two complex numbers in x + yi or x + yj text format and takes the form =IMDIV(inumber1, inumber2), where inumber1 is the complex numerator or dividend, and inumber2 is the complex denominator or divisor. |
IMEXP | Returns the exponential of a complex number in x + yi or x + yj text format and takes the form =IMEXP(inumber), where inumber is a complex number for which you want the exponential. |
IMLN | Returns the natural logarithm of a complex number in x + yi or x + yj text format and takes the form =IMLN(inumber), where inumber is a complex number for which you want the natural logarithm. |
IMLOG10 | Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format and takes the form =IMLOG10(inumber), where inumber is a complex number for which you want the common logarithm. |
IMLOG2 | Returns the base-2 logarithm of a complex number in x + yi or x + yj text format and takes the form =IMLOG2(inumber), where inumber is a complex number for which you want the base-2 logarithm. |
IMPOWER | Returns a complex number in x + yi or x + yj text format raised to a power and takes the form =IMPOWER(inumber, number), where inumber is a complex number you want to raise to a power, and number is the power to which you want to raise the complex number. |
IMPRODUCT | Returns the product of 2 to 29 complex numbers in x + yi or x + yj text format and takes the form =IMPRODUCT(inumber1, inumber2, . . .). The inumbers are 1 to 29 complex numbers to multiply. |
IMREAL | Returns the real coefficient of a complex number in x + yi or x + yj text format and takes the form =IMREAL(inumber), where inumber is a complex number for which you want the real coefficient. |
IMSIN | Returns the sine of a complex number in x + yi or x + yj text format and takes the form =IMSIN(inumber), where inumber is a complex number for which you want the sine. |
IMSQRT | Returns the square root of a complex number in x + yi or x + yj text format and takes the form =IMSQRT(inumber), where inumber is a complex number for which you want the square root. |
IMSUB | Returns the difference of two complex numbers in x + yi or x + yj text format and takes the form =IMSUB(inumber1, inumber2), where inumber1 is the complex number from which to subtract inumber2, and inumber2 is the complex number to subtract from inumber1. |
IMSUM | Returns the sum of two or more complex numbers in x + yi or x + yj text format and takes the form =IMSUM(inumber1, inumber2, . . .), where the inumbers are 1 to 29 complex numbers to add. |
INDEX | Returns a value or values, or a reference to a cell or range, using one of two forms, array: =INDEX(array, row_num, column_num) or reference: =INDEX(reference, row_num, column_num, area_num). The array form works only with array arguments and returns the resulting values located at the intersection of row_num and column_num. The reference form returns a cell address using similar arguments, where reference can be one or more ranges (areas), and area_num is needed only if more than one area is included in reference. See "Using Selected Lookup and Reference Functions" on page 512. |
INDIRECT | Returns the contents of a cell using its reference and takes the form =INDIRECT(ref_text, a1), where ref_text is a reference or a name, and a1 is a logical value indicating the type of reference used in ref_text (FALSE indicates R1C1 format, and TRUE or omitted indicates A1 format). See "Using Selected Lookup and Reference Functions" on page 512. |
INFO | Returns information about the current operating environment and takes the form =INFO(type_text), where type_text is text specifying what type of information you want returned. Information types include directory, memavail, memused, numfile, origin, osversion, recalc, release, system, and totmem. See Help for more information. |
INT | Rounds numbers down to the nearest integer and takes the form =INT(number). When number is negative, INT also rounds that number down to the nearest integer. See "Using the Rounding Functions" on page 500. |
INTERCEPT | Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values and takes the form =INTERCEPT(known_y's, known_x's), where known_y's is the dependent set of observations or data, and known_x's is the independent set of observations or data. |
INTRATE | Calculates the rate of interest (discount rate) for a fully invested security and takes the form =INTRATE(settlement, maturity, investment, redemption, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, investment is the amount invested in the security, redemption is the amount to be received at maturity, and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual; if 2 = actual/360, if 3 = actual/365; if 4 = European 30/360). See "Analyzing Securities" on page 544. |
IPMT | Computes the interest portion of an individual payment made to repay an amount over a specified time period with constant periodic payments and a constant interest rate and takes the form =IPMT(rate, period, nper, pv, fv, type), where rate is the interest rate; period is the number of an individual periodic payment; nper is the term (periods) of the investment; pv is the investment value today; fv is the investment value at the end of the term; and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See "Calculating Investments" on page 535. |
IRR | Returns the rate that causes the present value of the inflows from an investment to exactly equal the cost of the investment and takes the form =IRR(values, guess), where values is an array or a reference to a range of cells that contain numbers beginning with the cost expressed as a negative value, and guess is an approximate interest rate (assumes 10 percent if omitted). See "Calculating Investments" on page 535. |
ISBLANK | Returns TRUE if the referenced cell is empty; otherwise, returns FALSE. Uses the form =ISBLANK(value). See "Using the IS Information Functions" on page 511. |
ISERR | Returns TRUE if the value contains any error value except #N/A; otherwise, returns FALSE. Uses the form =ISERR(value). See "Using the IS Information Functions" on page 511. |
ISERROR | Returns TRUE if the value contains any error value (including #N/A); otherwise, returns FALSE. Uses the form =ISERROR(value). See "Using the IS Information Functions" on page 511. |
ISEVEN | Returns TRUE if the value is an even number; otherwise, returns FALSE. Uses the form =ISEVEN(value). See "Using the IS Information Functions" on page 511. |
ISLOGICAL | Returns TRUE if the value is a logical value, otherwise, returns FALSE. Uses the form =ISLOGICAL(value). See "Using the IS Information Functions" on page 511. |
ISNA | Returns TRUE if the value is the #N/A error value; otherwise, returns FALSE. Uses the form =ISNA(value). See "Using the IS Information Functions" on page 511. |
ISNONTEXT | Returns TRUE if the value is not text; otherwise, returns FALSE. Uses the form =ISNONTEXT(value). See "Using the IS Information Functions" on page 511. |
ISNUMBER | Returns TRUE if the value is a number; otherwise, returns FALSE. Uses the form =ISNUMBER(value). See "Using the IS Information Functions" on page 511. |
ISODD | Returns TRUE if the value is an odd number; otherwise, returns FALSE. Uses the form =ISODD(value). See "Using the IS Information Functions" on page 511. |
ISPMT | Calculates the interest paid during a specific period of an investment. Provided for Lotus 1-2-3 compatibility and takes the form =ISPMT(rate, per, nper, pv), where rate is the interest rate for the investment, per is the period for which you want to find the interest, nper is the total number of payment periods for the investment, and pv is the present value of the investment (or the loan amount). |
ISREF | Returns TRUE if the value is a reference; otherwise, returns FALSE. Uses the form =ISREF(value). See "Using the IS Information Functions" on page 511. |
ISTEXT | Returns TRUE if the value is text; otherwise, returns FALSE. Uses the form =ISTEXT(value). See "Using the IS Information Functions" on page 511. |
JIS | Changes text in single-byte character set languages to doublebyte characters and takes the form =JIS(text). text is either text or a reference to a cell containing text. Has no effect on double-byte characters. |
KURT | Returns the kurtosis of a data set (characterizes the relative "peakedness" or flatness of a distribution compared with the normal distribution), takes the form =KURT(number1, number2, . . .), and accepts up to 30 numeric arguments. |
LARGE | Returns the kth largest value in an input range and takes the form =LARGE(array, k), where k is the position from the largest value in array you want to find. See "Functions That Analyze Rank and Percentile" on page 555. |
LCM | Returns the least common multiple of integers (the smallest positive integer that is a multiple of all arguments), takes the form =LCM(number1, number2, . . .), and accepts up to 29 numeric integer arguments. |
LEFT | Returns the leftmost series of characters from a string and takes the form =LEFT(text, num_chars), where num_chars indicates how many characters you want to extract from the string (1 if omitted). See "Using the Substring Text Functions" on page 505. |
LEFTB | Returns the leftmost series of characters from a string, based on the specified number of bytes, and takes the form =LEFT(text, num_bytes), where num_bytes indicates how many characters you want to extract from the string, based on bytes. |
LEN | Returns the number of displayed characters in an entry and takes the form =LEN(text), where text is a number, a string enclosed in quotation marks, or a reference to a cell. Trailing zeros are ignored, but spaces are counted. See "Using Selected Text Functions" on page 503. |
LENB | Returns the number of characters in an entry, expressed in bytes, and takes the form =LENB(text). It is otherwise identical to the LEN function. This function is intended for use with double-byte characters. |
LINEST | Calculates the statistics for a line using the least squares method to arrive at a slope that best describes the given data and takes the form LINEST(known_y's, known_x's, const, stats). For arguments and usage details, see "The LINEST Function" on page 560. |
LN | Returns the natural (base e) logarithm of the positive number referred to by its argument and takes the form =LN(number). LN is the inverse of the EXP function. |
LOG | Returns the logarithm of a positive number using a specified base and takes the form =LOG(number, base). If you don't include the base argument, Excel assumes the base is 10. |
LOG10 | Returns the base-10 logarithm of a number and takes the form =LOG10(number), where number is a positive real number. |
LOGEST | Returns statistics describing known data in terms of an exponential curve and takes the form =LOGEST(known_y's, known_x's, const, stats). For arguments and usage details, see "The LOGEST Function" on page 566. |
LOGINV | Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is usually distributed with parameters mean and standard_dev and takes the form =LOGINV(probability, mean, standard_dev), where probability is a probability associated with the lognormal distribution, mean is the mean of ln(x), and standard_dev is the standard deviation of ln(x). |
LOGNORMDIST | Returns the cumulative lognormal distribution of x, where ln(x) is usually distributed with parameters mean and standard_dev. This function takes the form =LOGNORMDIST(x, mean, standard_dev), where x is the value at which to evaluate the function, mean is the mean of ln(x), and standard_dev is the standard deviation of ln(x). |
LOOKUP | Looks for a specified value in a one- or two-dimensional range and takes two forms, vector or array: =LOOKUP(lookup_value, lookup_vector, result_vector) or =LOOKUP(lookup_value, array), where lookup_value is the value to look for, lookup_vector is a one-row or one-column range containing the lookup values sorted in alphabetical order, result_vector is a range that contains the result values and must be identical in size to lookup_vector, and array is a two-dimensional range containing both lookup and result values. The array form of this function works like HLOOPKUP if array is wider than it is tall and works like VLOOKUP if array is taller than it is wide. See "Using Selected Lookup and Reference Functions" on page 512. |
LOWER | Converts a text string to all lowercase letters and takes the form =LOWER(text). See "Using Selected Text Functions" on page 503. |
MATCH | Returns the position in a list of the item that most closely matches a lookup value and takes the form =MATCH(lookup_value, lookup_array, match_type), where lookup_value is the value or string to look up, lookup_array is the range that contains the sorted values to compare, and match_type defines the rules for the search (if 1 or omitted, finds, in a range sorted in ascending order, the largest value that is less than or equal to lookup_value; if 0, finds the value that is equal to lookup_value; if -1, finds, in a range sorted in descending order, the smallest value that is greater than or equal to lookup_value). See "Using Selected Lookup and Reference Functions" on page 512. |
MAX | Returns the largest value in a range; takes the form =MAX(number1, number2, . . .); and accepts up to 30 arguments, ignoring text, error values, and logical values. See "Using Built-In Statistical Functions" on page 553. |
MAXA | Acts like MAX except text and logical values are included in the calculation. See "Using Built-In Statistical Functions" on page 553. |
MDETERM | Returns the matrix determinant of an array and takes the form =MDETERM(array), where array is a numeric array with an equal number of rows and columns. |
MDURATION | Calculates the annual modified duration for a security with interest payments made on a periodic basis, adjusted for market yield per number of coupon payments per year, and takes the form =MDURATION(settlement, maturity, coupon, yield, frequency, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; coupon is the security's annual coupon rate; yield is the annual yield of the security; frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly); and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
MEDIAN | Computes the median of a set of numbers; takes the form =MEDIAN(number1, number2, . . .); and can accept up to 30 arguments, ignoring text, error values, and logical values. See "Using Built-In Statistical Functions" on page 553. |
MID | Extracts a series of characters (substring) from a text string and takes the form =MID(text, start_num, num_chars), where text is the string from which you want to extract the substring, start_num is the location in the string where the substring begins (counting from the left), and num_chars is the number of characters you want to extract. See "Using the Substring Text Functions" on page 505. |
MIDB | Extracts a series of characters (substring) from a text string, based on the number of bytes you specify, and takes the form =MID(text, start_num, num_bytes), where text is the string from which you want to extract the substring, start_num is the location in the string where the substring begins (counting from the left), and num_bytes is the number of characters you want to extract, in bytes. This function is for use with doublebyte characters. |
MIN | Returns the smallest value in a range; takes the form =MIN(number1, number2, . . .); and accepts up to 30 arguments, ignoring text, error values, and logical values. See "Using Built-In Statistical Functions" on page 553. |
MINA | Acts like MIN except text and logical values are included in the calculation. See "Using Built-In Statistical Functions" on page 553. |
MINUTE | Returns the minute portion of a serial date/time value and takes the form =MINUTE(serial_number), where serial_number can be a time/date value, a reference, or text in time/date format enclosed in quotation marks. See "Working with Date and Time Functions" on page 531. |
MINVERSE | Returns the inverse matrix for the matrix stored in an array and takes the form =MINVERSE(array), where array is a numeric array with an equal number of rows and columns. |
MIRR | Calculates the rate of return of an investment, taking into account the cost of borrowed money and assuming resulting cash inflows are reinvested, and takes the form =MIRR(values, finance rate, reinvestment rate), where values is an array or a reference to a range of cells that contain numbers beginning with the cost expressed as a negative value, finance rate is the rate at which you borrow money, and reinvestment rate is the rate at which you reinvest the returns. See "Calculating Investments" on page 535. |
MMULT | Returns the matrix product of two arrays (resulting in an array with the same number of rows as array1 and the same number of columns as array2) and takes the form =MMULT(array1, array2). |
MOD | Returns the remainder of a division operation (modulus) and takes the form =MOD(number, divisor). If number is smaller than divisor, the result of the function equals number. If number is exactly divisible by divisor, the function returns 0. If divisor is 0, MOD returns the #DIV/0! error value. See "Using Selected Mathematical Functions" on page 498. |
MODE | Determines which value occurs most frequently in a set of numbers; takes the form =MODE(number1, number2, . . .); and accepts up to 30 arguments, ignoring text, error values, and logical values. See "Using Built-In Statistical Functions" on page 553. |
MONTH | Returns the value of the month portion of a serial date/time value and takes the form =MONTH(serial_number), where serial_number can be a date value, a reference, or text in date format enclosed in quotation marks. See "Working with Date and Time Functions" on page 531. |
MROUND | Rounds any number to a multiple you specify and takes the form =MROUND(number, multiple), where number and multiple must both have the same sign. The function rounds up if the remainder after dividing number by multiple is at least half the value of multiple. See "Using the Flexible MROUND Function" on page 501. |
MULTINOMIAL | Returns the ratio of the factorial of a sum of values to the product of factorials and takes the form =MULTINOMIAL(num1, num2, . . .), where nums are up to 29 values for which you want to find the multinomial. |
N | Returns a value converted to a number and takes the form =N(value), where value is the value you want converted. This function is included for compatibility with other spreadsheet programs but is not necessary in Excel. |
NA | Represents an alternative for the error value #N/A, which accepts no arguments and takes the form =NA( ). |
NEGBINOMDIST | Returns the negative binomial distribution (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), and takes the form =NEGBINOMDIST(number_f, number_s, probability_s), where number_f is the number of failures, number_s is the threshold number of successes, and probability_s is the probability of a success. |
NETWORKDAYS | Returns the number of working days between two given dates and takes the form =NETWORKDAYS(start_date, end_date, holidays), where start_date is the date you want to count from, end_date is the date you want to count to, and holidays is an array or reference containing any dates you want to exclude. See "Working with Specialized Date Functions" on page 533. |
NOMINAL | Returns the nominal annual interest rate and takes the form =NOMINAL(effect_rate, npery), where effect_rate is the effective interest rate, and npery is the number of compounding periods per year. |
NORMDIST | Returns the normal cumulative distribution for the specified mean and standard deviation and takes the form =NORMDIST(x, mean, standard_dev, cumulative), where x is the value for which you want the distribution; mean is the arithmetic mean of the distribution; standard_dev is the standard deviation of the distribution; and cumulative is a logical value that determines the form of the function (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability mass function). |
NORMINV | Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation and takes the form =NORMINV(probability, mean, standard_dev), where probability is a probability corresponding to the normal distribution, mean is the arithmetic mean of the distribution, and standard_dev is the standard deviation of the distribution. |
NORMSDIST | Returns the standard normal cumulative distribution function and takes the form =NORMSDIST(z). |
NORMSINV | Returns the inverse of the standard normal cumulative distribution (with a mean of zero and a standard deviation of one) and takes the form =NORMSINV(probability), where probability is a probability corresponding to the normal distribution. |
NOT | Helps develop compound conditional test formulas in conjunction with the simple logical operators: =, >, <, >=, <=, and <>. The NOT function has only one argument and takes the form =NOT(logical), where logical can be a conditional test, an array, or a reference to a cell containing a logical value. See "Using Selected Logical Functions" on page 508. |
NOW | Returns the serial value of the current date and time, takes the form =NOW( ), and accepts no arguments. See "Working with Date and Time Functions" on page 531. |
NPER | Computes the number of periods required to amortize a loan, given a specified periodic payment, and takes the form =NPER(rate, payment, present value, future value, type), where rate is the interest rate, payment is the amount of each periodic payment when individual amounts are the same, present value is the investment value today, future value is the investment value at the end of the term, and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See "Calculating Investments" on page 535. |
NPV | Determines the profitability of an investment and takes the form =NPV(rate, value1, value2, . . .), where rate is the interest rate, and the values represent up to 29 payments (or any size array) when individual amounts differ. See "Calculating Investments" on page 535. |
OCT2BIN | Converts an octal number to binary and takes the form =OCT2BIN(number, places), where number is the octal number you want to convert, and places is the number of characters to use (if omitted, uses the minimum number of characters necessary). |
OCT2DEC | Converts an octal number to decimal and takes the form =OCT2DEC(number), where number is the octal number you want to convert. |
OCT2HEX | Converts an octal number to hexadecimal and takes the form =OCT2HEX(number, places), where number is the octal number you want to convert, and places is the number of characters to use (if omitted, uses the minimum number of characters necessary). |
ODD | Rounds a number up to the nearest odd integer and takes the form =ODD(number). Negative numbers are correspondingly rounded down. See "Using the Rounding Functions" on page 500. |
ODDFPRICE | Returns the price per $100 of face value for a security having an odd first period and takes the form =ODDFPRICE(settlement, maturity, issue, first coupon, rate, yield, redemption, frequency, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; issue is the issue date of the security; first coupon is the security's first coupon due date as a serial date value; rate is the interest rate of the security at the issue date; yield is the annual yield of the security; redemption is the value of the security at redemption; frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly); and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). |
ODDFYIELD | Calculates the yield of a security that has an odd first period and takes the form =ODDFYIELD(settlement, maturity, issue, first coupon, rate, price, redemption, frequency, basis), where price is the security's price. See ODDFPRICE for additional argument definitions. |
ODDLPRICE | Calculates the price per $100 face value of a security having an odd last coupon period and takes the form =ODDLPRICE (settlement, maturity, last interest, rate, yield, redemption, frequency, basis), where last interest is the security's last coupon due date as a serial date value. See ODDFPRICE for additional argument definitions. |
ODDLYIELD | Calculates the yield of a security that has an odd last period and takes the form =ODDLYIELD(settlement, maturity, last interest, rate, price, redemption, frequency, basis), where last interest is the security's last coupon due date, and price is the security's price. See ODDFPRICE for additional argument definitions. |
OFFSET | Returns a reference of a specified height and width, located at a specified position relative to another specified reference, and takes the form =OFFSET(reference, rows, cols, height, width), where reference specifies the position from which the offset is calculated, rows and cols specify the vertical and horizontal distance from reference, and height and width specify the shape of the reference returned by the function. The rows and cols arguments can be positive or negative: Positive values specify offsets below and to the right of reference; negative values specify offsets above and to the left of reference. |
OR | Helps develop compound conditional test formulas in conjunction with logical operators and takes the form =OR(logical1, logical2, . . .), where the logicals can be up to 30 conditional tests, arrays, or references to cells that contain logical values. See "Using Selected Logical Functions" on page 508. |
PEARSON | Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1 to 1 (inclusive) and reflects the extent of a linear relationship between two data sets. This function takes the form =PEARSON(array1, array2), where array1 is a set of independent values, and array2 is a set of dependent values. |
PERCENTILE | Returns the member of an input range that is at a specified percentile ranking and takes the form =PERCENTILE(array, k), where array is the input range, and k is the rank you want to find. See "Using Functions That Analyze Rank and Percentile" on page 555. |
PERCENTRANK | Returns a percentile ranking for any member of a data set and takes the form =PERCENTRANK(array, x, significance), where array specifies the input range, x specifies the value whose rank you want to obtain, and the optional significance indicates the number of digits of precision you want. If significance is omitted, results are rounded to three digits (0.xxx or xx.x%). See "Using Functions That Analyze Rank and Percentile" on page 555. |
PERMUT | Returns the number of permutations for a given number of objects that can be selected from number objects and takes the form =PERMUT(number, number_chosen), where number is an integer that describes the number of objects, and number_chosen is an integer that describes the number of objects in each permutation. |
PHONETIC | Extracts, in Japanese, Simplified or Traditional Chinese, and Korean, the phonetic (furigana) characters from a referenced cell or range. Takes the form =PHONETIC(reference), where reference denotes a single cell or range. If reference is a range, the function returns phonetic text only from the cell in the upper-left corner. |
PI | Returns the value of pi, accurate to 14 decimal places (3.14159265358979), and takes the form =PI( ). It takes no arguments, but you must still type empty parentheses after the function name. To calculate the area of a circle, multiply the square of the circle's radius by the PI function. |
PMT | Computes the periodic payment required to amortize a loan over a specified number of periods and takes the form =PMT(rate, nper, pv, fv, type), where rate is the interest rate, nper is the term (periods) of the investment, pv is the investment value today, fv is the investment value at the end of the term, and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See "Calculating Investments" on page 535. |
POISSON | Returns the Poisson distribution and takes the form =POISSON(x, mean, cumulative), where x is the number of events; mean is the expected numeric value; and cumulative is a logical value that determines the form of the probability distribution returned (if TRUE, returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if FALSE, returns the Poisson probability mass function that the number of events occurring will be exactly x). |
POWER | Returns the result of a number raised to a power and takes the form =POWER(number, power), where number is the base number, and power is the exponent to which the base number is raised. |
PPMT | Computes the principal component of an individual payment made to repay a loan over a specified time period with constant periodic payments and a constant interest rate and takes the form =PPMT(rate, period, nper, pv, fv, type), where rate is the interest rate, period is the number of an individual periodic payment, nper is the term (periods) of the investment, pv is the investment value today, fv is the investment value at the end of the term, and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See "Calculating Investments" on page 535. |
PRICE | Calculates the price per $100 of a security that pays periodic interest and takes the form =PRICE(settlement, maturity, rate, yield, redemption, frequency, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; rate is the interest rate of the security at the issue date; yield is the annual yield of the security; redemption is the value of the security at redemption; frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly); and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
PRICEDISC | Returns the price per $100 of a discounted security and takes the form =PRICEDISC(settlement, maturity, discount, redemption, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; discount is the security's discount rate; redemption is the value of the security at redemption; and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
PRICEMAT | Returns the price per $100 of a security that pays interest at maturity and takes the form =PRICEMAT(settlement, maturity, issue, rate, yield, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; issue is the issue date of the security; rate is the interest rate of the security at the issue date; yield is the annual yield of the security; and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
PROB | Returns the probability that values in a range are between two limits and takes the form =PROB(x_range, prob_range, lower_limit, upper_limit), where x_range is the range of numeric values of x with which there are associated probabilities; prob_range is a set of probabilities associated with values in x_range; lower_limit is the lower bound on the value for which you want a probability; and upper_limit is the optional upper bound on the value for which you want a probability. |
PRODUCT | Multiplies all the numbers referenced by its arguments, takes the form =PRODUCT(number1, number2, . . .), and takes as many as 30 arguments. Text, logical values, and blank cells are ignored. See "Using Selected Mathematical Functions" on page 498. |
PROPER | Capitalizes the first letter in each word and any other letters in a text string that do not follow another letter-all other letters are converted to lowercase-and takes the form =PROPER(text). See "Using Selected Text Functions" on page 503. |
PV | Computes the present value of a series of equal periodic payments, or a lump-sum payment, and takes the form =PV(rate, nper, payment, future value, type), where rate is the interest rate; nper is the term (periods) of the investment; payment is the amount of each periodic payment when individual amounts are the same; future value is the investment value at the end of the term; and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See "Calculating Investments" on page 535. |
QUARTILE | Returns the value in an input range that represents a specified quarter-percentile and takes the form =QUARTILE(array, quart). For usage and argument details, see "The PERCENTILE and QUARTILE Functions" on page 557. |
QUOTIENT | Returns the integer portion of a division and takes the form =QUOTIENT(numerator, denominator), where numerator is the dividend and denominator is the divisor. |
RADIANS | Converts degrees to radians and takes the form =RADIANS(angle). angle represents an angle measured in degrees. |
RAND | Generates a random number between 0 and 1 and takes the form =RAND() with no arguments, but you must still type empty parentheses after the function name. The result changes with each sheet recalculation. See "Using Selected Mathematical Functions" on page 498. |
RANDBETWEEN | Generates random integer values between a specified range of numbers and takes the form =RANDBETWEEN(bottom, top), where bottom is the smallest, and top is the largest integer you want to use, inclusive. See "Using Selected Mathematical Functions" on page 498. |
RANK | Returns the ranked position of a particular number within a set of numbers and takes the form =RANK(number, ref, order). For usage and argument details, see "The RANK Function" on page 557. |
RATE | Calculates the rate of return of an investment that generates a series of equal periodic payments or a single lump-sum payment and takes the form =RATE(nper, payment, present value, future value, type, guess), where nper is the term (periods) of the investment; payment is the amount of each periodic payment when individual amounts are the same; present value is the investment value today; future value is the investment value at the end of the term; type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period); and guess is an approximate interest rate (assumes 10 percent if omitted). See "Calculating Investments" on page 535. |
RECEIVED | Calculates the amount received at maturity for a fully invested security and takes the form =RECEIVED(settlement, maturity, investment, discount, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; investment is the amount invested in the security; discount is the security's discount rate; and basis is the daycount basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
REPLACE | Substitutes one string of characters with another string and takes the form =REPLACE(old_text, start_num, num_chars, new_text), where old_text is the text string where you want to replace characters, start_num specifies the starting character to replace, num_chars specifies the number of characters to replace (counting from the left), and new_text specifies the text string to insert. See "Using the Substring Text Functions" on page 505. |
REPLACEB | Substitutes one string of characters with another string and takes the form =REPLACEB(old_text, start_num, num_bytes, new_text), where old_text is the text string in which you want to replace characters, start_num specifies the starting character to replace, num_bytes specifies the number of bytes to replace, and new_text specifies the text string to insert. This function is for use with double-byte characters. |
REPT | Fills a cell with a string of characters repeated a specified number of times and takes the form =REPT(text, number_times), where text specifies a string in double quotation marks, and number_times specifies how many times to repeat text. The result of the function cannot exceed 32,767 characters. |
RIGHT | Returns the rightmost series of characters from a string and takes the form =RIGHT(text, num_chars), where num_chars indicates how many characters you want to extract from the string (1, if omitted). Blank spaces count as characters. See "Using the Substring Text Functions" on page 505. |
RIGHTB | Returns the rightmost series of characters from a string, based on the number of bytes you specify, and takes the form =RIGHTB(text, num_bytes), where num_bytes indicates how many characters you want to extract from the string, based on bytes. This function is for use with double-byte characters. |
ROMAN | Converts an Arabic numeral to Roman numerals, as text, and takes the form =ROMAN(number, form), where number is the Arabic numeral you want converted, and form is a number specifying the type of Roman numeral you want (1, 2, or 3 = more concise notation; 4 or FALSE = simplified notation; TRUE = classic notation). |
ROUND | Rounds numbers to a specified number of decimal places and takes the form =ROUND(number, num_digits), where number can be a number, a reference to a cell that contains a number, or a formula that results in a number. num_digits can be any positive or negative integer and determines the number of decimal places. Type a negative num_digits to round to the left of the decimal; type zero to round to the nearest integer. See "Using the Rounding Functions" on page 500. |
ROUNDDOWN | Rounds numbers down to a specified number of decimal places and takes the same form and arguments as ROUND. See "Using the Rounding Functions" on page 500. |
ROUNDUP | Rounds numbers up to a specified number of decimal places and takes the same form and arguments as ROUND. See "Using the Rounding Functions" on page 500. |
ROW | Returns the row number of the referenced cell or range and takes the form =ROW(reference). If reference is omitted, the result is the row number of the cell containing the function. If reference is a range or a name and the function is entered as an array (by pressing Ctrl+Shift+Enter), the result is an array of the numbers of each of the rows or columns in the range. See "Using Selected Lookup and Reference Functions" on page 512. |
ROWS | Returns the number of rows in a reference or an array and takes the form =ROWS(array), where array is an array constant, a range reference, or a range name. See "Using Selected Lookup and Reference Functions" on page 512. |
RSQ | Returns the square of the Pearson product moment correlation coefficient through data points in the arrays known_y's and known_x's and takes the form =RSQ(known_y's, known_x's). |
RTD | Returns real-time data from a program that supports COM automation and takes the form =RTD(progID, server, topic1, topic2, . . .), where progID is the program identifier (enclosed in quotation marks) for a registered COM automation add-in that has been installed on the local computer, server is the name of the server where the add-in should be run (if other than the local computer), and topics are up to 28 parameters describing the real-time data you want. |
SEARCH | Returns the position of specified text within a string and takes the form =SEARCH(find_text, within_text, start_num), where find_text is the text you want to find, within_text indicates where to look, and start_num specifies the character position in within_text where you want to begin the search. See "Using the Substring Text Functions" on page 505. |
SEARCHB | Returns the position of specified text within a string, expressed in bytes; takes the form =SEARCHB(find_text, within_text, start_num); and is otherwise identical to SEARCH. |
SECOND | Returns the seconds portion of a serial date/time value and takes the form =SECOND(serial_number), where serial_number can be a time/date value, a reference, or text in time/date format enclosed in quotation marks. See "Working with Date and Time Functions" on page 531. |
SERIESSUM | Returns the sum of a power series and takes the form =SERIESSUM(x, n, m, coefficients), where x is the input value to the power series, n is the initial power to which you want to raise x, m is the step by which to increase n for each term in the series, and coefficients is a set of coefficients by which each successive power of x is multiplied. The number of values in coefficients determines the number of terms in the power series. |
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; takes the form =SIGN(number), where number is any real number. |
SIN | Returns the sine of an angle. The complement of the COS function, it takes the form =SIN(number), where number is the angle in radians. |
SINH | Returns the hyperbolic sine of a number and takes the form =SINH(number), where number is any real number. |
SKEW | Returns the skew of a distribution (the degree of asymmetry of a distribution around its mean), takes the form =SKEW(number1, number2, . . .), and accepts up to 30 arguments. |
SLN | Returns straight-line depreciation for an asset for a single period and takes the form =SLN(cost, salvage, life), where cost is the initial asset cost, salvage is the remaining value after asset is fully depreciated, and life is the length of depreciation time. See "Calculating Depreciation" on page 541. |
SLOPE | Returns the slope of a linear regression line and takes the form =SLOPE(known_y's, known_x's). For arguments and usage details, see "The SLOPE Function" on page 565. |
SMALL | Returns the kth smallest value in an input range and takes the form =SMALL(array, k), where k is the position from the smallest value in array you want to find. See "Using Functions That Analyze Rank and Percentile" on page 555. |
SQRT | Returns the positive square root of a number and takes the form =SQRT(number). |
SQRTPI | Returns the square root of (number * pi) and takes the form =SQRTPI(number). |
STANDARDIZE | Returns a normalized value from a distribution characterized by mean and standard_dev and takes the form =STANDARDIZE(x, mean, standard_dev), where x is the value you want to normalize, mean is the arithmetic mean of the distribution, and standard_dev is the standard deviation of the distribution. |
STDEV | Computes standard deviation, assuming that the arguments represent only a sample of the total population, and takes the form =STDEV(number1, number2, . . .), accepting up to 30 arguments. See "Using Sample and Population Statistical Functions" on page 558. |
STDEVA | Acts like STDEV except text and logical values are included in the calculation. See "Using Sample and Population Statistical Functions" on page 558. |
STDEVP | Computes the standard deviation, assuming that the arguments represent the total population, and takes the form =STDEVP(number1, number2, . . .). See "Using Sample and Population Statistical Functions" on page 558. |
STDEVPA | Acts like STDEVP except that text and logical values are included in the calculation. See "Using Sample and Population Statistical Functions" on page 558. |
STEYX | Calculates the standard error of a regression and takes the form =STEYX(known_y's, known_x's). For arguments and usage details, see "The SLOPE Function" on page 565. |
SUBSTITUTE | Replaces specified text with new text within a specified string and takes the form =SUBSTITUTE(text, old_text, new_text, instance_num), where text is the string you want to work on; old_text is the text to be replaced; new_text is the text to substitute; and instance_num is optional, indicating a specific occurrence of old_text within text. See "Using the Substring Text Functions" on page 505. |
SUBTOTAL | Returns a subtotal in a list or database and takes the form =SUBTOTAL(function_num, ref1, ref2, . . .), where function_num is a number that specifies which function to use in calculating subtotals (1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV, 8=STDEVP, 9=SUM, 10=VAR, 11=VARP), and the refs are 1 to 29 ranges or references for which you want the subtotal. |
SUM | Totals a series of numbers and takes the form =SUM(num1, num2, . . .), where the nums (max 30) can be numbers, formulas, ranges, or cell references. Ignores arguments that refer to text values, logical values, or blank cells. See "Using the SUM Function" on page 497. |
SUMIF | Tests each cell in a range before adding it to the total and takes the form =SUMIF(range, criteria, sum_range), where range is the range you want to test, criteria is the logical test to be performed on each cell, and sum_range specifies the cells to be totaled. See "Using Built-In Statistical Functions" on page 553. |
SUMIFS | Tests each cell in a range using multiple criteria before adding it to the total and takes the form =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, . . .), where sum_range is the range containing values you want to sum, criteria_rangeX is a cell range containing data to be evaluated, and criteriaX is a cell range containing values, expressions, references, or text that define which cells will be added to the total. |
SUMPRODUCT | Multiplies the value in each cell in a specified range by the corresponding cell in another equal-sized range and then adds the results. It takes the form =SUMPRODUCT (array1, array2, array3, . . .) and can include up to 30 arrays. Nonnumeric entries are treated as zero. See "Using Selected Mathematical Functions" on page 498. |
SUMSQ | Returns the sum of the squares of each specified value in a specified range, takes the form =SUMSQ(number1, number2, . . .), and takes up to 30 arguments or a single array or array reference. |
SUMX2MY2 | Calculates the sum of the differences of the squares of the corresponding values in x and y and takes the form =SUMX2MY2(array_x, array_y), where x and y are arrays that contain the same number of elements. |
SUMX2PY2 | Calculates the sum of the sum of the squares of the corresponding values in x and y and takes the form =SUMX2PY2(array_x, array_y), where x and y are arrays that contain the same number of elements. |
SUMXMY2 | Calculates the sum of the squares of the differences of the corresponding values in x and y and takes the form =SUMXMY2(array_x, array_y), where x and y are arrays that contain the same number of elements. |
SYD | Computes depreciation for a specific time period with the sum-of-the-years'-digits method and takes the form =SYD(cost, salvage, life, period), where cost is the initial asset cost, salvage is the remaining value after the asset is fully depreciated, life is the length of depreciation time, and period is the individual period to be computed. See "Calculating Depreciation" on page 541. |
T | Returns the text referred to by value and takes the form =T(value), where value is the value you want to test. This function is included for compatibility with other spreadsheet programs but is not necessary in Excel. |
TAN | Returns the tangent of an angle and takes the form =TAN(number), where number is the angle in radians. |
TANH | Returns the hyperbolic tangent of a number and takes the form =TANH(number), where number is any real number. |
TBILLEQ | Calculates the bond-equivalent yield for a U.S. Treasury bill and takes the form =TBILLEQ(settlement, maturity, discount), where settlement is the day you pay for the security, maturity is the maturity date of the security, and discount is the discount rate of the security. See "Analyzing Securities" on page 544. |
TBILLPRICE | Calculates the price per $100 of face value for a U.S. Treasury bill and takes the form =TBILLPRICE(settlement, maturity, discount), where settlement is the day you pay for the security, maturity is the maturity date of the security, and discount is the discount rate of the security. See "Analyzing Securities" on page 544. |
TBILLYIELD | Calculates a U.S. Treasury bill's yield and takes the form =TBILLYIELD(settlement, maturity, price), where settlement is the day you pay for the security, maturity is the maturity date of the security, and price is the security's price. See "Analyzing Securities" on page 544. |
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. This function takes the form =TDIST(x, degrees_freedom, tails), where x is the numeric value at which to evaluate the distribution; degrees_freedom is an integer indicating the number of degrees of freedom; and tails specifies the number of distribution tails to return (if 1, returns the one-tailed distribution; if 2, returns the two-tailed distribution). |
TEXT | Converts a number into a text string using a specified format and takes the form =TEXT(value, format_text), where value can be any number, formula, or cell reference, and format_text specifies the format using built-in custom formatting symbols. See "Using Selected Text Functions" on page 503. |
TIME | Returns the decimal number for a particular time and takes the form =TIME(hour, minute, second), where hour is a number from 0 (zero) to 23 representing the hour, minute is a number from 0 to 59 representing the minute, and second is a number from 0 to 59 representing the second. |
TIMEVALUE | Translates a time into a decimal value and takes the form =TIMEVALUE(time_text), where time_text represents a time entered as text in quotation marks. See "Working with Date and Time Functions" on page 531. |
TINV | Returns the t-value of the student's t-distribution as a function of the probability and the degrees of freedom and takes the form =TINV(probability, degrees_freedom), where probability is the probability associated with the two-tailed student's t-distribution, and degrees_freedom is the number of degrees of freedom to characterize the distribution. |
TODAY | Returns the serial value of the current date, takes the form =TODAY( ), and accepts no arguments. See "Working with Date and Time Functions" on page 531. |
TRANSPOSE | Changes the horizontal or vertical orientation of an array and takes the form =TRANSPOSE(array). If array is vertical, the result is horizontal, and vice versa. Must be entered as an array formula by pressing Ctrl+Shift+Enter, with a range selected with the same proportions as array. See "Using Selected Lookup and Reference Functions" on page 512. |
TREND | Returns values of points that lie along a linear trendline and takes the form =TREND(known_y's, known_x's, new_x's, const). For arguments and usage details, see "The TREND Function" on page 564. |
TRIM | Removes leading, trailing, and extra blank characters from a string, leaving single spaces between words, and takes the form =TRIM(text). See "Using Selected Text Functions" on page 503. |
TRIMMEAN | Returns the mean of the interior of a data set (the mean taken by excluding a percentage of data points from the top and bottom tails of a data set). This function takes the form =TRIMMEAN(array, percent), where array is the array or range of values to trim and average, and percent is the fractional number of data points to exclude from the calculation. |
TRUE | Represents an alternative for the logical condition TRUE, which accepts no arguments, and takes the form =TRUE( ). See "Using Selected Logical Functions" on page 508. |
TRUNC | Truncates everything to the right of the decimal point, regardless of its sign, and takes the form =TRUNC(number, num_digits). Truncates everything after the specified num_digits to the right of the decimal point. See "Using the Rounding Functions" on page 500. |
TTEST | Returns the probability associated with a student's t-test and takes the form =TTEST(array1, array2, tails, type), where array1 is the first data set; array2 is the second data set; tails specifies the number of distribution tails (if 1, uses the one-tailed distribution; if 2, uses the two-tailed distribution); and type is the kind of t-test to perform (1 = paired, 2 = two-sample equal variance, 3 = two-sample unequal variance). |
TYPE | Determines the type of value a cell contains and takes the form =TYPE(value). The result is one of the following numeric codes: 1 (number), 2 (text), 4 (logical value), 16 (error value), or 64 (array). See "Using Selected Lookup and Reference Functions" on page 512. |
UPPER | Converts a text string to all uppercase letters and takes the form =UPPER(text). See "Using Selected Text Functions" on page 503. |
VALUE | Converts a text string that represents a number to a number and takes the form =VALUE(text), where text is the text enclosed in quotation marks or a reference to a cell containing the text you want to convert. This function is included for compatibility with other spreadsheet programs but is not necessary in Excel. |
VAR | Computes variance, assuming that the arguments represent only a sample of the total population, and takes the form =VAR(number1, number2, . . .), accepting up to 30 arguments. See "Using Sample and Population Statistical Functions" on page 558. |
VARA | Acts like VAR except text and logical values are included in the calculation. See "Using Sample and Population Statistical Functions" on page 558. |
VARP | Computes variance, assuming that the arguments represent the total population, and takes the form =VARP(number1, number2, . . .). See "Using Sample and Population Statistical Functions" on page 558. |
VARPA | Acts like VARP except text and logical values are included in the calculation. See "Using Sample and Population Statistical Functions" on page 558. |
VDB | Calculates depreciation for any complete or partial period, using either double-declining balance or a specified accelerated-depreciation factor, and takes the form =VDB(cost, salvage, life, start_period, end_period, factor, no_switch), where cost is the initial asset cost, salvage is the remaining value after the asset is fully depreciated, life is the length of depreciation time, start_period is the period number after which depreciation begins, end_period is the last period calculated, factor is the rate at which the balance declines, and no_switch turns off the default switch to straight-line depreciation when it becomes greater than the declining balance. See "Calculating Depreciation" on page 541. |
VLOOKUP | Looks for a specified value in the leftmost column in a table, returns the value in the same row and a specified column, and takes the form =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), where lookup_value is the value to look for; table_array is the range containing the lookup and result values sorted in alphabetical order by the leftmost column; col_index_num is the column number containing the value you want to find; and range_lookup is a logical value, which, if false, forces an exact match. See "Using Selected Lookup and Reference Functions" on page 512. |
WEEKDAY | Returns a number value representing the day of the week for a specified date and takes the form =WEEKDAY(serial_number, return_type), where serial_number is a date value, a reference, or text in date form enclosed in quotation marks, and return_type determines the way the result is represented (if 1 or omitted, Sunday is day 1, if 2, Monday is day 1, if 3, Monday is day 0). See "Working with Date and Time Functions" on page 531. |
WEEKNUM | Returns a number that indicates where the week falls numerically within a year and takes the form =WEEKNUM(serial_num, return_type), where serial_num is a date within the week, and return_type is a number that determines the day on which the week begins (1 or omitted = week begins on Sunday, 2 = week begins on Monday). |
WEIBULL | Returns the Weibull distribution and takes the form =WEIBULL(x, alpha, beta, cumulative), where x is the value at which to evaluate the function, alpha is a parameter to the distribution, beta is a parameter to the distribution, and cumulative determines the form of the function. |
WORKDAY | Returns a date that is a specified number of working days before or after a given date and takes the form =WORKDAY(start_date, days, holidays), where start_date is the date you want to count from; days is the number of workdays before or after the start date, excluding weekends and holidays; and holidays is an array or reference containing any dates you want to exclude. See "Working with Specialized Date Functions" on page 533. |
XIRR | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic and takes the form =XIRR(values, dates, guess), where values is a series of cash flows that corresponds to a schedule of payments in dates, dates is a schedule of payment dates that corresponds to the cash flow payments, and guess is a number you think is close to the result. |
XNPV | Returns the net present value for a schedule of cash flows that is not necessarily periodic and takes the form =XNPV(rate, values, dates), where rate is the discount rate to apply to the cash flows, values is a series of cash flows that corresponds to a schedule of payments in dates, and dates is a schedule of payment dates that corresponds to the cash flow payments. |
YEAR | Returns the value of the year portion of a serial date/time value and takes the form =YEAR(serial_number), where serial_number can be a date value, a reference, or text in date format enclosed in quotation marks. See "Working with Date and Time Functions" on page 531. |
YEARFRAC | Returns a decimal number that represents the portion of a year that falls between two given dates and takes the form =YEARFRAC(start_date, end_date, basis), where start_date and end_date specify the span you want to convert to a decimal, and basis is the type of day count (0 or omitted = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360). See "Working with Specialized Date Functions" on page 531. |
YIELD | Determines the annual yield for a security that pays interest on a periodic basis and takes the form =YIELD(settlement, maturity, rate, price, redemption, frequency, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; rate is the interest rate of the security at the issue date; price is the security's price; redemption is the value of the security at redemption; frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly); and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
YIELDDISC | Calculates the annual yield for a discounted security and takes the form =YIELDDISC(settlement, maturity, price, redemption, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; price is the security's price; redemption is the value of the security at redemption; and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
YIELDMAT | Calculates the annual yield for a security that pays its interest at maturity and takes the form =YIELDMAT(settlement, maturity, issue, rate, price, basis), where settlement is the day you pay for the security; maturity is the maturity date of the security; issue is the issue date of the security; rate is the interest rate of the security at the issue date; price is the security's price; and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See "Analyzing Securities" on page 544. |
ZTEST | Returns the two-tailed P-value of a Z-test (generates a standard score for x with respect to the data set, array, and returns the two-tailed probability for the normal distribution). This function takes the form =ZTEST(array, x, sigma), where array is the array or range of data against which to test x, x is the value to test, and sigma is the known population's standard deviation. |