ABS

Returns the absolute value of a number. 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. Takes the form =ACCRINT(issue, first_interest, settlement, rate, par, frequency, basis, calc_method), 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), 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), and calc_method, if TRUE (1), calculates the accrued interest from issue date to settlement date, and if FALSE (0), calculates the interest accrued between first_interest and settlement. See Analyzing securities in Chapter 16.

ACCRINTM

Returns the interest accrued by a maturity security that pays interest at maturity. Takes the form =ACCRINTM(issue, settlement, rate, par, basis), where issue is the issue date of the security, settlement is the security’s settlement (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 in Chapter 16.

ACOS

Returns the arccosine (inverse cosine) of a number in radians. Takes the form =ACOS(number), where number is the cosine of an angle.

ACOSH

Returns the inverse hyperbolic cosine of a number. Takes the form =ACOSH(number), where number must be >=1.

ACOT

Returns the arccotangent (inverse cotangent) of a number. Takes the form =ACOT(number), where number must be a real number.

ACOTH

Returns the inverse hyperbolic arccotangent of a number. Takes the form =ACOTH(number), where number must be greater than 1.

ADDRESS

Builds references from numbers. Takes the form =ADDRESS(row_num, column_num, abs_num, a1, sheet_text), where 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; 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 in Chapter 14.

AGGREGATE

Returns an aggregate of a range or array by applying one of 19 aggregation functions with options to ignore hidden rows, error values, or both. For lists and ranges, it takes the form =AGGREGATE(function_num, options, ref1, ref2, ...). For arrays, it takes the form =AGGREGATE(function_num, options, array, k) Function numbers: 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV.S, 8 STDEV.P, 9 SUM, 10 VAR.S, 11 VAR.P, 12 MEDIAN, 13 MODE.SNGL, 14 LARGE, 15 SMALL, 16 PERCENTILE.INC, 17 QUARTILE.INC, 18 PERCENTILE.EXC, 19 QUARTILE.EXC. Options: 0 or omitted: Ignore nested SUBTOTAL and AGGREGATE functions; 1 Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions; 2 Ignore error values, nested SUBTOTAL and AGGREGATE functions; 3 Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions; 4 Ignore nothing; 5 Ignore hidden rows; 6 Ignore error values; 7 Ignore hidden rows and error values. For arrays, the following functions require a Ref2 argument: LARGE(array,k), SMALL(array,k), PERCENTILE.INC(array,k), QUARTILE.INC(array,quart), PERCENTILE.EXC(array,k), QUARTILE.EXC(array,quart).

AMORDEGRC

Returns the depreciation for each accounting period (French accounting system only), including any partial period. 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 in Chapter 14.

ARABIC

Converts a Roman numeral to an Arabic numeral. Takes the form =ARABIC(text), where text is a valid Roman numeral, or a reference to a cell containing one. Also, see the ROMAN function.

AREAS

Returns the number of areas in a reference (a cell or block of cells). 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 in Chapter 14.

ASC

Changes text in double-byte character set languages to single-byte characters. 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. Takes the form =ASIN(number), where number is the sine of the angle you want and must be from –1 to 1.

ASINH

Returns the inverse hyperbolic sine of a number. Takes the form =ASINH(number).

ATAN

Returns the arctangent of a number. 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. 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. 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. Accepts up to 30 arguments.

AVERAGE

Returns the arithmetic mean of the specified numbers. 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 in Chapter 17.

AVERAGEA

Acts like AVERAGE except text and logical values are included in the calculation. See Using built-in statistical functions in Chapter 17.

AVERAGEIF

Finds the arithmetic mean cells in the specified range that meet a given criteria. 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 upper-left cell of average_range as the beginning and the lower-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. Takes the form =BAHTTEXT(number), where number can be a reference to a cell containing a number or a formula that resolves to a number.

BASE

Converts a number into a text representation with the given base (radix). Takes the form =BASE(number, radix, min_length), where number is an integer >= 0 and <2⁵³; radix is the base that you want to convert the number into, which must be an integer >=2 and <=36. min_length is the optional minimum length of the returned string, which adds leading zeros if necessary to achieve the minimum length.

BESSELI

Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for imaginary arguments. 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. 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). 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.

BETA.DIST

Returns the cumulative beta probability density function. Takes the form =BETA.DIST(x, alpha, beta, cumulative, 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. If cumulative is TRUE, BETA.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. The old form of this function is BETADIST, which is still supported as a Compatibility Function.

BETA.INV

Returns the inverse of the cumulative beta probability density function. Takes the form =BETA.INV(probability, alpha, beta, A, B), where probability is a probability associated with the beta distribution. For additional argument descriptions, see BETA.DIST. The old form of this function is BETAINV, which is still supported as a Compatibility Function.

BIN2DEC

Converts a binary number to decimal. Takes the form =BIN2DEC(number), where number is the binary integer you want to convert.

BIN2HEX

Converts a binary number to hexadecimal. 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. 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

Supported as a Compatibility Function. See the new forms BINOM.DIST and BINOM.DIST.RANGE.

BINOM.DIST

Returns the individual term binomial distribution probability. Takes the form =BINOM.DIST(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 cumulative is TRUE, BINOM.DIST returns the probability that there are at most number_s successes; if cumulative is FALSE, BINOM.DIST returns the probability that there are number_s successes. The old form of this function is BINOMDIST, which is still supported as a Compatibility Function.

BINOM.DIST.RANGE

Returns the probability of a trial result using a binomial distribution. Takes the form =BINOM.DIST.RANGE(trials, probability_s, number_s, number_s2), where trials is the number of independent trials and must be >= 0; probability_s is probability of success in each trial and must be >=0 and <=1; number_s is the number of successes in trials and must be >=0 and <= Trials; optionally, Number_s2 returns the probability that the number of successful trials will fall between Number_s and number_s2 and must be >=Number_s and <=Trials.

BINOM.INV

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. Takes the form =BINOM.INV(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. The old form of this function is CRITBINOM, which is still supported as a Compatibility Function.

BITAND

Compares the representations of two numbers in binary form, returning a number indicating the bit positions that do not match. Takes the form =BITAND(number1, number2), where both numbers must be in decimal form and >=0.

BITLSHIFT

Returns a number that has been shifted to the left by a specified number of bits by adding zeros to the right of the number in binary form. Takes the form =BITLSHIFT(number, shift_amount), where number is an integer >= 0 and Shift_amount is an integer.

BITOR

Compares the representations of two numbers in binary form, returning a number indicating the bit positions that match. Takes the form =BITOR(number1, number2), where both numbers must be in decimal form and >=0.

BITRSHIFT

Returns a number that has been shifted to the right by a specified number of bits by removing digits to the right of the number in binary form. Takes the form =BITRSHIFT(number, shift_amount), where number is an integer >= 0 and Shift_amount is an integer.

BITXOR

Compares the representations of two numbers in binary form, returning a number indicating the result of an “exclusive OR” of its arguments. Takes the form =BITXOR(number1, number2), where number1 and number2 must be in decimal form and >=0.

CEILING

Rounds a number to the nearest given multiple of significance. Takes the form =CEILING(number, significance), where number and significance must be numeric and have the same sign. If they have different signs, Excel returns the #NUM! error value. See Using the rounding functions in Chapter 14.

CEILING.MATH

Rounds a number up to the nearest integer or the nearest multiple of significance. Takes the form =CEILING.MATH(number, significance, mode), where number must be less than 9.99E+307 and greater than –2.229E–308; optionally, significance is the multiple to which number should be rounded; optionally, mode indicates how negative numbers are handled and has no effect on positives. A mode of 1 rounds toward zero, and a mode of –1 rounds away from zero. This function replaces the CEILING.PRECISE function.

CELL

Returns information about the contents, location, or formatting of a cell. 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. The info_type argument can be any of the following: address, col (column #), color, contents, filename, format, parentheses, prefix, protect, row, type, or width. See Excel’s Help for a table of format codes returned.

CHAR

Returns the character that corresponds to an ASCII code number. Takes the form =CHAR(number), where number accepts ASCII codes with or without leading zeros. See Using selected text functions in Chapter 14.

CHIDIST

Supported as a Compatibility Function. See the new forms CHISQ.DIST and CHISQ.DIST.RT.

CHISQ.DIST

Returns the chi-squared distribution. Takes the form =CHISQ.DIST(X, deg_freedom, cumulative), where X is the value at which you want to evaluate the distribution and deg_freedom is the number of degrees of freedom. If cumulative is TRUE, CHISQ.DIST returns the cumulative distribution function, otherwise, it returns the probability density function. The old form of this function is CHIDIST, which is still supported as a Compatibility Function.

CHISQ.DIST.RT

Returns the right-tailed probability of the chi-squared distribution. Takes the form =CHISQ.DIST.RT(X, deg_freedom), where X is the value at which you want to evaluate the distribution and deg_freedom is the number of degrees of freedom. The old form of this function is CHIDIST, which is still supported as a Compatibility Function.

CHIINV

Supported as a Compatibility Function. See the new forms CHISQ.INV and CHISQ.INV.RT.

CHISQ.INV

Returns the inverse of the left-tailed probability of the chi-squared distribution. Takes the form =CHISQ.INV(probability, deg_freedom), where probability is a probability associated with the chi-squared distribution and deg_freedom is the number of degrees of freedom. The old form of this function is CHIINV, which is still supported as a Compatibility Function.

CHISQ.INV.RT

Returns the inverse of the right-tailed probability of the chi-squared distribution. Takes the form =CHISQ.INV.RT(probability, deg_freedom), where probability is a probability associated with the chi-squared distribution and deg_freedom is the number of degrees of freedom. The old form of this function is CHIINV, which is still supported as a Compatibility Function.

CHITEST

Supported as a Compatibility Function. See the new form CHISQ.TEST.

CHISQ.TEST

Returns the test for independence. Takes the form =CHISQ.TEST(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. The old form of this function is CHITEST, which is still supported as a Compatibility Function.

CHOOSE

Retrieves an item from a list of values. 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 in Chapter 14.

CLEAN

Removes nonprintable characters such as tabs and program-specific codes from a string. Takes the form =CLEAN(text). See Using selected text functions in Chapter 14.

CODE

Returns the ASCII code number for the first character of its argument. Takes the form =CODE(text). See Using selected text functions in Chapter 14.

COLUMN

Returns the column number of the referenced cell or range. 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 in Chapter 14.

COLUMNS

Returns the number of columns in a reference or an array. 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 in Chapter 14.

COMBIN

Determines the number of possible group combinations that can be derived from a pool of items. 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 in Chapter 14.

COMBINA

Determines the number of possible group combinations, including repetitions, that can be derived from a pool of items. Takes the form =COMBINA(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.

COMPLEX

Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj. 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 in Chapter 14.

CONFIDENCE

Supported as a Compatibility Function. See the new forms CONFIDENCE.NORM and CONFIDENCE.T.

CONFIDENCE.NORM

Returns the confidence interval for a population mean using a normal distribution. Takes the form =CONFIDENCE.NORM(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. The old form of this function is CONFIDENCE, which is still supported as a Compatibility Function.

CONFIDENCE.T

Returns the confidence interval for a population mean using a Student’s t distribution. Takes the form =CONFIDENCE.T(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. The old form of this function is CONFIDENCE, which is still supported as a Compatibility Function.

CONVERT

Converts a number from one measurement system to another. 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 Excel’s Help for a table of unit codes.

CORREL

Returns the correlation coefficient of the array1 and array2 cell ranges. 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. Takes the form =COS(number), where number is the angle in radians.

COSH

Returns the hyperbolic cosine of a number. Takes the form =COSH(number), where number is any real number.

COT

Returns the cotangent of an angle in radians. Takes the form =COT(number), where number is any real number.

COTH

Returns the hyperbolic cotangent of an angle in radians. Takes the form =COTH(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 in Chapter 17.

COUNTA

Acts like COUNT except text and logical values are included in the calculation. Takes the form =COUNT(value1, value2, ...). See Using built-in statistical functions in Chapter 17.

COUNTBLANK

Counts empty cells in a specified range. Takes the form =COUNTBLANK(range). See Using selected lookup and reference functions in Chapter 14.

COUNTIF

Counts only cells that match specified criteria. 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 in Chapter 17.

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. 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 in Chapter 16.

COUPDAYS

Calculates the number of days in the coupon period that contains the settlement date. Takes the form =COUPDAYS(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See Analyzing securities in Chapter 16.

COUPDAYSNC

Calculates the number of days from the settlement date to the next coupon date. Takes the form =COUPDAYSNC(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See Analyzing securities in Chapter 16.

COUPNCD

Calculates the next coupon date after the settlement date. Takes the form =COUPNCD(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See Analyzing securities in Chapter 16.

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 in Chapter 16.

COUPPCD

Calculates the coupon date previous to the settlement date. Takes the form =COUPPCD(settlement, maturity, frequency, basis). See COUPDAYBS for argument definitions. See Analyzing securities in Chapter 16.

COVAR

Supported as a Compatibility Function. See the new forms COVARIANCE.P and COVARIANCE.S.

COVARIANCE.P

Returns population covariance, the average of the products of deviations for each data point pair in two data sets. Takes the form =COVARIANCE.P(array1, array2), where array values are cell ranges containing integers. The old form of this function is COVAR, which is still supported as a Compatibility Function.

COVARIANCE.S

Returns sample covariance, the average of the products of deviations for each data point pair in two data sets. Takes the form =COVARIANCE.S(array1, array2), where the array values are cell ranges containing integers. The old form of this function is COVAR, which is still supported as a Compatibility Function.

CRITBINOM

See BINOM.INV

CSC

Returns the cosecant of an angle in radians. Takes the form =CSC(number), where number is any real number.

CSCH

Returns the hyperbolic cosecant of an angle in radians. Takes the form =CSCH(number), where number is any real number.

CUBEKPIMEMBER

Returns a key performance indicator (KPI) property and the name of the KPI. Takes the form =CUBEKPIMEMBER(connection, kpi_name, kpi_property, caption), where 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 KPICurrentTimeMember). This function is supported only when Excel is 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. Takes the form =CUBEMEMBER(connection, member_expression, caption), 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 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. 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. Takes the form =CUBERANKEDMEMBER(connection, set_expression, rank, caption), where 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. 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 of 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. 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. Takes the form =CUBEVALUE(connection, member_expression1, member_expression2, ...), where 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. 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. 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. 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. 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 in Chapter 15.

DAVERAGE

Averages the values in a column in a table or database that match conditions you specify. Takes the form =DAVERAGE(database, field, criteria), where database is the range of cells that make up the table or database and the first row of the table 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. 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 in Chapter 15.

DAYS

Returns the number of days between two dates. Takes the form =DAYS(end_date, start_date), where both arguments are serial date values or references to cells containing valid date values.

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. 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 method is FALSE or omitted, the function uses the U.S. (NASD) method; if method is TRUE, the function uses the European method.

DB

Computes fixed declining balance depreciation for a particular period in the asset’s life. 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 in Chapter 16.

DCOUNT

Counts the cells that contain numbers in a column in a table or database that match conditions you specify. Takes the form =DCOUNT(database, field, criteria), where database is the range of cells that make up the table 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. 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 in Chapter 16.

DECIMAL

Converts numeric text to a decimal number in a given base. Takes the form =DECIMAL(text, radix), where text is the numeric text you want to convert and radix is an integer specifying a base from 2 to 36.

DEC2BIN

Converts a decimal number to binary. 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. Takes the same form and arguments as DEC2BIN.

DEC2OCT

Converts a decimal number to octal. Takes the same form and arguments as DEC2BIN.

DEGREES

Converts radians to degrees. Takes the form =DEGREES(angle), where angle represents an angle measured in radians.

DELTA

Tests whether two values are equal. 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. Accepts up to 30 arguments.

DGET

Extracts a single value from a column in a table or database that matches conditions you specify. Takes the form =DGET(database, field, criteria), where database is the range of cells that make up the table 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. 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 of 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 in Chapter 16.

DMAX

Returns the largest number in a column in a table or database that matches conditions you specify. Takes the form =DMAX(database, field, criteria), where database is a range that makes up the table 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 table or database that matches conditions you specify. 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. 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 in Chapter 14.

DOLLARDE

Converts the familiar fractional pricing of securities to decimals. 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 in Chapter 16.

DOLLARFR

Converts a security price expressed in decimals to fractions. 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 in Chapter 16.

DPRODUCT

Multiplies the values in a column in a table or database that match conditions you specify. Takes the form =DPRODUCT(database, field, criteria), where database is a range that makes up the table 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 table or database that match conditions you specify. 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 table or database that match conditions you specify. Takes the same form and arguments as DPRODUCT.

DSUM

Adds the numbers in a column in a table or database that match conditions you specify. Takes the same form and arguments as DPRODUCT.

DURATION

Calculates the weighted average of the present value of a bond’s cash flows for a security whose interest payments are made on a periodic basis. 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 in Chapter 16.

DVAR

Estimates the variance of a population based on a sample, using the numbers in a column in a table or database that match conditions you specify. Takes the form =DVAR(database, field, criteria), where database is the range of cells that make up the table 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 table or database that match conditions you specify. 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. 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 in Chapter 15.

EFFECT

Returns the effective interest rate. Takes the form =EFFECT(nominal_rate, npery), where nominal_rate is the annual interest rate and npery is the number of annual compounding periods.

ENCODEURL

Returns a specified string to be encoded as a Universal Resource Locator (URL) for web use. Takes the form =ENCODEURL(text).

EOMONTH

Returns a date that falls on the last day of the month an indicated number of months before or after a given date. 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 in Chapter 15.

ERF

Returns the error function integrated between lower_limit and upper_limit. Takes the form =ERF(lower_limit, upper_limit), where lower_limit is the lower bound and upper_limit is the upper bound. If upper_limit is omitted, ERF integrates between zero and lower_limit.

ERF.PRECISE

Returns the error function. Takes the form =ERF.PRECISE(x), where x is the lower bound.

ERFC

Returns the complementary ERF function integrated between x and infinity. Takes the form =ERFC(x), where x is the lower bound for integrating ERF.

ERFC.PRECISE

Returns the precise complementary ERF function integrated between x and infinity. Takes the form =ERFC.PRECISE(x), where x is the lower bound for integrating ERF.

ERROR.TYPE

Detects the type of error value in a referenced cell. Takes the form =ERROR.TYPE(error_val), and 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 in Chapter 14.

EUROCONVERT

Converts a number to euros—or converts any EU member currency to euros or any other member currency. 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 uses a currency-specific rounding factor when FALSE, and triangulation_precision is an integer equal to or greater than 3 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 Excel’s Help for tables of ISO codes and rounding factors.

EVEN

Rounds a number up to the nearest even integer. Takes the form =EVEN(number). Negative numbers are correspondingly rounded down. See Using the rounding functions in Chapter 14.

EXACT

Determines whether two strings match exactly, including uppercase and lowercase letters but not including formatting differences. 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 in Chapter 14.

EXP

Computes the value of the constant e (approx. 2.71828183) raised to the power specified by its argument. Takes the form =EXP(number). The EXP function is the inverse of the LN function.

EXPON.DIST

Returns exponential distribution. Takes the form =EXPON.DIST(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 cumulative is TRUE, EXPON.DIST returns the cumulative distribution function; if cumulative is FALSE, it returns the probability density function.) The old form of this function is EXPONDIST, which is still supported as a Compatibility Function.

FACT

Returns the factorial of a number. Takes the form =FACT(number), where number is a positive integer.

FACTDOUBLE

Returns the double factorial of a number. Takes the form =FACTDOUBLE(number), where number is a positive integer.

FALSE

Represents an alternative for the logical condition FALSE. The FALSE function accepts no arguments and takes the form =FALSE(). See Using selected logical functions in Chapter 14.

F.DIST

Returns the F probability distribution. Takes the form =F.DIST(x, deg_freedom1, deg_freedom2, cumulative), where x is the value at which to evaluate the function, deg_freedom1 is the numerator degrees of freedom, and deg_freedom2 is the denominator. If cumulative is TRUE, F.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. The old form of this function is FDIST, which is still supported as a Compatibility Function.

F.DIST.RT

Returns the right-tailed F probability distribution. Takes the form =F.DIST.RT(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. The old form of this function is FDIST, which is still supported as a Compatibility Function.

FIND

Returns the position of specified text within a string. 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. The 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 in Chapter 14.

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 character sets, such as Chinese, Japanese, and Korean.

F.INV

Returns the inverse of the F probability distribution. Takes the form =F.INV(probability, deg_freedom1, deg_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. The old form of this function is FINV, which is still supported as a Compatibility Function.

F.INV.RT

Returns the inverse of the right-tailed F probability distribution. Takes the form =F.INV.RT(probability, deg_freedom1, deg_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. The old form of this function is FINV, which is still supported as a Compatibility Function.

FILTERXML

Returns data from XML content using a specified XPath expression. Takes the form =FILTERXML(xml, xpath), where xml is a valid XML string and xpath is a valid Xpath expression.

FISHER

Returns the Fisher transformation at x. Takes the form =FISHER(x), where x is a value between –1 and 1 (not inclusive).

FISHERINV

Returns the inverse of the Fisher transformation. 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. 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 (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. Takes the form =FLOOR(number, significance), where number and significance must be numeric and have the same sign. If they have different signs, Excel returns the #NUM! error value. See Using the rounding functions in Chapter 14.

FLOOR.MATH

Rounds a number down to the nearest integer or the nearest multiple of significance. Takes the form =FLOOR.MATH(number, significance, mode), where number is the number to be rounded down; optionally, significance is the multiple to which number should be rounded; optionally, mode indicates how negative numbers are handled and has no effect on positives. A mode of 1 rounds up toward zero, and a mode of –1 rounds down away from zero. This function replaces the old FLOOR.PRECISE function.

FORECAST

Returns a single point along a trend line. Takes the form =FORECAST(x, known_y’s, known_x’s). For arguments and usage details, see The FORECAST function in Chapter 17.

FORMULATEXT

Returns a formula as displayed in the formula bar, as a text string. Takes the form =FORMULATEXT(reference), where reference is a cell or range reference.

FREQUENCY

Returns the number of times that values occur within a population. Takes the form =FREQUENCY(data_array, bins_array). For usage and argument details, see Analyzing distribution with the FREQUENCY function in Chapter 17.

F.TEST

Returns the result of an F-test, the one-tailed probability that the variances in array1 and array2 are not significantly different. Takes the form =F.TEST(array1, array2). The old form of this function is FTEST, which is still supported for compatibility.

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 in Chapter 16.

FVSCHEDULE

Returns the future value of an initial principal after applying a series of variable compound interest rates. Takes the form =FVSCHEDULE(principal, schedule), where principal is the present value and schedule is an array of interest rates to apply.

GAMMA

Supported as a Compatibility Function. See the new form GAMMA.DIST.

GAMMA.DIST

Returns the gamma distribution. Takes the form =GAMMA.DIST(x, alpha, beta, cumulative), where x is the value at which you want to evaluate the distribution, alpha is a parameter to the distribution, and beta is a parameter to the distribution. If cumulative is TRUE, GAMMA.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. The old form of this function is GAMMADIST, which is still supported as a Compatibility Function.

GAMMA.INV

Returns the inverse of the gamma cumulative distribution. Takes the form =GAMMA.INV(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. The old form of this function is GAMMAINV, which is still supported as a Compatibility Function.

GAMMALN

Returns the natural logarithm of the gamma function. Takes the form =GAMMALN(x), where x is a positive value.

GAMMALN.PRECISE

Returns the natural logarithm of the gamma function. Takes the form =GAMMALN.PRECISE(x), where x is a positive value.

GAUSS

Returns the percent probability that a member of a normal population will fall between the mean and z standard deviations from the mean. Takes the form =GAUSS(z), where z is the number of standard deviations to use.

GCD

Returns the greatest common divisor of two or more integers (the largest integer that divides both number1 and number2 without a remainder). 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. 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). 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. 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. Takes the form =GROWTH(known_y’s, known_x’s, new_x’s, const). For arguments and usage details, see The GROWTH function in Chapter 17.

HARMEAN

Returns the harmonic mean of a data set. Takes the form =HARMEAN(number1, number2, ...), where the numbers are 1 to 30 positive values.

HEX2BIN

Converts a hexadecimal number to binary. 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. (It’s useful for padding the return value with leading zeros.)

HEX2DEC

Converts a hexadecimal number to decimal. Takes the form =HEX2DEC(number), where number is the hexadecimal number you want to convert.

HEX2OCT

Converts a hexadecimal number to octal. 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. (It’s useful for padding the return value with leading zeros.)

HLOOKUP

Looks for a specified value in the top row in a table, and returns the value in the same column and a specified row. 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 in Chapter 14.

HOUR

Returns the hour portion of a serial date/time value. 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 in Chapter 15.

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. 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.

HYPGEOM.DIST

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). Takes the form =HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative), 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_pop is the population size. If cumulative is TRUE, HYPGEOM.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. The old form of this function is HYPGEOMDIST, which is still supported as a Compatibility Function.

IF

Returns values based on supplied conditional tests. 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 in Chapter 14.

IFERROR

Returns a specified value when a formula evaluates to an error. 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.

IFNA

Returns a specified value when a formula evaluates to #N/A. Takes the form =IFNA(value, value_if_na), where value refers to the argument you want to check and value_if_na is the value you want to display if value returns #N/A.

IMABS

Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format. 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. 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. 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. 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. Takes the form =IMCOS(inumber), where inumber is a complex number for which you want the cosine.

IMCOSH

Returns the hyperbolic cosine of a complex number in x + yi or x + yj text format. Takes the form =IMCOSH(inumber), where inumber is a complex number for which you want the hyperbolic cosine.

IMCOT

Returns the cotangent of a complex number in x + yi or x + yj text format. Takes the form =IMCOT(inumber), where inumber is a complex number for which you want the cotangent.

IMCSC

Returns the cosecant of a complex number in x + yi or x + yj text format. Takes the form =IMCSC(inumber), where inumber is a complex number for which you want the cosecant.

IMCSCH

Returns the hyperbolic cosecant of a complex number in x + yi or x + yj text format. Takes the form =IMCSCH(inumber), where inumber is a complex number for which you want the hyperbolic cosecant.

IMDIV

Returns the quotient of two complex numbers in x + yi or x + yj text format. 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. 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. 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. 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. 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. 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. 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. Takes the form =IMREAL(inumber), where inumber is a complex number for which you want the real coefficient.

IMSEC

Returns the secant of a complex number in x + yi or x + yj text format. Takes the form =IMSEC(inumber), where inumber is a complex number for which you want the secant.

IMSECH

Returns the hyperbolic secant of a complex number in x + yi or x + yj text format. Takes the form =IMSECH(inumber), where inumber is a complex number for which you want the hyperbolic secant.

IMSIN

Returns the sine of a complex number in x + yi or x + yj text format. Takes the form =IMSIN(inumber), where inumber is a complex number for which you want the sine.

IMSINH

Returns the hyperbolic sine of a complex number in x + yi or x + yj text format. Takes the form =IMSINH(inumber), where inumber is a complex number for which you want the hyperbolic sine.

IMSQRT

Returns the square root of a complex number in x + yi or x + yj text format. 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. 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. Takes the form =IMSUM(inumber1, inumber2, ...), where the inumbers are 1 to 29 complex numbers to add.

IMTAN

Returns the tangent of a complex number in x + yi or x + yj text format. Takes the form =IMTAN(inumber), where inumber is a complex number for which you want the tangent.

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 in Chapter 14.

INDIRECT

Returns the contents of a cell using its reference. 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 in Chapter 14.

INFO

Returns information about the current operating environment. 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 Excel’s Help for more information.

INT

Rounds numbers down to the nearest integer. Takes the form =INT(number). When number is negative, INT also rounds that number down to the nearest integer. See Using the rounding functions in Chapter 14.

INTERCEPT

Calculates the point at which a line intersects the y axis by using existing x values and y values. 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. 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 in Chapter 16.

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. 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 in Chapter 16.

IRR

Returns the rate that causes the present value of the inflows from an investment to exactly equal the cost of the investment. 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 (10 percent if omitted). See Calculating investments in Chapter 16.

ISBLANK

Returns TRUE if the referenced cell is empty; otherwise, returns FALSE. Uses the form =ISBLANK(value). See Using the IS information functions in Chapter 14.

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 in Chapter 14.

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 in Chapter 14.

ISEVEN

Returns TRUE if the value is an even number; otherwise, returns FALSE. Uses the form =ISEVEN(number). See Using the IS information functions in Chapter 14.

ISFORMULA

Returns TRUE if the value is a formula; otherwise, returns FALSE. Uses the form =ISFORMULA(reference). See Using the IS information functions in Chapter 14.

ISLOGICAL

Returns TRUE if the value is a logical value; otherwise, returns FALSE. Uses the form =ISLOGICAL(value). See Using the IS information functions in Chapter 14.

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 in Chapter 14.

ISNONTEXT

Returns TRUE if the value is not text; otherwise, returns FALSE. Uses the form =ISNONTEXT(value). See Using the IS information functions in Chapter 14.

ISNUMBER

Returns TRUE if the value is a number; otherwise, returns FALSE. Uses the form =ISNUMBER(value). See Using the IS information functions in Chapter 14.

ISODD

Returns TRUE if the value is an odd number; otherwise, returns FALSE. Uses the form =ISODD(number). See Using the IS information functions in Chapter 14.

ISOWEEKNUM

Returns a number that indicates where the week falls numerically within a year, according to the European ISO standard, where the week containing the first Thursday of the year is the first week of the year. Takes the form =ISOWEEKNUM(date), where date is any valid date.

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 in Chapter 14.

ISTEXT

Returns TRUE if the value is text; otherwise, returns FALSE. Uses the form =ISTEXT(value). See Using the IS information functions in Chapter 14.

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. Takes the form =LARGE(array, k), where k is the position from the largest value in array you want to find. See Using functions that analyze rank and percentile in Chapter 17.

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. 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 in Chapter 14.

LEFTB

Returns the leftmost series of characters from a string, based on the specified number of bytes. Takes the form =LEFT(text, num_bytes), where num_bytes indicates how many characters you want to extract from the text string, based on bytes. This function is for use with double-byte character sets such as Chinese, Japanese, and Korean.

LEN

Returns the number of displayed characters in an entry. 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 in Chapter 14.

LENB

Returns the number of characters in an entry, expressed in bytes. 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. Takes the form LINEST(known_y’s, known_x’s, const, stats). For arguments and usage details, see The LINEST function in Chapter 17.

LN

Returns the natural (base e) logarithm of the positive number referred to by its argument. 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. 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. Takes the form =LOG10(number), where number is a positive real number.

LOGEST

Returns statistics describing known data in terms of an exponential curve. Takes the form =LOGEST(known_y’s, known_x’s, const, stats). For arguments and usage details, see The LOGEST function in Chapter 17.

LOGNORM.DIST

Returns the cumulative lognormal distribution of x, where ln(x) is usually distributed with parameters mean and standard_dev. Takes the form =LOGNORMDIST(x, mean, standard_dev, cumulative), 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). If cumulative is TRUE, LOGNORM.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. The old form of this function is LOGNORMDIST, which is still supported as a Compatibility Function.

LOGNORM.INV

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is usually distributed with parameters mean and standard_dev. 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). The old form of this function is LOGINV, which is still supported as a Compatibility Function.

LOOKUP

Looks for a specified value in a one-dimensional or two-dimensional range. 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 or like VLOOKUP if array is taller than it is wide. See Using selected lookup and reference functions in Chapter 14.

LOWER

Converts a text string to all lowercase letters. Takes the form =LOWER(text). See Using selected text functions in Chapter 14.

MATCH

Returns the position in a list of the item that most closely matches a lookup value. 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 in Chapter 14.

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 in Chapter 17.

MAXA

Acts like MAX except text and logical values are included in the calculation. See Using built-in statistical functions in Chapter 17.

MDETERM

Returns the matrix determinant of an array. 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. 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 in Chapter 16.

MEDIAN

Computes the median of a set of numbers. Takes the form =MEDIAN(number1, number2, ...), and accepts up to 30 arguments, ignoring text, error values, and logical values. See Using built-in statistical functions in Chapter 17.

MID

Extracts a series of characters (substring) from a text string. 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 in Chapter 14.

MIDB

Extracts a series of characters (substring) from a text string, based on the number of bytes you specify. 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 double-byte 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 in Chapter 17.

MINA

Acts like MIN except text and logical values are included in the calculation. See Using built-in statistical functions in Chapter 17.

MINUTE

Returns the minute portion of a serial date/time value. 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 in Chapter 15.

MINVERSE

Returns the inverse matrix for the matrix stored in an array. 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. 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 in Chapter 16.

MMULT

See MODE.MULT

MOD

Returns the remainder of a division operation (modulus). 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 in Chapter 14.

MODE.MULT

Returns a vertical array of the most frequently occurring values in a set of numbers. Takes the form =MODE.MULT(number1, number2, ...), and accepts up to 30 arguments, ignoring text, error values, and logical values. The old form of this function is MODE, which is still supported as a Compatibility Function. See Using built-in statistical functions in Chapter 17.

MODE.SNGL

Determines which value occurs most frequently in an array or range. Takes the form =MODE.SNGL(number1, number2, ...), and accepts up to 30 arguments, ignoring text, error values, and logical values. The old form of this function is LOGNORMDIST, which is still supported as a Compatibility Function. See Using built-in statistical functions in Chapter 17.

MONTH

Returns the value of the month portion of a serial date/time value. 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 in Chapter 15.

MROUND

Rounds any number to a multiple you specify. 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 in Chapter 14.

MULTINOMIAL

Returns the ratio of the factorial of a sum of values to the product of factorials. Takes the form =MULTINOMIAL(num1, num2, ...), where nums are up to 29 values for which you want to find the multinomial.

MUNIT

Returns a unit matrix of specified dimensions as an array. Takes the form =MUNIT(dimension), where dimension is an integer specifying the dimensions of the desired unit matrix.

N

Returns a value converted to a number. Takes the form =N(value), where value is the value you want to convert. 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. The NA function accepts no arguments and takes the form =NA().

NEGBINOM.DIST

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). Takes the form =NEGBINOM.DIST(number_f, number_s, probability_s, cumulative), 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. If cumulative is TRUE, NEGBINOM.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. The old form of this function is NEGBINOMDIST, which is still supported as a Compatibility Function.

NETWORKDAYS

Returns the number of working days between two given dates. 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 in Chapter 15.

NETWORKDAYS.INTL

Returns the number of working days between two given dates, and allows you to indicate specific days as weekends. Takes the form =NETWORKDAYS.INTL(start_date, end_date, weekend, holidays), where start_date is the date you want to count from, end_date is the date you want to count to, weekend is a number indicating the weekend days you want to use, and holidays is an array or reference containing any dates you want to exclude. Weekend numbers: 1 or omitted, Sat-Sun; 2, Sun-Mon; 3 Mon-Tue; 4 Tue-Wed; 5 Wed-Thu; 6 Thu-Fri; 7 Fri-Sat; 11 Sun only; 12 Mon only; 13 Tue only; 14 Wed only; 15 Thu only; 16 Fri only; 17 Sat only. See Working with specialized date functions in Chapter 15.

NOMINAL

Returns the nominal annual interest rate. 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.

NORM.DIST

Returns the normal cumulative distribution for the specified mean and standard deviation. Takes the form =NORM.DIST(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 cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if cumulative is FALSE, it returns the probability density function.) The old form of this function is NORMDIST, which is still supported as a Compatibility Function.

NORM.INV

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. Takes the form =NORM.INV(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. The old form of this function is NORMINV, which is still supported as a Compatibility Function.

NORMSDIST

Supported as a Compatibility Function. See the new form NORM.S.DIST.

NORM.S.DIST

Returns the standard normal cumulative distribution function. Takes the form =NORM.S.DIST(z, cumulative), where z is the value you want to use, and cumulative, if TRUE returns the cumulative distribution function, and if FALSE returns the probability mass function. The old form of this function is NORMSDIST, which is still supported as a Compatibility Function.

NORM.S.INV

Returns the inverse of the standard normal cumulative distribution (with a mean of zero and a standard deviation of one). Takes the form =NORM.S.INV(probability), where probability is a probability corresponding to the normal distribution. The old form of this function is NORMSINV, which is still supported as a Compatibility Function.

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 in Chapter 14.

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 in Chapter 15.

NPER

Computes the number of periods required to amortize a loan, given a specified periodic payment. 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 in Chapter 16.

NPV

Determines the profitability of an investment. 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 in Chapter 16.

NUMBERVALUE

Converts text to a number. Takes the form =NUMBERVALUE(text, decimal_separator, group_separator), where text is the text you want to convert, decimal_separator is optionally the character you want to use to separate the integer and decimal, and group_separator is optionally the character you want to use to separate groups of numbers (to separate thousands from millions, for example).

OCT2BIN

Converts an octal number to binary. 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, OCT2BIN uses the minimum number of characters necessary.)

OCT2DEC

Converts an octal number to decimal. Takes the form =OCT2DEC(number), where number is the octal number you want to convert.

OCT2HEX

Converts an octal number to hexadecimal. 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. Takes the form =ODD(number). Negative numbers are correspondingly rounded down. See Using the rounding functions in Chapter 14.

ODDFPRICE

Returns the price per $100 of face value for a security having an odd first period. 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. 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. 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. 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. 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. 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 in Chapter 14.

PDURATION

Returns the number of periods an investment requires to reach a specific value. Takes the form =PDURATION(rate, pv, fv), where rate is the periodic rate of interest, pv is the investment’s present value, and fv is the investment’s desired future value.

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.INC PERCENTILE.EXC

Returns the member of an input range that is at a specified percentile ranking. Takes the form =PERCENTILE.INC(array, k), where array is the input range (inclusive or exclusive, depending on the form of the function used) and k is the rank you want to find. The old form of this function is PERCENTILE, which is still supported as a Compatibility Function. See Using functions that analyze rank and percentile in Chapter 17.

PERCENTRANK.INC PERCENTRANK.EXC

Returns a percentile ranking for any member of a data set. Takes the form =PERCENTRANK.INC(array, x, significance), where array specifies the input range (inclusive or exclusive, depending on the form of the function used), 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%). The old form of this function is PERCENTRANK, which is still supported as a Compatibility Function. See Using functions that analyze rank and percentile in Chapter 17.

PERMUT

Returns the number of permutations for a given number of objects that can be selected from a larger group of objects. Takes the form =PERMUT(number, number_chosen), where number is an integer that describes the total number of objects you want to use and number_chosen is an integer that describes the number of objects you want in each permutation.

PERMUTATIONA

Returns the number of permutations, including repetitions, for a given number of objects that can be selected from a larger group of objects. Takes the form =PERMUTATIONA(number, number_chosen), where number is an integer that describes the total number of objects you want to use and number_chosen is an integer that describes the number of objects you want in each permutation.

PHI

Returns the density function value for a standard normal distribution. Takes the form =PHI(x), where x is the value you want to calculate.

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). Takes the form =PI(), and accepts 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. 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 in Chapter 16.

POISSON.DIST

Returns the Poisson distribution. Takes the form =POISSON.DIST(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 cumulative is TRUE, POISSON.DIST returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if cumulative is FALSE, it returns the Poisson probability mass function that the number of events occurring will be exactly x.) The old form of this function is POISSON, which is still supported as a Compatibility Function.

POWER

Returns the result of a number raised to a power. 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. 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 in Chapter 16.

PRICE

Calculates the price per $100 of a security that pays periodic interest. 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 in Chapter 16.

PRICEDISC

Returns the price per $100 of a discounted security. 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 in Chapter 16.

PRICEMAT

Returns the price per $100 of a security that pays interest at maturity. 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 in Chapter 16.

PROB

Returns the probability that values in a range are between two limits. 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 accepts as many as 30 arguments. Text, logical values, and blank cells are ignored. See Using selected mathematical functions in Chapter 14.

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. Takes the form =PROPER(text). See Using selected text functions in Chapter 14.

PV

Computes the present value of a series of equal periodic payments or a lump-sum payment. 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 in Chapter 16.

QUARTILE.INC QUARTILE.EXC

Returns the value in an input range (either inclusive or exclusive, depending on the form of the function used) that represents a specified quarter-percentile. Takes the form =QUARTILE.INC(array, quart). The old form of this function is QUARTILE, which is still supported as a Compatibility Function. For usage and argument details, see The PERCENTILE and QUARTILE functions in Chapter 17.

QUOTIENT

Returns the integer portion of a division. Takes the form =QUOTIENT(numerator, denominator), where numerator is the dividend and denominator is the divisor.

RADIANS

Converts degrees to radians. Takes the form =RADIANS(angle), where angle represents an angle measured in degrees.

RAND

Generates a random number between 0 and 1. 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 in Chapter 14.

RANDBETWEEN

Generates random integer values between a specified range of numbers. Takes the form =RANDBETWEEN(bottom, top), where bottom is the smallest integer you want to use and top is the largest, inclusive. See Using selected mathematical functions in Chapter 14.

RANK.AVG

Returns the ranked position of a particular number within a set of numbers. Takes the form =RANK.AVG(number, ref, order). If more than one value has the same rank, the function returns the average rank. The old form of this function is RANK, which is still supported as a Compatibility Function. For usage and argument details, see The RANK functions in Chapter 17.

RANK.EQ

Returns the ranked position of a particular number within a set of numbers. Takes the form =RANK.EQ(number, ref, order). If more than one value has the same rank, the function returns the top rank. The old form of this function is RANK, which is still supported as a Compatibility Function. For usage and argument details, see The RANK functions in Chapter 17.

RATE

Calculates the rate of return of an investment that generates a series of equal periodic payments or a single lump-sum payment. 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 (10 percent if omitted). See Calculating investments in Chapter 16.

RECEIVED

Calculates the amount received at maturity for a fully invested security. 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 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 in Chapter 16.

REPLACE

Substitutes one string of characters with another string. 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 in Chapter 14.

REPLACEB

Substitutes one string of characters with another string. 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. 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. Takes the form =RIGHT(text, num_chars), where num_chars indicates how many characters you want to extract from the text string (1, if omitted). Blank spaces count as characters. See Using the substring text functions in Chapter 14.

RIGHTB

Returns the rightmost series of characters from a string, based on the number of bytes you specify. Takes the form =RIGHTB(text, num_bytes), where num_bytes indicates how many characters you want to extract from the text string, based on bytes. This function is for use with double-byte characters.

ROMAN

Converts an Arabic numeral to Roman numerals, as text. Takes the form =ROMAN(number, form), where number is the Arabic numeral you want to convert 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). Also, see the ARABIC function.

ROUND

Rounds numbers to a specified number of decimal places. 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; and num_digits can be any positive or negative integer and determines the number of decimal places. Use a negative num_digits to round to the left of the decimal; use zero to round to the nearest integer. See Using the rounding functions in Chapter 14.

ROUNDDOWN

Rounds numbers down to a specified number of decimal places. Takes the same form and arguments as ROUND. See Using the rounding functions in Chapter 14.

ROUNDUP

Rounds numbers up to a specified number of decimal places. Takes the same form and arguments as ROUND. See Using the rounding functions in Chapter 14.

ROW

Returns the row number of the referenced cell or range. 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 in Chapter 14.

ROWS

Returns the number of rows in a reference or an array. 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 in Chapter 14.

RRI

Returns an equivalent interest rate for an investment based on its growth. Takes the form =RRI(nper, pv, fv), where nper is the number of periods, pv is the present value, and fv is the future value of the investment.

RSQ

Returns the square of the Pearson product moment correlation coefficient through data points in the arrays known_y’s and known_x’s. Takes the form =RSQ(known_y’s, known_x’s).

RTD

Returns real-time data from a program that supports COM automation. 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 topic values are up to 28 parameters describing the real-time data you want.